PostgreSQL JSONB internals: eliminating full table scans

SEO Meta Description: JSONB TOAST storage, GIN index anatomy, and the exact query rewrites that keep p99 latency under 10ms on 50M+ row SaaS tables with heterogeneous schemas.

---

You add a GIN index to your JSONB column, deploy to production, and watch p99 latency spike anyway. The query plan shows a Bitmap Heap Scan tearing through 50 million rows as if the index doesn’t exist. I’ve debugged this exact scenario more than once, and every time the root cause is the same: a mismatch between the operator in the WHERE clause and the operator class the index was built for. This post covers three layers you need to understand — TOAST storage, GIN internals, and query operator semantics — to fix these problems.

---

TL;DR

JSONB is not magic. Without understanding how PostgreSQL stores it (TOAST), how GIN indexes decompose it (posting lists per path-value), and which operators actually hit those indexes, you will scan 50 million rows when you meant to scan 50. Rewrite path-operator queries as containment queries, choose the right GIN operator class, and respect the TOAST threshold.

---

The storage layer: TOAST and what it costs you

PostgreSQL stores heap tuples on 8KB pages. Once a JSONB column pushes a row past roughly 2KB, the engine invokes TOAST (The Oversized Attribute Storage Technique). For JSONB, the default storage strategy is EXTENDED: PostgreSQL first tries pglz compression (or lz4 on PostgreSQL 14+), and if the result still exceeds the threshold, it moves the value off-page into a dedicated TOAST table.

-- Check actual storage strategy for your column
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'events'::regclass AND attname = 'payload';
-- 'x' = EXTENDED (compress then store external)
-- 'e' = EXTERNAL (store external, no compression)

What most teams miss: every query that touches a TOASTed column pays a decompression cost, even if you only need one nested key. If p99 is mysteriously high on what looks like an indexed query, check pg_stat_user_tables for heap_blks_read spikes and profile whether the TOAST table is involved. For hot, frequently-queried keys, promoting them to real columns via a generated column is often the right call.

---

GIN index anatomy: posting lists under the hood

A GIN (Generalized Inverted Index) for JSONB works like a full-text search index, but for structured paths. PostgreSQL decomposes each JSONB document into atomic elements and builds a posting list — a sorted array of heap tuple IDs (TIDs) — for each element.

Two operator classes exist, and the choice matters a lot:

Operator Class	Indexed Elements	Supported Operators	Index Size
jsonb_ops (default)	Keys and values independently	@>, ?, ?|, ?&	Larger
jsonb_path_ops	Hash of full path + value	@> only	~30–50% smaller

-- General purpose: use jsonb_ops
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Containment-only workload: use jsonb_path_ops
CREATE INDEX idx_events_payload_path ON events USING GIN (payload jsonb_path_ops);

In my experience, jsonb_path_ops consistently wins for SaaS event and audit log tables north of 50 million rows where the dominant query is @> containment. The smaller index fits better in shared_buffers, and cache hit rates improve measurably.

---

The query patterns that actually hit the index

This is where most engineers lose time. The path operator ->> with an equality check does not use your GIN index.

-- FULL TABLE SCAN — GIN index ignored
EXPLAIN SELECT * FROM events WHERE payload->>'status' = 'active';

-- INDEX SCAN — GIN containment operator
EXPLAIN SELECT * FROM events WHERE payload @> '{"status": "active"}';

-- Alternative: expression index for high-cardinality path extractions
CREATE INDEX idx_events_status ON events ((payload->>'status'));
SELECT * FROM events WHERE payload->>'status' = 'active'; -- now hits expression index

The rule: ->> with = requires an expression index on that specific path. @> uses your GIN index. Mixing both in a WHERE clause works fine — PostgreSQL’s planner will pick the cheaper path.

For nested paths, same deal:

-- Will NOT use GIN
WHERE payload->'user'->>'role' = 'admin'

-- WILL use GIN
WHERE payload @> '{"user": {"role": "admin"}}'

-- Or expression index
CREATE INDEX ON events ((payload->'user'->>'role'));

---

Keeping p99 under 10ms at scale

Three things sustain sub-10ms p99 on heterogeneous SaaS schemas:

1. Partial GIN indexes for dominant filter values cut index size dramatically. A partial index scoped to status = 'active' on a table where active rows are 5–10% of the data eliminates 90%+ of index entries.
2. Generated columns for the top 3–5 query paths eliminate TOAST overhead on hot reads.
3. jsonb_path_ops for pure @> workloads — the index compaction directly reduces I/O.

-- Partial GIN: only index active records
CREATE INDEX idx_active_events ON events USING GIN (payload jsonb_path_ops)
WHERE status = 'active';

-- Generated column for a hot path
ALTER TABLE events ADD COLUMN tenant_id TEXT
  GENERATED ALWAYS AS (payload->>'tenant_id') STORED;
CREATE INDEX ON events (tenant_id);

---

Takeaways

1. Run EXPLAIN (ANALYZE, BUFFERS) on your top JSONB queries. If you see Seq Scan or Bitmap Heap Scan with high buffer reads, your ->> queries are missing the GIN index. Rewrite them as @> containment queries or add expression indexes.

2. Switch to jsonb_path_ops for containment-only workloads. If ?, ?|, and ?& are absent from your query patterns, the smaller index will improve cache efficiency and reduce index maintenance overhead on write-heavy tables.

3. Promote hot paths to generated stored columns. For any JSONB key queried in more than 20% of your workload, the TOAST decompression and path-traversal cost adds up. A generated column with a B-tree index is faster, simpler for the planner, and easier to maintain.

---

Tags: backend architecture saas