I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table for each document type.

In practice however, the number of different document types is way too high for thinking of using the table-for-each-type approach. It seems to me that alternative approaches consist either in some kind of EAV representation, or in having a single table with columns like (document_id, document_type, jsonb_content).

The documents will be immutable—I won't edit fields inside a document.

I'll often need to run GROUP BY aggregations and general analytic operations over documents of a given type.

Would EAV or JSONB be better for my scenario?

  • 2
    If you are aggregating over the documents they apparently have some things in common. I would extract those common attributes into real columns in your table to make aggregation easier and more efficient.
    – user1822
    Feb 6, 2023 at 6:54
  • @a_horse_with_no_name The aggregations would always be within documents of a single type. Documents of different types wouldn't necessarily have fields in common. Having separate tables for each type would be best, but the number of different document types is too hight for that. Feb 6, 2023 at 7:30
  • For aggregation it sounds like EAV might be better. You need to make sure it's efficiently indexed. For example, you would want an index UNIQUE (EntityId, AttributeId) INCLUDE (Value) as well as UNIQUE (AttributeId, EntityId) INCLUDE (Value). You also want a separate table per datatype of Value, so you aren't mixing different datatypes in the same column. Feb 6, 2023 at 13:20
  • 1
    Anything is better than EAV. So the choice is simple. For a detailed answer, we'd have to know more about the queries you want to run. Typically, the best approach is to have common attributes as regular columns and the oddballs as JSON, just like you suggest. Feb 6, 2023 at 13:59


Your Answer

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

Browse other questions tagged or ask your own question.