JSONB: Winning the Schema Argument by Deferring It

Every team that has run a schema migration in production has a story about a column that should have been free. A nullable column is the easy case. The painful ones are the ones that need a value, a backfill, a constraint, and a coordinated deploy across services that were written by different people.

A JSONB column removes that category of work. It also removes the constraints you were relying on, quietly, and it moves a cost from deployment day to table bloat in month three. Both halves need to be understood before you choose it.

The cost you moved has a name, and it is the same one that makes a cheap DDL statement dangerous. An expression index on the JSONB path gives you back the ability to filter it cheaply, but adding one is still a schema change that queues behind readers, which is the lock queue argument in its index form. And a key you now have to enforce in application code is a key you have to enforce forever, which is the same trap as a unique constraint that a soft-deleted row still occupies.

The problem JSONB solves

Here is the migration you did not want to write, because the third-party provider added a field.

ALTER TABLE payments
  ADD COLUMN provider_reference text,
  ADD COLUMN provider_payload jsonb;

And then the next one, and the one after that, each one a deploy, each one a lock queue, each one requiring every service reading the table to be upgraded in step.

The document version is one column and a value.

ALTER TABLE payments
  ADD COLUMN provider_payload jsonb NOT NULL DEFAULT '{}'::jsonb;

// a new provider field arrives
var payload = new
{
    reference = providerReference,
    raw       = providerResponse,
    attempt   = attemptNumber,
};

cmd.Parameters.Add(new NpgsqlParameter("payload", NpgsqlDbType.Jsonb)
{
    Value = JsonSerializer.Serialize(payload),
});

No migration. The new key rides along in the value, and every existing row is still valid because nothing about the shape was declared. That is the whole argument, and it is a good one for genuinely variable data: provider payloads, per-tenant configuration, feature flags, event envelopes, anything whose shape you do not control.

The value of the deferral is entirely in the absence of DDL. If you are still writing ALTER TABLE to add a key, the column bought you nothing and cost you the query plan.

The rule for what belongs in a document

There is a short version and it is worth memorising.

  extracted in application code on most requests  ->  make it a column
  never read, never filtered, shape owned by someone else  ->  document
  everything in between  ->  document plus an index for the fields you query

The reasoning behind it is that the cost of JSONB is not paid for storing the data, it is paid for reading it. A column read is an offset into a fixed-width slot. A document field read is a parse of the value, a binary search through the key list, and a cast. A document field filtered in the database is worse again, because the planner has no useful statistics about how many rows will match until you give it some.

  one row, one field

  column     pg_attribute offset        -> int, nanoseconds
  jsonb      parse, key lookup, cast    -> text, microseconds, plus allocation
  jsonb @>   scan or index, recheck      -> depends entirely on the index

Once a document is large, the gap widens. A payload with four hundred keys is not a small object to parse, and if the row is compressed on disk then reading one field means decompressing all of it.

The failure mode to recognise is a table where every column is a document and every query reaches into one. That system has no schema, no foreign keys, no NOT NULL, and no usable statistics, and every query written against it is a guess about what is in there.

What it costs

The costs are specific and worth enumerating, because they are the reason to be careful rather than the reason to avoid it.

No constraint by default. A typo in a key name is a silent null. There is no declaration that the field must exist or must be an integer.

-- nothing stops this
UPDATE payments SET payload = '{"refernce": "abc"}'::jsonb;

No referential integrity. Nothing prevents a document containing an identifier that no longer exists, which is exactly the thing a foreign key was solving.

Planner blindness. The planner estimates cardinality for a filter on a generated column or an indexed expression because it has statistics for it. For payload->>'status' = 'failed' with no index, it is estimating with a default and the resulting plan is a guess.

No sorting or joining without help. ORDER BY payload->>'created' sorts text representations, so '10' sorts before '9'. A join on a document field cannot use a hash join without an expression index, and a merge join needs one too.

Write amplification. Covered separately, because it is the one that arrives late.

Backup and replication size. Large documents compress less well and replicate as-is.

None of these are reasons to avoid documents. They are the list of things you have taken on, and each one has a specific mitigation that costs work. The point is that the mitigation is required, not optional.

The hybrid that ages well

The pattern that survives contact with production is a document for the part you do not want to model, and real columns for the part you query. Generated columns make the split cheap.

ALTER TABLE payments
  ADD COLUMN payload jsonb NOT NULL DEFAULT '{}'::jsonb;

-- extract what you filter on, without a migration per attribute
ALTER TABLE payments
  ADD COLUMN status text
    GENERATED ALWAYS AS (payload ->> 'status') STORED;

ALTER TABLE payments
  ADD COLUMN provider_reference text
    GENERATED ALWAYS AS (payload ->> 'reference') STORED;

CREATE INDEX idx_payments_status        ON payments (status);
CREATE UNIQUE INDEX idx_payments_reference
    ON payments (provider_reference) WHERE provider_reference IS NOT NULL;

Now status behaves like a column: it has statistics, it sorts, it can be filtered with an ordinary index, and it can be a foreign key target if you ever want it to be. The payload column still absorbs every field nobody has asked about yet, and the next provider field still requires no migration.

  the split

    payload  (jsonb)   the whole provider response, shape unknown, never queried directly
                       adding a key: no DDL
    status   (text)    GENERATED from payload->>'status'
                       filtered, indexed, has planner statistics
    reference (text)   GENERATED, uniquely indexed, a real guarantee

The generated column is recomputed by Postgres on every write, so it must be a pure expression over the document. A function that is not immutable will be rejected, which is a useful constraint: it means the derived column is always the same for the same input.

Before PostgreSQL 18, generated columns were stored only. PostgreSQL 18 added virtual generated columns and made VIRTUAL the default: they compute on read and can be indexed, subject to tighter restrictions on user-defined functions and types. Choose STORED when avoiding read-time computation, supporting older versions, or publishing the generated value through logical replication matters; choose VIRTUAL when recomputation is cheap and duplicating the derived value is not.

Choosing the index

There are two shapes and they are not interchangeable.

A GIN index is the general purpose option. It indexes individual keys and values, so it supports the containment operators.

CREATE INDEX idx_payload ON payments USING gin (payload);

-- any of these can use it
SELECT * FROM payments WHERE payload @> '{"status": "failed"}';
SELECT * FROM payments WHERE payload ? 'provider_reference';
SELECT * FROM payments WHERE payload @? '$.tags[*] ? (@ == "urgent")';

GIN is large and slow to write. On a table with a lot of updates this is a real cost, and it is worth knowing the size before committing to it.

jsonb_path_ops is usually smaller and more selective for the operations it supports. It supports containment (@>) and jsonpath matching (@?, @@), but not the key-existence operators (?, ?|, ?&).

-- usually smaller; supports @>, @?, and @@, but not ?, ?|, or ?&
CREATE INDEX idx_payload ON payments USING gin (payload jsonb_path_ops);

If the workload is containment and jsonpath matching, benchmark jsonb_path_ops. If it needs key-existence operators, use the default jsonb_ops class or a targeted expression index. The operator set decides eligibility; measured index size, update cost, and query latency decide the choice.

For a single field, skip GIN entirely. A btree expression index is smaller, faster, and simpler.

-- a plain btree on the extracted value, for equality and range
CREATE INDEX idx_payload_status ON payments ((payload ->> 'status'));

You only need the generated column if you want the field to participate in more than one index, appear in SELECT *, or be referenced by a constraint. For a single filter, the expression index does the same job.

Update amplification

This is the one that catches people out, because it is invisible at the schema level and obvious in the table size a year later.

-- touches one key
UPDATE payments
SET payload = jsonb_set(payload, '{attempt}', '2')
WHERE id = 88123;

Postgres stores every row version. If the column is stored out of line, because the value is large enough, that out-of-line value has to exist for the new row version as well. Changing one key of a large document means writing a new copy of the whole document.

  a 4KB payload, one key updated

    heap:     new row version, TOAST pointer
    toast:    a new 4KB chunk, written for the new version
    old chunk: still there, still owned by the old row version

    change 1 key  ->  write the whole 4KB

Postgres 14 made an unchanged TOASTed value referenceable by the new tuple rather than re-copied, which removes the cost for updates that leave the document alone. It does not help when you change a key, and it does not help at all for a table where every update touches the document.

The mitigation is to size the document so it stays inline. Postgres stores values up to about 2KB in the row itself before considering TOAST, so a payload of a few hundred bytes is cheap to update and one of tens of kilobytes is not.

-- the threshold at which values move out of line
SELECT current_setting('toast_tuple_target');

Second mitigation: do not update the document when nothing in it changed. A common pattern is to rewrite the entire payload on every state transition, when a nullable column or a status column would carry the change and leave the document alone. That is the amplification made permanent, and the jsonb_set version would have been free.

Validating a document

If the shape is not yours, a check constraint is a reasonable floor, and it is cheap enough to leave on.

ALTER TABLE payments
  ADD CONSTRAINT payload_shape CHECK (
    payload ? 'status'
    AND jsonb_typeof(payload -> 'attempt') IN ('number', 'null')
    AND jsonb_typeof(payload -> 'reference') IN ('string', 'null')
  );

It runs on every write, it is evaluated by the planner as a filter, and it rejects the whole class of “someone sent a string where a number belongs” bugs at the boundary rather than three services downstream.

Where a real schema is needed, a JSON Schema document validated on the way in and a check constraint covering the critical keys is a reasonable combination. Validating on the way out is the wrong instinct; validate once, at the boundary, and let the rest of the system trust the column.

EF Core 8 has first-class JSON column mapping, which is worth using over a raw jsonb property because it gives a typed accessor and a change tracker that knows when the document was replaced.

modelBuilder.Entity<Payment>(b =>
{
    b.OwnsOne(p => p.Metadata, m =>
    {
        m.Property(x => x.Attempt).HasColumnName("attempt");
        m.Property(x => x.Channel).HasColumnName("channel");
        m.ToJson("payload");
    });

    b.Property(p => p.Status);   // real column, indexed, filtered
});

A production-ready architecture

  a new attribute arrives
        |
        v
  will anything filter, sort, join, or constrain on it?
        |
   +----+---------------------------+
   | no                             | yes
   v                                v
  put it in the document        is it stable and owned by us?
  no migration, no DDL              |
   |                          +-----+------+
   |                          |            |
   |                          v            v
   |                    few consumers   consumed widely
   |                          |            |
   |                          v            v
   |                    expression      generated column
   |                    index only      (STORED) + index
   |                                          |
   +---------------------+--------------------+
                         v
              validate shape with a CHECK
              keep the document small enough to inline
              never rewrite the document to record an unrelated change
                         |
                         v
              +-------------------------------+
              | version the schema you care   |
              | about explicitly, so an       |
              | unrecognised shape is visible |
              +-------------------------------+

A delivery checklist:

1. Decide per field whether it will be queried. Anything read on most requests should be a column or have an index.
2. Use generated columns rather than extracting in application code, so the derived value is queryable and has statistics.
3. Choose jsonb_path_ops for GIN unless you need the existence or jsonpath operators.
4. Keep documents small. A few hundred bytes stays inline and updates cheaply.
5. Never rewrite the whole document to record a change that belongs in a column.
6. Add a CHECK constraint for the keys that must be present and their types.
7. Watch table size and autovacuum activity after enabling a document column, since the write cost appears there.
8. Write ->> in string comparisons with awareness of collation, and remember it always returns text.
9. Do not put an identifier in a document and then reference it from another table. That is a foreign key you have chosen not to declare.
10. Keep the ORDER BY out of the document. Sorting text is sorting text, and '10' comes before '9'.

Failure stories worth testing

Filter on an unindexed document field and read the plan

The estimate will be a default and the plan will be a sequential scan. Adding the expression index or the generated column and re-running the same query is the whole argument for the hybrid, in two EXPLAIN runs.

Add a key to a document and confirm no migration is needed

The win is only real if adding the key is genuinely free. If a deploy is still required, something about the design has reintroduced the schema.

Compare update cost at 500 bytes and 40KB

Update one key in a small document and in a large one, then check pg_total_relation_size and autovacuum counters. The threshold is where the value moves out of line, and it is visible in the bloat.

Set jsonb_path_ops instead of the default and compare index size

On a large table the size difference is significant, and the write amplification difference shows up in write throughput. If you only use @>, the larger index is buying nothing.

Try to sort by a numeric field stored in a document

payload->>'amount' sorts lexicographically, so '100' precedes '9'. Casting does not change that the sort is text, and the fix is a real column rather than a cast in the query.

Common mistakes

Mistake	What actually happens	Better decision
Moving everything into a document	No constraints, no statistics, no foreign keys	Keep what you query as columns
Extracting a field in application code every request	A parse and an allocation per read	Generated column or expression index
Believing JSONB removes migrations	You still need DDL if you need an index	Add the index once, for fields you query
Using the default GIN without measuring	Large index, high write amplification	jsonb_path_ops if you only need @>
Rewriting the document for an unrelated change	The whole value is rewritten every time	Put the change in a column
Storing an identifier in the document and referencing it	No referential integrity, enforced by convention instead	A real column with a foreign key
No check constraint on an external payload	A typo becomes a silent null three services away	CHECK for required keys and types
Sorting or range filtering on ->>	Text semantics, wrong order for numbers	Cast to a type, or use a real column
Storing large documents by default	Every update rewrites a value stored out of line	Keep it small enough to stay inline
Reading ->> returns text	Numeric comparisons are lexicographic	Cast, and index the cast expression
Adding a document key in one service only	Reads of that key are version-dependent	Give the document a declared version

The complete story in one minute

A document column’s real benefit is that adding an optional key can require no table migration. That does not remove schema evolution: producers and consumers still need a versioning and compatibility contract. The win is real for provider payloads, sparse per-tenant configuration, and shapes owned outside the service, where forcing every observed field into a first-class relational column creates churn without adding a useful guarantee.

The costs are paid on read rather than on write, and that is the part that determines the design. A field nothing touches is nearly free in a document. A field you extract in application code on most requests costs a parse, a key lookup and a cast on every call, and it belongs in a column. A field you filter in the database is worse, because without statistics on an expression the planner is guessing, and sorting or range filtering a ->> result is a text operation in which '100' precedes '9'. The mature answer is a split: keep the document for the open-ended part, and derive the fields you query into generated columns or expression indexes, so they behave like real columns with statistics and constraints while the document still absorbs everything new.

Two things matter after that choice. PostgreSQL updates create a new row version, and changing one JSON key produces a new JSONB datum; TOAST and compression determine how much physical IO follows, so measure representative document sizes instead of repeating a fixed amplification number. Add CHECK constraints for keys that must be present with the right types, because a write-time rejection is cheaper than a silent null discovered three services downstream. Size the GIN index deliberately too: jsonb_path_ops is often smaller for containment/jsonpath workloads, while the default class is required for key-existence operators.

Technical references

• PostgreSQL: JSON types and JSONB operator classes
• PostgreSQL: generated columns
• PostgreSQL: indexes on expressions
• PostgreSQL: TOAST storage