Understanding PostgreSQL Parameters: Background Writer parameters

Introduction:

The background writer is a crucial component in PostgreSQL that plays an essential role in maintaining database performance and stability. Its main function is to write dirty pages (i.e., modified pages in memory) from the shared buffer cache to the disk in a controlled and efficient manner. This process helps reduce I/O spikes and ensures that the system remains responsive, especially under heavy load.

Although the Write-Ahead Logging (WAL) mechanism is responsible for recording all changes to ensure durability. The bgwriter helps by keeping the shared buffers clean and reducing the need for backend processes to perform writes themselves.

The pg_catalog views that tell a story:

Let’s break down some of the key metrics from pg_stat_bgwriter and pg_stat_checkpointer (from PG17) and what they can tell us about your system’s health and configuration.

1. maxwritten_clean: Is the background writer hitting its limits?
   The maxwritten_clean counter tracks how many times the background writer stopped writing buffers because it hit the bgwriter_lru_maxpages limit.

What to watch for: If this number is consistently high, it means the background writer isn’t allowed to do enough work during their cycle.

What to do: Consider increasing bgwriter_lru_maxpages to allow more buffers to be written per round.

Why it matters: A restricted bgwriter can lead to more dirty buffers being written by backend processes, which increases query latency and I/O spikes.

2. buffers_clean vs buffers_backend: Who’s doing the dirty work?
   buffers_clean: number of buffers written by the background writer,
   buffers_backend: number of buffers written by PostgreSQL backend processes (your queries).

Ideally, buffers_clean should be higher than buffers_backend. The more work the bgwriter does in the background, the less pressure on your query processes.

If buffers_backend > buffers_clean:

Increase bgwriter_lru_multiplier,
Decrease bgwriter_delay.

These settings make the background writer more aggressive and responsive.

Additionally, this could indicate:

Shared Buffers may be too small: If the “hot” part of your data doesn’t fit into shared buffers, you’ll see buffers churn between RAM and disk more frequently, causing backend processes to write their own dirty pages.

TL;DR: The background writer should ideally shoulder the I/O burden, not your user queries.

3. buffers_backend_fsync: Are queries doing their own fsync?
   This metric shows how often PostgreSQL backends (not the background writer) are forced to perform their own fsync() calls to flush buffers to disk. This typically happens when the internal fsync queue is full or stalled.

Rule of thumb:

Any non-zero value here is a red flag.

It suggests:

Disk I/O congestion
Improperly tuned checkpoint settings
Or, in older versions of PostgreSQL, limitations in fsync queue handling

Good news: Newer PostgreSQL versions (13+) have dramatically improved how fsync is handled, and in modern systems, it’s increasingly rare to see non-zero values here.

Several configuration parameters control the behavior of the background writer:

• bgwriter_delay,
• bgwriter_flush_after,
• bgwriter_lru_maxpages,
• bgwriter_lru_multiplier.

Why do we need to tune bgwriter parameters?

The bgwriter Parameters control the background writer process, which is responsible for delaying the flushing of dirty pages (modified data not yet written to disk). This helps reduce the load on backend processes and keeps the database responsive, especially under heavy workloads.

Tuning these parameters is important because they determine how aggressively and how frequently the background writer flushes dirty pages to disk. If not properly configured, it can lead to several issues:

• Performance bottlenecks during peak loads
• Unnecessary disk I/O if the background writer is too aggressive
• Backend processes are being forced to write data themselves, which slows down user queries

1.bgwriter_delay:

bgwriter_delay defines the delay between activity rounds of the background writer process in PostgreSQL. When transactions modify data, these changes need to be flushed to disk eventually. The background writer helps in writing dirty (modified) pages to disk in the background to reduce the load during checkpoints.

To check the current value:

postgres=# show bgwriter_delay ;
 bgwriter_delay 
----------------
 200ms
(1 row)

How to set bgwriter_delay

You can modify the parameter using the ALTER SYSTEM command:

postgres=# ALTER SYSTEM SET bgwriter_delay TO '150ms';
ALTER SYSTEM

After changing the setting, reload the PostgreSQL configuration to apply changes without restarting the server:

postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Verifying the Change

After the reload, re-check the parameter:

postgres=# show bgwriter_delay ;
 bgwriter_delay 
----------------
 150ms
(1 row)

Impact :

If your system experiences frequent high-write transactions, it is recommended to tune this value based on your server’s workload. Lowering the value (e.g., to 150ms) causes the background writer to run more frequently, helping keep modified pages flushed to disk more regularly. This can reduce latency spikes during checkpoints but may increase I/O. On the other hand, increasing the value e.g., to 250ms, means the background writer runs less frequently, which might be acceptable for systems with fewer writes.

2.bgwriter_flush_after:

bgwriter_flush_after controls how much data (in kilobytes) the background writer accumulates before issuing a flush to disk. When the background writer writes dirty (modified) pages to disk, this parameter defines the threshold after which an fsync is triggered to flush those writes to physical disk.

To check the current value:

postgres=# show bgwriter_flush_after;
 bgwriter_flush_after 
----------------------
 512kB
(1 row)

How to set bgwriter_flush_after

You can modify the parameter using the ALTER SYSTEM command:

postgres=# ALTER SYSTEM SET bgwriter_flush_after TO '256kB';
ALTER SYSTEM

To apply the changes to the PostgreSQL server, reload it.

Verifying the Change

After reload, re-check the parameter:

postgres=# show bgwriter_flush_after;
 bgwriter_flush_after 
----------------------
 256kB
(1 row)

Impact:

If you set this value too low, the system may issue flushes too frequently, causing unnecessary disk I/O. If it’s too high, you may experience I/O spikes as more data is flushed all at once. To tune these parameters bgwriter_flush_after Based on your storage performance. For fast disks (e.g., SSDs), a higher value might work well. For slower disks, smaller values can prevent I/O stalls.

3.bgwriter_lru_maxpages:

bgwriter_lru_maxpages controls the maximum number of dirty pages the background writer can write to disk in each round. During each cycle, the background writer scans and writes a limited number of these pages to help reduce I/O spikes during checkpoints.

To check the current value:

postgres=# show bgwriter_lru_maxpages ;
 bgwriter_lru_maxpages 
-----------------------
 100
(1 row)

How to set bgwriter_lru_maxpages

You can modify the parameter using the ALTER SYSTEM command:

postgres=# ALTER SYSTEM SET bgwriter_lru_maxpages TO 200;
ALTER SYSTEM

To apply the changes to the PostgreSQL server, reload it.

Verifying the Change

After reload, re-check the parameter:

postgres=# show bgwriter_lru_maxpages ;
 bgwriter_lru_maxpages 
-----------------------
 200
(1 row)

Impact:

If your system handles high transaction volumes, increasing this value allows more dirty pages to be written per round, which can improve performance by reducing the checkpoint burden. If the value is set too low (e.g., 50), the background writer may not keep up with the write load, potentially leading to disk I/O issues or slower performance during checkpoints. Increase the value if your system has frequent writes and sufficient I/O capacity. Lower it only if you’re seeing unnecessary background writes or want to reduce I/O pressure.

4.bgwriter_lru_multiplier:

The number of dirty buffers written in each round is based on the number of new buffers that have been needed by server processes during recent rounds.

To check the current value:

postgres=# show bgwriter_lru_multiplier;
 bgwriter_lru_multiplier 
-------------------------
 2
(1 row)

How to set bgwriter_lru_multiplier

You can modify the parameter using the ALTER SYSTEM command:

postgres=# ALTER SYSTEM SET bgwriter_lru_multiplier TO 2.5;
ALTER SYSTEM

To apply the changes to the PostgreSQL server, reload it.

Verifying the Change

After reload, re-check the parameter:

postgres=# show bgwriter_lru_multiplier;
 bgwriter_lru_multiplier 
-------------------------
 2.5
(1 row)

Impact:

Increasing the value of the Background Writer will make it more aggressive in its efforts to keep pages clean. This can be useful for systems with heavy write loads, as it reduces the likelihood of backends encountering dirty pages that need to be written to disk during transaction processing.

Conclusion:

The PostgreSQL background writer is a vital component responsible for writing dirty pages from memory to disk smoothly and efficiently. By offloading this task from backend processes, it helps reduce latency spikes, especially during checkpoints, and keeps the database responsive under heavy workloads.

Tuning background writer parameters such as bgwriter_delay, bgwriter_flush_after, bgwriter_lru_maxpages, and bgwriter_lru_multiplier allows administrators to control how aggressively and frequently these writes occur. Proper tuning helps balance write performance and I/O load, which is critical for high-throughput or write-intensive environments.

Understanding and configuring these settings enables PostgreSQL DBAs to prevention of unnecessary disk I/O, avoids performance bottlenecks, and maintains optimal buffer management. Overall, effective use of the background writer settings plays a key role in achieving consistent and stable database performance.