# Lesson 10: WAL / Checkpoint > Native Markdown source page: WAL / Checkpoint --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba2_10_wal_checkpoint.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_10_wal_checkpoint.html) > **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip) > **PDF pages:** 25 ## Page 1: WAL / Checkpoint {#source-page-001} ### Copyright © Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - Checkpointer Process - Background Writer Process - Monitoring ## Page 3: Checkpoint {#source-page-003} **Why Checkpoint Is Required** - Checkpoint Procedure - Crash Recovery Algorithm - Tuning ## Page 4: Why Checkpoint Is Required {#source-page-004} **Size of stored WAL records** - which WAL segment to start recovery from? - actively used page may not be evicted from cache - Recovery time - how long does crash recovery take? ### Speaker notes If no special measures are taken, an actively used page, once loaded into the buffer cache, might never get evicted. This means that crash recovery procedure will have to scan all the WAL records created since the server was started. In practice, it is indeed unacceptable for two main reasons. First, WAL files take up a lot of space, and all of them have to be stored on the server. Second, the recovery time could be extremely long as countless WAL records need to be processed. To prevent this, the checkpoint procedure flushes all dirty pages to disk without evicting them from the cache. A dedicated background process called checkpointer periodically performs a checkpoint. Once a checkpoint is completed, WAL records that precede its start are no longer required for recovery. ## Page 5: Checkpoint Procedure {#source-page-005} ![Source slide page 5: Checkpoint Procedure](images/page-005.png){#source-figure-005 caption="Source slide page 5: Checkpoint Procedure"} dirty CLOG buffers are written to disk ### Speaker notes Let’s examine in more detail what happens when a checkpoint is performed. First, RAM contains structures other than the buffer cache whose contents must be persisted to disk. In particular, the checkpoint flushes transaction status buffers (CLOG) to disk. Since the number of these buffers is small (only 128), they get written immediately. ## Page 6: Checkpoint Procedure {#source-page-006} ![Source slide page 6: Checkpoint Procedure](images/page-006.png){#source-figure-006 caption="Source slide page 6: Checkpoint Procedure"} modified pages in the buffer cache are marked ### Speaker notes Second, the main function of a checkpoint is to flush all pages from the buffer cache that were dirty at the moment the checkpoint started. Since the buffer cache can be very large, flushing all pages at once is problematic, as it would interrupt normal server operations and put excessive load on the I/O subsystem. Therefore, the checkpoint is spread out over time and turns from a point into an interval. Initially, all pages modified up to this point are marked in the buffer header with a special flag... ## Page 7: Checkpoint Procedure {#source-page-007} ![Source slide page 7: Checkpoint Procedure](images/page-007.png){#source-figure-007 caption="Source slide page 7: Checkpoint Procedure"} marked pages are gradually written to disk, the flag in the buffer header is reset - checkpoint\_completion\_target = 0.9 ### Speaker notes ... and then the checkpoint gradually iterates through all the buffers and flushes the marked pages to disk. Note that the pages are only written to disk, but not evicted from the cache. Therefore, the checkpoint procedure ignores both the buffer’s usage count and pin count. The marked buffers can also be written by backends, whichever reaches the buffer first. In any case, the flag is reset when the buffer is written, so the buffer will not be written twice. Pages continue to change in the buffer cache during the checkpoint. However, newly dirty buffers are not processed by the checkpoint, as they were not dirty when it started. The rate at which dirty buffers are written is controlled by the checkpoint\_completion\_target configuration parameter. It specifies the fraction of time between two adjacent checkpoints that should be used for writing pages. With the default value of 0.9, PostgreSQL should complete the checkpoint procedure shortly before the next scheduled one. Using values higher than the default 0.9 is not recommended, as the process may actually take a bit longer than specified by the parameter. ## Page 8: Checkpoint Procedure {#source-page-008} ![Source slide page 8: Checkpoint Procedure](images/page-008.png){#source-figure-008 caption="Source slide page 8: Checkpoint Procedure"} a checkpoint completion record is written to the WAL indicating its starting point - checkpoint’s LSN is written to the pg\_control file ### Speaker notes When completed, the checkpoint creates a WAL record marking the end of the checkpoint. This WAL record contains the LSN of the checkpoint’s start. Since the checkpoint does not write a WAL record when it starts, this LSN can reference any valid WAL record. Besides, a reference to this WAL record is written to the PGDATA/global/ pg\_control file. This allows the system to quickly identify the last completed checkpoint. ## Page 9: Checkpoint {#source-page-009} Examine /var/lib/postgresql/16/main/global/pg\_control control file. This can be done using the pg\_controldata utility. ```sh student$ sudo /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main ``` ```text pg_control version number: 1300 Catalog version number: 202307071 Database system identifier: 7650133164297395271 Database cluster state: in production pg_control last modified: Thu 11 Jun 2026 05:33:03 PM MSK Latest checkpoint location: 0/1BAF930 Latest checkpoint's REDO location: 0/1BAF930 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:743 Latest checkpoint's NextOID: 16390 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 722 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: Thu 11 Jun 2026 04:44:31 PM MSK Fake LSN counter for unlogged rels: 0/3E8 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no wal_level setting: replica wal_log_hints setting: off max_connections setting: 100 max_worker_processes setting: 8 max_wal_senders setting: 10 max_prepared_xacts setting: 0 max_locks_per_xact setting: 64 track_commit_timestamp setting: off Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float8 argument passing: by value Data page checksum version: 1 Mock authentication nonce: 0ac3fd7ced8cd3a3bf09a85ad1a792601ca4dc6655d42517f8f3ef4f9a36ae81 ``` We see a lot of reference information, with particular interest in the latest checkpoint data and cluster state “in production”. Let’s manually perform a checkpoint and see how this is reflected in the WAL and the control file. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/1BAF9A8 (1 row) ``` ```sql => SELECT pg_walfile_name('0/1BAF9A8'); ``` ## Page 10: Page 10 {#source-page-010} ```text pg_walfile_name -------------------------- 000000010000000000000001 (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/1BAFA58 (1 row) ``` A record is written to the WAL indicating the checkpoint completion (CHECKPOINT\_ONLINE): ```sql => CREATE EXTENSION pg_walinspect; ``` ```sql CREATE EXTENSION ``` ```sql => SELECT start_lsn, record_type, replace(description, ';', E'\n') AS description FROM pg_get_wal_records_info('0/1BAF9A8','0/1BAFA58') WHERE record_type = 'CHECKPOINT_ONLINE' \gx ``` ```text -[ RECORD 1 ]----------------------------------------- start_lsn | 0/1BAF9E0 record_type | CHECKPOINT_ONLINE description | redo 0/1BAF9A8 + | tli 1 + | prev tli 1 + | fpw true + | xid 0:743 + | oid 16390 + | multi 1 + | offset 0 + | oldest xid 722 in DB 1 + | oldest multi 1 in DB 1 + | oldest/newest commit timestamp xid: 0/0+ | oldest running xid 743 + | online ``` In the description of the record, the LSN of the checkpoint start (redo) is specified. Let’s compare with the control file data: ```sh student$ sudo /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main | egrep 'Latest.*location' ``` ```text Latest checkpoint location: 0/1BAF9E0 Latest checkpoint's REDO location: 0/1BAF9A8 ``` The LSN information is identical. ## Page 11: Recovery {#source-page-011} ![Source slide page 11: Recovery](images/page-011.png){#source-figure-011 caption="Source slide page 11: Recovery"} **During server startup after a crash** - 1 Find LSN0 corresponding to the start of the last completed - checkpoint - 2 Apply each WAL record starting from LSN0, - if the record’s LSN is greater than the page's LSN - 3 Abort transactions that were started but not committed - 4 Overwrite unlogged tables using their init files - 5 Perform a checkpoint ### Speaker notes If the server fails, at the subsequent start, the startup process detects this by looking into the pg\_control file to find the status different from "shut down“. Automatic recovery is done in this case. The process first reads the LSN of the last completed checkpoint record from that same pg\_control file. From this record, the process determines LSN0, the starting position of that checkpoint. (In case of recovery from a backup, the backup\_label file is present, and the checkpoint record is read from that file instead. For more details, see the DBA3 course.) Next, the startup process reads the WAL records one by one, starting from the determined position, sequentially applying records to pages, if necessary (the need is determined by comparing LSN of the on-disk page with LSN of the WAL record). Page modifications occur in the buffer cache, just as they do during normal operations. Records related to CLOG pages restore the status of transactions. Transactions not committed by the end of the recovery are considered aborted; their changes are not visible in any data snapshots. WAL records are applied to files in a similar way: for example, if it is clear from a record that the file should be created, but it does not exist, the file is created. At the end of the process, all unlogged tables are overwritten using the templates stored in the init files. At this point, the startup process completes the recovery and performs a checkpoint. ## Page 12: Crash Recovery {#source-page-012} Now we simulate a crash by forcibly shutting down the server. ```sql => CREATE DATABASE wal_checkpoint; ``` ```sql CREATE DATABASE ``` ```sql => \c wal_checkpoint ``` ```text You are now connected to database "wal_checkpoint" as user "student". ``` ```sql => CREATE TABLE test(t text); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO test VALUES ('Before crash'); ``` ```sql INSERT 0 1 ``` ```sh student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid) ``` There are currently WAL records on disk, but table pages have not been flushed. Check the cluster state: ```sh student$ sudo /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main | grep state ``` ```text Database cluster state: in production ``` The state has not changed. Upon startup, PostgreSQL will detect the crash and initiate the recovery. ```sh student$ sudo pg_ctlcluster 16 main start ``` ```sh student$ tail -n 6 /var/log/postgresql/postgresql-16-main.log ``` ```text 2026-06-11 17:33:07.592 MSK [22367] LOG: redo starts at 0/1BAF9A8 2026-06-11 17:33:07.602 MSK [22367] LOG: invalid record length at 0/201D6B0: expected at least 24, got 0 2026-06-11 17:33:07.602 MSK [22367] LOG: redo done at 0/201D688 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s 2026-06-11 17:33:07.607 MSK [22365] LOG: checkpoint starting: end-of-recovery immediate wait 2026-06-11 17:33:07.747 MSK [22365] LOG: checkpoint complete: wrote 975 buffers (6.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.007 s, sync=0.132 s, total=0.141 s; sync files=327, longest=0.002 s, average=0.001 s; distance=4535 kB, estimate=4535 kB; lsn=0/201D6B0, redo lsn=0/201D6B0 2026-06-11 17:33:07.749 MSK [22364] LOG: database system is ready to accept connections ``` ```sh student$ psql wal_checkpoint ``` ```sql => SELECT * FROM test; ``` ```text t -------------- Before crash (1 row) ``` As we can see, the table and data have been recovered. Stop the instance correctly. When it is stopped, PostgreSQL performs a checkpoint to flush all data to disk. ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main stop ``` Check the cluster state: ```sh student$ sudo /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main | grep state ``` ```text Database cluster state: shut down ``` Now the state is “shut down,” which corresponds to a correct stop. ## Page 13: Checkpoint frequency {#source-page-013} ![Source slide page 13: Checkpoint frequency](images/page-013.png){#source-figure-013 caption="Source slide page 13: Checkpoint frequency"} ↑ - checkpoint\_timeout = 5min - max\_wal\_size - = 1GB ### Speaker notes Checkpoints are typically set up based on the following factors. First, we need to determine an acceptable checkpoint frequency (based on the recovery time requirements and the size of WAL files generated during that period under normal load). The less frequently we need to run checkpoints, the better, as this reduces overhead. The acceptable frequency is set using the checkpoint\_timeout parameter (the default value of 5 minutes is too short, it is often increased up to 30 minutes). However, the load may exceed our planned capacity and too many WAL records may be generated within the specified time. To prevent this, we specify the total permissible size of WAL records in the max\_wal\_size parameter. For crash recovery, the server needs to keep WAL files from the start of the last completed checkpoint until the start of the current one (the size between checkpoints), plus WAL files that have accumulated during the current checkpoint. Therefore, the total WAL size can be estimated as: (1 + checkpoint\_completion\_target) × size between checkpoints. Thus, most checkpoints are performed on schedule, once every checkpoint\_timeout. However, under high load, checkpoints are performed more frequently to manage not to exceed the max\_wal\_size limit. ## Page 14: WAL File Size {#source-page-014} **Server stores WAL files** - required for recovery (typically < max\_wal\_size) - not yet consumed by replication slots - not yet archived, if continuous archiving is enabled - not exceeding wal\_keep\_size limit - not exceeding min\_wal\_size threshold (during recycling) - Parameters - max\_wal\_size - = 1GB - wal\_keep\_size - = 0 - wal\_recycle - = on - min\_wal\_size - = 80MB ### Speaker notes Note that the size specified in the max\_wal\_size parameter can be exceeded. This is not a strict limit, but a target for the checkpointer, which regulates its intensity when flushing dirty buffers. Moreover, the server cannot remove WAL files that have not been consumed by replication slots, and files that have not been archived during continuous archiving. This can lead to excessive disk space usage. So, when these features are used, continuous monitoring is essential. In cases where a replica falls behind, we can use the wal\_keep\_size parameter to set a minimum amount of WAL files that will be kept after a checkpoint. While this does not guarantee that a specific WAL record will be kept until the replica needs it, it allows the replication to proceed without a replication slot. By default, a certain number of WAL files are not deleted, instead they are renamed and reused. The min\_wal\_size parameter sets the minimum amount of WAL to keep. This avoids the overhead of repeatedly creating and deleting files. However, in copy-on-write filesystems, it is faster to create a new file, so it is recommended to disable recycling for them by setting wal\_recycle=off. ONFIG-WAL-CHECKPOINTS ## Page 15: WAL Size {#source-page-015} Start the instance again. ```sh student$ sudo pg_ctlcluster 16 main start ``` Set the minimum min\_wal\_size value and disable recycling so that no more than two segments remain after a checkpoint. ```sh student$ psql wal_checkpoint ``` ```sql => ALTER SYSTEM SET min_wal_size = '32MB'; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET wal_recycle = off; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Insert rows into the table: ```sh student$ psql wal_checkpoint ``` ```sql => INSERT INTO test SELECT g.id::text FROM generate_series(1, 1_000_000) AS g(id); ``` ```sql INSERT 0 1000000 ``` List of WAL files: ```sql => SELECT * FROM pg_ls_waldir() ORDER BY name; ``` ```text name | size | modification --------------------------+----------+------------------------ 000000010000000000000002 | 16777216 | 2026-06-11 17:33:12+03 000000010000000000000003 | 16777216 | 2026-06-11 17:33:12+03 000000010000000000000004 | 16777216 | 2026-06-11 17:33:13+03 000000010000000000000005 | 16777216 | 2026-06-11 17:33:13+03 (4 rows) ``` Let’s manually perform a checkpoint and examine the WAL again: ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT * FROM pg_ls_waldir() ORDER BY name; ``` ```text name | size | modification --------------------------+----------+------------------------ 000000010000000000000005 | 16777216 | 2026-06-11 17:33:13+03 000000010000000000000006 | 16777216 | 2026-06-11 17:33:13+03 (2 rows) ``` After the checkpoint, no more than two segments remain in WAL, including the one that was current at the start. And if there were any changes in the cluster after the start of the checkpoint, other segments could appear in the WAL. ## Page 16: Background Writer {#source-page-016} **Background Writer Process** - Tuning ## Page 17: Background Writer Process {#source-page-017} ![Source slide page 17: Background Writer Process](images/page-017.png){#source-figure-017 caption="Source slide page 17: Background Writer Process"} ### Speaker notes When a backend process needs to evict a page from the buffer cache, it may find that the buffer is dirty and will have to write its contents to disk. To reduce the frequency of such situations, there is also a background writer process (bgwriter) in addition to the checkpointer process. The background writer uses the same buffer search algorithm as backends, but it operates its own pointer which can be ahead of the next victim pointer but never falls behind it. The background writer flushes a buffer that meets all of the following criteria: ● Contain changed data (dirty) ● Not pinned (pin count = 0) ● Have usage count = 0 Thus, by running ahead, the background writer identifies buffers that are likely to be evicted soon. This strategy increases the probability that a victim buffer chosen by a backend is already clean. ## Page 18: Background Writer Process {#source-page-018} **Algorithm** - sleep for bgwriter\_delay - if the average number of buffers requested per cycle is N, then write - N × bgwriter\_lru\_multiplier ≤ bgwriter\_lru\_maxpages - dirty buffers - Parameters - bgwriter\_delay - = 200ms - bgwriter\_lru\_maxpages - = 100 - bgwriter\_lru\_multiplier - = 2.0 - bgwriter\_flush\_after - = 512kB ### Speaker notes The background writer process operates in cycles of up to bgwriter\_lru\_maxpages, sleeping for bgwriter\_delay between cycles. (So, if bgwriter\_lru\_maxpages is set to zero, the background writer will be disabled.) The exact number of buffers to write is determined by the average number of buffers recently requested by backends (using a moving average to smooth out variations between cycles while avoiding reliance on distant history). This calculated number of buffers is then multiplied by the bgwriter\_lru\_multiplier. If the process does not find any dirty buffers (indicating no system activity), it enters a sleep state, from which it is awakened by a backend’s request for a buffer. After that, the process wakes up and resumes normal operation. When large amounts of data need to be written, the server instructs the OS to perform an intermediate flush to storage (fsync) once the volume specified by the bgwriter\_flush\_after parameter is reached. This reduces transaction commit latency and the time needed for synchronization at the end of a checkpoint. The background writer should be configured after checkpointer tuning is complete. Both processes together should keep up with writing dirty buffers before backends require them. ## Page 19: Monitoring {#source-page-019} **Checkpoints** - checkpoint\_warning = 30s - log\_checkpoints = on - startup and checkpointer process statuses - Dirty buffer writes by processes - pg\_stat\_bgwriter and pg\_stat\_io views ### Speaker notes The checkpoint\_warning parameter allows to issue a warning in the log if checkpoints are performed more often than defined by the value. If this occurs frequently, consider increasing max\_wal\_size or decreasing the checkpoint\_timeout interval. The log\_checkpoints parameter, which is enabled by default, logs detailed information about each completed checkpoint. When the server is in recovery mode, we can monitor the status of the startup and checkpointer processes using OS utilities, such as ps. Statistics for processes writing dirty buffers (checkpoint, background writer, and backends), are displayed in the pg\_stat\_bgwriter view, and starting from version 16, in the pg\_stat\_io view. In PostgreSQL version 17, a new pg\_stat\_checkpointer view will be introduced, which contains checkpointer process statistics currently found in pg\_stat\_bgwriter. ## Page 20: Monitoring {#source-page-020} The checkpoint\_warning parameter issues a warning if checkpoints caused by exceeding the WAL size limit occur too frequently. Its default value is: ```text => SHOW checkpoint_warning; ``` ```text checkpoint_warning -------------------- 30s (1 row) ``` It should be adjusted to the checkpoint\_timeout value. The log\_checkpoints parameter allows us to log information about the checkpoints. It is enabled by default (starting from PostgreSQL 15): ```text => SHOW log_checkpoints; ``` ```text log_checkpoints ----------------- on (1 row) ``` Insert some data into the table and perform the checkpoint. ```sql => INSERT INTO test SELECT g.id::text FROM generate_series(1,100_000) AS g(id); ``` ```sql INSERT 0 100000 ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` Here is the type of information available in the WAL: ```sh student$ tail -n 2 /var/log/postgresql/postgresql-16-main.log ``` ```text 2026-06-11 17:33:13.514 MSK [22660] LOG: checkpoint starting: immediate force wait 2026-06-11 17:33:13.522 MSK [22660] LOG: checkpoint complete: wrote 445 buffers (2.7%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.002 s, sync=0.004 s, total=0.009 s; sync files=2, longest=0.003 s, average=0.002 s; distance=6274 kB, estimate=57043 kB; lsn=0/6379C38, redo lsn=0/6379C00 ``` Statistics for both the checkpointer and background writer are reflected in a single common view (originally both tasks were solved by one process; then their functions were separated, but the view remained). ```sql => SELECT * FROM pg_stat_bgwriter \gx ``` ```text -[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 0 checkpoints_req | 4 checkpoint_write_time | 22 checkpoint_sync_time | 159 buffers_checkpoint | 5846 buffers_clean | 0 maxwritten_clean | 0 buffers_backend | 5179 buffers_backend_fsync | 0 buffers_alloc | 6118 stats_reset | 2026-06-11 17:33:07.589821+03 ``` checkpoints\_timed — checkpoints on schedule (checkpoint\_timeout) checkpoints\_req — checkpoints by request (max\_wal\_size) and manual checkpoints buffers\_checkpoint — pages flushed during checkpoints buffers\_clean — pages flushed by the background writer buffers\_backend — pages flushed by backends maxwritten\_clean — number of stops upon reaching the bgwriter\_lru\_maxpages In a well-configured system, buffers\_backend value should be significantly lower than the sum of buffers\_checkpoint and buffers\_clean. A high checkpoints\_req value (compared to checkpoints\_timed) indicates that checkpoints occur more frequently than intended. ## Page 21: Page 21 {#source-page-021} However, the information about the amount of I/O provided by the pg\_stat\_bgwriter view is not quite accurate: the buffers\_backend column reflects the results of not only client processes, but also some others (for example, autovacuuming). Relation file extend operations are added to the same column, although this is not a write from the buffer cache to disk. The pg\_stat\_io view we used earlier provides statistics in a more descriptive form: ```sql => SELECT backend_type, sum(writes) AS writes, sum(fsyncs) AS fsyncs, sum(extends) AS extends, op_bytes FROM pg_stat_io WHERE backend_type IN ('checkpointer', 'client backend', 'background writer') GROUP BY backend_type, op_bytes; ``` ```text backend_type | writes | fsyncs | extends | op_bytes -------------------+--------+--------+---------+---------- client backend | 0 | 0 | 0 | 8192 background writer | 0 | 0 | | 8192 checkpointer | 5846 | 333 | | 8192 (3 rows) ``` writes — number of writes fsyncs — number of fsync calls extends — number of relation extend operations ## Page 22: Takeaways {#source-page-022} **The checkpoint procedure limits the amount of stored WAL files and reduces recovery time** - The checkpointer and background writer processes flush dirty buffers to disk - Backends can flush dirty buffers to disk, but as a last resort only ## Page 23: Practice {#source-page-023} **1. Set up checkpoints to run every 30 seconds. Set both min\\\_wal\\\_size and max\\\_wal\\\_size parameters to 32 MB.** - 2. Use the pgbench utility to generate a load of 100 transactions per second for several minutes. - 3. Get the WAL size generated during this period. 4. Review the statistics data and estimate the average size per checkpoint. Were all checkpoints executed on schedule? Explain the result. - 5. Reset the parameters to their default values. ## Page 24: 1. Checkpoint Configuration {#source-page-024} ```sql => ALTER SYSTEM SET checkpoint_timeout = '30s'; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET min_wal_size = '32MB'; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET max_wal_size = '32MB'; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ### 2. Load Let’s initialize tables. ```sql => CREATE DATABASE wal_checkpoint; ``` ```sql CREATE DATABASE ``` ```sh student$ pgbench -i wal_checkpoint ``` ```sql dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.16 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.10 s, vacuum 0.03 s, primary keys 0.03 s). ``` Reset statistics. ```sql => SELECT pg_stat_reset_shared('bgwriter'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` > [!NOTE] > **Note** > Note the current WAL position and start pgbench. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2C7D1D0 (1 row) ``` ```sh student$ pgbench -T 180 -R 100 wal_checkpoint ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. transaction type: scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 180 s number of transactions actually processed: 18076 number of failed transactions: 0 (0.000%) latency average = 0.661 ms ``` ## Page 25: 3. WAL File Size {#source-page-025} ```text latency stddev = 0.911 ms rate limit schedule lag: avg 0.131 (max 36.485) ms initial connection time = 1.941 ms tps = 100.434641 (without initial connection time) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/6E4CD20 (1 row) ``` ```sql => SELECT pg_size_pretty('0/6E4CD20'::pg_lsn - '0/2C7D1D0'::pg_lsn); ``` ```text pg_size_pretty ---------------- 66 MB (1 row) ``` ### 4. Statistics Number of scheduled and requested checkpoints: ```sql => SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter; ``` ```text checkpoints_timed | checkpoints_req -------------------+----------------- 2 | 4 (1 row) ``` Average WAL size per checkpoint: ```sql => SELECT pg_size_pretty(('0/6E4CD20'::pg_lsn - '0/2C7D1D0'::pg_lsn)/6) FROM pg_stat_bgwriter; ``` ```text pg_size_pretty ---------------- 11 MB (1 row) ``` Although the average WAL size per checkpoint does not exceed the set limit, some checkpoints were triggered off schedule. This indicates non-uniform streaming of WAL records (caused by autovacuum and other activity). Therefore, in production systems, measurements should be taken over sufficiently long time intervals. ### 5. Default Settings ```sql => ALTER SYSTEM RESET ALL; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ```