The PostgreSQL BGWRITER process

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

Extending PostgreSQL


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

PostgreSQL Administration

Internationalization and Localization


Replicating PostgreSQL Data with Slony

Contributed Modules


PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: