When a PostgreSQL server process reads data from disk, it first moves the page that contains the data into the shared buffer pool. The shared buffer pool is so named because it's a region of memory that's shared by all server processes that access a given cluster. Another way to look at it is that the shared buffer pool is shared by all processes spawned by a single postmaster. When the shared buffer pool fills, PostgreSQL starts pushing old pages out of the pool to make room for new ones. PostgreSQL uses the ARC algorithm, or, in older versions the LRU (least-recently-used) mechanism to select the page it evicts from the pool (see Chapter 4 for more information). If PostgreSQL chooses a page that has not been modified since it was placed in the pool, that page is simply discarded. On the other hand, if PostgreSQL chooses a page that has been modified, it must write the page to disk.
Prior to release 8.0, a modified page would hang around in the shared buffer pool until it was evicted by a server process or by a CHECKPOINT (a CHECKPOINT flushes all modified pages from the shared buffer pool). That meant that any given server process might need to wait for a disk write to complete before it could pull a page from disk into the shared buffer pool. That also meant that a CHECKPOINT could cause a flurry of disk activity because it would find many modified pages in the shared buffer pool.
PostgreSQL version 8.0 introduces a new process, the BGWRITER (short for background writer) that lurks in the background and occasionally flushes modified pages out of the shared buffer pool. The BGWRITER spends much of its time sleeping, but every time it wakes, it searches through the shared buffer pool looking for modified pages. After each search, the BGWRITER chooses some number of modified pages, writes them to disk, and evicts those pages from the shared buffer pool.
The BGWRITER improves overall performance in two ways. First, it tries to increase the number of free pages (or at least, the number of unmodified pages) in the shared buffer pool so that individual server processes won't have to wait for disk writes. Second, it decreases the number of modified pages found in the shared buffer pool when a CHECKPOINT occursthe BGWRITER smooths out the I/O spikes caused by CHECKPOINT s.
You can tune the BGWRITER with three configuration parameters: BGWRITER_DELAY, BGWRITER_PERCENT, and BGWRITER_MAXPAGES. The BGWRITER_DELAY parameter controls how long the BGWRITER process sleeps between each round. BGWRITER_PERCENT and BGWRITER_MAXPAGES limit the number of pages that the BGWRITER_PROCESS flushes during each round.
Part I: General PostgreSQL Use
Introduction to PostgreSQL and SQL
Working with Data in PostgreSQL
PostgreSQL SQL Syntax and Use
Part II: Programming with PostgreSQL
Introduction to PostgreSQL Programming
The PostgreSQL C APIlibpq
A Simpler C APIlibpgeasy
The New PostgreSQL C++ APIlibpqxx
Embedding SQL Commands in C Programsecpg
Using PostgreSQL from an ODBC Client Application
Using PostgreSQL from a Java Client Application
Using PostgreSQL with Perl
Using PostgreSQL with PHP
Using PostgreSQL with Tcl and Tcl/Tk
Using PostgreSQL with Python
Npgsql: The .NET Data Provider
Other Useful Programming Tools
Part III: PostgreSQL Administration
Introduction to PostgreSQL Administration
Internationalization and Localization
Replicating PostgreSQL Data with Slony