When does storing a document-shaped JSON value in a column beat storing the same data as attribute-value rows, and what do you give up by putting data inside JSON?
answer
- One row per entity, so no pivot
- Inverted index = any key; path index = named path
- No FK from inside a document
- Path predicates get guessed selectivity
- Whole-value rewrite on update; keys repeated per row
basics
~20 sA JSON column keeps one row per entity, so no pivoting, and it preserves nesting, arrays and basic scalar types; you can index whole documents or specific extracted paths. You give up foreign keys into the document, declarative per-field constraints, good selectivity estimates, and cheap partial updates.
solid answer
~60 sAgainst attribute-value rows, JSON wins on shape and read cost: the entity stays one row, so a read is one row fetch instead of a pivot; nesting and arrays are representable; scalars keep a rough type; and the engine can index the document (an inverted index supporting containment and key-existence) or a specific path via an expression or generated-column index. What you lose relative to real columns is significant. Nothing inside a document can be a foreign key. Per-field rules exist only as CHECK constraints over extracted expressions. Statistics for path predicates are weak, so the planner falls back to fixed guesses and misestimates joins. Updating one field usually rewrites the whole value, and large documents are stored out of line, so hot partial updates are expensive. Key names repeat in every row. Types drift, because nothing stops the string "5" appearing where the number 5 was expected. The production pattern: real columns for anything filtered, sorted, joined or reported on; JSON for the sparse tail; validate on write; index only the paths you query.
code
sql · 11 linesCREATE TABLE item (
id bigint PRIMARY KEY,
sku text NOT NULL UNIQUE,
price numeric(12,2) NOT NULL CHECK (price >= 0),
attrs jsonb NOT NULL DEFAULT '{}'::jsonb,
color text GENERATED ALWAYS AS (attrs->>'color') STORED,
CONSTRAINT attrs_is_object CHECK (jsonb_typeof(attrs) = 'object')
);
CREATE INDEX item_attrs_gin ON item USING gin (attrs);
CREATE INDEX item_color_idx ON item (color);go deeper
Say that JSON keeps the entity in one row so reads do not pivot, and that you lose foreign keys and column-level constraints on what is inside.
Contrast inverted whole-document indexes with path/generated-column indexes, and name the update-rewrite and repeated-key-name costs.
Argue the hybrid split explicitly, add the optimizer-statistics problem for path predicates, and describe the promotion path from JSON path to generated column to real column.
Own the enforcement boundary: what is guaranteed by the database, what by a single write gate, and how document-shaped storage affects downstream consumers, reporting and long-term migrations.
## What a JSON column changes Both attribute-value rows and a JSON column exist to store data whose fields are not known at design time. The difference is granularity. Attribute-value rows shred an entity into one row per field; a JSON column keeps the entity as a single row with one composite value. That single difference removes the largest practical cost of the attribute-value pattern: reads no longer pivot. Fetching an entity with 40 fields is one row fetch, not 40 rows collapsed by grouping. Writes are one row insert. Ordering, pagination and joins on the entity behave exactly like an ordinary table because the entity is an ordinary row. JSON also represents things attribute-value rows model badly: nested objects, arrays, and heterogeneous structures. And its scalars carry a coarse type - string, number, boolean, null - unlike a generic text value column. ## Indexing Two mechanisms matter. **Whole-document (inverted) indexes** map keys and values to the rows containing them and support containment questions - does this document contain this key, or this key/value pair - without you naming the path in advance. That preserves the flexibility motive: users can filter on any field. The index is larger and slower to maintain than a B-tree, and it does not help ordering or range scans. **Path indexes** are ordinary B-trees over an extracted expression, or over a generated/virtual column that materialises the path. These behave exactly like an index on a real column: range scans, ordering, uniqueness. They require you to know the path up front, which is the trade. A good rule is that inverted indexing serves ad-hoc exploration while path indexes serve the queries in your hot path, and any path that earns a B-tree probably deserves a real column. ## What you give up **Referential integrity.** A `supplier_id` inside a document cannot be declared a foreign key. Deleting a supplier will not be blocked and will not cascade; the dangling reference is found later, by a customer. **Declarative field rules.** Type, required-ness, enumerations and ranges can only be expressed as CHECK constraints over extracted expressions, which is workable for a handful of fields and unmaintainable for hundreds. Most teams validate against a schema in the application instead - which means it is enforced by whoever remembers to call the validator. **Estimation quality.** The optimizer has statistics for the column as a whole, not for `document->>'status'`. Predicates on paths get default selectivity guesses, so join order and join method choices degrade once a JSON predicate participates in a multi-table query. Materialising the path as a generated column with its own statistics is the standard fix. **Update cost.** In most engines updating any part of the value rewrites the whole value, and MVCC engines write a new row version anyway. Large documents are compressed and stored out of line, so a hot field updated frequently inside a big document costs far more than a narrow column update, and it churns every index on that row. **Storage overhead.** Every row repeats every key name. A table of ten million rows with a document containing twenty long field names is storing those names ten million times. Column names, by contrast, are stored once in the catalog. **Type drift and comparison semantics.** Nothing prevents `"5"` in one row and `5` in another, or a field that is a scalar in old rows and an array in new ones. Sorting mixes types by the JSON type ordering rather than by the semantics you intended, and equality between a JSON string and a SQL string may need explicit extraction and casting. **Discoverability.** No catalog query tells a new engineer what fields exist. The schema lives in code, documentation or nowhere. ## When JSON is the right call - A sparse long tail of fields that are read as a whole and rarely filtered individually. - Payloads that arrive already document-shaped and are stored for fidelity: webhook bodies, third-party API responses, audit snapshots, event payloads. - Configuration or presentation blobs whose structure is owned by a feature, not by queries. - Prototyping a field set before you know it well enough to commit to columns. ## When it is the wrong call - Anything filtered, ranged, sorted, grouped or joined at scale - that is a column. - Anything that must reference another table. - Anything with real domain rules that multiple services write. - A hot field inside a large document that is updated far more often than the rest. ## The pattern that survives review Hybrid. Keep identity, foreign keys and every queried attribute as real typed columns with constraints and indexes. Put the sparse remainder in one JSON column. Validate documents on write in a single gate rather than in each caller. Index only the paths you actually query, and when a path becomes hot, promote it: add a generated column over the path, index that, migrate readers, and eventually make it a plain column. Compared with attribute-value rows this gets you the same runtime flexibility with one-row reads and far fewer surprises - but it is still weaker than columns, and the answer that shows judgement says which half of the data goes where.
- A single field inside a large JSON document is updated on nearly every request. Why is that expensive, and what would you change?Most engines rewrite the entire value when any part of it changes, and a large document is compressed and stored out of line, so each update decompresses, rewrites and re-stores the whole payload plus a new row version and index churn. Move that field into its own narrow column - ideally in its own table if the rest of the row is also large and cold - so the hot write touches a few bytes instead of the whole document.
- How do you enforce that a JSON field is always present and numeric?Declaratively you can add a CHECK such as requiring the extracted path to be non-null and of JSON type number, or you can materialise the path as a generated column and put NOT NULL plus a type on that column, which also gives the optimizer statistics. Beyond a handful of fields this becomes unwieldy, so teams validate the document against a schema in one write gate - accepting that it is application-enforced and bypassable by direct SQL.
saying these in an interview costs you the question
- Claiming a foreign key can point out of or into a JSON document
- Assuming an inverted document index speeds up range scans and ORDER BY
- Believing a partial update to one JSON key rewrites only that key
- Putting the primary filter and sort columns inside the document because 'it is all indexed anyway'
- Treating JSON as schemaless and therefore free of validation obligations