What is this issue?
With synchronous_commit = off, PostgreSQL returns success to the client
before WAL is written to disk. This provides:
- Faster transaction commits (no disk wait)
- Higher throughput for write-heavy workloads
But with the risk:
- Commits in the last ~600ms may be lost on crash
- Data appears committed but isn't durable
This is different from fsync = off:
- synchronous_commit=off: Lose recent commits, no corruption
- fsync=off: Risk corruption, much more dangerous
Why it matters
Recent Data Loss
Up to 600ms of committed transactions can be lost
False Durability
Application thinks data is safe when it isn't
Acceptable Trade-off
May be acceptable for non-critical data
Per-Transaction Option
Can be set per-transaction instead of globally
How PG Pilot detects it
```sql SELECT name, setting FROM pg_settings WHERE name = 'synchronous_commit'; ```
How to fix it
Evaluate if this is intentional
Ask:
- Is losing recent commits acceptable for this workload?
- Was this set globally or per-transaction?
- Is the performance benefit needed?
Enable if needed
For maximum durability:
ALTER SYSTEM SET synchronous_commit = on;
SELECT pg_reload_conf();Use per-transaction instead
For selective async commits:
-- Default to safe
ALTER SYSTEM SET synchronous_commit = on;
-- Opt-in for non-critical writes
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO logs (...);
COMMIT;
Prevention
- Default to synchronous_commit = on
- Use per-transaction for specific workloads
- Document why async commit is used
- Monitor with PG Pilot