Your JSONB column became the schemaless disaster you migrated away from

A metadata JSONB column gets added to the events table because the requirements are still moving and nobody wants to run a migration every week. It is the reasonable call at the time. The column is documented in the pull request as temporary.

Two years later a query counts the distinct key sets in that column and returns 41 shapes. Three of them are load bearing, in the sense that production code branches on their presence. Nobody knows which three without reading every consumer, and one of the consumers is a data pipeline owned by another team.

What you gave up, item by item

The appeal of a schemaless column is that you skip the migration. What you actually skip is every guarantee the column would have carried.

A typed column tells you the value is a timestamp. A JSON key tells you somebody once wrote something that looked like a timestamp, in whatever format their client library produced, and that another writer may have used epoch milliseconds.

That last row is the one that produces the outage. Renaming a column is a migration with a review and a rollback. Renaming a JSON key is a code change in whichever service writes it, and every reader that expected the old key gets null instead of an error. Null flows through the system and turns into a wrong number in a report three weeks later.

There is no failure at the moment of the mistake, which is the property that makes a bug expensive. It is the same shape as dropping a foreign key and discovering the orphans months later: the check that would have failed loudly at write time is simply absent.

TOAST makes big documents expensive to read

This is the mechanical part people do not expect, and it is specific to how Postgres stores oversized values.

A row has to fit in an 8KB page. When a value gets too large, Postgres compresses it, and if it is still too large it moves it into a separate TOAST table in chunks, leaving a pointer behind. The threshold is around 2KB.

So a 40KB JSONB document is not in the row. It is a pointer to several rows in a side table, compressed.

flowchart LR
    Q["SELECT payload->>'status'"] --> ROW["Main table row<br/>holds a TOAST pointer"]
    ROW --> TT["TOAST table<br/>fetch all chunks"]
    TT --> DECOMP["Decompress<br/>entire document"]
    DECOMP --> PARSE["Parse JSONB"]
    PARSE --> KEY["Return one 6 byte string"]

    style TT stroke:#f59e0b,stroke-width:2px,color:#fff
    style DECOMP stroke:#ef4444,stroke-width:3px,color:#fff

Reading one small key from that document costs a second table access, a decompression of the whole value, and a parse. To return six bytes. Do that across 100,000 rows in an aggregate and the cost is substantial and completely invisible in the query text, which looks like a cheap projection.

Writes have the mirror problem. JSONB has no partial update. Changing one key means the whole document is rewritten, re-compressed and re-TOASTed, and the old version stays around as a dead tuple until vacuum. A frequently updated large JSONB column is a bloat engine.

The practical consequence: a JSONB column that is small and rarely updated is genuinely cheap. One that is large, or updated often, or both, has costs that scale with the size of the whole document rather than the part you touched.

Indexing it is a real decision, not a formality

Two options and they are not interchangeable.

A GIN index covers the whole document and supports containment.

CREATE INDEX idx_events_payload ON events USING gin (payload);

-- Uses it.
SELECT * FROM events WHERE payload @> '{"status": "failed"}';

This handles unpredictable query shapes, which is the actual use case for schemaless data. It is also large, often a meaningful fraction of the table size, and slow to update because every key and value gets indexed.

The jsonb_path_ops variant is smaller and faster at the cost of supporting only containment:

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

An expression index covers exactly one path and is far cheaper.

CREATE INDEX idx_events_user ON events ((payload->>'user_id'));

-- Matches only if the query uses the identical expression.
SELECT * FROM events WHERE payload->>'user_id' = '4711';

That last constraint catches people. Write payload->'user_id' = '"4711"' with a different operator and the planner will not match the index, which is the same expression matching problem that keeps planners off indexes generally.

Here is the thing worth sitting with: the moment you create an expression index on payload->>'user_id', you have declared that key to be schema. You are indexing it, you are querying it, you are depending on it. It has every property of a column except type safety, constraints, and visibility. At that point the flexible column is doing a worse job of being a column than a column would.

Promoting the keys that turned out to be real

The fix is not ripping out JSONB. It is recognising which keys graduated and moving them.

Generated columns give you a migration path without changing the write path:

ALTER TABLE events
    ADD COLUMN user_id bigint
    GENERATED ALWAYS AS ((payload->>'user_id')::bigint) STORED;

CREATE INDEX idx_events_user_id ON events (user_id);

Writers keep sending the key inside the document. Readers get a typed, indexable, visible column. The value is computed once on write instead of on every read, which removes the parse and the detoast from the read path entirely.

The limitation is that a generated column cannot be NOT NULL enforced against future writers who omit the key, and it will fail loudly if a writer sends a non-numeric value, which is arguably the correct behaviour and will still page someone.

For validation while the column is still JSONB, a check constraint gets you part of the way:

ALTER TABLE events ADD CONSTRAINT events_payload_shape CHECK (
    jsonb_typeof(payload->'user_id') = 'string'
    AND payload ? 'event_type'
);

This is worth doing on the keys you know are load bearing, because it converts a silent null into a rejected write.

Finding out what is actually in there is the first step and it is one query:

SELECT key, count(*), 
       count(DISTINCT jsonb_typeof(payload -> key)) AS distinct_types
FROM events, jsonb_object_keys(payload) AS key
GROUP BY key
ORDER BY count(*) DESC;

Keys appearing in nearly every row are schema pretending otherwise. Keys with more than one distinct type are a bug that has already happened and has not surfaced yet.

Where JSONB is genuinely right

I do not want to argue people out of it entirely, because there are cases where a column is the wrong tool.

Third party payloads retained verbatim for audit or replay belong in JSONB. You did not design the shape, you cannot control when it changes, and the value is in having exactly what arrived.

Tenant-defined custom fields belong in JSONB, because the alternative is either a table per tenant or an entity-attribute-value model, and both are worse.

A raw response stored next to a parsed and typed representation is a good pattern: the columns carry the data you query, the JSONB carries everything else in case you need it later.

What all of those have in common is that the database is storing the document rather than querying inside it. The failure mode starts when the WHERE clause reaches into the document, because that is the moment the data became schema and the column stopped being a convenience.