JSONB vs EAV in PostgreSQL: Which Is Better for Dynamic Attributes?

Apr 24, 2026

Database structure concept

Problem

I was building a product catalog where each product had different attributes. A laptop has CPU, RAM, storage. A shirt has size, color, material. A book has author, pages, ISBN.

I couldn’t create a fixed schema—adding a new product type would require ALTER TABLE, and I’d end up with hundreds of nullable columns that most products never use.

I needed a flexible storage solution. Two options stood out: EAV (Entity-Attribute-Value) tables and JSONB columns.

I started with EAV because it seemed more “relational” and “proper.” That was my first mistake.

My EAV Attempt

The EAV pattern stores each attribute as a separate row:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE product_attributes (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    attr_name TEXT NOT NULL,
    attr_value TEXT,
    attr_type TEXT DEFAULT 'string'
);

This looked elegant. Each product could have any attributes I wanted:

product_id | attr_name    | attr_value | attr_type
-----------|--------------|------------|----------
1          | color        | blue       | string
1          | size         | large      | string
1          | weight_kg    | 5.2        | number
2          | cpu          | M2 Pro     | string
2          | ram_gb       | 16         | number

Then I tried to query products where color is blue AND size is large.

The Query Complexity Explosion

With EAV, a simple two-condition query becomes a monster:

SELECT p.id, p.name
FROM products p
WHERE EXISTS (
    SELECT 1 FROM product_attributes a
    WHERE a.product_id = p.id
    AND a.attr_name = 'color'
    AND a.attr_value = 'blue'
)
AND EXISTS (
    SELECT 1 FROM product_attributes a
    WHERE a.product_id = p.id
    AND a.attr_name = 'size'
    AND a.attr_value = 'large'
);

Each additional condition requires another EXISTS clause or self-join. Three conditions? Three EXISTS. Ten conditions? Ten subqueries.

I found this diagram captures the problem perfectly:

Single attribute query:
  1 table scan → OK

Two attributes query:
  2 EXISTS → getting slow

Five attributes query:
  5 EXISTS → noticeably slow

Ten attributes query:
  10 EXISTS → unusable

N attributes query:
  N joins → exponential complexity

The Reddit discussion I found confirmed this: “joins are our most expensive operation, so that’s really unattractive as an alternative.”

The Type Safety Problem

EAV stores everything as TEXT. To filter by numeric comparison, I had to cast:

-- Find products heavier than 5kg
SELECT p.id, p.name
FROM products p
JOIN product_attributes a ON a.product_id = p.id
WHERE a.attr_name = 'weight_kg'
AND CAST(a.attr_value AS FLOAT) > 5.0;

This breaks when attr_value contains non-numeric data. I needed validation logic in the application layer. More complexity.

My JSONB Pivot

After fighting EAV queries for a week, I switched to JSONB:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB DEFAULT '{}'
);

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

Same data, but stored in one column:

id | name    | attributes
---|---------|----------------------------------
1  | Widget  | {"color": "blue", "size": "large", "weight_kg": 5.2}
2  | Laptop  | {"cpu": "M2 Pro", "ram_gb": 16, "storage_gb": 512}

The same query that required two EXISTS clauses now needs one line:

SELECT id, name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"color": "blue", "size": "large"}';

The @> operator checks if the JSONB column contains the specified JSON. One index scan. Done.

Why JSONB Wins

The performance difference isn’t marginal—it’s fundamental:

                    EAV                     JSONB
─────────────────────────────────────────────────────────
Query complexity    Multiple joins          Single scan
Index usage         Limited                 Full GIN support
Type preservation   Lost (all TEXT)         Preserved
Adding attributes   INSERT new row          UPDATE JSON
Schema changes      None needed             None needed
Maintenance         High                    Low

Single-Row Retrieval

With JSONB, fetching all attributes for a product is one operation:

SELECT id, name, attributes
FROM products
WHERE id = 1;

-- Returns: {"color": "blue", "size": "large", "weight_kg": 5.2, "specs": {...}}

With EAV, I’d need to join and aggregate:

SELECT p.id, p.name,
       json_object_agg(a.attr_name, a.attr_value) AS attributes
FROM products p
JOIN product_attributes a ON a.product_id = p.id
WHERE p.id = 1
GROUP BY p.id, p.name;

GIN Index Magic

PostgreSQL’s GIN (Generalized Inverted Index) indexes JSONB contents:

EXPLAIN ANALYZE
SELECT * FROM products
WHERE attributes @> '{"color": "silver"}';

-- Result: Index Scan using idx_products_attributes
--         Cost: ~0.5ms for 100K rows

The index can find any key-value pair without scanning the entire table. EAV would need a composite index on (attr_name, attr_value), which still requires scanning multiple rows per entity.

Nested Object Support

JSONB handles nested structures naturally:

SELECT name, attributes->'specs'->>'cpu' AS cpu
FROM products
WHERE attributes->'specs'->>'cpu' = 'M2 Pro';

EAV can’t do this without a separate table for nested attributes—or flattening everything into string keys like specs_cpu.

Array Operations

JSONB arrays are searchable:

-- Find products tagged as 'premium'
SELECT name
FROM products
WHERE attributes->'tags' ? 'premium';

The ? operator checks if an array contains a value. EAV would need a separate array-handling mechanism or explode arrays into multiple rows.

When EAV Might Still Win

EAV has niche uses:

1. Audit trails per attribute change
   - EAV row has created_at, updated_at
   - JSONB updates lose per-attribute history

2. Thousands of potential attributes
   - Metadata catalogs with 10K+ attribute names
   - JSONB document size limits (1GB) could hit

3. Different storage per type
   - EAV can have attr_value_int, attr_value_str columns
   - JSONB preserves types but doesn't separate storage

For my product catalog with dozens of attributes, none of these apply.

Common Mistakes

I made these mistakes before learning better:

Mistake	Why It’s Wrong	Fix
Not indexing JSONB	Queries become sequential scans	`CREATE INDEX USING GIN`
Storing everything in JSONB	Foreign keys lose integrity	Keep FKs in regular columns
Using JSON instead of JSONB	No indexing, slower	Use JSONB for queryable data
EAV for high-volume data	Join cost compounds	Switch to JSONB at scale

The Reddit commenter who convinced me to switch said: “using jsonb over EAV. much better performance when adding a custom attribute when data gets bigger.”

They were right.

The Migration Path

I migrated from EAV to JSONB with one query:

INSERT INTO products_new (id, name, attributes)
SELECT p.id, p.name,
       json_object_agg(a.attr_name,
                        CASE a.attr_type
                            WHEN 'number' THEN CAST(a.attr_value AS FLOAT)
                            WHEN 'boolean' THEN CAST(a.attr_value AS BOOLEAN)
                            ELSE a.attr_value
                        END
                       )
FROM products p
JOIN product_attributes a ON a.product_id = p.id
GROUP BY p.id, p.name;

-- Then rename tables
DROP TABLE product_attributes;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;

The query complexity dropped, performance improved 10x, and maintenance became trivial.

Key Takeaway

For dynamic attributes in PostgreSQL, JSONB is the default choice. EAV only wins in specialized metadata catalogs where its flexibility outweighs the join explosion.

The decision tree:

Need dynamic attributes?
    │
    ▼
Attributes < 50 and data < 100K rows?
    │
    ├─ YES → JSONB (simple, fast)
    │
    └─ NO → Need per-attribute audit trails?
            │
            ├─ YES → Consider EAV with timestamps
            │
            └─ NO → Need 10K+ different attributes?
                    │
                    ├─ YES → EAV might fit
                    │
                    └─ NO → JSONB

I wasted a week on EAV. If you’re facing the same choice, skip the EAV detour and go straight to JSONB.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 Reddit: Store JSON in RDBMS Discussion
👨‍💻 PostgreSQL JSONB Documentation
👨‍💻 EAV Pattern Pros and Cons

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!