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?

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.