JSONB vs Separate Tables: When Normalisation Stops Paying

I’ve reviewed a lot of Postgres schemas over the past few years, and nearly every one of them has a column called metadata, attributes, properties, or extra. It’s always JSONB. And when I ask what lives inside it, the answer is almost never “documents.” The answer is usually “stuff we hadn’t decided on yet.”

The reflex is easy to understand. Adding a column means a migration, a review, maybe a deploy coordination conversation. Dropping a key into an existing JSONB blob means none of that. So the blob grows, one deferred decision at a time, until half the application’s WHERE clauses are reaching inside it. 

 Postgres Does More Than You Think → post 1.

My point here is narrower than “JSONB bad.” JSONB is one of the best reasons to pick Postgres, and there’s a class of data where it’s clearly the right answer. But there’s a line, and it’s crisper than most teams think: JSONB is for data that really is a document. It’s not a place to park modeling decisions you don’t want to make yet. This post is about telling those two apart, and what it costs when you don’t.

Key takeaways

• A JSONB column earns its place when the data arrives as a document, leaves as a document, and its shape is controlled by someone outside your codebase.
• The hidden cost isn’t storage. It’s the planner: Postgres has limited visibility inside a document, so row estimates degrade and query plans go sideways as tables grow.
• Every expression index you add on a JSONB key is a column commitment you’re already making, minus the constraints and statistics that make columns worth having.
• The decision rule: if you filter, sort, join, or validate on a key, it wants to be a column. If you store and retrieve the whole thing, JSONB is fine.

When does a JSONB column actually earn its place?

A JSONB column earns its place when the data is genuinely a document: it arrives whole, it leaves whole, and its shape is decided somewhere you don’t control. Webhook payloads, third-party API responses, audit snapshots, user-defined custom fields. In all of those, the schema lives outside your codebase, and pretending otherwise means chasing someone else’s changes with migrations forever.

The classic example is a webhook inbox. Stripe, GitHub, or whoever sends you an event, and your job is to record it exactly as it came in, then process it later:

CREATE TABLE webhook_events (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source       text NOT NULL,
    event_type   text NOT NULL,
    received_at  timestamptz NOT NULL DEFAULT now(),
    payload      jsonb NOT NULL
);

-- The keys you route on live as real columns.
-- The document you don't control stays a document.
CREATE INDEX ON webhook_events (source, event_type, received_at);

Notice what happened there. The fields your system makes decisions on (source, event_type, received_at) got promoted to columns at design time, even though every one of them also exists inside the payload. That duplication isn’t waste. It’s the whole pattern: relational data on the outside, opaque document on the inside.

 [UNIQUE INSIGHT] The promote-on-ingest pattern: extract routing keys to columns at write time, keep the raw document for everything else.

Audit snapshots sit in the same category for a different reason. When you record “this is exactly what the order looked like when it shipped,” you want a frozen copy, deliberately decoupled from the live tables. If the products table changes next quarter, the snapshot shouldn’t. A document is the honest representation of a point-in-time copy, and normalizing it would actively break its meaning.

The same logic covers user-defined attributes. If your customers can define their own fields per tenant, you genuinely can’t know the keys, and a JSONB column beats the entity-attribute-value table people used to build for this. What’s the difference between that and a metadata column your own team writes to? Control. Your team knows the keys. It just hasn’t written them down.

Where’s the line? The moment your own application code filters on a specific key inside the document on a hot path, that key has stopped being document data. It’s relational data wearing a JSON costume, and everything in the next three sections starts applying to it.

What does JSONB cost you at query time?

The biggest query-time cost of JSONB isn’t parsing. It’s what the planner can’t see. Postgres keeps detailed statistics for regular columns: distinct-value counts, most-common values, histograms. For keys inside a JSONB document, it has close to nothing. A predicate like payload->>'status' = 'failed' gets a flat default selectivity estimate, not one based on how your data actually looks.

Why does an estimate matter if the query still returns the right rows? Because estimates drive plans. If Postgres guesses that a filter matches a large slice of the table when it really matches a sliver (or the reverse), it picks the wrong join strategy, the wrong index, the wrong order. The query is correct and slow, which is the worst kind of wrong because it degrades quietly as the table grows.

EXPLAIN
SELECT *
FROM webhook_events
WHERE payload->>'status' = 'failed';

-- The row estimate here comes from a default fraction,
-- not from statistics about the status key.
-- Compare against a real column, where pg_stats has
-- most-common values and an accurate histogram.

There’s a second cost that shows up with large documents: TOAST. When a row gets big, Postgres compresses it and stores it out of line. Reading one key out of a 40 KB document means detoasting and decompressing the whole document, every time, for every row the query touches. A ten-byte answer pays a forty-kilobyte toll.

Mid-sized documents that stay inline drag down queries that never mention them, too. A sequential scan reads whole pages, and when every row carries a few kilobytes of payload, far fewer rows fit per page, so even a count over a plain column churns through more I/O than the same table would need without the blob. The document doesn’t have to be queried to be paid for. It just has to be there.

 [PERSONAL EXPERIENCE] Diagnosed a reporting query that spent most of its time detoasting event payloads to read a single boolean. Promoting that one key to a column changed the plan and the runtime class.

I’ve watched this play out on a reporting workload where the query only needed one boolean per row, buried in a payload column that averaged tens of kilobytes. The plan looked reasonable. The runtime didn’t. Promoting that single key to a real column didn’t just make the filter cheaper, it let the planner see the data for the first time.

Where’s the line? On small tables and cold paths, none of this matters, and reaching into a document occasionally is fine. It starts to matter when a JSONB predicate sits on a hot path over a growing table, because the estimate error and the detoast toll both compound with size. 

 Indexing strategies deep dive → post 7.

Can’t you just add a GIN index and move on?

A GIN index fixes containment lookups, and almost nothing else. It’s the standard reflex once JSONB queries slow down, and it’s worth being precise about what it buys you. GIN with jsonb_ops (or the smaller jsonb_path_ops) accelerates questions like “does this document contain this key-value pair”:

CREATE INDEX idx_events_payload
ON webhook_events
USING GIN (payload jsonb_path_ops);

-- Fast: containment.
SELECT id
FROM webhook_events
WHERE payload @> '{"status": "failed"}';

That’s a real capability, and for genuine document workloads (find every event mentioning this customer, anywhere in the payload) it’s exactly what you want. But look at what it doesn’t cover. Range predicates: no. Sorting by a key: no. Joining on a key: no. The everyday relational operations that pushed you toward indexing in the first place are precisely the ones GIN doesn’t help with.

The usual next step is an expression B-tree index on the specific key:

CREATE INDEX idx_events_status
ON webhook_events ((payload->>'status'));

-- Now equality and range work through the index,
-- and ANALYZE collects statistics on the expression.

This genuinely works, and it even fixes part of the statistics problem from the last section, because Postgres gathers stats on indexed expressions. But be honest about what you just did. You committed to that key’s name, its location in the document, and its text representation, permanently, in your schema. You made a column commitment. You just made it in the least visible place possible, with no type enforcement, no NOT NULL, and a cast sitting in front of every comparison.

One expression index is pragmatic. Three or four on the same JSONB column is a table announcing that it wants to exist. And every one of them is extra write amplification, because each insert and update maintains them all.

Where’s the line? A GIN index is the right tool when your queries are honestly about containment across unpredictable keys. The moment you’re stacking expression indexes on known keys, the flexibility argument is gone, and the only thing JSONB is still saving you is the migration you didn’t want to write. 

 Database Choices That Quietly Kill Your AWS Bill → post 5.

What breaks first: constraints, writes, or migrations?

Constraints break first, and writes hurt the longest. Start with constraints, because this is the cost teams feel last and should feel first. Inside a JSONB document there are no foreign keys, no per-key NOT NULL, no types. A customer_id inside a payload can point at a customer that was deleted last year, and nothing in the database will ever object. A typo writes "staus" instead of "status" and you’ve silently forked your schema.

You can claw some of this back with CHECK constraints, and for a small contract it’s worth doing:

ALTER TABLE webhook_events
ADD CONSTRAINT payload_has_status
CHECK (
    payload ? 'status'
    AND payload->>'status' IN ('pending', 'processed', 'failed')
);

But notice the shape of what you’re writing. That’s a schema definition, expressed in the clumsiest syntax Postgres offers, validated at write time only, invisible to anyone reading the table definition for column types. If you find yourself writing more than one or two of these against the same column, the database is telling you something.

Writes are the quieter problem. JSONB has no in-place update. Changing one key means writing a complete new copy of the document, and because of MVCC, a complete new version of the row. A one-character change to a 30 KB document is a 30 KB write, plus WAL, plus the index maintenance from the last section, plus the dead tuple autovacuum has to clean up. Update-heavy JSONB columns are how tables bloat while looking innocent.

 [PERSONAL EXPERIENCE] A tenant-settings JSONB column updated on every user preference change turned into the top WAL producer on the cluster. Splitting the hot keys into a narrow table dropped write volume dramatically. –>

I traced a cluster’s WAL growth once to exactly this: a tenant settings document that got rewritten every time any user toggled any preference. The hot keys moved to a narrow table with one row per setting, the document kept the cold ones, and the write volume problem went away without touching application features.

And migrations? The dirty secret is that JSONB doesn’t remove migrations, it moves them into application code. Renaming a key means every reader handles both spellings until a backfill finishes. Changing a value’s type means defensive parsing forever. The schema still exists. It’s just enforced by convention, in every service that touches the column, instead of in one place by the database.

Where’s the line? Documents that are written once and read whole (audit logs, inbound events, snapshots) dodge almost all of this, which is exactly why they’re the good use case. Documents that are updated key-by-key under load are paying the full price.

How do you promote a JSONB key to a real column without downtime?

The same way you make any risky schema change: expand, backfill, flip, contract. Nothing about JSONB changes the playbook, it just adds a step where the data comes out of the document. 

 Zero-downtime schema migrations → post 8.

The sequence that’s worked for me on large, live tables:

-- 1. Expand: add the column, nullable, no default.
ALTER TABLE webhook_events ADD COLUMN status text;

-- 2. Dual-write going forward (app code, or a trigger
--    if you can't touch every writer):
CREATE OR REPLACE FUNCTION sync_status() RETURNS trigger AS $$
BEGIN
    NEW.status := NEW.payload->>'status';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_status
BEFORE INSERT OR UPDATE ON webhook_events
FOR EACH ROW EXECUTE FUNCTION sync_status();

-- 3. Backfill in small batches, keyed on the primary key,
--    with pauses, so you never hold long locks or flood WAL.
UPDATE webhook_events
SET status = payload->>'status'
WHERE id BETWEEN 1 AND 10000 AND status IS NULL;

-- 4. Index the new column, concurrently.
CREATE INDEX CONCURRENTLY idx_events_status_col
ON webhook_events (status);

-- 5. Enforce, without a long lock.
ALTER TABLE webhook_events
ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE webhook_events VALIDATE CONSTRAINT status_not_null;

Then flip reads to the column, watch it for a cycle, and only afterwards decide whether the key stays in the document (harmless for an append-only inbox) or gets stripped from new writes.

Two shortcuts, one worth taking. If the table is small, skip the ceremony: add the column and backfill in one transaction during a quiet window, done in seconds. The tempting shortcut to avoid is a stored generated column (GENERATED ALWAYS AS (payload->>'status') STORED). It’s elegant, but adding one to an existing table forces a full table rewrite under an exclusive lock, which is exactly the downtime you were trying not to take on a big table.

Where’s the line? This playbook is for tables large and hot enough that a rewrite hurts. If a full rewrite finishes inside your maintenance tolerance, the five-step dance is overhead. Measure first.

So how do you actually decide?

Four questions settle nearly every JSONB vs separate tables call I’ve been part of. Ask them per field, not per feature, because the right answer is usually a mix.

1. Who controls the shape? If the keys are defined by an external system or by your end users, JSONB is honest: you can’t write a migration for a schema you don’t own. If your own team controls the keys, the flexibility argument is mostly an excuse to skip a review.

2. Do you filter, sort, or join on it? Any key that appears in a WHERE, ORDER BY, or JOIN on a path you care about wants planner statistics, a typed index, and cheap access. That’s a column. One occasional ad-hoc query doesn’t count. A dashboard does.

3. Does it need to be correct? Foreign keys, NOT NULL, enum-style value sets, type guarantees. If wrong values in this field create incidents, the database should be allowed to reject them, and it can only do that properly for columns.

4. Do you update it independently? Fields that change on their own schedule, under real write volume, don’t belong inside a document that gets fully rewritten each time. Whole-document replacement is fine. Key-by-key churn isn’t.

Read the answers the obvious way: externally controlled, read whole, rarely filtered, rarely updated in place points at JSONB. Any strong yes on questions 2 through 4 points at a column or a child table. And when a document has three hot keys and forty cold ones, do what the webhook table did: promote the three, keep the blob.

Flexibility is a loan

The practical default is dull and it works: model what you know as columns and tables, and reserve JSONB for data that’s genuinely a document with a shape you don’t control. That’s not a compromise position. It’s what each tool is actually for.

The failure mode is always the same, and it’s never dramatic. A metadata column absorbs one deferred decision, then ten. Expression indexes accumulate. CHECK constraints try to hold the line. Queries slow down in ways EXPLAIN half-explains, and one day the team is doing an expand-and-backfill migration under pressure that would’ve been a boring CREATE TABLE two years earlier, for free.

JSONB’s flexibility is real, but it’s borrowed against future query plans, future constraints, and future migrations. Borrow it for documents, where you never have to pay it back. When you borrow it to skip a modeling conversation, the interest compounds quietly, and that’s when flexibility becomes debt.

Frequently asked questions

• Is JSONB slower than regular columns in Postgres?

For whole-document reads and writes, no, it performs well. For filtering, sorting, or joining on individual keys, yes: planner estimates degrade, large documents detoast on every access, and single-key updates rewrite the whole document. Speed depends on your access pattern, not on the type itself.

• When should I use JSONB instead of a separate table?

Use JSONB when data arrives and leaves as a whole document and its shape is controlled externally: webhook payloads, third-party API responses, user-defined attributes. Use separate tables the moment you filter, sort, join, or validate individual fields, because those operations want columns, statistics, and constraints.

• Can you index individual JSONB fields in Postgres?

Yes, two ways. A GIN index accelerates containment queries across the whole document. An expression B-tree index on a specific key supports equality, range, and sort operations for that key. Each expression index is effectively a per-key schema commitment, so heavily indexed keys usually belong in real columns.

• How do I move a JSONB field into a real column without downtime?

Expand and contract. Add a nullable column, dual-write from the application or a trigger, backfill existing rows in small batches, index the new column concurrently, then flip reads and validate a NOT NULL check. Avoid stored generated columns on large tables, since adding one forces a full table rewrite.

• Does JSONB give Postgres the same flexibility as MongoDB?

Mostly, for storage. JSONB holds schemaless documents with indexing and a rich operator set, which covers the common document use cases inside a relational system. The difference is workload shape: MongoDB is built around document-first access patterns end to end, while Postgres treats documents as one tool among many.