Modeling variable product data: alternatives to jsonb

PostgreSQL's jsonb type has become the default answer for storing flexible, product-like data, but it is not the only valid approach. An alternative is an entity-attribute-value (EAV) model, which stores all attributes in a dedicated table and uses one or more associative tables to connect those attributes to entities. Both designs can solve the same problem, but the EAV version may be a better fit in some scenarios.

In the EAV model, all attributes for a table such as wares live in a single "Attributes" table, with data type variations handled by separate columns. This avoids the proliferation of separate tables such as attstring and attint that you often see in attribute-based examples. The layout requires more null checking in practice, but has not been a problem at scale. A common alternative is to store every value as text and perform conversion at query time.

Another distinction is how the relationship is managed. In many attribute-table designs, the object ID is repeated in each row of every attribute table. The EAV approach introduces one associative table (for example, "WaresAttributes") that maps wares to Attributes. The Attributes table itself never needs to know anything about individual wares entries, which decouples the two concerns.

From a SQL perspective, the number of statements required in an attribute-table arrangement grows with the number of attribute tables and the need to inject object IDs into each insert. The EAV structure generally touches at most three tables, and often just one. At a large company, this is the model in production, with thousands of products and attributes each, and PostgreSQL reports no performance issues.

Performance versus jsonb: an open question

It is not yet clear whether jsonb actually outperforms a well-indexed EAV model when measured end-to-end. A benchmark that compares query speed, write throughput, and storage size would help settle whether the convenience of jsonb translates into a measurable advantage. Until such data exists, the choice between the two remains a matter of trade-offs: jsonb simplifies the schema and query path, while an EAV design with a single attributes table is simpler to reason about in terms of data integrity and is already proven in production loads.