Comparison

PostgreSQL JSON vs JSONB: Storage and Indexes

Choose JSON for preserving incoming text, JSONB for querying documents, or typed columns for core fields. Verify semantics and index cost before migrating.

Editorial update

Decision in brief

Use jsonb for documents you regularly filter or inspect with JSON operators. Use json when you need to retain the input JSON text, including whitespace, object-key order and repeated keys. If the exact original bytes are part of a signed or audited message, store the raw payload separately. Keep frequently joined, sorted or constrained values in typed columns.

JSON or JSONB: JSON — Preserve input text. JSONB — Query documents. Typed columns — Join and sort.
Keep exact signed bytes in a separate raw payload. Test any conversion with duplicate keys and representative queries.

Compare fidelity, query operators, and indexing cost

For an input object with two occurrences of the same key, json keeps both in the stored text and JSON functions treat the last as operative. jsonb stores the last value only and can change the textual representation. Before converting an existing column, test duplicate keys, missing keys versus JSON null, large numbers, and payloads rejected by jsonb’s stricter input rules.

Pick an index for the operators and paths the application actually uses. The default jsonb_ops GIN operator class covers key existence, containment and supported JSON path matches. jsonb_path_ops covers containment and supported JSON path matches but has no entries for structures containing no values, such as an empty nested object. For a stable status scalar, a B-tree expression index on (payload ->> 'status') can support a predicate with the same expression. A numeric predicate needs a matching cast on both the index expression and query. Consider a typed column for a scalar with its own validation, foreign key or sort requirement.

WorkloadStart withWhat to verify
Archive incoming JSON and replay the same textjson, with raw bytes stored separately when requiredDuplicate keys, textual round trips and any signature check
Find documents by varying keys or nested containmentjsonb and a GIN operator class matched to the queryIndex coverage on real operators and selectivity, index size and write speed
Filter or join on the same numeric or text fieldTyped column or a matching B-tree expression indexInvalid casts, field constraints and performance on common and rare values
Update small fields in large documents frequentlySmaller documents or typed columns for the hot fieldsWrite latency, table and index growth, and read behavior across payload sizes

Two HN threads about choosing an actual query shape

In the practical JSONB workflow thread, RoxiHaidi asked whether the visual model would expose nested JSONB fields across relationships. Before hiding a key in a document, write down which joins depend on it and which table should enforce its validity.

In a separate HN submission, craigkerstiens shared Paul Ramsey’s experiment on JSONB and TOAST. The underlying experiment varied document size and compared filtering inside JSONB with materializing a field. Repeat those dimensions with your expected sizes and workload instead of importing the benchmark’s timings.

Test the cast and the query plan side by side

Use a staging copy with ordinary payloads plus edge fixtures. Define which round-trip guarantees matter, the expected query results, the p95 deadline and the allowed increase in write time or index space.

  1. Round-trip the same fixtures as json and jsonb. Include an object with duplicate keys, nested empty objects, missing versus null fields and a numeric boundary. Record parse failures and any unacceptable changes to archived text.
  2. Load production-shaped data into comparable tables. Run one containment search, one key-existence search and one scalar filter with separate warm and controlled cold-cache runs where relevant. Inspect EXPLAIN plans and compare p95, buffer work and index footprint.
  3. Try the smallest useful index, then repeat inserts and updates on the largest expected documents. Measure write latency and total table-plus-index space before accepting a read-time improvement.
  4. Promote only if the query results, fidelity and write budget hold. Stop a json-to-jsonb migration if the application relies on a textual form that would be lost. Preserve the original payload and test a separate query column first.

Related articles