Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
Glad you found it helpful! That mental shift - from viewing JSONB purely 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?
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
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 with pg_wal_lsn_diff(), alongside EXPLAIN (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.
I measure AI coding claims in public. LGTM β Let's Get the Measurements: real runs, real numbers, receipts. Every article is an experiment I actually ran. Books & packs: aheadkit.gumroad.com
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.
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
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 JSONB payload 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 on UPDATE while adding a second INSERT plus primary-key index write upfront and a JOIN on 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!
I measure AI coding claims in public. LGTM β Let's Get the Measurements: real runs, real numbers, receipts. Every article is an experiment I actually ran. Books & packs: aheadkit.gumroad.com
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?
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
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 JSONB value 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 extra INSERT and primary-key index write upfront.
I measure AI coding claims in public. LGTM β Let's Get the Measurements: real runs, real numbers, receipts. Every article is an experiment I actually ran. Books & packs: aheadkit.gumroad.com
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?
Solo developer building practical CLI tools for Linux auditing and B2B data extraction. Creator of Vaultcheck and NPILift. I write about silent failures, false green lights, and infrastructure reliabi
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.
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
That is a great diagnostic catch. When the JSONB column isn't indexed, pg_stat_user_tables can show a deceptively healthy hot_pct on the main heap while out-of-line TOAST rewrites still quietly inflate WAL and IOPS. Pairing that HOT ratio check with WAL or pg_toast growth makes it immediately clear when pulling out hot scalar columns will pay off. Thanks for sharing that query!
Solo developer building practical CLI tools for Linux auditing and B2B data extraction. Creator of Vaultcheck and NPILift. I write about silent failures, false green lights, and infrastructure reliabi
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.
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
Querying reltoastrelid straight from pg_class is super clean and gives an instant read on whether TOAST is quietly taking the brunt of the writes. Watching toast climb while heap stays flat is the clearest signal you can get before committing to a schema change. Thanks for adding that sharp query to the thread!
Solo developer building practical CLI tools for Linux auditing and B2B data extraction. Creator of Vaultcheck and NPILift. I write about silent failures, false green lights, and infrastructure reliabi
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?
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
Yes, idx_events_jsonb_gin was 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 the UPDATE target 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?
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
Once the UPDATE target 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 the JSONB column 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.
Freelance fullstack developer creating fast, modern, and refined applications. I focus on simplicity, performance, and real value for users. I also write tech-related articles here.
Spot on. That distinction, read optimization versus write optimization, sums it up perfectly. A lot of teams reach for GENERATED ALWAYS AS STORED hoping to fix slow queries without touching their UPDATE statements, only to wonder why WAL volume and IOPS stay completely flat. Leaving the JSONB column untouched on the write path is what actually lets PostgreSQL reuse those TOAST pointers. Appreciate you highlighting that nuance.
Full-Stack Python Engineer & Technical Author. I build AI-powered backends with FastAPI and write deep-dive tutorials on LLMs and automation. Published on SitePoint.
Location
Umuaka, Imo State, Nigeria.
Education
Ekwe Secondary School | WAEC and NECO
Pronouns
He/Him
Work
Founder & Project Manager at Geminio OS | Technical Writer
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!
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.