Scenario
After a server power failure, the team finds that 3,000 financial transactions that applications acknowledged as committed are missing from the database. The DBA investigates and finds synchronous_commit = off was set globally 6 months ago by a developer trying to speed up a batch import — it was never reverted. The committed transactions that occurred within the WAL buffer flush window (~200ms) before the crash were lost.
How to Identify
Conditions:
synchronous_commit = off set globally in postgresql.conf or postgresql.auto.conf- Application committed transactions were not written to WAL before COMMIT returned
- After crash recovery, committed transactions are missing
pg_stat_wal shows wal_write count lower than wal_records count (asynchronous)- The system had a crash, hard shutdown, or OOM kill
Analysis Steps
-- Check current synchronous_commit setting
SHOW synchronous_commit;
-- 'off' = transactions committed without waiting for WAL write → data loss on crash
-- 'on' = transactions committed only after WAL flushed to disk (default)
-- Check if it was changed at system level (not just session)
SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'synchronous_commit';
-- source='configuration file' = set globally (affects all connections)
-- source='default' = using default ('on') → safe
-- Check for per-role overrides
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig::text LIKE '%synchronous_commit%';
-- Check for per-database overrides
SELECT datname, datconfig
FROM pg_database
WHERE datconfig::text LIKE '%synchronous_commit%';
-- Check WAL buffer size (affects how much data can be in-flight when sync_commit=off)
SHOW wal_writer_delay;
-- Data committed with synchronous_commit=off is at risk for up to wal_writer_delay ms
Pitfalls
synchronous_commit = off does NOT cause data corruption on crash — PostgreSQL still recovers cleanly. But recently committed transactions (within the WAL writer flush window, default 200ms) may be silently rolled back after restart. Applications believe the data was committed.- This is not acceptable for financial data, order processing, or any table where application-acknowledged commits must be durable.
synchronous_commit = off is appropriate for: logging tables, session state, analytics event streams where occasional transaction loss is tolerable.- Setting
fsync = off is fundamentally different and far more dangerous — it can cause data corruption and heap file damage. Never confuse the two.
ALTER ROLE and ALTER DATABASE SET overrides silently override postgresql.conf. Always check both.
Resolution Approach
Revert synchronous_commit = on for all critical paths. Use synchronous_commit = off only at the session or transaction level for explicit non-critical paths (event logging, analytics writes). After fixing: identify and manually reconcile any lost transactions from application logs.