PostgreSQL JSONB Internals for Semi-Structured SaaS Data

--- title: "PostgreSQL JSONB Internals: Eliminating Full Table Scans on 50M+ Row Tables" published: true description: "JSONB TOAST storage, GIN index anatomy, and the exact query rewrites that keep p99 latency under 10ms on large SaaS tables with heterogeneous schemas." tags: postgresql, architecture, performance, api canonical_url: https://blog.mvp-factory.dev/postgresql-jsonb-internals-full-table-scans --- 

What We Are Building

By the end of this workshop, you will understand why a GIN index on a JSONB column can be completely ignored at runtime — and exactly how to fix it. We will walk through three layers: TOAST storage, GIN index internals, and the operator semantics that determine whether your WHERE clause hits the index or kicks off a 50-million-row scan.

Prerequisites: PostgreSQL 13+, basic familiarity with EXPLAIN ANALYZE, and a table with a JSONB column you are already querying in production (or staging).

Step 1 — Understand What TOAST Is Doing to Your Reads

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.

Here is the minimal setup to verify what your column is doing:

-- 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) 

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. We will get there in Step 4.

Step 2 — Understand GIN Index Anatomy

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.

Let me show you a pattern I use in every project: choosing the right operator class before you create the index.

-- 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); 

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.

Step 3 — Rewrite the Queries That Actually Hit the Index

This is where most engineers lose hours. 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. The same applies to nested paths:

-- Will NOT use GIN WHERE payload->'user'->>'role' = 'admin' -- WILL use GIN WHERE payload @> '{"user": {"role": "admin"}}' -- Or expression index on the nested path CREATE INDEX ON events ((payload->'user'->>'role')); 

Mixing both in a WHERE clause works fine — PostgreSQL's planner will pick the cheaper path.

Step 4 — Sustain Sub-10ms p99 at Scale

Three things keep p99 under 10ms on heterogeneous SaaS schemas:

Partial GIN indexes for dominant filter values cut index size dramatically. A partial index scoped to active rows — where they represent 5–10% of the data — eliminates 90%+ of index entries.

Generated columns for the top 3–5 query paths eliminate TOAST overhead on hot reads.

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); 

Gotchas

The index exists but is never used. Nine times out of ten it is a ->> with = that needs to become @> containment or an expression index. Run EXPLAIN (ANALYZE, BUFFERS) — if you see Seq Scan or Bitmap Heap Scan with high buffer reads, this is your culprit.

Wrong operator class for the workload. If ?, ?|, and ?& are absent from your query patterns, jsonb_ops is wasting space. Switch to jsonb_path_ops and rebuild. The docs do not make this tradeoff obvious.

TOAST decompression hiding in plain sight. A query can hit the GIN index correctly and still be slow because every matched row decompresses a large JSONB blob. Promote any key queried in more than 20% of your workload to a generated stored column with a B-tree index.

Partial index condition mismatch. A partial index defined with WHERE status = 'active' will only be used by queries that also include that exact condition. If your query does not filter on status, the partial index is invisible to the planner.

Conclusion

JSONB is not magic. The fix is mechanical: run EXPLAIN (ANALYZE, BUFFERS) on your top JSONB queries, rewrite ->> equality checks as @> containment queries or expression indexes, switch to jsonb_path_ops for containment-only workloads, and promote hot paths to generated stored columns. Apply all three and 50-million-row tables stop being a problem.

Further reading:

• PostgreSQL TOAST documentation
• GIN Index Types
• JSONB Indexing