The Starting Point

A few weeks ago, Stormatics was brought in to work with a fast-growing social media platform whose PostgreSQL database had grown significantly, and performance bottlenecks were beginning to affect business operations. The engagement kicked off the way every good engagement should: not with assumptions, but with questions. Before touching a single setting, we walked through a detailed list covering connection handling, autovacuum behavior, storage bloat, application code patterns, query performance, replication health, and infrastructure planning because a database this size rarely has one problem. It has a dozen small ones compounding into a big one.

That instinct turned out to be exactly right. The headline finding, and the one with the largest single projected impact, was that the database was generating approximately 1.8 TB of write-ahead log (WAL) per day. For context, that’s not just a storage cost; it directly worsens replication lag, lengthens backup windows, and adds infrastructure overhead. Reducing it was a stated priority from day one.

What follows is the story of how we found the root cause, why it wasn’t as simple as “turn one knob,” and the full toolkit of fixes, some large, some small, that we applied along the way, each backed by real data. 

Finding the Root Cause, Not Guessing at It

The instinct to ask “where is the WAL actually coming from” before proposing anything is what separates a real diagnosis from a guess. We used PostgreSQL’s own internal statistics views, pg_stat_wal and pg_stat_bgwriter, rather than assumptions, and the numbers told a clear story:

Full-page image share of total WAL:

SELECT ROUND(100.0 * wal_fpi / NULLIF(wal_records, 0), 2) AS fpi_pct_of_records

FROM pg_stat_wal;

Checkpoint trigger breakdown, timer vs. volume:

SELECT

    ROUND(100.0 * checkpoints_req

        / NULLIF(checkpoints_timed + checkpoints_req, 0), 2)

        AS pct_volume_based,

    ROUND(100.0 * checkpoints_timed

        / NULLIF(checkpoints_timed + checkpoints_req, 0), 2)

        AS pct_time_based

FROM pg_stat_bgwriter;

39% of all WAL records were full-page images. Every time a data page is touched for the first time after a checkpoint, PostgreSQL writes a full copy of that page to WAL as a crash-safety measure, not just the small change: the entire page. This is normal, necessary behavior. The problem was frequency: checkpoints were firing on a fixed 5-minute timer, confirmed via pg_stat_bgwriter to be timer-driven 86.3% of the time, far more often than the actual write volume justified. Every checkpoint reset that “insurance copy” mechanism, meaning the same busy pages were paying the full-page-copy cost over and over, every five minutes, regardless of how much had actually changed.

We recommended shifting the checkpoint trigger from a fixed timer to a volume-based one (max_wal_size), so checkpoints would fire based on how much data had genuinely changed, not on a clock. But a recommendation without evidence isn’t worth much, so before proposing it, we built a workload-normalized comparison: measuring WAL generated per row change, not raw daily totals, specifically to control for the natural swings in traffic between quiet and busy periods. That analysis projected a 25.25% reduction in total WAL generation from this single change. It’s the largest lever in the entire engagement, and, like any responsible recommendation involving a real availability tradeoff (longer checkpoint intervals mean more WAL to replay if the database ever crashes), the final decision on when to implement it rests with the client.

The Rest of the Toolkit

WAL reduction was never going to come from one lever alone. Several other issues, each real and independently confirmed, contributed to the total picture.

Autovacuum Tuning

On the two highest-written tables in the schema, dead-tuple accumulation had crept into double digit percentages of live rows, meaning a meaningful share of every table scan was wading through rows that no longer mattered. We tightened the autovacuum_vacuum_scale_factor on both, and the measured result was dramatic: dead-tuple percentage dropped from double digits to under 2% on both tables, even as both continued to grow in absolute size. This is the kind of fix that’s easy to overlook precisely because it works quietly in the background, until it doesn’t.

Dead-tuple percentage, checked before and after tuning:

SELECT relname,n_live_tup,n_dead_tup,

ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct

FROM pg_stat_user_tables

WHERE relname ILIKE ANY (ARRAY['events','user_sessions','idx_events_created_at']);

Fillfactor, Calculated Rather Than Guessed 

PostgreSQL’s HOT (Heap-Only Tuple) update mechanism lets a row be updated in place, without touching a single index, if there’s free space reserved on the page. By default, PostgreSQL packs pages completely full, leaving zero room for this optimization. We derived the correct fillfactor value per table from actual average row size and page geometry, not a generic industry rule of thumb, and applied it to the highest-churn tables. The measured HOT-update ratio improved meaningfully on both tables i.e., SocialEvent increased from 35% to 54.25% and GlobalEvent increased from 48% to 60.69%, directly reducing the WAL cost of every subsequent write.

HOT-update ratio, checked before and after applying fillfactor:

SELECT relname,n_tup_upd,n_tup_hot_upd,

       ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2)

           AS hot_update_pct

FROM pg_stat_user_tables

WHERE relname ILIKE ANY (ARRAY['events','user_sessions','idx_events_created_at']);

Buffer Pool Diagnostics

This one wasn’t on anyone’s original list; we found it while investigating a different question. Using pg_stat_bgwriter, we traced that 61% of all dirty-page writes were being forced onto live application queries instead of being handled smoothly by PostgreSQL’s background writer process. The root cause: bgwriter_lru_maxpages, the setting controlling how many pages the background writer can clean per cycle, had never been tuned beyond its default, leaving it with a cleaning capacity far below the instance’s actual write volume. We calculated a specific recommendation, raising it roughly 5x, directly from the measured gap, rather than picking an arbitrary number.

Share of dirty-page writes forced onto live queries instead of the background writer:

Share of dirty-page writes forced onto live queries instead of the background writer:

SELECT buffers_clean,

       buffers_backend,

       buffers_checkpoint,

       ROUND(100.0 * buffers_backend

        / NULLIF(buffers_backend + buffers_checkpoint + buffers_clean, 0),2)

 AS pct_backend_forced

FROM pg_stat_bgwriter;

Application-level query patterns. We identified several concrete, fixable issues in the application layer:

• Unconditional UPDATE statements. Several UPDATE calls fired on every request regardless of whether the underlying data had actually changed, generating pure, avoidable WAL for no-op writes.
• Unbatched single-row updates. A number of update patterns hit the database one row at a time when they could be safely batched into far fewer round trips.
• A data-type mismatch. One bulk update query was failing outright due to a type mismatch between the application layer and the schema.

Each of these was traced back to real, reproducible query patterns, not inferred, but confirmed against actual pg_stat_statements data and, where the picture wasn’t yet clear, verified against query logs requested directly from the client.

A no-op guard added to an update that was previously unconditional:

-- Example: guarding an UPDATE so it only writes when a value

-- has genuinely changed, instead of firing unconditionally

UPDATE "events"

SET "username" = $1, "location" = $2

WHERE "events"."genre" = $3

  AND array(SELECT unnest("type") ORDER BY 1)

      IS DISTINCT FROM array(SELECT unnest($4::text[]) ORDER BY 1)

RETURNING "username", "location", "id";

An Honest Story About Getting It Right

Not every finding in this engagement was resolved neatly, and that’s worth talking about, because the willingness to say “we’re not sure yet” is part of doing this work responsibly.

Early on, we identified a 140GB index that appeared, by every metric available to us, to be essentially unused, with a single scan recorded against millions of writes required to maintain it. Removing an unused index of that size would have been a meaningful, low-risk win.

The scan count that first flagged this index as a drop candidate:

SELECT indexrelname,

       idx_scan,

       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE indexrelname ILIKE ANY (ARRAY['%location_idx%']);

Before acting on it, though, the client's own team reported seeing significantly higher usage from their side, attributing it to search traffic on a separate node. We checked the primary database's live replication connections directly, twice, independently:

Confirming (or ruling out) a physical replica that could explain the usage gap:

SELECT client_addr, application_name, state, sync_state

FROM pg_stat_replication;

That check didn’t show a physical replica that could account for the usage the client described. Rather than force a conclusion in either direction on a production index of that size, we held the decision open. Sometimes the most useful thing a consultant can do is refuse to manufacture certainty that isn’t there yet, and keep investigating instead.

A similar moment happened with an early finding about application update patterns. Initial analysis of change-data-capture logs suggested one high-traffic table was rewriting every single column on every update, regardless of what had actually changed, which made for a compelling, high-impact story. On closer verification against the database’s own query statistics, that specific claim didn’t hold up under scrutiny: the underlying data source turned out to lack the “before” snapshot needed to make that comparison reliably, and the real query patterns told a different, more nuanced story. We corrected the finding, in writing, before it went any further. A wrong diagnosis presented confidently is worse than an uncertain one presented honestly, and catching your own mistake before a client does is exactly what good diagnostic work is supposed to look like.

Closing Thought

None of the individual fixes described here: autovacuum tuning, fillfactor calculation, checkpoint interval adjustment, buffer pool sizing, and application query patterns; would have solved the underlying problem alone. What mattered was treating WAL generation as a system, not a single dial to turn: measuring before recommending, validating projections with workload-normalized data rather than raw totals, and being willing to say “we don’t know yet” when the evidence didn’t fully agree. That combination, rigor plus honesty about uncertainty, is what actually moves a database from “generating almost two terabytes of WAL a day” toward a defensible, well-understood, and improving state.

If your team is facing similar symptoms: runaway WAL growth, replication lag, or a database that’s simply outgrown its original tuning, this is exactly the kind of systematic diagnostic work Stormatics does.