In this article
- What you will learn
- Prerequisites
- How it works
- Look at the checkpointer and its settings
- Watch a checkpoint happen
- Scheduled and forced checkpoints
- Measure what frequent checkpoints cost
- How to choose checkpoint settings for steady performance
- The background writer: who writes your dirty pages?
- Common mistakes and how to fix them
- Practice exercise
- Quick reference
- Summary and what's next
This is Part 16 of the PostgreSQL DBA series. A checkpoint is the moment PostgreSQL makes sure every change recorded in the WAL so far has also been written to the real table and index files. Checkpoints decide how long crash recovery takes, how much WAL your server writes and whether your disks see a smooth stream of writes or sudden bursts. The background writer is a helper process that cleans dirty pages in memory so your queries do not have to.
In Part 15 (WAL explained: write-ahead log, wal_level and segments) you saw that WAL is the notebook PostgreSQL writes in before it touches the data files. By the end of this part you will be able to read every number in a checkpoint log line, tell scheduled checkpoints from forced ones, fix the checkpoints are occurring too frequently warning, tune max_wal_size and checkpoint_timeout, and check whether the background writer is doing its job. Every command was run on PostgreSQL 18 on Ubuntu and the outputs are real.
What you will learn
- What a checkpoint does, what triggers one, and why PostgreSQL spreads its writes over several minutes.
- How to read the checkpoint lines in the server log and the
pg_stat_checkpointerview (both changed in PostgreSQL 17 and 18). - How a small
max_wal_sizecauses forced checkpoints and more than doubles WAL volume, and how to fix it. - What the background writer does, and how to see who writes dirty pages with
pg_stat_bgwriterandpg_stat_io. - Which settings need only a reload, their defaults, and how to undo every change.
Prerequisites
- Parts 1 to 15 of this series, especially Part 1 (server processes and memory), Part 5 (postgresql.conf, reload vs restart) and Part 15 (WAL, LSNs and full page writes).
- One Ubuntu server (we call it db1) with PostgreSQL 18 installed from the PGDG repository as in Part 2, cluster 18 main running on port 5432.
- A user with sudo rights. SQL runs as the postgres operating system user through psql.
- About 2 GB of free disk space for the test database and the extra WAL.
- A lab server, not production. Several steps deliberately set bad values to show the symptoms.
How it works
When a query changes a row, PostgreSQL changes the 8 kB page in shared buffers (its memory cache) and writes a WAL record. The page in memory is now newer than the copy on disk. Such a page is called a dirty page or dirty buffer. PostgreSQL does not rush to write it: the WAL already protects the change, and the same page may be changed again a moment later.
Dirty pages cannot stay in memory forever. If the server crashed, recovery would have to replay WAL all the way back to the oldest unwritten change. A checkpoint puts a limit on that. The checkpointer process writes every dirty page to the data files, asks the operating system to flush them to disk (fsync), then writes a checkpoint record into the WAL and saves its position in the control file global/pg_control. After that, crash recovery only needs to start from the checkpoint's redo point, and older WAL segments can be recycled or removed.
A simple comparison: WAL is the notebook PostgreSQL writes in first, and the checkpoint is the moment it copies the notebook into the real ledger and draws a line. Anything above the line no longer needs the notebook.
A checkpoint starts for one of these reasons, and the log line names the reason:
| Reason in the log | What triggered it |
|---|---|
| time | checkpoint_timeout passed since the last checkpoint (default 5min). If no WAL was written since the previous checkpoint, it is skipped. |
| wal | The WAL written since the last checkpoint is about to exceed max_wal_size (default 1GB). |
| immediate force wait | Someone ran the CHECKPOINT command. |
| shutdown | A clean server stop always ends with a checkpoint. |
Writing thousands of pages at once would flood the disks and slow every query. So a scheduled checkpoint is throttled: the checkpointer spreads the writes so that it finishes when about checkpoint_completion_target (default 0.9) of the interval has passed. A slow checkpoint is normal. A checkpoint that writes for 4 minutes out of 5 is working as designed.
Checkpoints also have a hidden cost. With full_page_writes on (the default, see Part 15), the first change to a page after each checkpoint writes the whole 8 kB page into the WAL. More frequent checkpoints mean more of these full page images, so more WAL, more archive space and more replication traffic.
The background writer (bgwriter) is a separate process with a different job. Every bgwriter_delay (200ms) it looks ahead in shared buffers and writes some dirty pages that are likely to be reused soon, so that a session that needs a free buffer finds a clean one. If no clean buffer is available, the session (a client backend) must write the dirty page itself before it can read new data, and the query waits for that write. The background writer does not replace checkpoints; it only reduces how often queries write pages themselves.
Three processes write data pages, and you will measure each of them in this part:
| Process | When it writes | Where you see it |
|---|---|---|
| checkpointer | Every checkpoint: all dirty pages | pg_stat_checkpointer, pg_stat_io (backend_type checkpointer), checkpoint log lines |
| background writer | Every 200ms: a few pages likely to be reused soon | pg_stat_bgwriter, pg_stat_io (backend_type background writer) |
| client backend | When a query needs a buffer and only dirty ones are free | pg_stat_io (backend_type client backend, column writes) |
Look at the checkpointer and its settings
Step 1: Check the current checkpoint and background writer settings
On db1, as a sudo user, list the settings this part is about, with their current value, unit, context and built-in default:
sudo -u postgres psql -X -c "SELECT name, setting, unit, context, boot_val FROM pg_settings WHERE name IN ('checkpoint_timeout','checkpoint_completion_target','checkpoint_flush_after','checkpoint_warning','max_wal_size','min_wal_size','log_checkpoints','bgwriter_delay','bgwriter_lru_maxpages','bgwriter_lru_multiplier','bgwriter_flush_after','shared_buffers','full_page_writes') ORDER BY name;" name | setting | unit | context | boot_val
------------------------------+---------+------+------------+----------
bgwriter_delay | 200 | ms | sighup | 200
bgwriter_flush_after | 64 | 8kB | sighup | 64
bgwriter_lru_maxpages | 100 | | sighup | 100
bgwriter_lru_multiplier | 2 | | sighup | 2
checkpoint_completion_target | 0.9 | | sighup | 0.9
checkpoint_flush_after | 32 | 8kB | sighup | 32
checkpoint_timeout | 300 | s | sighup | 300
checkpoint_warning | 30 | s | sighup | 30
full_page_writes | on | | sighup | on
log_checkpoints | on | | sighup | on
max_wal_size | 1024 | MB | sighup | 1024
min_wal_size | 80 | MB | sighup | 80
shared_buffers | 16384 | 8kB | postmaster | 16384
(13 rows)Every checkpoint and background writer setting has context sighup: it can be set only in postgresql.conf (or on the server command line) and takes effect on a reload, no restart needed. Only shared_buffers (context postmaster) needs a restart. Some values are shown in 8 kB pages: checkpoint_flush_after 32 means 256kB and bgwriter_flush_after 64 means 512kB, the Linux defaults.
| Parameter | Default | What it controls |
|---|---|---|
| checkpoint_timeout | 5min (range 30s to 1d) | Maximum time between automatic checkpoints. |
| max_wal_size | 1GB | WAL allowed to build up between checkpoints before one is forced. A soft limit. |
| min_wal_size | 80MB | Below this much WAL, old segments are always recycled rather than removed. |
| checkpoint_completion_target | 0.9 | Fraction of the interval over which checkpoint writes are spread. |
| checkpoint_warning | 30s | Log a warning if WAL-triggered checkpoints come closer together than this. 0 disables. |
| checkpoint_flush_after | 256kB on Linux | After this much checkpoint writing, ask the OS to flush to storage. |
| log_checkpoints | on | Log each checkpoint and restartpoint with statistics. |
| bgwriter_delay | 200ms | Sleep between background writer rounds. |
| bgwriter_lru_maxpages | 100 | Most buffers the background writer writes per round. 0 disables background writing. |
| bgwriter_lru_multiplier | 2.0 | How far ahead of recent demand the background writer cleans buffers. |
| bgwriter_flush_after | 512kB on Linux | After this much background writer output, ask the OS to flush. |
On Ubuntu, /etc/postgresql/18/main/postgresql.conf already has uncommented lines max_wal_size = 1GB and min_wal_size = 80MB; the other settings are commented out (they use the default). Check with grep:
grep -nE "^#?(checkpoint|max_wal|min_wal|bgwriter|log_checkpoints)" /etc/postgresql/18/main/postgresql.conf197:#bgwriter_delay = 200ms # 10-10000ms between rounds
198:#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
199:#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
200:#bgwriter_flush_after = 512kB # measured in pages, 0 disables
267:#checkpoint_timeout = 5min # range 30s-1d
268:#checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
269:#checkpoint_flush_after = 256kB # measured in pages, 0 disables
270:#checkpoint_warning = 30s # 0 disables
271:max_wal_size = 1GB
272:min_wal_size = 80MB
343:#max_wal_senders = 10 # max number of walsender processes
597:#log_checkpoints = onStep 2: Find the checkpointer and background writer processes
On db1, as a sudo user, list the server processes owned by postgres, then ask PostgreSQL for the same processes:
ps -u postgres -o pid,cmd | grep -E "18/main|postgres:" | grep -v grep
sudo -u postgres psql -X -c "SELECT pid, backend_type FROM pg_stat_activity WHERE backend_type IN ('checkpointer','background writer','walwriter') ORDER BY backend_type;" 2235 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/18/main/postgresql.conf
2236 postgres: 18/main: io worker 0
2237 postgres: 18/main: io worker 1
2238 postgres: 18/main: io worker 2
2239 postgres: 18/main: checkpointer
2240 postgres: 18/main: background writer
2242 postgres: 18/main: walwriter
2243 postgres: 18/main: autovacuum launcher
2244 postgres: 18/main: logical replication launcher
pid | backend_type
------+-------------------
2240 | background writer
2239 | checkpointer
2242 | walwriter
(3 rows)The checkpointer (PID 2239) and background writer (PID 2240) are separate processes started by the postmaster. The walwriter is different again: it writes WAL, not data pages. Remember PID 2239: every checkpoint line in the log carries it in square brackets.
Step 3: Read where the last checkpoint is
The control file records the latest checkpoint. On db1, as a sudo user, read it with SQL and with pg_controldata (the binary is in /usr/lib/postgresql/18/bin/):
sudo -u postgres psql -X -c "SELECT checkpoint_lsn, redo_lsn, redo_wal_file, checkpoint_time FROM pg_control_checkpoint();"
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main | grep -iE "checkpoint|REDO|state" checkpoint_lsn | redo_lsn | redo_wal_file | checkpoint_time
----------------+-----------+--------------------------+------------------------
0/175EC38 | 0/175EC38 | 000000010000000000000001 | 2026-11-11 12:46:20+00
(1 row)
Database cluster state: in production
Latest checkpoint location: 0/175EC38
Latest checkpoint's REDO location: 0/175EC38
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:752
Latest checkpoint's NextOID: 13636
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 744
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Wed Nov 11 12:46:20 2026The REDO location is where crash recovery would start reading WAL. Here it equals the checkpoint location because nothing else was happening while the checkpoint ran; on a busy server the redo point is earlier, at the moment the checkpoint started. pg_control_checkpoint() returns the same data as pg_controldata but works over a normal database connection.
Watch a checkpoint happen
Step 4: Create the lab database and an orders table
On db1, as a sudo user, create a database called lab, install the pg_buffercache extension (a contrib module shipped in the postgresql-18 package that shows what is in shared buffers), and load 200,000 orders:
sudo -u postgres psql -X -c "CREATE DATABASE lab;"
sudo -u postgres psql -X -d labCREATE EXTENSION pg_buffercache;
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL,
amount numeric(10,2) NOT NULL,
status text NOT NULL DEFAULT 'new',
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (customer_id, amount)
SELECT (random()*999)::int + 1, round((random()*5000)::numeric, 2)
FROM generate_series(1, 200000);CREATE DATABASE
CREATE EXTENSION
CREATE TABLE
INSERT 0 200000Using pg_buffercache functions needs superuser or the pg_monitor role; we run as postgres. To undo the whole lab later, DROP DATABASE lab; (Step 16).
Step 5: Create dirty pages and count them
Still in psql on the lab database, start from a clean slate: run a checkpoint, reset the checkpointer and bgwriter counters with pg_stat_reset_shared(), then update one order in ten and count dirty buffers with pg_buffercache_summary():
CHECKPOINT;
SELECT pg_stat_reset_shared('checkpointer');
SELECT pg_stat_reset_shared('bgwriter');
SELECT pg_size_pretty(pg_table_size('orders')) AS table_size;
UPDATE orders SET status = 'paid' WHERE order_id % 10 = 0;
SELECT buffers_used, buffers_dirty, buffers_unused
FROM pg_buffercache_summary();CHECKPOINT
pg_stat_reset_shared
----------------------
(1 row)
pg_stat_reset_shared
----------------------
(1 row)
table_size
------------
12 MB
(1 row)
UPDATE 20000
buffers_used | buffers_dirty | buffers_unused
--------------+---------------+----------------
4259 | 2171 | 12125
(1 row)The update changed 20,000 rows. The changes are safe in the WAL, but 2,171 buffers (about 17 MB) in shared buffers are now newer than the data files. Nothing has written them yet. pg_stat_reset_shared() takes a target name; 'checkpointer', 'bgwriter', 'io' and 'wal' are the ones used in this part, and calling it with no argument resets all of them.
Step 6: Run CHECKPOINT and read the log line
In the same psql session, force a checkpoint with timing on, and count dirty buffers again:
\timing on
CHECKPOINT;
SELECT buffers_used, buffers_dirty FROM pg_buffercache_summary();Timing is on.
CHECKPOINT
Time: 35.188 ms
buffers_used | buffers_dirty
--------------+---------------
4259 | 0
(1 row)Dirty buffers dropped to 0: the checkpoint wrote all of them. The pages are still in memory (buffers_used is unchanged), they are just clean now. A manual CHECKPOINT runs at full speed, without throttling, which is why it took only 35 ms here.
On db1, as a sudo user, read the last two lines of the server log:
sudo tail -n 2 /var/log/postgresql/postgresql-18-main.log2026-11-11 12:48:15.577 UTC [2239] LOG: checkpoint starting: immediate force wait
2026-11-11 12:48:15.611 UTC [2239] LOG: checkpoint complete: wrote 2171 buffers (13.3%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=0.019 s, sync=0.013 s, total=0.035 s; sync files=9, longest=0.012 s, average=0.002 s; distance=20402 kB, estimate=34050 kB; lsn=0/4E07338, redo lsn=0/4E072E0Because log_checkpoints is on, every checkpoint writes these two lines. The checkpointer wrote exactly the 2,171 dirty buffers we counted. Here is what each part means:
| Field | Meaning |
|---|---|
| immediate force wait | Why it started: CHECKPOINT command, run at full speed, and the caller waited for it. |
| wrote 2171 buffers (13.3%) | Shared buffers written, and that number as a percentage of shared_buffers (16,384 buffers = 128MB). |
| wrote 1 SLRU buffers | Small internal caches (such as transaction status) written. Shown separately since PostgreSQL 18. |
| 0 WAL file(s) added, 0 removed, 1 recycled | What happened to old WAL segments now that they are no longer needed for crash recovery. |
| write=0.019 s | Time spent writing pages. For a scheduled checkpoint this is long on purpose (throttling). |
| sync=0.013 s, sync files=9, longest, average | Time spent waiting for fsync, and how many files were synced. A long sync points to slow storage. |
| total=0.035 s | Whole checkpoint duration. |
| distance=20402 kB | WAL written since the previous checkpoint's redo point (about 20 MB). |
| estimate=34050 kB | PostgreSQL's running estimate of WAL per checkpoint cycle, used to decide how many segments to keep. |
| lsn, redo lsn | Where the checkpoint record is in the WAL, and where recovery would start. |
Step 7: Read pg_stat_checkpointer
In psql, read the checkpointer statistics since the reset in Step 5:
SELECT num_timed, num_requested, num_done, buffers_written,
slru_written, write_time, sync_time
FROM pg_stat_checkpointer; num_timed | num_requested | num_done | buffers_written | slru_written | write_time | sync_time
-----------+---------------+----------+-----------------+--------------+------------+-----------
0 | 1 | 1 | 2171 | 1 | 19 | 13
(1 row)One requested checkpoint (our CHECKPOINT command), one done, 2,171 buffers written; write_time and sync_time are in milliseconds and match the log line. num_timed and num_requested count skipped checkpoints too (an idle server skips scheduled ones); num_done counts only completed ones. The Step 5 checkpoint does not appear because it ran before the reset.
Scheduled and forced checkpoints
Step 8: Watch a scheduled checkpoint spread its writes
Now let the checkpointer work on its own schedule. In psql on lab, dirty the pages again, then watch the dirty buffer count and the checkpointer's wait event every 30 seconds with psql's \watch (i= sets the interval in seconds). Press Ctrl+C to stop it once the checkpoint has finished.
UPDATE orders SET status = 'shipped' WHERE order_id % 10 = 0;
SELECT now()::time(0) AS at, s.buffers_dirty, a.wait_event
FROM pg_buffercache_summary() s, pg_stat_activity a
WHERE a.backend_type = 'checkpointer' \watch i=30UPDATE 20000
at | buffers_dirty | wait_event
----------+---------------+----------------------
12:48:27 | 2198 | CheckpointerMain
12:49:27 | 2228 | CheckpointerMain
12:52:57 | 2228 | CheckpointerMain
12:53:27 | 2108 | CheckpointWriteDelay
12:53:57 | 1809 | CheckpointWriteDelay
12:54:27 | 1509 | CheckpointWriteDelay
12:54:57 | 1210 | CheckpointWriteDelay
12:55:27 | 910 | CheckpointWriteDelay
12:55:57 | 611 | CheckpointWriteDelay
12:56:27 | 311 | CheckpointWriteDelay
12:56:58 | 12 | CheckpointWriteDelayFor five minutes after the last checkpoint (12:48:15) nothing happened: the checkpointer waited in its main loop (wait event CheckpointerMain). At 12:53:15, checkpoint_timeout expired and the checkpoint started. It then wrote about 300 buffers every 30 seconds, pausing between writes (wait event CheckpointWriteDelay). That steady, gentle rate is the throttling controlled by checkpoint_completion_target. As a sudo user, check the log:
sudo grep -A1 "checkpoint starting: time" /var/log/postgresql/postgresql-18-main.log2026-11-11 12:53:15.695 UTC [2239] LOG: checkpoint starting: time
2026-11-11 12:56:58.773 UTC [2239] LOG: checkpoint complete: wrote 2228 buffers (13.6%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=223.073 s, sync=0.002 s, total=223.079 s; sync files=14, longest=0.001 s, average=0.001 s; distance=22758 kB, estimate=32921 kB; lsn=0/6440E20, redo lsn=0/6440D90The reason is time, and write=223.073 s: about 17 MB was trickled out over almost four minutes, while the manual checkpoint in Step 6 wrote a similar amount in 19 milliseconds. A long write time on a scheduled checkpoint is not a problem; a long sync time is the number to watch. pg_stat_checkpointer now shows the two kinds side by side:
SELECT num_timed, num_requested, num_done, buffers_written, write_time, sync_time
FROM pg_stat_checkpointer; num_timed | num_requested | num_done | buffers_written | write_time | sync_time
-----------+---------------+----------+-----------------+------------+-----------
1 | 1 | 2 | 4399 | 223092 | 15
(1 row)Step 9: Shrink max_wal_size to force WAL-triggered checkpoints (lab only)
On db1, as a sudo user, edit /etc/postgresql/18/main/postgresql.conf (for example with sudo nano /etc/postgresql/18/main/postgresql.conf) and change the max_wal_size line:
# before
max_wal_size = 1GB
# after
max_wal_size = 64MBmax_wal_size is reloadable, so reload the cluster and verify:
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SHOW max_wal_size;"
sudo grep 'parameter "max_wal_size"' /var/log/postgresql/postgresql-18-main.log | tail -n 1 max_wal_size
--------------
64MB
(1 row)
2026-11-11 12:57:12.734 UTC [2235] LOG: parameter "max_wal_size" changed to "64MB"The postmaster (PID 2235) logged the change, and SHOW confirms the new value. SELECT pg_reload_conf(); as a superuser does the same as the systemctl reload.
Step 10: Load data and read the warning
In psql on lab, reset the checkpointer counters, insert one million orders, and then calculate what share of checkpoints were forced:
SELECT pg_stat_reset_shared('checkpointer');
INSERT INTO orders (customer_id, amount)
SELECT (random()*999)::int + 1, round((random()*5000)::numeric, 2)
FROM generate_series(1, 1000000);
SELECT num_timed, num_requested,
round(100.0 * num_requested / NULLIF(num_timed + num_requested, 0), 1) AS pct_requested,
buffers_written,
round((write_time + sync_time) / 1000) AS io_seconds,
stats_reset::timestamp(0)
FROM pg_stat_checkpointer;INSERT 0 1000000
num_timed | num_requested | pct_requested | buffers_written | io_seconds | stats_reset
-----------+---------------+---------------+-----------------+------------+---------------------
0 | 4 | 100.0 | 8041 | 2 | 2026-11-11 13:02:43
(1 row)One insert of a million rows caused four checkpoints within seconds, all requested (pct_requested 100). As a sudo user, look at the log:
sudo grep -E "checkpoint|HINT" /var/log/postgresql/postgresql-18-main.log | tail -n 202026-11-11 13:02:45.688 UTC [2239] LOG: checkpoints are occurring too frequently (1 second apart)
2026-11-11 13:02:45.688 UTC [2239] HINT: Consider increasing the configuration parameter "max_wal_size".
2026-11-11 13:02:45.688 UTC [2239] LOG: checkpoint starting: wal
2026-11-11 13:02:46.246 UTC [2239] LOG: checkpoint complete: wrote 2350 buffers (14.3%), wrote 0 SLRU buffers; 0 WAL file(s) added, 2 removed, 0 recycled; write=0.495 s, sync=0.030 s, total=0.559 s; sync files=3, longest=0.019 s, average=0.010 s; distance=35952 kB, estimate=521292 kB; lsn=1/39A52208, redo lsn=1/37746C08
2026-11-11 13:02:46.246 UTC [2239] LOG: checkpoints are occurring too frequently (1 second apart)
2026-11-11 13:02:46.246 UTC [2239] HINT: Consider increasing the configuration parameter "max_wal_size".The reason is now wal, and because these checkpoints came closer together than checkpoint_warning (30s), PostgreSQL logged a warning with a hint telling you exactly what to change. The documentation's advice: an occasional warning (for example during a large one-off COPY) is not a cause for alarm, but if it appears often, raise the checkpoint settings. Notice also write=0.495 s: a WAL-triggered checkpoint is in a hurry, because max_wal_size is about to be exceeded, so its writes are much less spread out.
Measure what frequent checkpoints cost
Step 11: Create a pgbench test database
To see the cost under a realistic write load, use pgbench, the benchmark tool shipped with PostgreSQL. On db1, as a sudo user, create a separate database and fill it:
sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 50 -q bench| Option | What it does |
|---|---|
| -i | Initialization mode: create and fill the pgbench tables. |
| -s 50 | Scale factor: multiply the rows generated by 50 (5,000,000 rows in pgbench_accounts). |
| -q | Quiet logging: one progress message every 5 seconds instead of one per 100,000 rows. |
5000000 of 5000000 tuples (100%) of pgbench_accounts done (elapsed 4.12 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 5.75 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 4.16 s, vacuum 0.33 s, primary keys 1.26 s).Verify the size with sudo -u postgres psql -X -d bench -c "SELECT pg_size_pretty(pg_database_size('bench'));". It returned 755 MB, far bigger than the 128MB of shared buffers, so pages are constantly read in and pushed out, as on a real server.
Step 12: Run a write workload with max_wal_size = 64MB
max_wal_size is still 64MB from Step 9. On db1, as a sudo user, take a checkpoint, reset the counters, run 60 seconds of updates, then read the results. Run this same block again in Steps 13 and 15:
sudo -u postgres psql -X -q -d bench -c "CHECKPOINT;" -c "SELECT pg_stat_reset_shared('checkpointer'); SELECT pg_stat_reset_shared('bgwriter'); SELECT pg_stat_reset_shared('wal'); SELECT pg_stat_reset_shared('io');" > /dev/null
sudo -u postgres pgbench -N -c 4 -j 2 -T 60 bench 2>&1 | grep -E "^tps|latency average|actually processed"
sudo -u postgres psql -X -d bench \
-c "SELECT num_timed, num_requested, num_done, buffers_written FROM pg_stat_checkpointer;" \
-c "SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_size FROM pg_stat_wal;" \
-c "SELECT buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter;" \
-c "SELECT backend_type, sum(writes) AS writes, sum(evictions) AS evictions, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object = 'relation' AND backend_type IN ('client backend','checkpointer','background writer') GROUP BY backend_type ORDER BY backend_type;"| pgbench option | What it does |
|---|---|
| -N | Run the simple-update built-in script: skips updates of pgbench_tellers and pgbench_branches, avoiding contention on those small tables. |
| -c 4 | Number of simulated clients (concurrent database sessions). |
| -j 2 | Number of worker threads in pgbench. |
| -T 60 | Run for 60 seconds. |
number of transactions actually processed: 221387
latency average = 1.084 ms
tps = 3689.990157 (without initial connection time)
num_timed | num_requested | num_done | buffers_written
-----------+---------------+----------+-----------------
0 | 57 | 56 | 220320
(1 row)
wal_records | wal_fpi | wal_size
-------------+---------+----------
1105691 | 229712 | 1828 MB
(1 row)
buffers_clean | maxwritten_clean | buffers_alloc
---------------+------------------+---------------
1290 | 0 | 304966
(1 row)
backend_type | writes | evictions | fsyncs
-------------------+--------+-----------+--------
background writer | 1290 | | 0
checkpointer | 220320 | | 253
client backend | 0 | 304468 | 0
(3 rows)In one minute the server ran 57 checkpoints, nearly one a second. The checkpointer wrote 220,320 pages and made 253 fsync calls. The WAL tells the bigger story: 1,828 MB, of which 229,712 records were full page images, because every checkpoint restarts the "first change to this page" rule.
Step 13: Raise max_wal_size and checkpoint_timeout, and measure again
On db1, as a sudo user, edit /etc/postgresql/18/main/postgresql.conf again. These values are an example for this lab; the next section explains how to choose your own:
# before
#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 64MB
# after
checkpoint_timeout = 15min # range 30s-1d
max_wal_size = 4GBsudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SELECT name, setting, unit, boot_val, pending_restart FROM pg_settings WHERE name IN ('checkpoint_timeout','max_wal_size');" name | setting | unit | boot_val | pending_restart
--------------------+---------+------+----------+-----------------
checkpoint_timeout | 900 | s | 300 | f
max_wal_size | 4096 | MB | 1024 | f
(2 rows)pending_restart is f: both values are live without a restart. The log shows parameter "checkpoint_timeout" changed to "15min" and parameter "max_wal_size" changed to "4GB". Now run the Step 12 measurement block again:
number of transactions actually processed: 221480
latency average = 1.084 ms
tps = 3691.360711 (without initial connection time)
num_timed | num_requested | num_done | buffers_written
-----------+---------------+----------+-----------------
0 | 0 | 0 | 0
(1 row)
wal_records | wal_fpi | wal_size
-------------+---------+----------
986191 | 87829 | 736 MB
(1 row)| Measure (60 s, same workload) | max_wal_size 64MB | max_wal_size 4GB, timeout 15min |
|---|---|---|
| Checkpoints | 57 (all requested) | 0 |
| Pages written by the checkpointer | 220,320 | 0 |
| Full page images in WAL | 229,712 | 87,829 |
| WAL generated | 1828 MB | 736 MB |
| Transactions per second | 3,690 | 3,691 |
The same amount of work produced 2.5 times more WAL with the small setting. Every extra megabyte of WAL must be written, archived (Part 19) and streamed to standbys (Part 33). On this test machine, with fast storage and lots of free memory for the operating system cache, throughput did not change; on busier or slower storage, frequent checkpoints also cost latency. To be fair to the large setting: a 60-second run simply ends before any checkpoint is due. In steady operation it still runs one every 15 minutes, spread over about 13.5 minutes.
How to choose checkpoint settings for steady performance
The documentation's guidance comes down to a few rules. These apply to production, after you have measured your own workload:
- Most checkpoints should be timed. Check pg_stat_checkpointer over a day or a week. If num_requested is a large share of all checkpoints, or the log shows checkpoints are occurring too frequently often, raise max_wal_size.
- Size max_wal_size from real WAL volume. The distance= field in checkpoint log lines is the WAL written per checkpoint cycle. Set max_wal_size comfortably above the distance you see at busy times, so the timeout fires first.
- Balance checkpoint_timeout against crash recovery time. A longer interval means fewer checkpoints and fewer full page images, but crash recovery may replay up to a full interval of WAL. The documentation warns that raising either checkpoint_timeout or max_wal_size can increase recovery time.
- Leave checkpoint_completion_target at 0.9. The documentation does not recommend lowering it (it causes bursts of I/O), and setting it to 1.0 is likely to make checkpoints finish late.
- Plan disk space for pg_wal. max_wal_size is a soft limit: under heavy load, a failing archive_command or a lagging replication slot, pg_wal can grow beyond it. Leave plenty of headroom.
- Leave checkpoint_flush_after at its default unless testing shows a gain. It reduces the stall at the final fsync, but the documentation notes it can hurt workloads larger than shared_buffers but smaller than the OS page cache.
The background writer: who writes your dirty pages?
Step 14: Read the background writer and pg_stat_io numbers
Look at the rest of the output from the Step 13 run, with max_wal_size 4GB and the default background writer settings:
buffers_clean | maxwritten_clean | buffers_alloc
---------------+------------------+---------------
29301 | 293 | 308516
(1 row)
backend_type | writes | evictions | fsyncs
-------------------+--------+-----------+--------
background writer | 29301 | | 0
checkpointer | 0 | | 0
client backend | 166672 | 308184 | 0
(3 rows)| Column | View | Meaning |
|---|---|---|
| buffers_clean | pg_stat_bgwriter | Buffers written by the background writer. |
| maxwritten_clean | pg_stat_bgwriter | Times the background writer stopped a round because it hit bgwriter_lru_maxpages. |
| buffers_alloc | pg_stat_bgwriter | Buffers allocated (pages read into shared buffers). |
| writes (client backend) | pg_stat_io | Pages that query sessions had to write themselves. |
| evictions | pg_stat_io | Times a block was pushed out of a buffer to make room for another. |
With no checkpoints, someone still had to write dirty pages to make room for new ones. The background writer wrote 29,301, but the client backends, the sessions running your queries, wrote 166,672. maxwritten_clean = 293 means the background writer hit its limit of 100 pages per round 293 times in 60 seconds: it wanted to do more but was not allowed to.
The documentation states it plainly: client backends should be able to rely on the checkpointer and background writer for most writes, and large numbers of writes by client backends could indicate a misconfiguration of shared buffers or of the checkpointer. Here the main cause is that the 755 MB database does not fit in 128MB of shared buffers; Part 28 covers shared_buffers sizing. The background writer settings are the other lever.
Step 15: Try a more active background writer, then measure
On db1, as a sudo user, edit /etc/postgresql/18/main/postgresql.conf:
# before
#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
# after
bgwriter_lru_maxpages = 1000 # max buffers written/round, 0 disables
bgwriter_lru_multiplier = 4.0 # 0-10.0 multiplier on buffers scanned/roundsudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SELECT name, setting FROM pg_settings WHERE name LIKE 'bgwriter_lru%';" name | setting
-------------------------+---------
bgwriter_lru_maxpages | 1000
bgwriter_lru_multiplier | 4
(2 rows)Run the Step 12 measurement block again:
number of transactions actually processed: 280709
latency average = 0.855 ms
tps = 4678.542205 (without initial connection time)
num_timed | num_requested | num_done | buffers_written
-----------+---------------+----------+-----------------
0 | 0 | 0 | 0
(1 row)
wal_records | wal_fpi | wal_size
-------------+---------+----------
1212034 | 87738 | 746 MB
(1 row)
buffers_clean | maxwritten_clean | buffers_alloc
---------------+------------------+---------------
257917 | 211 | 387224
(1 row)
backend_type | writes | evictions | fsyncs
-------------------+--------+-----------+--------
background writer | 257917 | | 0
checkpointer | 0 | | 0
client backend | 4083 | 386644 | 0
(3 rows)The work moved: the background writer now wrote 257,917 pages and client backends only 4,083, down from 166,672. That is the effect you want, because a query that writes a page must wait for that write.
Treat the throughput numbers with care. This run showed 4,679 tps, but an earlier run with exactly the same background writer settings on the same machine gave 2,895 tps, while two default runs gave 3,804 and 3,874. On a shared test VM, run-to-run noise is larger than the effect. The documentation also warns that the background writer causes a net increase in total I/O, since a page dirtied repeatedly may be written several times instead of once per checkpoint. So change these settings only when pg_stat_io shows client backends doing many writes and query latency suffers, and confirm on your own hardware with several runs.
Step 16: Undo the lab changes
On db1, as a sudo user, put postgresql.conf back to the Ubuntu defaults:
# restore these lines
#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 1GB
#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/roundsudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SELECT name, setting, unit, boot_val FROM pg_settings WHERE name IN ('checkpoint_timeout','max_wal_size','bgwriter_lru_maxpages','bgwriter_lru_multiplier') ORDER BY name;" name | setting | unit | boot_val
-------------------------+---------+------+----------
bgwriter_lru_maxpages | 100 | | 100
bgwriter_lru_multiplier | 2 | | 2
checkpoint_timeout | 300 | s | 300
max_wal_size | 1024 | MB | 1024
(4 rows)When a commented-out setting is reloaded, the log confirms it, for example parameter "checkpoint_timeout" removed from configuration file, reset to default. Then remove the test databases:
sudo -u postgres psql -X -c "DROP DATABASE bench;"
sudo -u postgres psql -X -c "DROP DATABASE lab;"DROP DATABASE
DROP DATABASECommon mistakes and how to fix them
LOG: checkpoints are occurring too frequently
LOG: checkpoints are occurring too frequently (1 second apart)
HINT: Consider increasing the configuration parameter "max_wal_size".Fix: raise max_wal_size in /etc/postgresql/18/main/postgresql.conf and reload (Step 13). If it appears only during a nightly bulk load, that is expected; if it appears all day, the setting is too small for your WAL volume.
ERROR: permission denied to execute CHECKPOINT command
ERROR: permission denied to execute CHECKPOINT command
DETAIL: Only roles with privileges of the "pg_checkpoint" role may execute this command.Fix: only superusers and members of the predefined role pg_checkpoint can run CHECKPOINT. If a maintenance role really needs it, GRANT pg_checkpoint TO maint_user; (undo with REVOKE). Better still, question why it needs to: the documentation says CHECKPOINT is not intended for use during normal operation. Running it from cron every minute adds I/O bursts and full page images and gains nothing.
ERROR: column "checkpoints_timed" does not exist
ERROR: column "checkpoints_timed" does not exist
LINE 1: SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwri...
^Fix: monitoring queries written for PostgreSQL 16 or earlier break on 17 and 18. Use the new names: checkpoints_timed is now pg_stat_checkpointer.num_timed, checkpoints_req is num_requested, buffers_checkpoint is buffers_written, checkpoint_write_time and checkpoint_sync_time are write_time and sync_time. buffers_backend was removed; use the writes column of pg_stat_io for backend_type 'client backend'.
ERROR: 10 s is outside the valid range for parameter "checkpoint_timeout"
ERROR: 10 s is outside the valid range for parameter "checkpoint_timeout" (30 s .. 86400 s)Fix: checkpoint_timeout accepts 30s to 1d. ALTER SYSTEM rejects a bad value immediately; if you type the same value into postgresql.conf, a reload logs the error and keeps the old value, so always check with SHOW after a reload.
Reading a long write= time as a problem
A scheduled checkpoint with write=223 s is healthy: it is throttled to finish around 90% of the interval. Look instead at sync= and longest= (slow storage), at a high share of requested checkpoints, and at client backend writes in pg_stat_io.
Setting checkpoint_timeout or max_wal_size very high without checking recovery time and disk
Fewer checkpoints means more WAL to replay after a crash and more WAL kept in pg_wal. Before raising them, make sure the recovery time is acceptable and that the disk holding /var/lib/postgresql/18/main/pg_wal has room well beyond max_wal_size.
Practice exercise
- On your lab server, set max_wal_size to 128MB, reload, and load one million rows into a test table. How many checkpoints were requested, and what reason does the log show for them?
- Write one query that shows, since the last reset, the share of requested checkpoints and the number of pages each of the three writer processes wrote. Restore max_wal_size = 1GB afterwards.
Quick reference
# Show checkpoint and background writer settings
sudo -u postgres psql -X -c "SELECT name, setting, unit, context, boot_val FROM pg_settings WHERE name LIKE 'checkpoint%' OR name LIKE 'bgwriter%' OR name IN ('max_wal_size','min_wal_size','log_checkpoints');"
# Find the checkpointer and background writer processes
sudo -u postgres psql -X -c "SELECT pid, backend_type FROM pg_stat_activity WHERE backend_type IN ('checkpointer','background writer');"
# Where is the last checkpoint and redo point?
sudo -u postgres psql -X -c "SELECT checkpoint_lsn, redo_lsn, redo_wal_file, checkpoint_time FROM pg_control_checkpoint();"
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata /var/lib/postgresql/18/main | grep -i checkpoint
# Count dirty buffers (needs the pg_buffercache extension in that database)
sudo -u postgres psql -X -d lab -c "SELECT buffers_used, buffers_dirty FROM pg_buffercache_summary();"
# Force a checkpoint (superuser or pg_checkpoint; not for routine use)
sudo -u postgres psql -X -c "CHECKPOINT;"
# Read checkpoint log lines
sudo grep -E "checkpoint (starting|complete)|too frequently" /var/log/postgresql/postgresql-18-main.log | tail -n 20
# Checkpoint health: share of requested checkpoints
sudo -u postgres psql -X -c "SELECT num_timed, num_requested, round(100.0 * num_requested / NULLIF(num_timed + num_requested, 0), 1) AS pct_requested, buffers_written, stats_reset FROM pg_stat_checkpointer;"
# Who writes dirty pages?
sudo -u postgres psql -X -c "SELECT buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter;"
sudo -u postgres psql -X -c "SELECT backend_type, sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object = 'relation' GROUP BY backend_type ORDER BY writes DESC NULLS LAST;"
# WAL volume and full page images
sudo -u postgres psql -X -c "SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) FROM pg_stat_wal;"
# Reset counters before a measurement
sudo -u postgres psql -X -c "SELECT pg_stat_reset_shared('checkpointer'); SELECT pg_stat_reset_shared('bgwriter'); SELECT pg_stat_reset_shared('io'); SELECT pg_stat_reset_shared('wal');"
# After editing max_wal_size, checkpoint_timeout or bgwriter_* in /etc/postgresql/18/main/postgresql.conf: reload (no restart needed)
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name IN ('max_wal_size','checkpoint_timeout');"Summary and what's next
- A checkpoint writes every dirty page to the data files, syncs them and records a redo point; crash recovery starts there, and older WAL can be recycled.
- Checkpoints start on time (checkpoint_timeout, default 5min), on WAL volume (max_wal_size, default 1GB), on CHECKPOINT, or at shutdown; the log line names the reason.
- Scheduled checkpoints are throttled by checkpoint_completion_target (0.9); a long write= time is normal, a long sync= time is not.
- In PostgreSQL 17 and 18, checkpoint statistics are in pg_stat_checkpointer; backend writes are in pg_stat_io.
- Frequent requested checkpoints and the too frequently warning mean max_wal_size is too small; in the lab it made 2.5 times more WAL.
- The background writer cleans buffers ahead of demand so queries do not write pages themselves; tune it only after measuring client backend writes.
- All checkpoint and bgwriter settings need only a reload; record the old value and know how to undo every change.
In Part 17 we move to backup and recovery with logical backups using pg_dump, pg_dumpall and pg_restore.