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

  • 1. The mental model: JSONB is not one column
  • 2. How PostgreSQL stores large JSONB values
  • 3. Why high-write workloads create TOAST churn
  • 4. Measuring the problem
  • 5. The remediation toolkit
  • 6. Reference implementation: a batched JSONB writer
  • 7. Production patterns and edge cases
  • 8. 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:

  1. Inline storage — the JSONB value fits in the main heap tuple and is stored directly on the page.
  2. Compressed inline storage — the value is compressed but still fits in the tuple.
  3. 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_id oid -- identifier linking chunks to a value chunk_seq int4 -- ordering of the chunk chunk_data bytea -- 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:

  1. The new heap tuple, including the TOAST pointer.
  2. The new TOAST chunk rows.
  3. 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 FULL takes an ACCESS EXCLUSIVE lock. On a live system, plan a maintenance window or use pg_repack.
  • The change only affects newly written or rewritten tuples. Existing tuples keep their old layout until they are rewritten.
  • EXTERNAL means 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 tuples are written in one statement, because full-page images and page-header overhead are amortized.

INSERT INTO events (tenant_id, payload, created_at) SELECT * FROM jsonb_to_recordset($1::jsonb) AS x(tenant_id integer, payload jsonb, created_at timestamptz); 

For high-throughput ingestion, COPY is usually faster still:

psql -d appdb -c "\copy events (tenant_id, payload, created_at) FROM '/data/events.csv' WITH (FORMAT csv)" 

Lever 5: Tune autovacuum for TOAST tables

TOAST tables have their own autovacuum settings:

ALTER TABLE pg_toast.pg_toast_16384 SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 1000 ); 

More aggressive vacuuming on TOAST tables removes dead chunks faster, limiting bloat. The tradeoff is more I/O spent on vacuum.

You can also set cluster-wide defaults:

ALTER SYSTEM SET toast.autovacuum_vacuum_scale_factor = 0.02; ALTER SYSTEM SET toast.autovacuum_vacuum_threshold = 1000; SELECT pg_reload_conf(); 

Lever 6: Monitor WAL generation continuously

Track WAL rate and TOAST size as first-class metrics:

CREATE OR REPLACE VIEW monitor_toast_wal AS SELECT now() AS sampled_at, pg_current_wal_lsn() AS current_lsn, pg_size_pretty(pg_total_relation_size('events')) AS events_total_size, pg_size_pretty(pg_relation_size('pg_toast.pg_toast_16384')) AS toast_size, (SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'events') AS live_tuples; 

Sample it on a schedule and alert on regressions:

SELECT max(wal_bytes) - min(wal_bytes) AS wal_generated, max(toast_size) - min(toast_size) AS toast_growth FROM monitor_toast_wal WHERE sampled_at > now() - interval '1 hour'; 

6. Reference implementation: a batched JSONB writer

Here is a complete, runnable example that demonstrates the core techniques working together. It assumes PostgreSQL 15 or later.

Schema

DROP TABLE IF EXISTS event_batches; DROP TABLE IF EXISTS events; CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tenant_id integer NOT NULL, status text NOT NULL DEFAULT 'pending', payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); -- Hot path: uncompressed out-of-line storage. ALTER TABLE events ALTER COLUMN payload SET STORAGE EXTERNAL; -- Cold path: append-only payloads, written once. CREATE TABLE event_payload_archive ( event_id bigint PRIMARY KEY, payload jsonb NOT NULL ); CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at DESC); 

Batch insert function

CREATE OR REPLACE FUNCTION ingest_event_batch( p_batch jsonb ) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE v_inserted bigint; BEGIN INSERT INTO events (tenant_id, payload, created_at) SELECT (x ->> 'tenant_id')::integer, x -> 'payload', COALESCE((x ->> 'created_at')::timestamptz, now()) FROM jsonb_array_elements(p_batch) AS x; GET DIAGNOSTICS v_inserted = ROW_COUNT; -- Archive the same payload for cold storage without a second rewrite. INSERT INTO event_payload_archive (event_id, payload) SELECT e.id, e.payload FROM events e WHERE e.tenant_id = (p_batch -> 0 ->> 'tenant_id')::integer AND e.created_at >= now() - interval '1 second' ON CONFLICT DO NOTHING; RETURN v_inserted; END; $$; 

In production, the archive insert should use a more precise correlation key (for example, a batch UUID written into every row) rather than the time window above. The time-window version here is intentionally simple and illustrative.

Test it

SELECT ingest_event_batch( jsonb_build_array( jsonb_build_object( 'tenant_id', 42, 'payload', jsonb_build_object( 'event_type', 'order.created', 'order_id', 1001, 'items', jsonb_build_array( jsonb_build_object('sku', 'ABC-123', 'qty', 2), jsonb_build_object('sku', 'XYZ-789', 'qty', 1) ), 'metadata', repeat('x', 5000) ), 'created_at', now() ), jsonb_build_object( 'tenant_id', 42, 'payload', jsonb_build_object( 'event_type', 'order.created', 'order_id', 1002, 'items', jsonb_build_array( jsonb_build_object('sku', 'DEF-456', 'qty', 5) ), 'metadata', repeat('y', 5000) ), 'created_at', now() ) ) ); 

Verify the storage layout

SELECT id, tenant_id, pg_column_size(payload) AS stored_bytes, octet_length(payload::text) AS raw_bytes FROM events ORDER BY id; -- Confirm the TOAST relation is being used. SELECT chunk_id, count(*) AS chunks, sum(length(chunk_data)) AS bytes FROM pg_toast.pg_toast_16384 GROUP BY chunk_id; 

Efficient status update

Because status is now a real column, updating it does not rewrite the payload:

UPDATE events SET status = 'processed' WHERE id = 1; 

Compare the WAL generated by this statement against an equivalent jsonb_set update on the payload itself. The difference is dramatic once the payload is large.

7. Production patterns and edge cases

The jsonb_set rewrite cost in detail

UPDATE events SET payload = jsonb_set(payload, '{status}', '"processed"'::jsonb) WHERE id = 1; 

This expression must:

  1. Read and detoast the existing JSONB value.
  2. Parse it into the binary JSONB representation.
  3. Locate the target path.
  4. Construct a new binary representation.
  5. Recompress it.
  6. Write a new TOAST value and log it to WAL.

The cost is proportional to document size, not to the size of the change.

Bounded JSONB growth

If a JSONB column accumulates unbounded data, cap it. PostgreSQL 15+ offers a neat trick using generated columns:

ALTER TABLE events ADD COLUMN payload_size integer GENERATED ALWAYS AS (pg_column_size(payload)) STORED; CREATE INDEX events_payload_size_idx ON events (payload_size) Source:https://dev.to/tamizuddin/jsonb-at-scale-taming-postgresql-toast-churn-and-wal-bloat-on-high-write-workloads-46md

Related posts