6

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?

2
  • 3
    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. Commented Apr 23, 2012 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 ;-) Commented Sep 3, 2012 at 12:09

1 Answer 1

15

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.

3
  • 3
    +1 for hstore. It is amazingly fast when using an index on the column.
    – user1822
    Commented Mar 14, 2012 at 9:12
  • 2
    You can add that MySQL has a hard limit (61) on how many tables can be joined in a query. Commented Apr 23, 2012 at 7:15
  • 3
    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. Commented Sep 3, 2012 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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