PostgreSQL DBA Part 16: Checkpoints and Background Writer Tuning

PostgreSQL DBA Part 16: Checkpoints and Background Writer Tuning
In this article
  1. What you will learn
  2. Prerequisites
  3. How it works
  4. Look at the checkpointer and its settings
  5. Watch a checkpoint happen
  6. Scheduled and forced checkpoints
  7. Measure what frequent checkpoints cost
  8. How to choose checkpoint settings for steady performance
  9. The background writer: who writes your dirty pages?
  10. Common mistakes and how to fix them
  11. Practice exercise
  12. Quick reference
  13. 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_checkpointer view (both changed in PostgreSQL 17 and 18).
  • How a small max_wal_size causes 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_bgwriter and pg_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 logWhat triggered it
timecheckpoint_timeout passed since the last checkpoint (default 5min). If no WAL was written since the previous checkpoint, it is skipped.
walThe WAL written since the last checkpoint is about to exceed max_wal_size (default 1GB).
immediate force waitSomeone ran the CHECKPOINT command.
shutdownA 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:

ProcessWhen it writesWhere you see it
checkpointerEvery checkpoint: all dirty pagespg_stat_checkpointer, pg_stat_io (backend_type checkpointer), checkpoint log lines
background writerEvery 200ms: a few pages likely to be reused soonpg_stat_bgwriter, pg_stat_io (backend_type background writer)
client backendWhen a query needs a buffer and only dirty ones are freepg_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;"
List checkpoint and bgwriter settings
             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)
Output

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.

ParameterDefaultWhat it controls
checkpoint_timeout5min (range 30s to 1d)Maximum time between automatic checkpoints.
max_wal_size1GBWAL allowed to build up between checkpoints before one is forced. A soft limit.
min_wal_size80MBBelow this much WAL, old segments are always recycled rather than removed.
checkpoint_completion_target0.9Fraction of the interval over which checkpoint writes are spread.
checkpoint_warning30sLog a warning if WAL-triggered checkpoints come closer together than this. 0 disables.
checkpoint_flush_after256kB on LinuxAfter this much checkpoint writing, ask the OS to flush to storage.
log_checkpointsonLog each checkpoint and restartpoint with statistics.
bgwriter_delay200msSleep between background writer rounds.
bgwriter_lru_maxpages100Most buffers the background writer writes per round. 0 disables background writing.
bgwriter_lru_multiplier2.0How far ahead of recent demand the background writer cleans buffers.
bgwriter_flush_after512kB on LinuxAfter 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.conf
Find the lines in postgresql.conf
197:#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 = on
Output

Step 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;"
Show the auxiliary processes
 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)
Output

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"
Read the latest checkpoint
 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 2026
Output

The 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 lab
Create the lab database and connect
CREATE 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);
Run in psql, connected to lab
CREATE DATABASE
CREATE EXTENSION
CREATE TABLE
INSERT 0 200000
Output

Using 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();
Reset counters, then dirty some pages
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)
Output

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();
Force a checkpoint
Timing is on.
CHECKPOINT
Time: 35.188 ms
 buffers_used | buffers_dirty
--------------+---------------
         4259 |             0
(1 row)
Output

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.log
Read the checkpoint log lines
2026-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/4E072E0
Output

Because 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:

FieldMeaning
immediate force waitWhy 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 buffersSmall internal caches (such as transaction status) written. Shown separately since PostgreSQL 18.
0 WAL file(s) added, 0 removed, 1 recycledWhat happened to old WAL segments now that they are no longer needed for crash recovery.
write=0.019 sTime spent writing pages. For a scheduled checkpoint this is long on purpose (throttling).
sync=0.013 s, sync files=9, longest, averageTime spent waiting for fsync, and how many files were synced. A long sync points to slow storage.
total=0.035 sWhole checkpoint duration.
distance=20402 kBWAL written since the previous checkpoint's redo point (about 20 MB).
estimate=34050 kBPostgreSQL's running estimate of WAL per checkpoint cycle, used to decide how many segments to keep.
lsn, redo lsnWhere 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;
Checkpoint counters
 num_timed | num_requested | num_done | buffers_written | slru_written | write_time | sync_time
-----------+---------------+----------+-----------------+--------------+------------+-----------
         0 |             1 |        1 |            2171 |            1 |         19 |        13
(1 row)
Output

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=30
Dirty pages over time
UPDATE 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 | CheckpointWriteDelay
Output (one row every 30 s, some idle rows removed)

For 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.log
Find the scheduled checkpoint
2026-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/6440D90
Output

The 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;
Counters after the scheduled checkpoint
 num_timed | num_requested | num_done | buffers_written | write_time | sync_time
-----------+---------------+----------+-----------------+------------+-----------
         1 |             1 |        2 |            4399 |     223092 |        15
(1 row)
Output

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 = 64MB
/etc/postgresql/18/main/postgresql.conf

max_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
Reload and verify
 max_wal_size
--------------
 64MB
(1 row)

2026-11-11 12:57:12.734 UTC [2235] LOG:  parameter "max_wal_size" changed to "64MB"
Output

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;
Bulk load with a tiny max_wal_size
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)
Output

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 20
Read the warning
2026-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".
Output (excerpt)

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
Initialise pgbench at scale 50
OptionWhat it does
-iInitialization mode: create and fill the pgbench tables.
-s 50Scale factor: multiply the rows generated by 50 (5,000,000 rows in pgbench_accounts).
-qQuiet 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).
Output

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;"
Measurement block: reset, run 60 s, read statistics
pgbench optionWhat it does
-NRun the simple-update built-in script: skips updates of pgbench_tellers and pgbench_branches, avoiding contention on those small tables.
-c 4Number of simulated clients (concurrent database sessions).
-j 2Number of worker threads in pgbench.
-T 60Run 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)
Output

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 = 4GB
/etc/postgresql/18/main/postgresql.conf
sudo 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');"
Reload and verify
        name        | setting | unit | boot_val | pending_restart
--------------------+---------+------+----------+-----------------
 checkpoint_timeout | 900     | s    | 300      | f
 max_wal_size       | 4096    | MB   | 1024     | f
(2 rows)
Output

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)
Output (first part)
Measure (60 s, same workload)max_wal_size 64MBmax_wal_size 4GB, timeout 15min
Checkpoints57 (all requested)0
Pages written by the checkpointer220,3200
Full page images in WAL229,71287,829
WAL generated1828 MB736 MB
Transactions per second3,6903,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)
Output (second part of the Step 13 run)
ColumnViewMeaning
buffers_cleanpg_stat_bgwriterBuffers written by the background writer.
maxwritten_cleanpg_stat_bgwriterTimes the background writer stopped a round because it hit bgwriter_lru_maxpages.
buffers_allocpg_stat_bgwriterBuffers allocated (pages read into shared buffers).
writes (client backend)pg_stat_ioPages that query sessions had to write themselves.
evictionspg_stat_ioTimes 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/round
/etc/postgresql/18/main/postgresql.conf
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -X -c "SELECT name, setting FROM pg_settings WHERE name LIKE 'bgwriter_lru%';"
Reload and verify
          name           | setting
-------------------------+---------
 bgwriter_lru_maxpages   | 1000
 bgwriter_lru_multiplier | 4
(2 rows)
Output

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)
Output

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/round
/etc/postgresql/18/main/postgresql.conf
sudo 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;"
Reload and check the defaults are back
          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)
Output

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;"
Remove the lab databases
DROP DATABASE
DROP DATABASE
Output

Common 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".
Server log

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.
Output

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...
               ^
Output

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)
Output of ALTER SYSTEM SET checkpoint_timeout = '10s';

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

  1. 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?
  2. 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');"
All commands from this part

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.

PT Ganesh

An experienced PostgreSQL DBA/SQL developer with years of expertise in designing and implementing robust database solutions. Proficient in writing complex queries, developing stored procedures, and conducting performance tuning to deliver highly efficient data-driven applications that meet business requirements.

Home
SQL
PLSQL
Interview Q&A