How to store thousands of properties related to a record in PostgreSQL?

Question

I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static and nearly all operation will be reads.

Below are the options I'm thinking about to solve this problem.

1) A column for each property

This falls apart quickly as the max number of columns available in postgres is ~1600. So really, this isn't an option.

2) Create an hstore or JSONB column to store the key value columns.

This seems to work but I've read there are practical limits to how much data should be stored in hstore or JSONB. There are a lot of key/values and I think both hstore and JSONB will store these in TOAST which could have a negative impact on performance.

What potential issues/practical limits should I be aware of if using approach #2?

What are some other approaches to solving this problem?

• user1822: I think the only option you have is to actually create a sample database with a hstore column and see how this performs.
• András Váczi: ... or jsonb, if you have typed data (i.e., of non-text type). You can also try EAV, if you are brave.
• TehNrd: @dezso What makes EAV scary?

Answer 1

You could create a table to hold the key/value pairs like such:

CREATE TABLE KEY_VALUE (
 ID BIGINT, -- THIS COULD BE A FKEY TO YOUR '300K' RECORD TABLE
 KEY VARCHAR,
 VALUE VARCHAR
);

As long as this table is indexed properly (an index on id/key, I imagine), getting any key/value pairs you are interested in should still be very quick, even if it's millions of rows large. Granted - this solution as a viable option really depends on what scope of data you expect to store with your key/value pairs (is it all text? or numbers? etc..). Perhaps adding a 3rd column to say what type of data the value is would help.

EDIT

• https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Just to be clear, btw - I figured this might work given the sheer number of different key->value pairs you said you would have.. If it were a small number, I would probably just have a "details" type of table where each value was stored in a column.

• TehNrd: Is this essentially an EAV model?
• Joishi Bodio: New acronym for me ... looks it up Yes, that is the gist of what I was suggesting. I have no idea about what Dezso may have been commenting about (with being brave) .. I have never used the above model.
• András Váczi: Aaron's post summarizes the possible pitfalls very well. With careful design and planning, this may be a viable solution. It usually has relatively high development costs, as far as I can tell, so using some ready-to-use structures like hstore or jsonb can mean faster delivery. YMMV, as nearly always.