JSONB at Scale: Taming PostgreSQL TOAST Churn and WAL Bloat on High-Write Workloads
Originally published on tamiz.pro.
JSONB is one of PostgreSQL's most useful data types for semi-structured data, but it also hides one of the database's most expensive internal mechanisms.
Large documents are moved out of the main heap into TOAST storage, compressed along the way, and - when the row changes - rewritten. On write-heavy systems, that combination can produce surprising WAL amplification, bloated tables, and storage amplification long after the original data has been compacted.
The core problem is not JSONB itself. It is that many workloads treat JSONB like an opaque scalar column while PostgreSQL treats it like a variable-length object with expensive update semantics. Understanding that boundary is the difference between a healthy high-write system and one that spends its I/O budget rewriting the same payload over and over.
This guide walks through the internals of TOAST, explains where WAL amplification comes from, and provides a concrete engineering path from diagnosis to remediation.
Table of Contents
- The mental model: JSONB is not one column
- How PostgreSQL stores large JSONB values
- Why high-write workloads create TOAST churn
- Measuring the problem
- The remediation toolkit
- Reference implementation: a batched JSONB writer
- Production patterns and edge cases
- Frequently Asked Questions
1. The mental model: JSONB is not one column
A common source of confusion is the idea that a jsonb column occupies a single, self-contained slot in a row. That is true only for small values.
PostgreSQL uses a fixed-width pointer in the row itself and stores the actual payload elsewhere once the value exceeds the threshold for in-row storage.
For a row like this:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id integer NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
The payload column is not necessarily inline. The storage decision depends on the tuple size, the available space in the page, and the value's compressibility.
A small JSONB document may stay in the heap tuple. A large document is compressed and, if needed, pushed into TOAST storage.
Three distinct storage paths exist:
- Inline storage - the JSONB value fits in the main heap tuple and is stored directly on the page.
- Compressed inline storage - the value is compressed but still fits in the tuple.
- External TOAST storage - the value is compressed and then stored out-of-line in a TOAST table, with only a small pointer remaining in the heap tuple.
This distinction matters because the cost profile of each path is different. Inline writes are cheap. Compressed inline writes require CPU. External TOAST writes add a second relation, additional index-like bookkeeping, and an extra round of WAL.
The key insight When you UPDATE a row and change only a small field, PostgreSQL does not patch the JSONB value in place. PostgreSQL has no fine-grained, in-place JSONB mutation. It constructs a new value and writes a new tuple version.
If that new value is externally stored, the old TOAST chunk may be reused only if the storage code determines it can be shared. In practice, for changed or rewritten values, you get a fresh TOAST write.
The result is a cost model that looks roughly like this:
row update cost ≈ heap tuple write + TOAST value write (if external) + compression CPU + WAL for both heap and TOAST + dead tuple cleanup + index maintenance
On a workload that updates a small subset of fields in a large JSONB document thousands of times per second, the TOAST write and its WAL can dominate the actual business logic.
2. How PostgreSQL stores large JSONB values
TOAST - the acronym stands for The On-disk Self-Optimizing Table - is PostgreSQL's mechanism for handling variable-length attributes larger than a page-sized tuple.
The TOAST table When a table contains a TOASTable column, PostgreSQL creates a companion relation with a name like pg_toast_16384. This table has the same structure as the original table but with a special trigger that moves large values out of line.
The TOAST table stores four columns per TOASTable column:
chunk_idoid-- identifier linking chunks to a valuechunk_seqint4-- ordering of the chunkchunk_databytea-- the actual compressed bytes
Values larger than TOAST_TUPLE_THRESHOLD (around 2 KB) are split into chunks of roughly TOAST_MAX_CHUNK_SIZE (around 2 KB as well, adjusted for page overhead). Each chunk is a separate row in the TOAST table.
Compression: PGLZ and LZ4 Before storage, PostgreSQL attempts to compress the value using PGLZ, the built-in algorithm. Since PostgreSQL 14, you can enable LZ4 compression, which is significantly faster while producing somewhat larger output than PGLZ for many data shapes.
You can control this at the column level:
ALTER TABLE events ALTER COLUMN payload SET STORAGE EXTERNAL;
The available modes are:
- Storage mode Behavior When to use it
- PLAIN Never compress or move out of line; errors if the row is too large Very large values you want stored verbatim and never compressed
- EXTENDED Compress first, move out of line only if still too large Default; good general-purpose choice
- EXTERNAL Move out of line first, compress only if that saves space Write-heavy workloads where compression CPU is the bottleneck
- MAIN Always keep inline, compress if needed Small, bounded values
The default for jsonb is EXTENDED, and that default is often exactly wrong for high-write workloads.
Why WAL amplification happens Every physical change is logged in the Write-Ahead Log. When you update a row with an externally stored JSONB value, the WAL contains:
- The new heap tuple, including the TOAST pointer.
- The new TOAST chunk rows.
- WAL for the index entries that reference the row.
The TOAST portion can be substantially larger than the logical change you made. If your JSONB document is 100 KB and you update a single status field, the logical delta is a few bytes. The physical write is 100 KB, plus WAL for it.
This is not a bug. It is the natural consequence of PostgreSQL's MVCC design combined with copy-on-write semantics for variable-length values.
The engineering response is to reduce the number of bytes written per logical operation, or to reduce the frequency of those operations.
3. Why high-write workloads create TOAST churn
Let's characterize the workload patterns that cause the most damage.
Pattern A: read-modify-write of large documents
UPDATE events SET payload = jsonb_set(payload, '{status}', '"processed"'::jsonb) WHERE id = $1;
This rewrites the entire document. If the document is 50 KB and externally stored, each update writes 50 KB of TOAST data and generates corresponding WAL.
Pattern B: upsert loops with high conflict rates When many workers contend on the same key, PostgreSQL's ON CONFLICT DO UPDATE path repeatedly rewrites the row, and each rewrite regenerates the TOAST value.
Pattern C: telemetry and event ingestion with mutable payloads Event tables often accumulate a JSONB payload that grows over time as processing stages add metadata. Each stage appends a key, rewriting the whole document.
Pattern D: unbounded JSONB growth If a JSONB column accumulates history, counters, or nested arrays without bounds, the value size grows monotonically. The write cost per operation grows with it, even though the operation itself is constant-size.
Pattern E: aggressive autovacuum settings misapplied to TOAST Vacuuming the parent table does not automatically keep TOAST tables in good shape in every configuration. You need to understand how TOAST cleanup interacts with autovacuum and toast.autovacuum_* settings.
4. Measuring the problem
Before changing anything, establish a baseline. This section covers the queries and tools that will tell you whether TOAST churn is actually your bottleneck.
Step 1: Find tables with large average row sizes
SELECT schemaname, relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS heap_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS toast_and_index_size, round(avg_row_len, 1) AS avg_row_bytes FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;
A large gap between heap_size and total_size is a strong signal that TOAST and indexes are consuming significant space.
Step 2: Measure TOAST usage per table PostgreSQL exposes TOAST statistics through pg_class and pg_stat_user_tables:
SELECT c.relname AS table_name, pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size, t.n_live_tup, t.n_dead_tup, t.n_mod_since_analyze FROM pg_class c JOIN pg_stat_user_tables t ON t.relid = c.oid WHERE c.reltoastrelid <> 0 ORDER BY pg_relation_size(c.reltoastrelid) DESC;
Step 3: Inspect actual storage modes The pg_column_size function returns the number of bytes used to store a value, including any TOAST compression:
SELECT id, pg_column_size(payload) AS stored_bytes, octet_length(payload::text) AS raw_text_bytes, pg_column_size(payload) * 1.0 / NULLIF(octet_length(payload::text), 0) AS compression_ratio FROM events ORDER BY stored_bytes DESC LIMIT 20;
A ratio near 1.0 means the data is not compressing well. A ratio around 0.3 means PGLZ is doing a good job. Values in the tens of kilobytes are candidates for TOAST.
Step 4: Look at WAL generation PostgreSQL 15+ exposes per-table WAL statistics:
SELECT wal_records, wal_bytes, wal_fpi, round(wal_bytes / 1024.0 / 1024.0, 2) AS wal_mb, round(wal_bytes::numeric / NULLIF(n_tup_ins + n_tup_upd + n_tup_del, 0), 0) AS avg_wal_bytes_per_dml FROM pg_stat_wal CROSS JOIN pg_stat_database d WHERE d.datname = current_database();
More practically, use pg_stat_wal (cluster-wide) and pg_stat_database for xact_commit rates, then sample pg_current_wal_lsn() over a fixed interval:
-- Run twice, one minute apart, and subtract.
SELECT pg_current_wal_lsn();
The difference tells you the WAL bytes per second your cluster is generating.
Step 5: Profile with pg_stat_statements Find the statements generating the most WAL. pg_stat_statements tracks wal_bytes when the track_wal_io_timing and related options are enabled in PostgreSQL 15+:
SELECT query, calls, total_exec_time, wal_bytes, round(wal_bytes::numeric / NULLIF(calls, 0)) AS avg_wal_per_call FROM pg_stat_statements WHERE wal_bytes > 0 ORDER BY wal_bytes DESC LIMIT 20;
This is usually where you find the UPDATE ... jsonb_set(...) pattern.
Step 6: Inspect the TOAST table directly
SELECT chunk_id, count(*) AS chunks, sum(length(chunk_data)) AS total_bytes, avg(length(chunk_data)) AS avg_chunk_bytes FROM pg_toast.pg_toast_16384 GROUP BY chunk_id ORDER BY total_bytes DESC LIMIT 10;
Replace 16384 with the actual OID of your table's TOAST relation, found via:
SELECT c.reltoastrelid::regclass FROM pg_class c WHERE c.relname = 'events';
5. The remediation toolkit
The goal is to reduce bytes written per logical operation. There are several complementary levers, ordered by how much they typically help.
Lever 1: Set STORAGE EXTERNAL on hot JSONB columns This is the single highest-leverage change for CPU-bound high-write workloads. It skips compression for values that go out of line, trading storage for CPU and write throughput.
ALTER TABLE events ALTER COLUMN payload SET STORAGE EXTERNAL;
-- Force a rewrite so the new storage mode applies to existing rows.
VACUUM FULL events;
Important caveats:
VACUUM FULLtakes anACCESS EXCLUSIVElock. On a live system, plan a maintenance window or usepg_repack.- The change only affects newly written or rewritten tuples. Existing tuples keep their old layout until they are rewritten.
EXTERNALmeans no compression at all for out-of-line values. If your JSON compresses 5:1, your TOAST table will grow substantially. Verify the tradeoff on real data.- If the value fits inline, it is still stored inline, uncompressed.
Measure the effect:
-- Before and after: compare WAL generation rate and CPU usage.
SELECT pg_size_pretty(pg_relation_size('pg_toast.pg_toast_16384')) AS toast_size, (SELECT count(*) FROM events WHERE pg_column_size(payload) > 2000) AS external_values;
Lever 2: Enable LZ4 compression If you want compression but less CPU, LZ4 is a strong option:
ALTER SYSTEM SET lz4 TO on;
SELECT pg_reload_conf();
Then force a rewrite of affected rows:
VACUUM FULL events;
LZ4 typically compresses 2-3x faster than PGLZ with slightly worse ratios. For JSON payloads with many repeated keys, the ratio difference is usually modest.
Lever 3: Reduce the frequency of large rewrites The best fix is often architectural: stop rewriting the whole document for small changes.
Normalize hot fields into real columns. If status, tenant_id, or retry_count are queried or updated frequently, move them out of JSONB:
ALTER TABLE events ADD COLUMN status text, ADD COLUMN retry_count integer NOT NULL DEFAULT 0;
UPDATE events SET status = payload ->> 'status', retry_count = COALESCE((payload ->> 'retry_count')::integer, 0);
Now updates touch only the scalar column:
UPDATE events SET status = 'processed' WHERE id = $1;
This eliminates the TOAST rewrite entirely for that field.
Split cold and hot data. Keep a small, frequently-updated working set in one table and append large, rarely-modified payloads to another:
CREATE TABLE event_payloads (event_id bigint PRIMARY KEY REFERENCES events(id) ON DELETE CASCADE, payload jsonb NOT NULL);
The events table stays small and cheap to update; the payload table is written once and read rarely.
Use append-only semantics. For event streams, don't update at all:
INSERT INTO events (tenant_id, payload, created_at) VALUES ($1, $2, now());
A consumer maintains derived state. This eliminates TOAST churn by construction.
Lever 4: Batch writes If you must write many rows, batch them. PostgreSQL's WAL cost per row drops significantly when many tup
Comments
No comments yet. Start the discussion.