# Lesson 11: WAL / WAL Tuning > Native Markdown source page: WAL / WAL Tuning --- 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_11_wal_tuning.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_11_wal_tuning.html) > **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip) > **PDF pages:** 22 ## Page 1: WAL / WAL Tuning {#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} - WAL Levels and Use Cases - Write Reliability - Performance ## Page 3: WAL Levels {#source-page-003} **Settings** - the higher the level, - the more information - wal\_level = replica - is in the WAL - Minimal - crash recovery - Replica - point-in-time recovery, physical replication - + bulk data operations, locks, IDs of running transactions - Logical - logical replication - + information for logical decoding ### Speaker notes Beyond its primary purpose of restoring consistency after a crash, the WAL can be used in other scenarios, if additional information is recorded. Several WAL levels are available, set by the wal\_level parameter. The minimal level contains information needed only for crash recovery. Bulk data operations (such as CREATE TABLE AS SELECT, CREATE INDEX, etc.) are not logged; durability of these operations is guaranteed by immediately flushing the data to disk. The replica level (the default) enables system recovery from hot backups created with the pg\_basebackup utility. To achieve this, all data changes are logged, including bulk operations. This level also enables physical replication, i.e. streaming WAL records to another server (these topics are covered in the DBA3 course). For this purpose, the WAL includes information about certain locks. The logical level is used for logical replication and it must be enabled on the publisher. In this case, the WAL includes additional information that makes it possible to accurately decode the meaning of operations from the WAL records. The higher the level, the more information is written to the WAL, consequently increasing its total size. ## Page 4: WAL Levels {#source-page-004} Let’s use a new database. ```sql => CREATE DATABASE wal_tuning; ``` ```sql CREATE DATABASE ``` ```sql => \c wal_tuning ``` ```text You are now connected to database "wal_tuning" as user "student". ``` ```sql => CREATE EXTENSION pg_walinspect; ``` ```sql CREATE EXTENSION ``` Set the WAL level to minimal (this also requires setting max\_wal\_senders to zero and restarting the server). ```sql => ALTER SYSTEM SET wal_level = minimal; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET max_wal_senders = 0; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql wal_tuning ``` Examine how CREATE TABLE AS SELECT is logged. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/1FF13F8 (1 row) ``` ```sql => CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000); ``` ```sql SELECT 3000 ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2026920 (1 row) ``` WAL size: ```sql => SELECT '0/2026920'::pg_lsn - '0/1FF13F8'::pg_lsn; ``` ```text ?column? ---------- 218408 (1 row) ``` No WAL records are related to the row inserts into the table: ```sql => SELECT resource_manager, record_length, xid, start_lsn, record_type FROM pg_get_wal_records_info('0/1FF13F8','0/2026920') WHERE record_type IN ('CREATE','INSERT+INIT','COMMIT') ORDER BY start_lsn; ``` ```sql resource_manager | record_length | xid | start_lsn | record_type ------------------+---------------+-----+-----------+------------- Storage | 42 | 0 | 0/1FF52E0 | CREATE Transaction | 34 | 745 | 0/20268F8 | COMMIT (2 rows) ``` At the minimal level, changes made by CREATE TABLE AS SELECT, CREATE INDEX, TRUNCATE and some other statements are not logged. These operators always perform their own synchronization to ensure durability. The WAL contains only records necessary for crash recovery. ## Page 5: Page 5 {#source-page-005} Drop the table, restore the default level (replica) and repeat the experiment. ```sql => DROP TABLE t_wal; ``` ```sql DROP TABLE ``` ```sql => ALTER SYSTEM RESET wal_level; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM RESET max_wal_senders; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql wal_tuning ``` ```text => SHOW wal_level; ``` ```text wal_level ----------- replica (1 row) ``` Let’s see how CREATE TABLE AS SELECT is logged now. ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2030B50 (1 row) ``` ```sql => CREATE TABLE t_wal(n) AS SELECT 1 FROM generate_series(1,3000); ``` ```sql SELECT 3000 ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2073D98 (1 row) ``` WAL size has increased: ```sql => SELECT '0/2073D98'::pg_lsn - '0/2030B50'::pg_lsn; ``` ```text ?column? ---------- 275016 (1 row) ``` In addition to system catalog changes, the WAL now includes the following records: CREATE — relation file creation INSERT+INIT — row insertion into the table COMMIT — transaction commit ```sql => SELECT resource_manager, record_length, xid, start_lsn, record_type FROM pg_get_wal_records_info('0/2030B50','0/2073D98') WHERE record_type IN ('CREATE','INSERT+INIT','COMMIT') ORDER BY start_lsn; ``` ## Page 6: Page 6 {#source-page-006} ```sql resource_manager | record_length | xid | start_lsn | record_type ------------------+---------------+-----+-----------+------------- Storage | 42 | 0 | 0/20340E0 | CREATE Heap | 59 | 747 | 0/2044BC8 | INSERT+INIT Heap | 59 | 747 | 0/2048478 | INSERT+INIT Heap | 59 | 747 | 0/204BD10 | INSERT+INIT Heap | 59 | 747 | 0/204F5C0 | INSERT+INIT Heap | 59 | 747 | 0/2052E70 | INSERT+INIT Heap | 59 | 747 | 0/2056720 | INSERT+INIT Heap | 59 | 747 | 0/2059FB8 | INSERT+INIT Heap | 59 | 747 | 0/205D868 | INSERT+INIT Heap | 59 | 747 | 0/2061118 | INSERT+INIT Heap | 59 | 747 | 0/20649C8 | INSERT+INIT Heap | 59 | 747 | 0/2068278 | INSERT+INIT Heap | 59 | 747 | 0/206BB10 | INSERT+INIT Heap | 59 | 747 | 0/206F3C0 | INSERT+INIT Heap | 59 | 747 | 0/2072C70 | INSERT+INIT Transaction | 421 | 747 | 0/2073BF0 | COMMIT (16 rows) ``` At the replica level, the WAL contains all data changes, they can be applied to a physical backup or a replica. ## Page 7: Write Reliability {#source-page-007} **Caching** - Data Corruption - Non-Atomic Page Writes ## Page 8: Caching {#source-page-008} ![Source slide page 8: Caching](images/page-008.png){#source-figure-008 caption="Source slide page 8: Caching"} **Disk Synchronization** - data must reach persistent storage - through multiple cache layers - DBMS requests the OS to perform synchronization - using the method specified in wal\_sync\_method - double caching must be considered - Settings - fsync = on - wal\_sync\_method - (pg\_test\_fsync utility) ### Speaker notes Reliability is affected by several factors. Firstly, there are various cache layers between the database and persistent storage (such as a hard disk platter). When durable writes are required, PostgreSQL uses the method specified in the wal\_sync\_method parameter. While there are several options, two main approaches exist: either issuing a sync command to the OS after writing (fsync, fdatasync), or opening/writing a file with a special flag that requests synchronization, and when applicable, direct I/O bypassing the OS cache. The pg\_test\_fsync tool helps determine the most suitable method for your specific OS and file system. Regardless of the method chosen, this operation incurs significant overhead. The administrator should also ensure that data actually reaches the disk and is not delayed in controller or disk cache. If the controller cache defers writes, the controller should have a battery backup unit. For disks, write-through mode is recommended. Technically, we can disable synchronization (using the fsync parameter), but this essentially eliminates any durability guarantees. The only reasonable scenario for disabling this parameter is temporarily boosting performance during operations where data can be easily restored (for example, initial data migration). ## Page 9: Data Corruption {#source-page-009} **WAL record checksums** - always enabled, CRC-32 - Page checksums - should be enabled during cluster initialization - overhead, ```text initdb -k ``` - increased - WAL size - Settings - data\_checksums - ignore\_checksum\_failure = off ### Speaker notes Secondly, data can also be corrupted on storage media, when transmitted over interface cables, etc. Some of these errors are handled at the hardware level, but others are not. For quick detection of an issue, checksums are always provided in WAL records. Data pages can also be protected with checksums. While it is better to enable them during cluster initialization, they can also be activated later using the pg\_checksums utility when the server is stopped. In a production environment, checksums must be enabled despite the overhead of their calculation and verification. This reduces the probability of undetected corruption. We can check whether checksums are enabled using the data\_checksums parameter (read-only). The ignore\_checksum\_failure parameter allows a transaction that read a corrupted page to continue without interruption, though it is not recommended to enable it in production environment. ## Page 10: Non-Atomic Page Writes {#source-page-010} **Full page image** - page might be partially written if a crash occurs - full page image (FPI) is written to the WAL - upon its first modification after a checkpoint - during recovery, WAL records are applied to the saved image - Settings - increases - WAL size - full\_page\_writes = on - wal\_compression = off ### Speaker notes Thirdly, there is an issue of atomicity of writing. A data page is 8 KB in size (or larger: 16 KB, 32 KB), while at the low level, writing is done in blocks that are typically smaller (512 bytes, 4 KB, etc.). Therefore, in case of a power outage, a data page can be written partially. It is clear that during recovery, it makes no sense to apply regular WAL records to such a page. To prevent this, PostgreSQL can write a full page image (FPI) to the WAL the first time a page is modified after a checkpoint. This feature is turned on/off by the full\_page\_writes parameter and should only be disabled if the file system and hardware themselves guarantee atomic writing. When we encounter an FPI in the WAL during recovery, we definitely write it to disk (since it is more reliable, as it is protected by a checksum like all WAL records). Then we apply regular WAL records to this restored page. Although PostgreSQL excludes unused space from FPIs, they still significantly increase WAL volume. If page checksums are enabled in the cluster, an additional WAL record is generated when hint bits are modified, reflecting the checksum change. We can reduce WAL size by enabling FPI compression with the wal\_compression parameter. Supported compression methods are pglz, lz4, and zstd. “On” selects the pglz method, “off” disables compression. ## Page 11: Checksums {#source-page-011} Create another table: ```sql => CREATE TABLE t(id integer); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t VALUES (1),(2),(3); ``` ```sql INSERT 0 3 ``` Here is the file containing the data: ```sql => SELECT pg_relation_filepath('t'); ``` ```text pg_relation_filepath ---------------------- base/16390/16404 (1 row) ``` Stop the server and change several bytes in the page (erase the LSN of the last WAL record from the header). ```sh student$ sudo pg_ctlcluster 16 main stop ``` ```sh student$ sudo dd if=/dev/zero of=/var/lib/postgresql/16/main/base/16390/16404 oflag=dsync conv=notrunc bs=1 count=8 ``` ```text 8+0 records in 8+0 records out 8 bytes copied, 0.00252789 s, 3.2 kB/s ``` Stopping the server is not strictly necessary. This scenario requires the following: The page is written to disk and evicted from the buffer cache. Corruption occurs. The page is read from disk. Now start the server. ```sh student$ sudo pg_ctlcluster 16 main start ``` ```sh student$ psql wal_tuning ``` Let’s try to read the table: ```sql => SELECT * FROM t; ``` ```text WARNING: page verification failed, calculated checksum 40449 but expected 18950 ERROR: invalid page in block 0 of relation base/16390/16404 ``` The ignore\_checksum\_failure parameter allows us to try reading a table, despite the risk of getting corrupted data (e.g., if a backup is not available): ```text => SET ignore_checksum_failure = on; ``` ```text SET ``` ```sql => SELECT * FROM t; ``` ```text WARNING: page verification failed, calculated checksum 40449 but expected 18950 id ---- 1 2 3 (3 rows) ``` ## Page 12: Performance {#source-page-012} **I/O Pattern** - Synchronous Writing - Asynchronous Writing ## Page 13: I/O Pattern {#source-page-013} **Continuous writing** - sequential writes, no random access - I/O pattern differs from the rest of the system - under high load — placement on separate physical disks - (symbolic link from PGDATA/pg\_wal) - Infrequent reading - during recovery - when walsender processes are running and the replica cannot keep up - with receiving records ### Speaker notes During normal operation, WAL files are written continuously and sequentially. Since there is no random access, regular HDDs handle this I/O pattern perfectly well. However, this I/O pattern differs significantly from how data files are accessed. Therefore, it is often more advisable to place the WAL on a separate physical disk (or disks) mounted to the server’s filesystem. Instead of the PGDATA/pg\_wal directory, we should create a symbolic link pointing to the appropriate directory. However, there is one situation where WAL files should to be read (besides the obvious case of crash recovery). It occurs when streaming replication is used and the replica cannot keep up with receiving WAL records while they are still cached in the primary server’s memory. In this case, the walsender process has to read the required data from disk. Interaction with replica is covered in detail in the Replication module of DBA3 course. For monitoring and evaluating WAL performance, PostgreSQL version 14 introduced the pg\_stat\_wal view. ## Page 14: Synchronous Writing {#source-page-014} **Algorithm** - when changes are committed, all accumulated WAL records are flushed, - including the commit record - waiting for commit\_delay if at least commit\_siblings transactions are active - Properties - durability is guaranteed - response time increases - Settings - enable under - heavy load of short - synchronous\_commit = on - commit\_delay - = 0 - commit\_siblings - = 5 - transactions ### Speaker notes WAL records are written in one of two modes: synchronous (where transaction commit cannot proceed until all of the transaction’s WAL records are persisted to disk) and asynchronous (where the WAL is flushed by a background process at regular intervals). Writing mode is set by the synchronous\_commit parameter. Since synchronization is time-consuming, it is advisable to perform it as infrequently as possible. To achieve this, the WAL writer process introduces a delay specified by the commit\_delay parameter, but only when there are at least commit\_siblings active transactions. This delay allows some transactions to complete, so their WAL records can be synchronized in a single operation. The process then flushes WAL to disk up to the required LSN (or slightly higher if new records were added during the delay). Synchronous writing guarantees durability: if a transaction is committed, all its WAL records are already on disk and will not be lost. The disadvantage of synchronous writing is that it increases response time (the COMMIT command does not return control until synchronization is completed) and reduces system performance. ## Page 15: Asynchronous Writing {#source-page-015} **Algorithm** - write cycles every wal\_writer\_delay - only completely filled pages are written; - if no new full pages are available, the current one is written - Properties - consistency is guaranteed, but not durability - committed changes can be lost (3 × wal\_writer\_delay) - Settings - can be - changed at the - transaction level - synchronous\_commit - wal\_writer\_delay = 200ms - wal\_writer\_flush\_after = 1MB ### Speaker notes In asynchronous writing mode, the wal writer process flushes accumulated WAL records either after the wal\_writer\_delay time or when the wal\_writer\_flush\_after size threshold is reached: ● If one or more pages in the buffers have been completely filled since the last flush, only those full pages (or part of them) are written. Note that the WAL cache operates as a ring buffer, only a continuous sequence of pages can be written at once. This approach prevents the same page from being synchronized several times under heavy write load. ● If no new full pages have appeared, the current (partially filled) page is written instead. Asynchronous writing is more efficient than synchronous writing because a transaction commit does not wait for the write operation to complete. However, this comes at the cost of reduced reliability: committed data can be lost if a crash occurs within 3 × wal\_writer\_delay time after the commit. The synchronous\_commit parameter can be set per transaction. It allows to increase performance by trading off reliability of some transactions only. In practice, both modes work together. WAL records of a long-running transaction are written asynchronously to free up WAL buffers. However, if a dirty data buffer needs to be flushed and its corresponding WAL record has not been written to disk yet, it will be immediately flushed in synchronous mode. ## Page 16: Impact of Synchronous Commit on Performance {#source-page-016} The default mode is synchronous commit. ```text => SHOW synchronous_commit; ``` ```text synchronous_commit -------------------- on (1 row) ``` Run a basic performance test using pgbench. First, initialize the required tables... ```sh student$ pgbench -i wal_tuning ``` ```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.05 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.11 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.06 s, vacuum 0.02 s, primary keys 0.03 s). ``` ...and reset the WAL statistics: ```sql => SELECT pg_stat_reset_shared('wal'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` Run the test for 10 seconds. ```sh student$ pgbench -P 1 -T 10 wal_tuning ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. progress: 1.0 s, 2759.9 tps, lat 0.361 ms stddev 0.058, 0 failed progress: 2.0 s, 2852.1 tps, lat 0.350 ms stddev 0.046, 0 failed progress: 3.0 s, 2862.8 tps, lat 0.349 ms stddev 0.049, 0 failed progress: 4.0 s, 2910.2 tps, lat 0.343 ms stddev 0.042, 0 failed progress: 5.0 s, 2896.8 tps, lat 0.345 ms stddev 0.089, 0 failed progress: 6.0 s, 2761.0 tps, lat 0.362 ms stddev 0.113, 0 failed progress: 7.0 s, 2882.9 tps, lat 0.347 ms stddev 0.044, 0 failed progress: 8.0 s, 2826.3 tps, lat 0.354 ms stddev 0.103, 0 failed progress: 9.0 s, 2881.8 tps, lat 0.347 ms stddev 0.041, 0 failed progress: 10.0 s, 2855.1 tps, lat 0.350 ms stddev 0.047, 0 failed transaction type: scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 10 s number of transactions actually processed: 28494 number of failed transactions: 0 (0.000%) latency average = 0.351 ms latency stddev = 0.069 ms initial connection time = 1.909 ms tps = 2849.427639 (without initial connection time) ``` In the pgbench results, we focus on transaction count or throughput (tps); in the pg\_stat\_wal view, we analyze the number of write and sync operations. ```sql => SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal; ``` ## Page 17: Page 17 {#source-page-017} ```text wal_records | wal_bytes | wal_write | wal_sync -------------+-----------+-----------+---------- 172984 | 11512136 | 28502 | 28498 (1 row) ``` Now let’s set asynchronous mode. ```sql => ALTER SYSTEM SET synchronous_commit = off; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Reset accumulated statistics. ```sql => SELECT pg_stat_reset_shared('wal'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` Run the test again. ```sh student$ pgbench -P 1 -T 10 wal_tuning ``` ```sql pgbench (16.2 (Ubuntu 16.2-2.pgdg24.04+1)) starting vacuum...end. progress: 1.0 s, 4349.8 tps, lat 0.229 ms stddev 0.265, 0 failed progress: 2.0 s, 5400.0 tps, lat 0.185 ms stddev 0.204, 0 failed progress: 3.0 s, 3825.0 tps, lat 0.261 ms stddev 0.485, 0 failed progress: 4.0 s, 5423.1 tps, lat 0.184 ms stddev 0.239, 0 failed progress: 5.0 s, 5433.0 tps, lat 0.184 ms stddev 0.059, 0 failed progress: 6.0 s, 5620.8 tps, lat 0.178 ms stddev 0.038, 0 failed progress: 7.0 s, 5580.0 tps, lat 0.179 ms stddev 0.042, 0 failed progress: 8.0 s, 5599.0 tps, lat 0.178 ms stddev 0.054, 0 failed progress: 9.0 s, 5734.2 tps, lat 0.174 ms stddev 0.038, 0 failed progress: 10.0 s, 5685.8 tps, lat 0.176 ms stddev 0.061, 0 failed transaction type: scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 10 s number of transactions actually processed: 52652 number of failed transactions: 0 (0.000%) latency average = 0.190 ms latency stddev = 0.188 ms initial connection time = 2.380 ms tps = 5266.273793 (without initial connection time) ``` ```sql => SELECT wal_records, wal_bytes, wal_write, wal_sync FROM pg_stat_wal; ``` ```text wal_records | wal_bytes | wal_write | wal_sync -------------+-----------+-----------+---------- 319462 | 21081934 | 2704 | 52 (1 row) ``` In addition to the increased tps, we can see the source of the performance improvement: the number of WAL sync operations has decreased significantly. Obviously, the ratio may differ on a production system, but the performance is significantly higher in asynchronous mode. ## Page 18: Takeaways {#source-page-018} **The WAL allows to restore consistency after a crash, but it can also be used for other purposes** - To ensure reliability, tuning is required not only at the DBMS level, but also at the OS, file system, and hardware levels - Configuration allows a flexible trade-off between durability and performance ## Page 19: Practice {#source-page-019} 1. Examine how the full\_page\_writes parameter affects the size of WAL records. - To do this, repeat a simple pgbench test given in the - demonstration with different WAL settings. Before starting - each test, perform a checkpoint. - Explain the results. - 2. Calculate the compression ratio achieved by using wal\_compression parameter. ### Speaker notes For a detailed analysis, you can use the pg\_get\_wal\_stats function in the pg\_walinspect extension. ## Page 20: 1a. Full page writes = on {#source-page-020} ```sql => CREATE DATABASE wal_tuning; ``` ```sql CREATE DATABASE ``` ```sql => \c wal_tuning ``` ```text You are now connected to database "wal_tuning" as user "student". ``` ```sql => CREATE EXTENSION pg_walinspect; ``` ```sql CREATE EXTENSION ``` ```sh student$ pgbench -i wal_tuning ``` ```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.07 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.15 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.08 s, vacuum 0.03 s, primary keys 0.03 s). ``` ```text => SHOW full_page_writes; ``` ```text full_page_writes ------------------ on (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/2C5F6B0 (1 row) ``` Let’s run the test for 10 seconds. ```sh student$ pgbench -T 10 wal_tuning ``` ```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: 10 s number of transactions actually processed: 25820 number of failed transactions: 0 (0.000%) latency average = 0.387 ms initial connection time = 1.297 ms tps = 2582.223104 (without initial connection time) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/4569550 (1 row) ``` Examine WAL statistics: ## Page 21: Page 21 {#source-page-021} ```sql => SELECT sum(count) count, pg_size_pretty(sum(record_size)) record_size, ``` ```sql pg_size_pretty(sum(fpi_size)) fpi_size, pg_size_pretty(sum(combined_size)) combined_size FROM pg_get_wal_stats('0/2C5F6B0', '0/4569550'); ``` ```text count | record_size | fpi_size | combined_size --------+-------------+----------+--------------- 156905 | 10191 kB | 15 MB | 25 MB (1 row) ``` Full page images (FPIs) make up a significant part of the overall WAL size. ### 1b. Full page writes = off ```sql => ALTER SYSTEM SET full_page_writes = off; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/45753D0 (1 row) ``` Let’s run the test for 10 seconds. ```sh student$ pgbench -T 10 wal_tuning ``` ```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: 10 s number of transactions actually processed: 25889 number of failed transactions: 0 (0.000%) latency average = 0.386 ms initial connection time = 2.338 ms tps = 2589.464244 (without initial connection time) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/4FAF938 (1 row) ``` Examine WAL statistics: ```sql => SELECT sum(count) count, pg_size_pretty(sum(record_size)) record_size, pg_size_pretty(sum(fpi_size)) fpi_size, pg_size_pretty(sum(combined_size)) combined_size FROM pg_get_wal_stats('0/45753D0', '0/4FAF938'); ``` ```text count | record_size | fpi_size | combined_size --------+-------------+----------+--------------- 157777 | 10079 kB | 0 bytes | 10079 kB (1 row) ``` WAL size decreased significantly since FPIs are no longer written to WAL. ## Page 22: 2. Compression {#source-page-022} ```sql => ALTER SYSTEM SET full_page_writes = on; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET wal_compression = on; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/4FAFA08 (1 row) ``` Let’s run the test for 10 seconds. ```sh student$ pgbench -T 10 wal_tuning ``` ```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: 10 s number of transactions actually processed: 25316 number of failed transactions: 0 (0.000%) latency average = 0.395 ms initial connection time = 2.721 ms tps = 2532.072991 (without initial connection time) ``` ```sql => SELECT pg_current_wal_insert_lsn(); ``` ```text pg_current_wal_insert_lsn --------------------------- 0/5CAAA90 (1 row) ``` Examine WAL statistics: ```sql => SELECT sum(count) count, pg_size_pretty(sum(record_size)) record_size, pg_size_pretty(sum(fpi_size)) fpi_size, pg_size_pretty(sum(combined_size)) combined_size FROM pg_get_wal_stats('0/4FAFA08', '0/5CAAA90'); ``` ```text count | record_size | fpi_size | combined_size --------+-------------+----------+--------------- 156288 | 10085 kB | 2820 kB | 13 MB (1 row) ``` In this scenario, when the number of FPIs is still high, the WAL size decreases severalfold. Despite the additional CPU overhead, the benefits of compression are worthwhile when FPIs are enabled.