JSONB Columns: When Your Relational Database Wants to Be NoSQL

The Column That Ate the Schema

It started with one column. Products had a few category-specific attributes (screen size for monitors, fabric for shirts, wattage for lamps), and adding a nullable column for each felt silly. So you added attrs jsonb, and it worked well.

A year later, attrs holds the product's price "for the new pricing experiment," the supplier ID, a nested array of variants, and three different spellings of colour. A report filtering by color takes nine seconds. The GIN index you added doesn't help. One product has "price": "19.99" as a string, and the checkout code crashed on it.

None of that is JSONB's fault. It did exactly what you asked: it stored whatever you gave it. The problem is that a JSONB column hands you a document database's flexibility with none of a relational table's guarantees, and it's easy to keep using it after the data stops being flexible.

What JSONB Actually Is

The key fact about storage: a JSONB value is a single value. PostgreSQL doesn't store each key separately, so it can't update one key in place. jsonb_set(attrs, '{stock}', '5') builds a complete new document and writes all of it, along with a new version of the row (that's how PostgreSQL's MVCC works for every update). For a 40 KB document, changing one number writes 40 KB.

MySQL's JSON type is also stored in a binary format and supports partial in-place updates in some cases, but the trade-offs in this post, around constraints, statistics, and indexing, apply to both.

Where JSONB Fits

The pattern that works: the things you join on, constrain, sort by, and update independently are real columns. JSONB holds what's genuinely variable or opaque. Good fits:

• Sparse, varying attributes. Product specs that differ by category, custom fields that customers define, survey answers. Modeling these as columns means hundreds of mostly-null columns; modeling them as an entity-attribute-value table means painful pivot queries.
• External payloads you store as received. Webhook bodies, API responses, supplier feeds. Keeping the original lets you reprocess it later, and you don't control its shape anyway.
• Data read and written as a whole. User preferences, UI layout state, a feature's config object. If the application always loads and saves the entire thing, there's nothing to gain from splitting it into columns.
• Early-stage fields. When you don't know yet which attributes matter, JSONB lets you collect them and promote the important ones to columns once you do.

What You Give Up

Every guarantee the database normally gives you about a column stops at the edge of a JSON document.

No types. "price": 19.99 and "price": "19.99" are both valid. So is a missing price. The database will store all three, and every reader has to handle all three.

No foreign keys. A supplier_id inside a document can't reference the suppliers table. The database can't stop it from pointing at nothing. (Why Your Foreign Keys Might Be Lying to You covers what happens without that guarantee.)

No NOT NULL, no UNIQUE, no DEFAULT on individual keys, unless you build them yourself with CHECK constraints, expression indexes, or a JSON Schema extension.

No statistics. This one is subtle and causes real slowdowns. PostgreSQL's planner keeps statistics on column values (most common values, histograms) to estimate how many rows a filter will match. For an expression like attrs->>'color' = 'red', it has none, so it uses a hard-coded guess: 0.5% of rows for an equality, a third of rows for a range comparison. If red is actually 30% of your products, the planner's estimate is off by 60×, and it may pick a nested loop where a hash join would have been far faster.

Whole-document writes. As above, every update rewrites the full value. Frequently updated keys inside large documents produce a lot of write volume, table bloat, and WAL.

Indexing JSONB: Operators, Not Intentions

This is where most JSONB performance problems come from. PostgreSQL chooses an index by matching the query's operator and expression against what the index supports. Two queries that mean the same thing can behave completely differently.

The classic trap: you create a GIN index on attrs, then query with WHERE attrs->>'color' = 'red'. The GIN index can't answer ->> comparisons, so you get a sequential scan over every document. Rewriting the filter as WHERE attrs @> '{"color": "red"}' uses the index, and returns the same rows.

The simulator runs four common JSONB filters against five index choices on a million-row table. Watch which combinations use the index, and what happens to the planner's row estimate.

CREATE INDEX ON products USING gin (attrs);
SELECT * FROM products WHERE attrs->>'color' = 'red';

Seq Scan on products
  Filter: attrs->>'color' = 'red'
  Rows Removed by Filter: 950,000

Combinations worth trying:

• ->> equality with GIN jsonb_ops. No index use. Switch the query to @> containment and the same index works.
• ? key exists with jsonb_path_ops. The smaller GIN variant doesn't index keys on their own, only paths to values, so it can't answer existence checks.
• ->> equality with no index, then with the B-tree on that expression. Watch the estimate. Without the index the planner guesses 5,000 rows; with it, PostgreSQL collects statistics on the indexed expression and the estimate becomes accurate.
• The update slider. Changing a single key in a 32 KB document writes over 4,000 times more data than updating an 8-byte column.

Promoting a Key to a Column

When a key inside a document starts being filtered, sorted, joined, or constrained on, it has stopped being flexible data. Promote it. A stored generated column does this without changing the writers:

ALTER TABLE products
ADD COLUMN color text
GENERATED ALWAYS AS (attrs->>'color') STORED;

CREATE INDEX ON products (color);

-- Queries use a normal column, with normal statistics:
SELECT id, sku FROM products WHERE color = 'red';

The column is kept in sync by the database on every write, gets planner statistics like any column, and can carry NOT NULL or CHECK constraints. Be aware that adding a stored generated column rewrites the table, so on a large table schedule it like any other heavy migration (Zero-Downtime Database Migrations covers the options). The other route is a regular column plus a backfill, after which the application writes the column directly and the key is dropped from the document.

Signs a JSONB Column Has Outgrown Itself

• The same key shows up in most WHERE clauses or ORDER BYs. It's a column already; only the schema doesn't know.
• Code checks types defensively. if isinstance(price, str) means the data has no type guarantee and readers are compensating.
• Keys reference other tables. You want a foreign key and can't have one.
• One key is updated far more often than the rest. Each update rewrites the whole document. Move the hot key out.
• Key names have drifted. colour, color, and Color all exist. Without a schema, nothing stopped it.
• Every document has the same keys. If the structure isn't varying, you're paying for flexibility you don't use.

Deciding

Key Takeaways

• Use jsonb, not json, unless you need the original text byte for byte.
• JSONB fits variable, opaque, or whole-object data: sparse attributes, external payloads, settings. Keep joined, constrained, and frequently updated fields as columns.
• You lose types, constraints, foreign keys, and statistics at the edge of the document. Add them back with CHECK constraints and expression statistics, or promote the key.
• Indexes match operators, not meaning. GIN handles @> and ?; ->> comparisons need an expression index. Rewrite queries to match the index you have.
• Every update rewrites the whole document. Keep hot keys out of large documents.
• Promote keys once they settle. A stored generated column turns a frequently queried key into a real column without changing writers.

References

• PostgreSQL docs: JSON Types, including jsonb indexing and the jsonb_ops vs jsonb_path_ops operator classes
• PostgreSQL docs: JSON Functions and Operators
• PostgreSQL docs: TOAST
• PostgreSQL docs: Generated Columns
• PostgreSQL docs: CREATE STATISTICS, including statistics on expressions