The Postgres JSONB Anti-Pattern: When Flexible Schema Becomes No Schema

It always starts reasonably. You have a users table, a new feature needs three more fields, and writing a migration feels like ceremony. So you add a metadata jsonb column and drop the fields in there. It works. The next feature does the same. Six months later, status, plan, country and signup_source all live inside that JSONB blob, half your WHERE clauses are metadata->>'status' = 'active', a query that should take 2ms takes 400ms, and adding an index feels like it should help but somehow does not fix it.

This is the JSONB anti-pattern, and it is one of the most expensive mistakes in Postgres precisely because it does not hurt at first. JSONB is a genuinely excellent feature. The failure is not using it; the failure is using it as a way to avoid designing a schema. My rule is blunt: if you filter, join, sort or constrain on a field, it is a column, not a JSON key. "Flexible schema" is a real thing you sometimes want. "No schema" is what you actually get, and you cannot index your way out of it.

Why the column beats the JSON key, mechanically

This is not taste. There are four concrete reasons the blob costs you, and understanding them tells you exactly which fields to pull out.

You cannot cheaply index and constrain a buried field

A normal column gets a normal B-tree index that Postgres uses for equality, ranges and sorts. A field inside JSONB needs an expression index on (metadata->>'status'), and you need a separate one for every key you query, every cast, and every sort direction. Worse, the JSON value is text, so metadata->>'age' > 18 compares strings unless you cast, and '9' > '18' is true in text. A real age int column just works, and it can carry a CHECK constraint, a NOT NULL, and a foreign key. None of those exist for a JSON key. Your database cannot protect data it cannot see.

Every update rewrites the whole row, not the field

Postgres is MVCC: an UPDATE never edits in place, it writes a new version of the entire row and marks the old one dead. If your JSONB document is large and you update one key inside it, Postgres rewrites the whole document, and if it is big enough to be TOAST-compressed and stored out-of-line, it rewrites that too. Update a narrow last_seen timestamptz column and the new row version is tiny. Update one key in a 40KB metadata blob and you rewrite 40KB, generate 40KB of WAL, and hand autovacuum 40KB of dead tuple to clean up, every single time. On a hot field this is a bloat and write-amplification engine, and it is the same dead-tuple pressure described in why the default autovacuum settings are too passive.

You cannot join or aggregate efficiently

You cannot put a foreign key on metadata->>'org_id'. So relationships that should be enforced and joined on an indexed integer become text extractions the planner cannot reason about well, and GROUP BY metadata->>'country' re-parses JSON for every row instead of scanning a column. As the row count grows, the query plans degrade in ways an index cannot rescue, because the planner has poor statistics on values hidden inside a document.

TEXT is even worse: it re-parses on every access

If you stored JSON in a text or json column instead of jsonb, every single access re-parses the whole document from scratch, and you cannot GIN-index it at all. jsonb is a parsed binary form: it is slower to write once, far faster to read and index. If you are going to store JSON, it must be jsonb. There is almost no case in an application schema for the plain json type.

The fix: unbury the hot fields with a real migration

You cannot refactor this with a config change. It is a migration, and the sooner you do it the cheaper it is, because the JSON structure calcifies across your app the longer you wait. Here is the safe, no-downtime order for one field.

Step 1: add the real column, nullable

ALTER TABLE users ADD COLUMN status text;

Adding a nullable column with no default is a fast metadata-only change in modern Postgres; it does not rewrite the table. Expect it to return almost instantly even on a large table.

Step 2: backfill from the JSON, in batches

Do not run one giant UPDATE that locks the table and bloats it. Backfill in batches so autovacuum keeps up:

UPDATE users
SET status = metadata->>'status'
WHERE status IS NULL
  AND id IN (
    SELECT id FROM users WHERE status IS NULL LIMIT 5000
  );
-- repeat until 0 rows affected

Step 3: index and constrain the new column

CREATE INDEX CONCURRENTLY idx_users_status ON users (status);
ALTER TABLE users ADD CONSTRAINT users_status_chk
  CHECK (status IN ('active','trial','cancelled'));

Use CONCURRENTLY so the index build does not lock writes. Now you have the equality, range and sort performance a JSON key never gave you, plus a constraint that stops garbage values at the door.

Step 4: switch reads and writes to the column, then drop the key

Deploy code that reads and writes status as a column. Once nothing references metadata->>'status', remove it from the blob so the two do not drift:

UPDATE users SET metadata = metadata - 'status' WHERE metadata ? 'status';

If you genuinely must query inside JSONB

Some data really is variable and you do need containment search over it. Use a GIN index, and prefer jsonb_path_ops, which is smaller and faster for the common containment (@>) queries, at the cost of supporting fewer operators:

CREATE INDEX idx_events_data ON events USING gin (data jsonb_path_ops);
-- uses the index:
SELECT * FROM events WHERE data @> '{"type": "signup"}';

Two honest caveats. A GIN index accelerates containment, not ORDER BY and not range comparisons on extracted text, so it is not a substitute for a column when you sort or do >/<. And a GIN index over a wide document that is written heavily is itself expensive to maintain, so indexing "everything just in case" bloats your write path. Index the containment you actually run, nothing more. Postgres also lets you build a targeted expression index on a single hot key (CREATE INDEX ON t ((data->>'k'))), but if a key is hot enough to need that, it has earned promotion to a real column.

What people get wrong

"JSONB is schemaless, so it is more flexible." It is schemaless in the sense that nothing enforces the shape, which is exactly the problem. Flexibility you cannot constrain is not a feature on a field that must be active, trial, or cancelled; it is a guarantee that eventually one row says "Active", one says "ACTIVE " with a space, and one is missing the key entirely. Constraints are the point of a database.

"I will just add an expression index and it is as good as a column." It is not. You need a separate expression index per key and per cast, the planner has weaker statistics on expressions, you still pay the full-document rewrite on every update, and you still cannot put a foreign key or a NOT NULL on it. An expression index treats a symptom; it does not make a buried field a first-class one.

"Storing it as json is basically the same as jsonb." No. json keeps the raw text and re-parses on every access and cannot be GIN-indexed; jsonb is parsed once into a binary form. For anything you query, jsonb is the only correct choice.

When it is still slow after you move the field

If you have promoted the hot fields to columns and queries are still slow, confirm the planner is using the new index with EXPLAIN (ANALYZE, BUFFERS) and check for the loops multiplier that makes a per-row cost look cheap. If the table is bloated from years of full-document JSONB rewrites, the dead tuples do not disappear on their own: run a VACUUM (VERBOSE, ANALYZE) and, if bloat is severe, plan a pg_repack or a maintenance-window VACUUM FULL to reclaim the space the old pattern wasted. And if a single query still parses JSON for millions of rows, you have another field to unbury, so apply the same four-step migration to it rather than reaching for a bigger box.