Sample Header Ad - 728x90

Choosing between Entity-Attribute-Value or JSONB representations

1 vote
0 answers
2443 views
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](https://www.postgresql.org/docs/current/datatype-json.html) be better for my scenario?
Asked by Daniel Díaz Carrete (113 rep)
Feb 5, 2023, 10:04 PM