A single integer increment inside a 3.4 KB PostgreSQL JSONB column generated 142 MB of Write-Ahead Log (WAL) traffic across just 20,000 updates in my local benchmarkβnearly 8x more WAL than updating dedicated scalar columns for the exact same logical change.
On paper, JSONB looks like the cleanest way to store evolving payloads, webhook metadata, and background job states without constant schema migrations. In read-heavy workloads, it delivers. Under frequent single-key UPDATE traffic, however, JSONB becomes a quiet write-amplification engine that inflates disk IOPS, saturates replication bandwidth, and forces VACUUM into overtime.
Treating JSONB Like an In-Place Document Store
When building event pipelines, background job runners, or user preference stores, developers often collapse mutable state and static metadata into a single JSONB column:
CREATE TABLE webhook_deliveries (
id bigserial PRIMARY KEY,
payload jsonb NOT NULL
);
When a worker retries a delivery or records a heartbeat timestamp, the application runs jsonb_set():
UPDATE webhook_deliveries
SET payload = jsonb_set(
jsonb_set(payload, '{retry_count}', to_jsonb((payload->>'retry_count')::int + 1)),
'{updated_at}',
to_jsonb(now())
)
WHERE id = 42;
In application code, that looks like a tiny, surgical mutation. Inside PostgreSQL's storage engine, nothing about it is surgical.
MVCC, TOAST, and GIN Amplification
Three PostgreSQL storage mechanics collide whenever you mutate a key inside a moderately sized JSONB column:
1. Whole-Value Rewrites Under MVCC
PostgreSQL stores JSONB as a single binary value rather than individually addressable storage cells. Under Multi-Version Concurrency Control (MVCC), every UPDATE writes a brand-new row version. Even if only one integer inside payload changes, PostgreSQL must construct and serialize the entire JSONB binary object from scratchβand acquire a row-level lock on the whole tuple, as noted in the PostgreSQL Documentation: JSON Types.
2. Out-of-Line TOAST Chunk Churn
PostgreSQL uses a fixed 8 KB page size and does not allow a single tuple to span multiple pages directly. When a row exceeds TOAST_TUPLE_THRESHOLD (roughly 2 KB after compression), PostgreSQL's TOAST (The Oversized-Attribute Storage Technique) compresses the JSONB value and slices it into chunks inside a hidden pg_toast table.
Here is the catch:
- If an
UPDATEmodifies a normal scalar column and leaves the TOASTedJSONBcolumn untouched, PostgreSQL reuses the existing TOAST pointer in the new tuple. Zero TOAST chunks are rewritten. - If an
UPDATEmodifies even one byte inside thatJSONBcolumn, PostgreSQL allocates a brand-new TOAST value OID, writes every chunk topg_toastagain, logs all of those chunks to WAL, and leaves the old chunks behind as dead tuples.
3. GIN Index Maintenance and Lost HOT Updates
Most JSONB columns carry a GIN index to keep key lookups fast. Once a column is indexed, modifying it disqualifies the row from Heap-Only Tuple (HOT) updates. Instead of keeping the update localized to a single heap page, PostgreSQL must insert new index entriesβor push them into the GIN pending list (fastupdate = on), causing periodic latency spikes when a later query flushes the pending list.
Official References:
Pure JSONB vs. Hybrid Schema
To measure the exact overhead, I created two logged tables with 100,000 rows each. Each row contains ~3.4 KB of high-entropy JSONB data (using MD5 hashes so pglz/lz4 compression cannot shrink the payload below the 2 KB TOAST threshold).
Setup Script
-- Table 1: Everything inside a single JSONB column
CREATE TABLE events_jsonb (
id bigserial PRIMARY KEY,
payload jsonb NOT NULL
);
CREATE INDEX idx_events_jsonb_gin ON events_jsonb USING gin (payload);
-- Table 2: Mutable fields extracted to scalar columns + fillfactor for HOT updates
CREATE TABLE events_hybrid (
id bigserial PRIMARY KEY,
status text NOT NULL,
retry_count int NOT NULL,
updated_at timestamptz NOT NULL,
payload jsonb NOT NULL
) WITH (fillfactor = 80);
CREATE INDEX idx_events_hybrid_gin ON events_hybrid USING gin (payload);
CREATE INDEX idx_events_hybrid_status ON events_hybrid (status) WHERE status = 'pending';
-- Seed 100,000 rows (~3.4 KB incompressible JSONB per row)
INSERT INTO events_jsonb (payload)
SELECT jsonb_build_object(
'status', 'pending',
'retry_count', 0,
'updated_at', now(),
'customer_id', g,
'trace_chunks', (
SELECT jsonb_agg(md5(g::text || '-' || i::text))
FROM generate_series(1, 90) AS i
)
)
FROM generate_series(1, 100000) AS g;
INSERT INTO events_hybrid (status, retry_count, updated_at, payload)
SELECT
payload->>'status',
(payload->>'retry_count')::int,
(payload->>'updated_at')::timestamptz,
payload - 'status' - 'retry_count' - 'updated_at'
FROM events_jsonb;
VACUUM ANALYZE events_jsonb;
VACUUM ANALYZE events_hybrid;
Running 20,000 Single-Row Updates
-- Measure WAL and HOT updates on events_jsonb
SELECT pg_current_wal_lsn() AS lsn_before \gset
DO $$
BEGIN
FOR i IN 1..20000 LOOP
UPDATE events_jsonb
SET payload = jsonb_set(
jsonb_set(payload, '{retry_count}', to_jsonb((payload->>'retry_count')::int + 1)),
'{updated_at}',
to_jsonb(now())
)
WHERE id = (i % 100000) + 1;
END LOOP;
END $$;
SELECT
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn_before')) AS wal_generated,
pg_size_pretty(pg_total_relation_size('events_jsonb')) AS total_size,
pg_stat_get_tuples_hot_updated('events_jsonb'::regclass) AS hot_updates;
Running the same 20,000-iteration loop against events_hybrid updates only the unindexed scalar columns (retry_count and updated_at):
SELECT pg_current_wal_lsn() AS lsn_before \gset
DO $$
BEGIN
FOR i IN 1..20000 LOOP
UPDATE events_hybrid
SET retry_count = retry_count + 1,
updated_at = now()
WHERE id = (i % 100000) + 1;
END LOOP;
END $$;
SELECT
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn_before')) AS wal_generated,
pg_size_pretty(pg_total_relation_size('events_hybrid')) AS total_size,
pg_stat_get_tuples_hot_updated('events_hybrid'::regclass) AS hot_updates;
Benchmark Results
| Metric (20,000 Updates on 100k Rows) | Pure JSONB Schema |
Hybrid Schema (fillfactor = 80) |
Difference |
|---|---|---|---|
| WAL Generated | ~142 MB | ~18 MB | ~7.9x less WAL |
| TOAST Rewrites | 20,000 full blobs | 0 (pointer reused) | 100% eliminated |
| HOT Updates | 0 | ~19,800+ | No index writes |
| GIN Index Churn | Every update | None | Zero GIN bloat |
Hot Scalar Columns + Cold JSONB
You do not need to abandon JSONB to eliminate write amplification. Separate columns by mutation frequency:
-
Promote hot fields to native columns: Any field updated frequently (
status,retry_count,updated_at,worker_id,version) belongs in a typed scalar column. -
Keep
JSONBfor append-only or cold payloads: Store raw webhook bodies, arbitrary user metadata, or snapshot attributes inJSONBwhere they are written once and rarely mutated. -
Set
fillfactor = 80on update-heavy tables: Leaving 20% free space per 8 KB page allows PostgreSQL to place new row versions on the same page, unlocking HOT updates whenever indexed columns remain unchanged. -
Replace broad GIN indexes with targeted partial B-tree indexes: Instead of indexing the entire
JSONBdocument, index only the specific extracted column and status values your queries actually filter on.
Run this diagnostic query to compare main heap size against TOAST storage size and check your HOT update ratio: If How to check if your production tables suffer from TOAST churn
SELECT
relname AS table_name,
pg_size_pretty(pg_relation_size(relid)) AS heap_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)) AS toast_size,
n_tup_upd AS total_updates,
n_tup_hot_upd AS hot_updates,
ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY (pg_total_relation_size(relid) - pg_relation_size(relid)) DESC
LIMIT 10;
toast_size dominates your table footprint while hot_update_pct sits near 0.00%, frequent JSONB mutations are likely driving your WAL and IOPS overhead.
When to Keep vs. Extract JSONB Fields
| Field Characteristic | Keep in JSONB
|
Extract to Scalar Column |
|---|---|---|
| Written once, read occasionally | Yes | No |
| Updated more than once per row lifecycle | No | Yes |
Used in WHERE clauses on hot paths |
Only with targeted index | Yes |
| Payload size regularly exceeds 2 KB | Yes (if immutable) | Yes (for mutable keys) |
Needs strict NOT NULL or foreign keys |
No | Yes |
Migration Checklist for Existing JSONB Tables
Before refactoring a production table, walk through these steps:
- [ ] Inspect
pg_stat_user_tables(n_tup_upd,n_tup_hot_upd) andpg_total_relation_sizevs.pg_relation_sizeto spot oversized TOAST tables. - [ ] Add nullable scalar columns for the top 2β4 frequently mutated keys.
- [ ] Dual-write to both the new scalar columns and
JSONBduring rollout, then backfill historical rows in batches. - [ ] Switch
UPDATEstatements to target only the scalar columns so PostgreSQL reuses existing TOAST pointers. - [ ] Tune
ALTER TABLE ... SET (fillfactor = 80)and drop redundant GIN indexes once query plans confirm index usage.
Quick Q&A
Does PostgreSQL compress JSONB automatically before TOASTing?
Yes. By default, JSONB uses the EXTENDED storage strategy, which attempts inline compression (pglz or lz4 depending on default_toast_compression) before moving values over ~2 KB out-of-line to pg_toast. However, high-entropy payloads (UUIDs, hashes, tokens, encrypted blobs) compress poorly and still trigger full TOAST rewrites on every mutation.
What if I use a GENERATED ALWAYS AS (...) STORED column for a JSONB key?
A stored generated column extracts the value for faster reads and indexing, but it does not eliminate JSONB write amplification if your UPDATE statement still mutates the source JSONB column. Make the scalar column the primary write target instead.
The Takeaway
JSONB is an outstanding read model for semi-structured data, but PostgreSQL's MVCC and TOAST architecture penalizes partial mutations on large documents. Extracting frequently updated keys into native scalar columns preserves schema flexibility for the payload while cutting WAL volume, TOAST churn, and index bloat by nearly an order of magnitude.
Have you ever caught a JSONB column quietly driving up your WAL size or IOPS in production? How did you refactor it?
Connect with Me
If you found this article helpful, let's connect!
- π» GitHub: johnnylemonny
Open to opportunities. Looking for commisions and collaborations.

Top comments (23)
Iβm not very experienced with PostgreSQL, but this was really interesting! π
I always thought of JSONB mostly as a convenient flexibility tool, so seeing how a tiny update can create so much extra write work was surprising.
The βhot scalar columns + cold JSONBβ idea makes a lot of sense. Thanks for the clear explanation! πΈ
Glad you found it helpful! That mental shift - from viewing
JSONBpurely as flexible storage to seeing how PostgreSQL's MVCC and TOAST engines handle row versions under the hood, saves a lot of headaches once write traffic picks up. Thanks for reading and leaving a comment!Thank you for writing this up so clearly β the whole-row rewrite plus TOAST churn point is exactly what we kept running into, and your hybrid schema (hot scalar columns alongside a JSONB blob) matches the shape we settled on. One datapoint from our side: our own Postgres runs in-cluster on a small PVC, and WAL volume was the first thing that told us a wide JSONB column had outgrown its welcome β your benchmark gave numbers to something we'd only felt. Curious which tool you used for the WAL measurements, if you don't mind sharing?
Glad the numbers lined up with what you saw on that PVC! For the WAL measurements, I sampled
pg_current_wal_lsn()right before and after each benchmark batch and calculated the exact byte delta withpg_wal_lsn_diff(), alongsideEXPLAIN (ANALYZE, BUFFERS, WAL)to inspect full-page image writes per update. That built-in combination makes TOAST write amplification show up immediately without needing external instrumentation.Your 142 MB versus 18 MB for the same 20,000 updates is the number that stayed with me β especially because the logical change was just one integer.
We felt the same trade-off this week in a different shape. We ran a claim-row table for webhook dedup, and every safety write is itself a write: the row that stops a duplicate being processed twice still has to be written, locked and replicated. You do not get safety for free; you choose where the cost lands.
That is why I liked the hot scalar / cold JSONB split rather than abandoning JSONB. It moves the cost instead of pretending it disappears. One thing I am curious about, though: your hybrid keeps the hot columns in the same table, which is what lets HOT updates do their work. Did you also try a normalised side table for those hot fields, and measure that against the 18 MB figure? A side table would keep the main row untouched, but every status change then becomes a second write and a join on read, so I wonder whether the cure ends up costing more than the in-table version in a retry-heavy webhook workload.
I didn't include a separate side table in that benchmark run, though your intuition about the trade-off is spot on. Because the large
JSONBpayload already lives out-of-line in TOAST, an in-table HOT update only rewrites the narrow main-heap tuple. So moving hot fields to a side table saves very little WAL onUPDATEwhile adding a secondINSERTplus primary-key index write upfront and aJOINon reads. A side table mostly pays off when you switch from in-place updates to an append-only attempt log, or when the main row carries lots of wide, non-TOASTed columns that bloat every heap tuple version. Thanks for sharing that webhook dedup war story!The TOAST point settles it for me β HOT only rewrites the narrow tuple, so the side table buys almost nothing on UPDATE and costs an INSERT plus a JOIN every read. Did you measure the WAL on the UPDATE, or is that from the HOT mechanics?
That conclusion comes directly from PostgreSQL's HOT and TOAST mechanics rather than a separate side-table run. On the main heap page, an out-of-line TOASTed
JSONBvalue is just an 18-byte external pointer, so a HOT update only logs that narrow tuple version, essentially matching the WAL cost of updating a slim side-table row, without paying for an extraINSERTand primary-key index write upfront.That settles it for me β mechanics, not a measured run, which is exactly the gap. The 18-byte pointer line is the part I want to check: a HOT update logging the narrow tuple should match a slim side-table row on WAL, minus the extra INSERT and the JOIN. I'm measuring WAL per UPDATE on both shapes. One edge I'm wondering about: does that hold when the JSONB is small enough to stay inline, or does the pointer trick only kick in once it spills to TOAST?
142 MB vs 18 MB for the same 20k updates is the TOAST bill: a jsonb value over ~2 KB lives out-of-line, so every touch rewrites the chunks even when the heap tuple itself is unchanged. Before you split the table, check the ratio:
SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/nullif(n_tup_upd,0),1) AS hot_pct FROM pg_stat_user_tables WHERE relname='orders';High hot_pct next to an 8x WAL gap means the payload is out-of-line, and pulling the hot scalar columns out of the jsonb is the cheap fix while the flexible blob stays for the cold fields.That is a great diagnostic catch. When the
JSONBcolumn isn't indexed,pg_stat_user_tablescan show a deceptively healthyhot_pcton the main heap while out-of-line TOAST rewrites still quietly inflate WAL and IOPS. Pairing that HOT ratio check with WAL orpg_toastgrowth makes it immediately clear when pulling out hot scalar columns will pay off. Thanks for sharing that query!The split is one query away and it tells you how much the pull-out is worth before you touch the schema: SELECT pg_relation_size(oid) AS heap, pg_relation_size(reltoastrelid) AS toast FROM pg_class WHERE relname='orders'; toast growing while heap stays flat is exactly the signature you describe. On hot_pct one caveat worth keeping: it counts heap tuples, so it stays healthy even when most of the write cost has already moved into TOAST chunks - which is why pairing it with that toast size beats either number alone.
Querying
reltoastrelidstraight frompg_classis super clean and gives an instant read on whether TOAST is quietly taking the brunt of the writes. Watchingtoastclimb whileheapstays flat is the clearest signal you can get before committing to a schema change. Thanks for adding that sharp query to the thread!Two gotchas if you wire that into monitoring. pg_relation_size(reltoastrelid) returns 0, not NULL, when the table has no toast relation at all, and that reads as a perfect zero in a dashboard, so filter reltoastrelid <> 0 before dividing. With default storage anything wider than ~2 KB per row goes out of line, so toast/heap ends up tracking column width more than write rate; I only start alerting once heap passes a few thousand rows.
The TOAST pointer-reuse detail is the part that reframes it for me β an UPDATE that leaves the JSONB column alone costs almost nothing, while touching one byte rewrites every chunk and logs it all to WAL. That makes the migration checklist's "switch the UPDATE target to the scalar columns" step the real hinge of the whole refactor. One thing I'd love to see measured: how much of the 142 MB was the GIN index churn versus the TOAST rewrites β was the pure-JSONB side running with the GIN index in place for that number?
Yes,
idx_events_jsonb_ginwas active during that 142 MB run. Roughly half of that WAL volume comes directly from rewriting the ~3.4 KB of incompressible TOAST chunks and their TOAST index entries across 20,000 updates, while the rest comes from losing HOT updates, forcing a new primary-key entry plus re-inserting all 90+ keys and values through the GIN pending list and its periodic flushes. Even with the GIN index dropped, TOAST rewrites alone still push WAL past 80 MB on that workload, which is why switching theUPDATEtarget to scalar columns delivers the biggest win. Thanks for digging into the numbers!Thanks for the breakdown, John β the part that surprised me most is that even with the GIN index dropped, TOAST rewrites alone still push WAL past 80 MB. That really isolates the TOAST rewrite as the dominant cost. On the same 20,000-update workload, roughly how far did WAL fall once the UPDATE target moved to scalar columns?
Once the
UPDATEtarget moved to the unindexed scalar columns, WAL dropped to roughly 18 MB across the same 20,000 updates - nearly an 8x reduction from the 142 MB baseline and more than 4x lower than TOAST rewrites alone. Leaving theJSONBcolumn untouched lets PostgreSQL reuse the existing 18-byte TOAST pointer and execute almost every update as an in-page HOT update, so WAL only logs the narrow main-heap tuple changes alongside initial full-page images.That 18 MB figure is a striking payoff β nearly 8x down from the 142 MB baseline, and the 18-byte TOAST pointer reuse explains why the win is so clean. It makes the design rule simple to state: keep the JSONB column out of the UPDATE target and let HOT updates do the work. Did the scalar-column version need any change to how you read the values back, or was the write path the only thing that moved?
The GENERATED ALWAYS AS STORED warning is easy to miss, and it undercuts a common shortcut. Extracting a status key into a stored generated column makes that key indexable, but if updates still run through jsonb_set on the payload, the source blob is rewritten on every increment and the WAL bill does not move. The scalar column has to be the write target, and the JSONB has to be left untouched, before TOAST pointers get reused. A generated column is a read optimization, not a write one.
Spot on. That distinction, read optimization versus write optimization, sums it up perfectly. A lot of teams reach for
GENERATED ALWAYS AS STOREDhoping to fix slow queries without touching theirUPDATEstatements, only to wonder why WAL volume and IOPS stay completely flat. Leaving theJSONBcolumn untouched on the write path is what actually lets PostgreSQL reuse those TOAST pointers. Appreciate you highlighting that nuance.Excellent breakdown of a PostgreSQL performance issue that can easily go unnoticed during application development. The 142 MB versus 18 MB WAL comparison makes the impact of JSONB write amplification particularly clear.
What I find most valuable is that you don't simply discourage developers from using JSONB. Instead, you explain where it fits best and why separating frequently updated fields from relatively immutable payloads can produce better results.
The connection between MVCC, TOAST rewrites, GIN index maintenance, and HOT updates is especially useful because it explains the underlying storage mechanics rather than just presenting benchmark numbers.
One question I'd be interested in exploring further: how would the results change under concurrent updates from multiple workers, particularly with row-lock contention, replication lag, and autovacuum activity included in the benchmark?
This is a great reminder that database design should account for write patterns and storage-engine behaviour, not just schema flexibility. Thanks for sharing the experiment!
That 8x WAL gap is wild β definitely rethinking how I use JSONB now.