What is the Write-Ahead Log actually for?

Before Postgres changes a data page in memory, it first writes a record of that change to the WAL and ensures the WAL record is durably on disk — that's what makes crash recovery possible: after a crash, Postgres replays the WAL to reconstruct any changes that were committed but hadn't yet been written back to the actual data files. It's the mechanism, not just a log for humans to read.

What does synchronous_commit actually control?

Whether COMMIT waits for its WAL record to be flushed to durable storage before returning control to the client. With it on (the default), a COMMIT that returns successfully guarantees the transaction survives a crash. With it off, COMMIT returns immediately after writing to the WAL buffer in memory — faster, but a crash in the small window before that buffer is flushed can lose transactions that appeared to commit.

Is turning synchronous_commit off ever a reasonable choice?

For workloads that can tolerate losing the last fraction of a second of transactions on a crash — high-volume analytics ingestion, session/cache-like tables, data that's reproducible from another source — the throughput gain can be worth it. It should never be the default for anything representing money, orders, or other data with no other copy.

Does turning synchronous_commit off risk data corruption?

No — it only risks losing the most recent few commits if the server crashes in the small window before an async flush completes; already-flushed data and the database's internal consistency are unaffected either way. This is a durability setting, not a correctness one — Postgres never returns inconsistent data because of it.

Why did a single large batch INSERT not show this difference, but 200 individual inserts did?

One INSERT of many rows is one transaction with one commit, so it pays the WAL-flush cost exactly once regardless of how many rows it contains. 200 separate autocommitted INSERT statements are 200 separate transactions, each with its own commit and its own flush — that's what makes the per-commit durability cost visible at all.

The WAL Durability Tradeoff: 0.30s vs 0.02s for the Same 200 Inserts

Every committed write has to hit durable storage before COMMIT returns — turning that guarantee off doesn't change what gets written, only when Postgres waits for it, and the difference is measured, not asserted.

By SkillByExample · Published Sep 13, 2026

postgresql wal durability synchronous-commit performance

COMMIT isn’t just a bookkeeping signal — with default settings, it doesn’t return until the transaction’s record is durably on disk. That guarantee has a real, measurable cost.

Every write goes to the WAL first

SELECT pg_current_wal_lsn();
SELECT wal_records, wal_fpi, wal_bytes FROM pg_stat_wal;

 pg_current_wal_lsn
--------------------
 0/90B5150
(1 row)

 wal_records | wal_fpi | wal_bytes
-------------+---------+-----------
      646971 |    8947 | 133386176
(1 row)

Every change this container has made so far — every INSERT, every VACUUM, every index build across this whole track — is represented as a WAL record. pg_current_wal_lsn() is the current position in that log; it only ever moves forward.

The cost of a durable commit, measured

200 individual INSERT statements, each its own autocommitted transaction, with the default synchronous_commit = on:

time psql -U postgres -f many_inserts.sql > /dev/null

real    0m0.30s

Same 200 inserts, with synchronous_commit = off:

ALTER SYSTEM SET synchronous_commit = off;
SELECT pg_reload_conf();

time psql -U postgres -f many_inserts2.sql > /dev/null

real    0m0.02s

0.30 seconds down to 0.02 — roughly 15x, for the identical 200 rows. The rows are written to the WAL either way; the only difference is whether COMMIT waits for that write to be flushed to durable storage before returning. Turned off, the client gets control back immediately, and a crash in the small window before the async flush completes could lose those commits.

ALTER SYSTEM SET synchronous_commit = on;
SELECT pg_reload_conf();

Reset back to the safe default before anything in this track depends on durability again.

Practice

1. Confirm synchronous_commit is back to on after resetting it.

SHOW synchronous_commit;

 synchronous_commit
--------------------
 on
(1 row)

Worth checking explicitly rather than assuming — ALTER SYSTEM writes to postgresql.auto.conf and needs pg_reload_conf() (or a restart) to actually take effect, so a forgotten reload leaves the setting silently unchanged.

2. Check how many WAL bytes a single large transaction generates versus many small ones.

SELECT wal_bytes FROM pg_stat_wal;
INSERT INTO books (title, author, genre, published_year)
SELECT 'Bulk ' || i, 'A', 'scifi', 2000 FROM generate_series(1, 5000) i;
SELECT wal_bytes FROM pg_stat_wal;

 wal_bytes
-----------
 133386176
(1 row)

INSERT 0 5000
 wal_bytes
-----------
 145688634

Roughly 12.3MB of WAL for 5,000 rows in one transaction — the WAL volume scales with the amount of data changed, not with the number of separate commits, which is the other half of why batching writes into fewer transactions helps throughput independent of the synchronous_commit setting.

3. Look up the current checkpoint statistics.

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

 num_timed | num_requested
-----------+---------------
         3 |             1

A checkpoint flushes all dirty data pages to disk and lets Postgres reuse older WAL segments — num_timed counts checkpoints triggered by the regular checkpoint_timeout schedule (three have already elapsed during this track’s testing session), while num_requested counts the one triggered early, by VACUUM FULL in an earlier lesson.

Next: backup and point-in-time recovery — restoring a database to the exact moment before a mistake.

Every write goes to the WAL first

The cost of a durable commit, measured

Practice

Frequently Asked Questions