Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?

share|improve this question
2  
EAV is almost universally an anti pattern that can and should be avoided if at all possible. I recommend Bill Karwin's SQL Anti as a book that discusses this exact issue. – Aaron Brown Apr 23 '12 at 3:09
EAV is sometimes useful, particularly when your data isn't very relational. For example, storing arguments for shell scripts to be called ;-) – Chris Travers Sep 3 '12 at 12:09

1 Answer

up vote 12 down vote accepted

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling.

In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an "index_merge" feature to substitute for that.

For PostgreSQL you may also be interested in the hstore extension for storing sets of key/value pairs.

share|improve this answer
2  
+1 for hstore. It is amazingly fast when using an index on the column. – a_horse_with_no_name Mar 14 '12 at 9:12
Thank you Erwin! – Alex Mar 15 '12 at 1:04
2  
You can add that MySQL has a hard limit (61) on how many tables can be joined in a query. – ypercube Apr 23 '12 at 7:15
2  
Also, I would add that PostgreSQL has some very useful functions for making EAV work in a sane manner (like array_agg, unnest, and the like). The sorts of areas we use EAV for in LedgerSMB (settings, next-in-series stuff, menu script arguments, etc) are all areas where the data is actually perfect for EAV and these functions come in very useful there. I expect to be using hstore more in the future in these cases. – Chris Travers Sep 3 '12 at 12:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.