When JSONB Is the Wrong Choice and Typed Columns Win

JSONB is easy to start with and easy to outgrow. Nothing breaks on the day you outgrow it. Queries get a little longer, an index is added, a cast is repeated in a fourth file, and one day somebody asks why the report that groups by status takes eleven seconds. This article lists the signs that a JSONB column has become a table in disguise, what the usual workarounds cost on real numbers, and a migration that moves the important fields to columns without downtime and without losing the flexible remainder.

The signs

Look for these in the code and the queries before you look at benchmarks:

1. Every row has the same keys. Run select jsonb_object_keys(attrs), count(*) from products group by 1. If most keys appear in 100 percent of rows, they are columns the schema does not know about.

2. The same expressions keep returning. (attrs ->> 'price')::numeric in the report, the API, the export and the admin screen. Each is a place where the cast can drift out of step.

3. Joins on a value inside the document. join customers c on c.id = (o.doc #>> '{customer,id}')::int means the document holds a foreign key.

4. **Constraints are written as CHECK on expressions**, or not written, with the validation in application code in three services.

5. Frequent single-field updates. A counter or a status inside the document rewrites the whole thing for each change, as measured in updating one key inside a JSONB column.

6. Documents are growing steadily, with new keys that are all optional columns of a single concept.

7. Nobody can say what keys exist. If a new developer has to run a query to find out what an attrs column holds, the schema is in the data and not in the database.

Two or three of these is normal for a document column. Five or six means the column is carrying the model.

What it costs

Size and speed

The hub guide on JSONB (working with JSONB in PostgreSQL from first query to index) builds a typed copy of the catalog. The comparison:

 jsonb_table | columns_table 
-------------+---------------
 59 MB       | 17 MB

The same group by brand with an average over price took 184 ms and 139 ms on two runs against the JSONB table and 18 ms and 17 ms against the typed one, with identical results. The document stores every key name in every row. The typed table stores each name once in the catalog and keeps numbers as numbers. A price as a JSONB number costs more bytes and more decoding work than a numeric(8,2).

For documents that are large enough to be compressed and stored out of line, the cost shows up as detoasting: reading one small key out of a 6 KB document took about 7 ms across 4,000 rows against 0.2 ms for a column lookup.

Statistics and plans

The planner keeps statistics per column. For an expression it has none, unless an index exists on that expression. In the hub guide's example, attrs ->> 'brand' = 'Acme' was estimated at 417 rows per worker while 13,333 came back. After I created an expression index, analyze collected statistics for it, and a lookup by customer in the orders table was estimated at 50 rows and returned 50. So the problem is solvable one expression at a time, by adding an index whose only job is to give the planner numbers. It is a sign you are rebuilding a column.

Joins

A join through the document works. On 50,000 orders joined to 1,000 customers, c.id = (o.doc #>> '{customer,id}')::int ran as a hash join in 27 ms, and the planner estimated the join size correctly. With an index on the expression, where (o.doc #>> '{customer,id}')::int = 42 used a bitmap scan and took 0.17 ms. Both are good enough for many applications. What the document cannot give you without help is referential integrity.

Integrity

A key in a document is not a foreign key. Nothing stops "customer": {"id": 5000} when there is no customer 5000, and nothing stops the customer from being deleted. You can get most of the way with a stored generated column:

alter table mig_orders add column customer_id int
  generated always as ((doc #>> '{customer,id}')::int) stored;
alter table mig_orders add constraint fk_cust
  foreign key (customer_id) references mig_customers (id);

PostgreSQL accepts this. Inserting an order whose document said customer 5000 failed with insert or update on table "mig_orders" violates foreign key constraint "fk_cust", and deleting customer 5 while orders referenced it failed the same way. So a document can have integrity for the fields you promote. That shows how far you can go with JSONB: you can promote fields one at a time, with their constraints, and keep the rest as a document. It also shows the limit. Each promoted field adds a generated column, an index and a constraint, and when the list reaches ten, you are maintaining a table with a JSON column on the side, written the long way.

Contention and bloat

Rows are rewritten whole. Two sessions updating different keys of the same document serialize on the row lock. Dead tuples are the size of the whole document. A GIN index on the column disables HOT updates (1,261 of 3,000 updates were HOT without it and none with it). A table with a hot status flag and an attrs blob of 2 KB writes 2 KB for each flag change.

Migrating a field out of the document

Do it one field at a time, in a way that keeps the application working throughout. This is the order I would use on a live table, with the statements from my test.

1. Add the column, nullable and unconstrained.

alter table mig_orders add column customer_id int;

Adding a nullable column with no default is a catalog change and returns at once. (A column with a volatile default, or a stored generated column, rewrites the table; that is the reason to prefer a plain column for the final state.)

2. Make new writes fill it. Change the application, or add a trigger, so every insert and update sets customer_id from the same source as the document. Until this is deployed, any backfill is chasing a moving target.

3. Backfill in batches.

update mig_orders set customer_id = (doc #>> '{customer,id}')::int
where id <= 20000 and customer_id is null;

Repeat for further id ranges, committing between batches. My run did 20,000 and then 30,000 rows with two statements, with no nulls left (select count(*) filter (where customer_id is null) returned 0). On a big live table use ranges of 10,000 to 100,000 rows and watch replication lag; each updated row is a full new copy of the document, so the table will grow until vacuum catches up.

4. Add constraints without a long lock.

alter table mig_orders add constraint cid_nn check (customer_id is not null) not valid;
alter table mig_orders validate constraint cid_nn;

not valid takes a brief lock and checks only new rows. validate constraint scans the table with a weaker lock that allows reads and writes. A foreign key follows the same pattern: add constraint ... foreign key ... not valid, then validate. In my run the foreign key failed first, with duplicate key value violates unique constraint "mig_customers_pkey", when I tried to re-insert the customer I had deleted for the previous test, a reminder that the checks find the data problems you did not know about. Expect that. A migration is also a data audit.

5. Switch the reads. Replace (doc #>> '{customer,id}')::int with customer_id everywhere, then drop the expression index you had made as a stopgap.

6. Remove the key from the document, last. Only when nothing reads it:

update mig_orders set doc = jsonb_set(doc, '{customer}', (doc -> 'customer') - 'id') where id <= 3;

This leaves "customer": {} in my test, which shows that removing a key can leave empty containers behind. Clean those up in the same statement, or leave the duplicate in the document for a release or two as insurance. Dropping the key is the only irreversible step in the list, which is why it is the last.

Throughout, adding and removing keys from JSONB covers the edit functions, and for the case where the whole table should become several, an INSERT ... SELECT with jsonb_to_recordset (expanding JSONB into rows) turns an embedded array into a child table.

What to keep in JSONB

Migrating everything is not the goal. Keep in the document the parts that are:

• Optional and sparse, such as attributes that exist for 5 percent of rows.

• Varying by type, like the per-category specs of a catalog.

• Passed through, such as a third-party payload you store and return.

• Rarely filtered, even if they are frequently read.

The tool for deciding is the key-frequency query from the signs list, run on production data. Anything above about 90 percent presence that you read in WHERE, JOIN or ORDER BY is a candidate.

When JSONB is still the right call

The warning signs are not a verdict. JSONB is still right in several situations I would not argue with.

The shape really varies. A product catalog across hundreds of categories, form builders, event payloads from many sources: forcing a column per attribute makes a table with 400 mostly-null columns, which is worse.

The schema is changing weekly. In the first months of a product, a flexible column lets you move faster, and you promote fields when they settle. Starting with one JSONB column and a plan to promote is a legitimate approach, as long as you do promote.

The data is read whole. If the application loads the document, edits it and saves it, and the database never looks inside, a column of jsonb (or json, or text) is a perfectly honest container.

The table is small. A few thousand rows will not show any of the costs above.

Documents are the unit of work. Configuration, saved views, draft content, user preferences. These are documents in the product, and the database storing them as documents is not a design failure.

If you are weighing an actual document database against Postgres for the same data, choosing between a JSON column and a document database takes the decision further, and JSON versus JSONB in PostgreSQL covers the type itself. The manual's own advice is in the JSON types chapter, and the migration locks follow ALTER TABLE's rules.