JSONB, and When Not to Use It

jsonb stores a parsed, binary JSON document that Postgres can index, query with containment operators and constrain like any other value. It is the feature that stops teams from running a document database alongside their relational one. It is also the fastest way to move an unversioned schema past a code review, because a column that accepts any shape never rejects anything.

Cartwheel currently has neither benefit. products.attributes is a text column holding JSON that the Python service parses on every read, so as far as the database is concerned it is a string: no key can be indexed, no key can be required, and the planner's statistics describe string lengths. Fixing that means a table rewrite — affordable on a catalogue of tens of thousands of products, and the same statement against orders would be the outage the numbers topic warned about.

json vs jsonb

json keeps an exact copy of the input text and reparses it on every access, preserving insignificant whitespace, the order of object keys, and duplicate keys. jsonb parses once at write time into a decomposed binary form, discards whitespace and key order, keeps only the last value for a duplicate key, and is the only one of the two that supports indexing.

SELECT '{"b":1,   "a":2, "a":3}'::json;
 {"b":1,   "a":2, "a":3}

SELECT '{"b":1,   "a":2, "a":3}'::jsonb;
 {"a": 3, "b": 1}

The jsonb result answered the duplicate-key question — last value wins — and normalized the layout, and it did the parsing once instead of on every read for the next five years. The documentation's recommendation is that most applications should prefer jsonb unless they have a specialized need such as a legacy assumption about key ordering. Picking json because it came first in the autocomplete list gives up indexing entirely.

ALTER TABLE products
  ALTER COLUMN attributes TYPE jsonb USING attributes::jsonb;

That statement parses every existing document, rejects any row whose text was never valid JSON, and rewrites the table under an ACCESS EXCLUSIVE lock. On products it runs in seconds, because the table holds tens of thousands of rows rather than tens of millions.

Reading Into a Document

Four extraction operators and a path language cover almost everything. -> returns a jsonb value, ->> returns text, and #> and #>> do the same for a path rather than a single key. Containment, written @>, asks whether one document contains another. The SQL/JSON path language expresses the questions the arrows cannot.

SELECT attributes->'brand'              FROM products WHERE id = 4471;   -- "Braeburn Farms"
SELECT attributes->>'brand'             FROM products WHERE id = 4471;   -- Braeburn Farms
SELECT attributes#>>'{nutrition,kcal}'  FROM products WHERE id = 4471;   -- 52

SELECT count(*) FROM products WHERE attributes @> '{"organic": true}';
SELECT count(*) FROM products WHERE attributes @? '$.pack[*] ? (@.grams > 500)';

The first two lines differ by one character and by their type: -> hands back a JSON string complete with its quotes, so comparing it against a text literal either raises an operator error or never matches at all. Compare with ->>. Containment ignores the order of array elements and treats duplicates as one, which makes it the right operator for "is this product tagged organic" and the wrong one for anything positional. The path form is the only one of the five that can express "any element of this array where grams exceeds 500".

Indexing It

A document is indexable in three different ways and the choice follows the access pattern rather than the data.

-- every key and every value, independently: containment and key existence
CREATE INDEX products_attributes_gin ON products USING gin (attributes);

-- smaller and sharper, containment only
CREATE INDEX products_attributes_gin ON products USING gin (attributes jsonb_path_ops);

-- one key that is queried constantly
CREATE INDEX products_brand_idx ON products ((attributes->>'brand'));

The default GIN operator class indexes each key and each value as separate entries, which answers key-existence and containment across the whole document at the cost of a large index. jsonb_path_ops stores a hash of each value together with the path that leads to it: smaller than the default class, more selective, faster for containment, and blind to structures that hold no value at all. When a single key dominates the workload, a plain B-tree on the extracted expression beats both, because it is a fraction of the size and it supports ranges and ordering that GIN cannot. Under jsonb_path_ops, a search for {"a": {}} falls back to scanning the whole index.

What It Costs

The whole document is one column value, and that is where the bill starts. Postgres invokes TOAST once a row exceeds roughly 2 KB: the value is compressed and, if that is not enough on its own, moved out of line into a side relation, which is the mechanism Chapter 5 opens up. Every query that reads or filters on attributes then pays to fetch and decompress the document back, including a GIN-indexed containment query, which has to recheck the candidate rows against the real thing.

Updates are worse, because Postgres has no partial update of a value. Changing one key in a 4 KB document writes a complete new version of the row and a complete new TOAST chain, and the first modification of each of those pages after a checkpoint puts a full 8 KB page image into the write-ahead log. A counter bumped inside a 4 KB document costs hundreds of kilobytes of WAL.

The third cost is the one that bites hardest and shows up last. The planner's statistics on a jsonb column are coarse, so the row estimate for a @> predicate is frequently a guess — Chapter 9 catches one of those guesses on Cartwheel in EXPLAIN. GIN still returns the right rows. The wrong estimate propagates upward, flips a join order, and turns a nested loop into a plan that reads far more than it needed to.

Where the Boundary Is

Cartwheel keeps attributes as a document because a bottle of olive oil and a bag of ice genuinely do not share a field list: one has an origin, a pressing date and an acidity, the other has a cube size and a bag weight. Inventing forty mostly-null columns to hold that, or a key-value side table with a join for every attribute, would both be worse. What does not go in the document is price, sku or the stock level, because those are filtered, sorted, constrained and joined.

Anything with a business rule attached belongs in a column, which is short enough to apply in review. Store the whole application object as one blob and query it with @> for everything, and you get no constraints, no usable statistics, no cheap partial update, and a products table that decompresses a 4 KB document to answer a question about a price.

Constraining a Document

Some of the guarantees the document threw away can be bought back without giving up the flexibility that justified it.

ALTER TABLE products
  ADD CONSTRAINT products_attributes_object
      CHECK (jsonb_typeof(attributes) = 'object'),
  ADD CONSTRAINT products_attributes_unit
      CHECK (attributes ? 'unit');

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

The first constraint refuses an array or a bare string arriving where the application expects an object. The second turns a missing required key into a write that fails immediately rather than a NULL-shaped bug found three releases later. The generated column leaves the document as the source of truth while handing the planner a typed, indexable value, and it has to say STORED explicitly: since 18 generated columns are virtual by default, and a virtual one cannot be indexed.