LLM Best Practices

Postgres: JSONB

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Use JSONB for rows whose shape varies, and promote any key you filter or sort on to a column. jsonb stores a decomposed binary form that supports containment queries, GIN indexes, and subscripting; json keeps the raw text and reparses it on every access. The umbrella rules live in postgres.

Prefer jsonb over json

json preserves whitespace, key order, and duplicate keys. jsonb drops whitespace, does not preserve key order, and keeps only the last value of a duplicate key. Use json only to round-trip the exact text a third party sent.

ALTER TABLE webhook_events ADD COLUMN payload JSONB NOT NULL;

Use JSONB for variable-shape data, not a column-per-key bag

JSONB fits payloads whose schema is set by someone else: webhook bodies, audit events, third-party API responses, model output, dynamic form answers. It does not fit a fixed set of attributes such as { theme, locale, timezone } hidden in one column. For ancestry data, the materialized-path pattern pairs well with a JSONB metadata column.

Know the operators

  • -> returns JSONB; ->> returns text. payload -> 'user' ->> 'id'.
  • #> and #>> walk a path: payload #>> '{user,address,country}'.
  • @> tests containment: payload @> '{"status": "paid"}'. GIN-indexable.
  • ? tests top-level key existence. GIN-indexable with the default class only.
  • @? and @@ evaluate jsonpath: payload @? '$.line_items[*] ? (@.amount_cents > 10000)'.
  • Subscripting reads and writes: payload['status'], UPDATE t SET payload['status'] = '"paid"'.

Cast ->> results for typed predicates: (payload ->> 'amount')::int > 1000. In Postgres 17 and later, JSON_TABLE(), JSON_QUERY(), JSON_VALUE(), and JSON_EXISTS() follow the SQL/JSON standard; use JSON_TABLE to turn an array of objects into rows instead of jsonb_array_elements plus a lateral join.

Index with GIN or a B-tree expression

  • Containment and jsonpath on many keys: CREATE INDEX ON webhook_events USING GIN (payload jsonb_path_ops);. This class is smaller and faster than the default and supports @>, @?, and @@.
  • Key-existence operators (?, ?|, ?&): use the default jsonb_ops class.
  • Equality or range on one key: CREATE INDEX ON orders ((payload ->> 'status'));. The query must use the same expression.

See postgres-indexes for the full decision tree and confirm the planner uses the index with postgres-explain.

Promote hot keys to real columns

When a key appears in WHERE, ORDER BY, or GROUP BY on a hot path, promote it. Columns carry statistics, NOT NULL, CHECK, and foreign keys; JSONB paths carry none of them, and the planner estimates them poorly.

ALTER TABLE orders ADD COLUMN status TEXT;
-- backfill in batches outside the migration:
UPDATE orders SET status = payload ->> 'status' WHERE status IS NULL AND id BETWEEN $1 AND $2;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Run the backfill as a batched job; see migrations.

Watch the size of large documents

Values over about 2 KB are TOASTed out of line, so SELECT * on a JSONB-heavy table pulls TOAST data per row. Select only the keys you need. default_toast_compression is pglz; lz4 is available per column when Postgres was built with LZ4: ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;. Measure with pg_column_size(payload) on representative rows, and partition cold history if documents are large; see postgres-partitioning.

Backlinks