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?
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.