Key/Value Postgres Sql Table Performance

Question

I am currently building a Rails app where there is a "documents" data table that stores references to pdfs living on an S3 server. These documents could have 100 different types. Each type can have up to 20 attributes or meta info.

My dilemma is do I make 100 relational tables for every doc type or just create one key/value data table with a reference to the doc_id.

My gut tells me to go key/value for flexibility for searching and supporting more and more document types over time without having to create new migrations. However, I know there are pitfalls with this technique. My first concern of course is the size of the table. The key/value table could end up with millions of rows.

On the other hand, having 100 attribute tables would be nightmare to query against in a full text search situation.

So bottom line is, by going with key/value, is performance on a 3 column Postgres table with potentially millions of rows a scaling problem? Also what about joins on the value field?

This data would almost never change by the way. So it would be 90% reads.

Jack Christensen · Accepted Answer · 2012-07-11 02:51:37Z

1

Consider a single table with an hstore column. It is a PostgreSQL data type designed for storing key/value pairs.

http://www.postgresql.org/docs/9.1/static/hstore.html

There are also multiple Ruby gems that add hstore support to ActiveRecord. Here is one that I wrote: https://github.com/JackC/surus You can search ruby gems for about a dozen more alternatives as well.

answered Jul 11, 2012 at 2:51

Jack Christensen

1124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Jaffe Over a year ago

Thanks Jack, this is EXACTLY the functionally I need.

Collectives™ on Stack Overflow

Key/Value Postgres Sql Table Performance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related