How to Gauge WAL Volume and What Bigger Checkpoints Really Cost
A reader recently underscored a clever way to track WAL output: using pg_current_wal_insert_lsn() in a query like SELECT pg_size_pretty(pg_current_wal_insert_lsn() - '0/00000000'::pg_lsn); to calculate total bytes generated. The suggestion is that monitoring systems should watch this value—or at least its rate—to stay ahead of storage growth.
Tuning, Not Restarting
The feedback also clarifies that altering max_wal_size or synchronous_commit only requires a reload, not a full restart. That’s a simple win for anyone tweaking settings on the fly.
The Crash Recovery Trade-off
More important, the reader highlights a subtle risk in aggressive WAL limits. Setting max_wal_size = 150GB with checkpoint_timeout = 1d doesn’t just delay checkpoints—it means a checkpoint triggers only after 150GB of WAL accumulates or a day passes, whichever hits first. In a crash, PostgreSQL must replay all that WAL (up to 150GB or a full day of transactions) before accepting connections, extending downtime. That’s a crucial caveat for any DBA tempted to stretch those numbers.
