PostgreSQL write performance is a path, not a single counter. Inserts, updates and deletes create table and index changes, WAL, checkpoints and eventually cleanup work. A problem may surface as write latency, growing table bloat, an overworked autovacuum worker, replication lag or storage pressure long after the original application change.
Separate foreground latency from background work
Compare transaction latency and commit rate with WAL generation, checkpoint behaviour, disk activity, dead tuples and autovacuum progress. A write burst can be normal, but a sustained pattern with rising latency or cleanup debt needs an explanation. Long transactions are especially important because they can prevent old row versions from being removed, even while autovacuum is running.
Investigate in this order
- Find the tables and statements creating the changed write volume.
- Check transaction duration and idle-in-transaction sessions.
- Review dead-tuple growth, autovacuum timing and configured thresholds.
- Compare storage latency and IOPS with checkpoint and WAL activity.
- Check replica and replication-slot behaviour where WAL retention is growing.
Tune with a workload hypothesis
Autovacuum settings, checkpoint settings and storage capacity should follow measured table churn and latency requirements. A global change based on one table can make another workload worse. Prefer targeted table settings, safe transaction design and query changes where evidence supports them.
Recheck the system through at least one normal workload cycle. Write paths often have delayed effects, so a quick improvement immediately after a restart is not sufficient proof.
Find which tables are falling behind
Start with estimated dead tuples, table activity and recent vacuum timestamps. These are clues, not a precise measurement of physical bloat. Compare repeated samples and table size before selecting a maintenance action.
SELECT relname, n_live_tup, n_dead_tup,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Check long transactions and vacuum progress next. A busy table with many dead tuples and an old transaction requires different treatment from a quiet table whose threshold has not triggered. PostgreSQL statistics views change between major releases; for example, checkpoint metrics have moved into a separate view in newer versions. Use documentation matching the installed version.
Example: disk use grows after a downstream outage
A logical replication consumer stops reading while the primary keeps accepting writes. A replication slot can retain WAL needed by that consumer. Increasing vacuum frequency does not resolve this retention requirement. Inspect slot activity and retained WAL, the downstream error and the recovery plan. Database table size and WAL storage should be plotted separately so this failure does not look like unexplained table growth.
Do not drop a slot simply because it owns retained data. The consumer may require reinitialisation once its recovery position is lost. Coordinate the recovery choice with its owner, and define a retention or capacity safeguard appropriate to the acceptable outage window. Space relief without a working replication path is only partial recovery.
Why a normal vacuum may not shrink the disk
Ordinary VACUUM generally makes space reusable within the relation rather than returning all of it to the filesystem. VACUUM FULL rewrites the table and requires a stronger lock and additional resources. Treat it as planned maintenance with a verified space and downtime budget, not a routine first response to dead tuples.
For a write-latency fix, observe an entire batch and cleanup cycle. Measure transaction latency, WAL generation, replica freshness and vacuum progress after the change. A smaller batch may improve lock duration but increase commit frequency; an index removed to reduce write cost may slow important reads. Validate those tradeoffs against the service's actual workload.
Keep the evidence for the next incident
Mini DBA PostgreSQL monitoring provides query and metric history alongside session, lock, vacuum and WAL diagnostics, helping teams compare an incident with normal operation. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading