a month ago
Hi all, I've been having an issue lately where sometimes my Postgres checkpoints will take a very long time to complete which locks the DB pool and any other processes waiting on the DB until it completes. For example earlier today, I had a checkpoint take almost 12 minutes while the typical checkpoint time is roughly 30-60 seconds for a checkpoint. All of the DB metrics seem to look healthy and I can't find anything on my end that would point to what the DB is doing for the 12 minutes of the checkpoint. Any ideas on what this could be?
Log: 2026-07-22 11:43:30.784 UTC [40] LOG: checkpoint complete: wrote 551 buffers (3.4%), wrote 4 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=1.511 s, sync=0.278 s, total=735.392 s; sync files=244, longest=0.027 s, average=0.002 s; distance=12846 kB, estimate=45071 kB; lsn=2D/A6FB45E8, redo lsn=2D/A6919C08
Thank you!
2 Replies
a month ago
This thread has been opened as a bounty so the community can help solve it.
Status changed to Open Railway • 30 days ago
a month ago
This usually happens when Postgres is waiting on something else during the checkpoint. The key clue in your log:
total=735.392 s ← 12+ minutes
write=1.511 s
sync=0.278 s
The write+sync is only ~2 seconds, so something is stalling the checkpoint.
Main suspects & fixes:
- WAL Archiving hanging
If archive_mode = on and archive_command is slow, checkpoints wait for WAL to archive.
Check: SELECT archived_count, failed_count FROM pg_stat_archiver;
Fix: Temporarily disable archiving to test:
ALTER SYSTEM SET archive_mode = off;
SELECT pg_reload_conf();
- Long-running transactions
Open transactions block WAL cleanup.
Check: SELECT pid, state, age(now(), xact_start) AS age, query
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY age DESC;
Fix: Commit or terminate long-running transactions.
- Tune checkpoint settings (reduce frequency & spread I/O):
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '1GB';
SELECT pg_reload_conf();
- Disk I/O contention
Check iostat/iotop for high latency. Move pg_wal to faster storage if possible.
- Autovacuum interference
Long vacuum operations can delay checkpoints.
Check: SELECT pid, query, state, age(now(), xact_start) AS age
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' AND state != 'idle';
Tune autovacuum to run more frequently with less impact:
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 1000
Start with #1 and #2 – most likely the cause.
zrpai
This usually happens when Postgres is waiting on something else during the checkpoint. The key clue in your log: total=735.392 s ← 12+ minutes write=1.511 s sync=0.278 s The write+sync is only ~2 seconds, so something is stalling the checkpoint. **Main suspects & fixes:** 1. WAL Archiving hanging If archive_mode = on and archive_command is slow, checkpoints wait for WAL to archive. Check: SELECT archived_count, failed_count FROM pg_stat_archiver; Fix: Temporarily disable archiving to test: ALTER SYSTEM SET archive_mode = off; SELECT pg_reload_conf(); 2. Long-running transactions Open transactions block WAL cleanup. Check: SELECT pid, state, age(now(), xact_start) AS age, query FROM pg_stat_activity WHERE state != 'idle' AND xact_start IS NOT NULL ORDER BY age DESC; Fix: Commit or terminate long-running transactions. 3. Tune checkpoint settings (reduce frequency & spread I/O): ALTER SYSTEM SET checkpoint_timeout = '15min'; ALTER SYSTEM SET checkpoint_completion_target = 0.9; ALTER SYSTEM SET max_wal_size = '2GB'; ALTER SYSTEM SET min_wal_size = '1GB'; SELECT pg_reload_conf(); 4. Disk I/O contention Check iostat/iotop for high latency. Move pg_wal to faster storage if possible. 5. Autovacuum interference Long vacuum operations can delay checkpoints. Check: SELECT pid, query, state, age(now(), xact_start) AS age FROM pg_stat_activity WHERE query LIKE '%VACUUM%' AND state != 'idle'; Tune autovacuum to run more frequently with less impact: autovacuum_vacuum_scale_factor = 0.05 autovacuum_vacuum_threshold = 1000 Start with #1 and #2 – most likely the cause.
a month ago
Thank you for the response. I checked the archive and there were no failed archives. No long running transactions, and no long vacuum operations. Not sure if I should still go ahead and modify the settings if not seeing any issues with those.