LLM Best Practices

Postgres: JSONB

4 min read

View markdown source

Overview

Use JSONB when the row’s shape genuinely varies. JSONB stores a parsed binary representation that supports containment queries, key lookups, and GIN indexes; JSON stores raw text and reparses on every access. This page covers the choice, the operators, the indexing rules, and the size cost. The umbrella rules live in postgres.

Prefer JSONB over JSON

json keeps the original whitespace and key order. jsonb parses to a binary form and deduplicates keys. Pick JSONB for every storage case; pick JSON only when you need to roundtrip the literal text a third party sent.

ALTER TABLE webhook_events ADD COLUMN payload JSONB NOT NULL;

JSONB supports indexes, the containment operator @>, and jsonpath. JSON does not. The only cost is the parse on insert.

Reach for JSONB on variable-shape data, not as a key-value dump

JSONB fits payloads whose schema is set by an external system or by user input: webhook bodies, audit events, third-party API responses, model output, dynamic form answers. It does not fit a fixed set of columns rewritten as keys.

-- Good: per-row variable-shape payload from a webhook
INSERT INTO webhook_events (source, payload) VALUES ('stripe', $1);
 
-- Bad: a known fixed schema hidden inside a JSON bag
-- users.preferences JSONB containing { theme, locale, timezone, notifications_enabled }

When you find yourself querying the same key in every request, promote it to a column. JSONB queries cost more than column queries, plan worse, and lose statistics.

Know the operators

Five operators cover most production use.

  • -> returns a JSONB child. payload -> 'user' -> 'id' returns JSONB.
  • ->> returns text. payload -> 'user' ->> 'id' returns the string.
  • @> containment. payload @> '{"status": "paid"}' is true when the right side is a subtree of the left. Indexable with GIN.
  • ? key existence. payload ? 'invoice_id' is true when the top-level key exists. Indexable with GIN default opclass.
  • #> and #>> walk a path. payload #>> '{user, address, country}'.

Use ->> plus a cast for type-checked predicates: (payload ->> 'amount')::int > 1000. The cast is required because JSONB does not know your domain.

Index with GIN and jsonb_path_ops for containment

The default GIN opclass indexes every key and value. jsonb_path_ops indexes only the paths used by @>. It is smaller, faster to build, and faster to query when containment is the only operator you need.

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

For key-existence queries (?, ?&, ?|), keep the default opclass. See postgres-indexes for the broader index decision tree. Validate the planner picks the index with postgres-explain.

Use jsonpath for nested predicates

jsonpath queries express filters on nested JSON without unrolling into joins. They run against the JSONB binary form and are indexable with jsonb_path_ops when the query reduces to containment.

SELECT id FROM webhook_events
WHERE payload @? '$.line_items[*] ? (@.amount_cents > 10000)';

@? returns true when the path matches any element. @@ returns true for a boolean predicate. Reach for jsonpath when the alternative is jsonb_array_elements plus a lateral join.

Promote hot keys to real columns

The moment a JSONB key appears in a WHERE, ORDER BY, or GROUP BY clause on a hot path, promote it.

ALTER TABLE orders ADD COLUMN status TEXT;
UPDATE orders SET status = payload ->> 'status' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
CREATE INDEX orders_status_idx ON orders (status);

Run the backfill as a batched job, not in the migration; see migrations. A real column carries NOT NULL, CHECK, and foreign-key constraints. JSONB carries none.

Account for the size cost

JSONB rows are roughly 60 percent larger than the equivalent columns. The binary form stores key names per row, types per value, and a small header. Heap size translates to cold-cache reads, replication bandwidth, and backup time.

  • TOAST stores JSONB over roughly 2 KB out of line. A SELECT * on a JSONB-heavy table pulls TOAST per row; project only the keys you need.
  • Compression helps. Postgres 17 defaults to LZ4 for TOAST; verify with \d+ tablename that compression is on.
  • Run pg_column_size(payload) against representative rows. If the average row is large, partition the cold history; see postgres-partitioning.

Graph View

Backlinks