Sample Header Ad - 728x90

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

1 vote
1 answer
1038 views
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?
Asked by TehNrd (111 rep)
Jan 22, 2015, 04:06 PM
Last activity: Jan 23, 2018, 08:31 PM