What is the most efficient method for matching arbitrary key / value pairs with a table row in Postgres DB?

Question

I have a completely different relational data schema for my Postgres 8.4 database, but I need the ability to associate arbitrary key / value pairs with several of my tables, with the assigned keys changing in rows. Keys / value pairs are generated by the user, so I can’t predict them ahead of time or change the ordered schema changes.

I have the following requirements:

• A key / value pair will often be read, sometimes written. Reading should be fast enough.
• No (present), you need to request keys or values. (But it may come in handy someday.)

I see the following possible solutions:

• Entity-Attribute-Value pattern / antipattern . Annoying, but annoyance will be generally compensated by my ORM.
• Save key / value pairs as serialized JSON data in a text column. A simple solution, and again ORM will come in handy, but I can kiss my future need for myself to say goodbye.
• Storing keys / values ​​in some other NoSQL files - perhaps this is a key / value or document store. ORM does not help here. I will have to independently manage individual requests (and emerging data integrity problems?).

I am worried about query performance, as I hope they will have a lot of them. I also worry about programmer performance, as I have to build, maintain and use the damn thing. Is there an obvious better approach here? Or something that I missed?

Answer 1

This is exactly the hstore type for PostgreSQL.
http://www.postgresql.org/docs/current/static/hstore.html

It is very fast (you can index it) and pretty easy to handle. The only drawback is that you can only store character data, but you will also have a problem with other solutions.

The index supports the "exists" operator, so you can query fast enough for rows where a certain key is present, or for rows where a particular attribute has a specific value.

And with 9.0, it got even better because some size restrictions were removed.

Answer 2

hstore is generally a good solution for this, but personally, I prefer to use public key tables: value. One table with definitions, another table with values ​​and relation to the values ​​of the binding to the definition and relation to the values ​​of the binding to a specific record in another table.

Why am I against hstore? Because it looks like a registry template. Often referred to as an example of an anti-pattern. You can put something there, it is difficult to easily check if it is still necessary, when loading an entire line (especially in ORM), the whole hstore is loaded, which can make a lot of unwanted and very little sense. Not to mention the need to convert the hstore data type to your language type and convert back when saving. This way you get some type conversion overhead.

So I'm actually trying to convert all hstores into the company I work for for simple value tables :. This is not so difficult because the structures stored here in hstore are huge (or at least large), and reading / writing an object breaks up huge overhead function calls. So a simple task like "select * from base_product, where id = 1;" makes a server sweat and severely impacts performance. You want to point out that the performance problem is not because db, but because python has to convert the results received from postgres several times. Although the key: value does not require such a conversion.

Answer 3

As you do not control the data, do not try to complicate it.

create table sometable_attributes ( sometable_id int not null references sometable(sometable_id), attribute_key varchar(50) not null check (length(attribute_key>0)), attribute_value varchar(5000) not null, primary_key(sometable_id, attribute_key) ); 

This is similar to EAV, but without the attribute_keys table, which has no added value unless you control what will be there.

For speed, you should periodically execute "cluster sometable_attributes using sometable_attributes_idx", so all attributes for one row will be physically close.