---
title: "Postgres: JSONB"
slug: "postgres-jsonb"
category: "backend"
tags: ["backend", "postgres", "jsonb", "json", "schema"]
status: "stable"
last_updated: 2026-08-14
summary: "Use JSONB for variable-shape data, index with GIN jsonb_path_ops, and promote fields to columns the moment you query them in WHERE clauses."
related:
  [
    "[[backend/postgres]]",
    "[[backend/postgres-indexes]]",
    "[[backend/postgres-explain]]",
    "[[backend/postgres-full-text-search]]",
    "[[backend/migrations]]",
  ]
---

## 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 [[backend/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.

```sql
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. For hierarchical data where each row stores its ancestry, the [[glossary/materialized-path]] pattern works well alongside a JSONB metadata column.

```sql
-- 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.

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

For key-existence queries (`?`, `?&`, `?|`), keep the default opclass. See [[backend/postgres-indexes]] for the broader index decision tree. Validate the planner picks the index with [[backend/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.

```sql
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.

```sql
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 [[backend/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 defaults to LZ4 for TOAST since version 17; 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 [[backend/postgres-partitioning]].

## Related

- [[backend/postgres]]
- [[backend/postgres-indexes]]
- [[backend/postgres-explain]]
- [[backend/postgres-full-text-search]]
- [[backend/postgres-partitioning]]
- [[backend/migrations]]