Emergent schema (JSON columns)

Keep the fields you know as columns and the rest in a JSON column, then promote keys to real columns once the data shows which ones matter.

How it works

Some fields are certain from day one: an ID, an owner, a type, a timestamp. Those become ordinary typed columns. Everything that varies, or that you can’t predict yet, goes into one JSON column, such as props jsonb in PostgreSQL. New keys need no migration, and the real shape of the data emerges from what gets written. You query keys with operators like props->>'plan' or containment, props @> '{"plan":"pro"}', and index them two ways: one GIN index over the whole column answers containment on any key, while an expression index on (props->>'country') serves one key and is smaller. The weak spots are the ones columns used to cover. JSON has no date type, nothing stops a typo’d key, and the planner keeps no statistics on keys inside the column, so it guesses (0.5% selectivity for an equality test, in PostgreSQL’s defaults) and can pick a terrible join. When a key turns out to be hot (filtered, joined or aggregated all the time), promote it: a generated column that extracts and casts it, or a real column you backfill, gets a type, constraints, a plain index and statistics. JSON also repeats every key name in every row, so a key present in most rows usually costs less as a column.

Think of it as

A paper form with a few printed boxes and a big “notes” area. Clerks write whatever they need in the notes. Once you notice half of them write “allergies” there, you print an allergies box on the next batch of forms.

Reach for it when

A relational app where some records carry varying or user-supplied fields: event properties, integration payloads, product specs
Early in a product, when you don’t yet know which fields will matter
Storing an API payload as received, next to the few fields you query

Think twice when

Fields that are present in most rows and used in filters, joins or reports; make them columns
Data that needs foreign keys, exact decimal types or strict validation on every field
Analytics over billions of rows on many keys; a columnar store or extracted columns will be much faster

A real example

A product-analytics service stores 2 billion events in PostgreSQL with account_id, type and at as columns and customer-defined properties in props jsonb. A GIN index with jsonb_path_ops answers “events where plan is pro” on any key. A monthly check of jsonb_object_keys shows amount in 70% of purchase events and in every revenue report, so it becomes a stored generated numeric column with its own index, and the planner stops underestimating revenue queries. Heap, which runs this kind of system, reported saving about 30% of disk by pulling 45 commonly used fields out of JSONB into columns.

JSON columns in the common SQL databases#

Database	Storage	Indexing	Validation
PostgreSQL	`json` keeps the exact text and reparses it; `jsonb` (since 9.4) is a decomposed binary form that is faster to query but drops key order, whitespace and duplicate keys	GIN on the whole column (`jsonb_ops`, or the smaller `jsonb_path_ops` for `@>` and JSON path); B-tree expression indexes on single keys; generated columns (stored since 12, virtual by default since 18)	`CHECK` constraints on expressions; JSON Schema through extensions such as Supabase’s `pg_jsonschema`
MySQL	Native `JSON` type since 5.7.8 (2015), validated on write and stored in a binary format that can look up a key without reading the whole document	JSON columns aren’t indexed directly: index a generated column that extracts a scalar, or use a multi-valued index on a JSON array (InnoDB)	`JSON_SCHEMA_VALID()` (JSON Schema draft 4) inside a `CHECK` constraint
SQLite	No JSON column type: JSON is text, or since 3.45.0 (2024) a binary JSONB blob; JSON functions and the `->` and `->>` operators are built in since 3.38.0 (2022)	Expression indexes, or generated columns (since 3.31.0), which can be indexed like ordinary columns	`CHECK (json_valid(...))` and other expressions

Adding a stored generated column in PostgreSQL rewrites the whole table and its indexes, and fails if any row’s value can’t be cast; a virtual generated column (PostgreSQL 18) needs no rewrite.

When the database finds the schema itself#

Some engines do the promotion automatically, extracting frequent keys into hidden columns.

Snowflake VARIANT

commercial

When semi-structured data is loaded into a VARIANT column, Snowflake extracts as much of it as it can into columnar form, by default up to 200 elements per partition, and keeps the rest as one parsed column. Elements with even one JSON null aren’t extracted.

Umbra’s JSON tiles

research, 2021

Durner, Leis and Neumann’s JSON tiles detect the most important keys while loading, store them in columns, and keep statistics on frequent keys so the optimizer can plan joins. It won a SIGMOD 2021 best paper award.

DuckDB `read_json`

open source

DuckDB samples 20,480 objects by default to infer column names and types, turning JSON files into typed columns. Objects with many rarely-present fields become a MAP instead of hundreds of columns.

In code · sql

-- PostgreSQL: known fields as columns, the rest in jsonb
CREATE TABLE event (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint      NOT NULL,
  type       text        NOT NULL,
  at         timestamptz NOT NULL DEFAULT now(),
  props      jsonb       NOT NULL DEFAULT '{}',
  CHECK (jsonb_typeof(props) = 'object')
);

-- One GIN index answers containment on any key
CREATE INDEX event_props ON event USING gin (props jsonb_path_ops);
SELECT count(*) FROM event WHERE props @> '{"plan": "pro"}';

-- Let the data say which keys matter
SELECT k, count(*) FROM event, jsonb_object_keys(props) AS k
GROUP BY k ORDER BY count(*) DESC LIMIT 20;

-- A key you filter on: an expression index (ANALYZE then keeps its statistics)
CREATE INDEX event_country ON event ((props->>'country'));
-- or statistics alone, without an index (PostgreSQL 14+)
CREATE STATISTICS event_device ON (props->>'device') FROM event;

-- A hot key: promote it to a typed column (STORED rewrites the table)
ALTER TABLE event ADD COLUMN amount numeric
  GENERATED ALWAYS AS ((props->>'amount')::numeric) STORED;
CREATE INDEX event_amount ON event (amount);

Often confused with

Entity-attribute-value (EAV)Document databases Wide tables Hybrid (medium) tables Relational databases EAV, JSON column, wide table or document?

Where it came from2012–2025

Relational databases ask for the schema first; web apps increasingly had data whose shape changed weekly. Teams tried EAV tables, then XML columns, then plain text holding JSON. PostgreSQL added a json type in 9.2 (2012), which only checked and stored text, and jsonb in 9.4 (2014), a binary form that could be indexed with GIN. MySQL followed with a native JSON type in 5.7.8 (2015), and SQLite made its JSON functions standard in 3.38 (2022) and added binary JSONB in 3.45 (2024).

The first wave stored everything in JSON and learned the cost. In 2016 Dan Robinson of Heap described a query that ran about 2,000 times slower on JSONB than on columns because PostgreSQL keeps no statistics on keys inside JSONB, and a table that took twice the disk because every row repeats its key names. The advice that emerged was the hybrid: columns for common fields, JSON for the long tail. Databases added the pieces to promote keys: generated columns in PostgreSQL 12 (2019), statistics on expressions in 14 (2021), virtual generated columns in 18 (2025).

The name comes from database research. In 2015 Minh-Duc Pham, Linnea Passing, Orri Erling and Peter Boncz showed how to detect an “emergent” relational schema in RDF data, tables of properties that often occur together, and found it explained well over 90% of the triples in a wide range of datasets. The same idea for JSON appears in Umbra’s JSON tiles (2021), which extract frequent keys into columns automatically, and in Snowflake’s subcolumnarization of VARIANT data.

Why it’s built this way

Columns for the stable core, JSON for the rest: IDs, owners and timestamps are known up front and used everywhere; the varying tail isn’t. Splitting them keeps joins and constraints on the core while new keys need no migration.
jsonb over json: PostgreSQL’s json stores an exact copy of the text and reparses it on every access. jsonb pays a little at insert to decompose the document, then supports indexing and fast key access; it gives up key order, whitespace and duplicate keys.
Index by containment or by key: A GIN index over the whole column answers questions about any key but stores every key and value; an expression index on one key is smaller and faster. jsonb_path_ops hashes each path and value, making GIN indexes smaller and more selective for containment.
Promote hot keys instead of designing everything up front: The planner has no statistics on JSON keys and key names repeat in every row. Once usage shows which keys matter, a generated or real column gets a type, constraints, statistics and plain indexes, without guessing on day one.

Timeline

2012
PostgreSQL 9.2 adds a json data type
2014
PostgreSQL 9.4 adds binary, indexable jsonb
2015
MySQL 5.7.8 adds a native JSON type; Pham, Boncz and colleagues publish emergent relational schemas for RDF
2016
Heap’s “When To Avoid JSONB In A PostgreSQL Schema” on missing statistics
2019
PostgreSQL 12 adds stored generated columns
2021
JSON tiles in Umbra extract frequent keys automatically; PostgreSQL 14 adds statistics on expressions
2025
PostgreSQL 18 makes generated columns virtual by default

Sources20

Improve this page on GitHub