Lab: what a commit costs, and what a checkpoint adds

Before you start

Use a throwaway database you are allowed to reconfigure. Step 2 changes a server setting and puts it back. Our numbers come from PostgreSQL 18.3 on an Apple Silicon laptop with its built-in SSD; commit timings on your machine will differ, possibly by orders of magnitude.

1. A thousand tiny commits

A procedure can commit inside a loop, so it can produce thousands of separate transactions quickly:

CREATE TABLE events (id int, at timestamptz DEFAULT clock_timestamp());

CREATE PROCEDURE many_commits(n int) LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1..n LOOP
    INSERT INTO events (id) VALUES (i);
    COMMIT;
  END LOOP;
END $$;

\timing on
SET synchronous_commit = on;    CALL many_commits(5000);
SET synchronous_commit = off;   CALL many_commits(5000);

With on, each commit waits for its log record to be flushed. With off, it returns at once and the log is flushed shortly after.

2. How durable was that, really?

31 microseconds is suspiciously fast for a write that survives a power cut. Check how PostgreSQL forces the flush:

SHOW wal_sync_method;    -- open_datasync on our Mac

On macOS, the system’s flush calls hand data to the drive, but the drive may keep it in its own cache, and the manual page warns that a power loss can then lose it. PostgreSQL’s fsync_writethrough method also forces the drive’s cache. Measure it:

ALTER SYSTEM SET wal_sync_method = 'fsync_writethrough';
SELECT pg_reload_conf();
SET synchronous_commit = on;
CALL many_commits(500);
ALTER SYSTEM RESET wal_sync_method;
SELECT pg_reload_conf();

145 times slower. That is the real cost of making each commit reach permanent storage on this machine. Which number your production server pays depends on its operating system, its storage, and whether the storage has protected write caches. Servers usually run Linux, where the defaults are different; measure there, not on a laptop.

3. The checkpoint tax

Make a table of about 5,300 pages, force a checkpoint, then change one row on each of 5,000 different pages, twice:

CREATE TABLE wide (id int PRIMARY KEY, n int, pad text);
INSERT INTO wide SELECT g, 0, repeat('x', 200) FROM generate_series(1, 175000) g;
VACUUM ANALYZE wide;

CHECKPOINT;
SELECT pg_current_wal_insert_lsn() AS a0 \gset
UPDATE wide SET n = n + 1 WHERE id % 35 = 1;    -- one row on each of 5,000 pages
SELECT pg_current_wal_insert_lsn() AS a1 \gset
UPDATE wide SET n = n + 1 WHERE id % 35 = 2;    -- the same pages again
SELECT pg_current_wal_insert_lsn() AS a2 \gset

SELECT pg_size_pretty(pg_wal_lsn_diff(:'a1', :'a0')) AS first_update,
       pg_size_pretty(pg_wal_lsn_diff(:'a2', :'a1')) AS second_update;

The same change to the same 5,000 rows, 23 times the log. pg_waldump --stats over the first range shows why: full page images were 95.6% of it, about 42 MB, because each of the 5,000 pages was copied into the log the first time it changed after the checkpoint. The second update found those pages already logged.

What you measured

• Synchronous commit costs one log flush per transaction; turning it off removed nearly all of that.
• How long a “durable” flush takes depends on what the storage promises. On this laptop the honest version cost 145 times the default.
• The first change to each page after a checkpoint writes the whole page to the log. Frequent checkpoints, or changes spread across many pages, multiply write volume.

Clean up

DROP TABLE events, wide;
DROP PROCEDURE many_commits(int);

Check yourself

1. Why did 5,000 commits take 13 times longer with synchronous_commit = on?

   Show answer

   Each commit waited for its WAL record to be flushed to storage before returning. With off, commits return immediately and the log is flushed shortly afterwards.

   I could explain this without looking

2. Why was fsync_writethrough so much slower than the default on macOS?

   Show answer

   It also forces the drive to empty its write cache to permanent storage. The default flush only hands data to the drive, which may keep it in a volatile cache.

   I could explain this without looking

3. Why did the first update after a checkpoint write 23 times the WAL of the second?

   Show answer

   With full_page_writes, the first change to each page after a checkpoint copies the whole 8 kB page into the log. The second update changed pages that had already been copied, so it logged only the small change records.

   I could explain this without looking