0

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?

Improve this question
4
  • 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.
    – a_horse_with_no_name
    Feb 6 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.
    – Daniel Díaz Carrete
    Feb 6 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.
    – Charlieface
    Feb 6 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.
    – Laurenz Albe
    Feb 6 at 13:59

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.