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:
- 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_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:
- 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 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:
- Read and detoast the existing JSONB value.
- Parse it into the binary JSONB representation.
- Locate the target path.
- Construct a new binary representation.
- Recompress it.
- 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)
WHERE payload_size > 100000;
Use this index to find outliers and alert on them, or to drive a cleanup job that moves oversized payloads to cold storage.
Partitioning for retention
Time-based partitioning combined with DROP TABLE gives O(1) retention without vacuum pressure:
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Dropping an old partition removes its TOAST data immediately, without the dead-tuple cleanup cost of a mass DELETE.
When JSONB is genuinely the wrong choice
If you find yourself constantly extracting the same fields, querying JSONB paths in hot paths, or rewriting documents to update a single attribute, the data is relational. Normalize it.
JSONB shines when the shape is genuinely variable, reads are more common than writes, and the document is mostly immutable once written. It struggles when the shape is stable but unknown to you, when writes are frequent, and when documents grow without bound.
Connection-level settings that help
For bulk ingestion, consider raising synchronous_commit temporarily for non-critical writes:
SET synchronous_commit = off;
-- bulk load
-- reset per connection
RESET synchronous_commit;
This reduces WAL flush frequency. It trades durability on commit for throughput, so use it only for data you can regenerate or reconcile.
8. Frequently Asked Questions
Does STORAGE EXTERNAL always reduce WAL?
Not always. It eliminates the CPU cost of compression, which often lets the system write more rows per second and therefore generate more total WAL. It reduces per-row CPU and latency, but total WAL can rise if throughput increases substantially. The correct metric to track is bytes of WAL per logical operation, not total WAL alone.
Should I run VACUUM FULL to apply a storage mode change?
VACUUM FULL rewrites the table and its TOAST data, applying the new storage mode, but it locks the table exclusively. For large production tables, use pg_repack, which does the same work with a much shorter lock window, or accept that new storage modes apply only to newly written rows.
Is there a way to avoid TOAST entirely for JSONB?\n
Only by keeping values small enough to fit inline. If your JSONB payloads exceed a few kilobytes, TOAST is unavoidable. The realistic options are to reduce the value size, split hot and cold fields, or reduce write frequency.
How do I know if my TOAST table is bloated?
Compare pg_relation_size(reltoastrelid) against the sum of live chunk sizes. A large difference, or a TOAST table that keeps growing while the parent table's row count stays flat, indicates dead chunks that vacuum has not yet reclaimed. Check pg_stat_user_tables for the parent and run VACUUM on the TOAST relation explicitly if needed.
Top comments (0)