JSONB vs normalized columns vs EAV — how do you model flexible attributes without killing query performance?

Q: JSONB vs normalized columns vs EAV — how do you model flexible attributes without killing query performance?

**Target Audience:** Developers designing a catalogue, settings or metadata store where attributes vary per row, and candidates facing a schema design question that tests trade-offs rather than syntax. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 for everything `jsonb`, run in a scratch database for this page. The EAV examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine) and the type trap was also run on PostgreSQL 16 with the same answer. MySQL facts are cited. > **How to read this doc:** Each idea is first explained in plain language, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every number, plan estimate and error message shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First Imagine a shop that keeps its product details on index cards. For laptops, the card has printed boxes: RAM, screen size, colour. Fast to read, imposs

The problem

Question presented to candidate: "We sell laptops, shirts and headphones, and every category has different attributes that product managers keep adding. How would you model those flexible attributes in PostgreSQL? Compare typed columns, an EAV table and JSONB, and tell me how each one performs when we filter on them."

What a strong answer should cover:

The three models: typed columns (per category table or nullable columns), entity-attribute-value rows, and a jsonb document per row, plus the hybrid: real columns for core, hot and constrained fields, jsonb for the long tail
EAV costs: one row per attribute (3x the rows and about 3.5x the storage in the demo), a pivot or self-join per filter, values stored as text so comparisons and constraints break
JSONB costs: no per-key statistics (the planner guessed 100 rows where 4000 matched), no foreign keys or types per key unless you add CHECK constraints, and whole-document rewrites on update
Fixes that keep JSONB fast: expression indexes (which also give the planner statistics), GIN for containment, generated columns to promote a hot key, CHECK constraints for required types
A decision rule: promote any attribute you filter, sort, join or constrain on to a real column; keep display-only and rarely queried attributes in JSONB; avoid EAV except for truly user-defined fields

Clarifying questions expected:

Which attributes are filtered or sorted on, and how often, versus only displayed?
Who defines new attributes: engineers in a migration, or end users at runtime?
Do attributes need types, required-ness or references to other tables?

Code / implementation expected: Yes — the same filter on EAV and JSONB, and a hybrid PostgreSQL table with a generated column, a CHECK constraint and evidence from the planner.

Schema used by the examples:

jsonb
data-modeling
schema-design

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers designing a catalogue, settings or metadata store where attributes vary per row, and candidates facing a schema design question that tests trade-offs rather than syntax. Difficulty: Hard Engines: PostgreSQL 16.15 for everything jsonb, run in a scratch database for this page. The EAV examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine) and the type trap was also run on PostgreSQL 16 with the same answer. MySQL facts are cited.

How to read this doc: Each idea is first explained in plain language, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every number, plan estimate and error message shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

Imagine a shop that keeps its product details on

The solution is hidden so you can try first

15 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLEAV: pivot attribute rows back into columns to filter on two attributes

SQLThe EAV type trap: values are TEXT, so 8 compares as greater than 16

PostgreSQL hybrid: core columns, a jsonb tail, a generated column for a hot key and a CHECK on a required key

CREATE TABLE catalog (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC NOT NULL,
  attrs JSONB NOT NULL DEFAULT '{}',
  ram_gb INTEGER GENERATED ALWAYS AS ((attrs ->> 'ram_gb')::INTEGER) STORED,
  CHECK (category <> 'laptop' OR jsonb_typeof(attrs -> 'ram_gb') = 'number')
);
INSERT INTO catalog (id, name, category, price, attrs)
SELECT p.id, p.name, p.category, p.price, d.attrs::JSONB
FROM products p JOIN product_docs d ON d.product_id = p.id;

SELECT name, category, ram_gb, attrs ->> 'color' AS color
FROM catalog
WHERE ram_gb >= 16 OR attrs @> '{"wireless": true}'
ORDER BY id;

PostgreSQL: the planner has no statistics for a jsonb key until an expression index exists

CREATE TABLE items (
  id INTEGER PRIMARY KEY,
  color TEXT NOT NULL,
  attrs JSONB NOT NULL
);
INSERT INTO items
SELECT g,
       (ARRAY['silver','grey','black','blue','white'])[g % 5 + 1],
       jsonb_build_object('color', (ARRAY['silver','grey','black','blue','white'])[g % 5 + 1],
                          'ram_gb', (ARRAY[8, 16, 32])[g % 3 + 1])
FROM generate_series(1, 20000) AS g;
ANALYZE items;

CREATE FUNCTION estimate(q TEXT) RETURNS INTEGER AS $$
DECLARE plan JSON;
BEGIN
  EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO plan;
  RETURN (plan -> 0 -> 'Plan' ->> 'Plan Rows')::INTEGER;
END $$ LANGUAGE plpgsql;

CREATE TEMP TABLE result (step INTEGER, predicate TEXT, estimated INTEGER, actual INTEGER);
INSERT INTO result VALUES
  (1, 'color = silver (real column)',
      estimate('SELECT * FROM items WHERE color = ''silver'''),
      (SELECT COUNT(*) FROM items WHERE color = 'silver')),
  (2, 'attrs ->> color = silver',
      estimate('SELECT * FROM items WHERE attrs ->> ''color'' = ''silver'''),
      (SELECT COUNT(*) FROM items WHERE attrs ->> 'color' = 'silver'));

-- An expression index makes ANALYZE collect statistics on that expression
CREATE INDEX items_color_expr ON items ((attrs ->> 'color'));
ANALYZE items;
INSERT INTO result VALUES
  (3, 'attrs ->> color = silver, after expression index',
      estimate('SELECT * FROM items WHERE attrs ->> ''color'' = ''silver'''),
      (SELECT COUNT(*) FROM items WHERE attrs ->> 'color' = 'silver'));

SELECT predicate, estimated, actual FROM result ORDER BY step;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.