WAL
Buffer Cache
16
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.
2
Topics
Buffer Cache Design and Usage
Eviction Mechanism
Bulk Eviction and Buffer Rings
Tuning
Local Cache for Temporary Tables
Cache Prewarming
3
Buffer Cache
2 1 0
free buffers
shared_buffers
dirty
buffer
hash table
1
usage
count
next victim
pinned
buffer
The function of the buffer cache is to smooth out the difference in
performance of two types of memory: RAM (fast, but limited) and disk
storage (slow, but large). To work with data, whether reading or modifying it,
processes read pages into the buffer cache. While the page is in the cache,
we save on I/O operations.
The buffer cache is located in the server’s shared memory and is structured
as an array of buffers. Each buffer consists of space for one data page and
a header. The header contains, among other things:
●
Location of the page on the disk (file and page number)
●
Buffer usage count (incremented each time the process reads or changes
the buffer)
●
A flag indicating that the data on the page has been modified and must
eventually be written to disk (such a buffer is called dirty)
●
A pin count tracking the number of processes currently working with the
buffer content
The size of the buffer cache is set by the shared_buffers parameter.
Changing this parameter requires a server restart.
The cache initially contains empty buffers, and all of them are chained into
the list of free buffers. The purpose of the next victim pointer will become
clear a bit later.
A hash table is used to quickly find the required page in the cache.
4
Page in the Cache
1
3
+1
1 0
free buffers
next victim
hash table
pinned
buffer
When a process needs to read a page, it first attempts to find it in the buffer
cache using the hash table.
The file and the page number in the file are used as the hash key. The
process finds the buffer number and quickly checks whether it really
contains the page needed. As in any hash table, collisions are possible here;
in this case, the process will have to check several pages.
If the required page is found in the cache, the process must pin the buffer
(incrementing the pin count), and increment the buffer usage count. Since
multiple processes can pin the same buffer simultaneously, a counter is
used for this purpose, not a boolean flag.
While a buffer is pinned (pin count is greater than zero), the buffer is
considered to be used and its contents should not change drastically. For
example, a new tuple may be added into the page. This does not interfere
with any operations due to multiversion concurrency control and visibility
rules. However, a different page cannot be read into a pinned buffer.
6
Reading into a Free Buffer
1
3 1
1 0
free buffers
next victim
hash table
It may happen that the required page is not found in the cache. In this case,
the page should be read from disk to any buffer.
If the free list contains buffers, one is selected from them first.
The buffer the pointer referred to is pinned, the required page is read into it,
and the usage count is set to one.
The free buffer pointer moves to the next available buffer in the list, if any.
Besides, a reference to the loaded page must be written to the hash table so
that it can be found in the future.
7
Reading into a Free Buffer
1
3 1 1
1 0
free buffers
next victim
hash table
In the end, the last free buffer will be occupied.
Now the reference to a free buffer is empty. Consequently, in order to read a
new page into any occupied buffer, we have to evict the page located there.
Buffers are returned to the free buffer list when a table is dropped or
truncated, or when part of the tail pages are cut off during vacuuming, that
is, in cases where the page in the buffer is not replaced by another one, but
simply disappears.
9
Reading with Eviction
1
1 12
–1
pinned,
cannot be
evicted
1 0
free buffers
next victim
hash table
The eviction mechanism uses a next victim pointer.
The clock-sweep algorithm circularly goes through all buffers, decrementing
their usage counts. The first buffer with a usage count of zero that can be
occupied by a new page will be selected.
The higher the buffer’s usage count (the more often it is used), the more
likely it is to remain in the cache. To prevent excessive looping when counts
are large, the maximum value of the usage count is limited to 5.
In our example, the process first accesses the buffer by pointer. The buffer is
pinned (i.e., used by some process), so its page cannot be evicted.
Nevertheless, we reduce the usage count by one and proceed to the next
buffer.
10
Reading with Eviction
1
1 12
being
used, too early
to evict
0 0
–1
free buffers
next victim
hash table
The next buffer is not pinned, but its usage count is greater than zero.
We reduce the counter by one, giving the page a chance to remain in the
cache.
11
Reading with Eviction
1
1 12
can be
evicted
0 0
free buffers
next victim
hash table
Finally, we reach an unpinned buffer with a zero usage count. The page in
this buffer will be evicted.
However, in our example, the buffer appears to be dirty, i.e., it contains
modified data. Therefore, its page needs to be saved to disk first. To do this,
the buffer is pinned (to show other processes that it is in use), after which
the page is written to disk.
This is hardly a good situation since the process that is going to read the
page has to wait until another page’s data is written. This effect is alleviated
by checkpoint and background writer processes, which are covered in the
Checkpoint lesson.
12
Reading with Eviction
1
1 12
0 1
+1
free buffers
next victim
hash table
Once the page from a dirty buffer is written to disk, a new page is read into
the free buffer as described earlier.
The reference to the next victim now points to the next buffer, and the newly
loaded buffer has time to increase the usage count while the pointer makes
a full circle through the buffer cache and comes back around.
13
Tuning
Settings
shared_buffers = 128MB
Buffer cache should contain active data
if the size is too small, useful pages are constantly evicted
if the size is too large, overhead grows pointlessly
first approximation is 1/4 of RAM
Double caching should be considered
if a page is not in the DBMS cache, it may be found in the OS cache
OS eviction algorithm is not aware of the database-specific features
↑
The cache size is set by the shared_buffers parameter. The default value of
128 MB is too low.
What do we need to consider to choose the appropriate value? Even the
largest database has a limited data set that is actively processed.
Ideally, this set (plus some space for one-time data) must fit into the buffer
cache.
If the cache size is smaller, actively used pages will constantly evict each
other, thus leading to excessive I/O operations. A key indicator of this is
when all pages in the cache have a high usage count.
However, with a large cache size, overhead for its maintenance will
increase.
It is usually recommended to take 1/4 of RAM for the first approximation.
However, it is important to understand that the optimal value depends not on
the size of the RAM, but on the specific data and workload.
Note that PostgreSQL works with the disk via the operating system, so
double caching occurs: pages are stored both in the buffer cache and in the
OS cache. Therefore, a cache miss does not necessarily lead to actual
physical I/O. However, the OS eviction algorithm is not aware of database-
specific requirements.
15
Bulk Eviction
buffer ring
shared_buffers
process
works
with data
process
joined
the ring
excluded from the ring
or
flushed to disk
dirty
buffer
During operations that perform bulk data reading or writing, there is a risk of
evicting useful pages from the buffer cache with one-time data. To avoid this,
so called buffer rings are used: only a small part of the buffer cache is
dedicated for each operation. The eviction is carried out only within the ring,
so the rest of the data in the buffer cache is not affected.
If dirty buffers appear in the ring during operation, they will be either removed
from the ring or flushed to disk.
16
Buffer Ring
Sequential reading
32 pages
dirty buffer is excluded from the ring
Vacuuming
vacuum_buffer_usage_limit = 32
dirty buffer is flushed to disk and page is evicted
Bulk Writing
≤2048 pages
dirty buffer is flushed to disk and page is evicted
During a sequential scan of a table, a buffer ring of 32 pages is used. If dirty
buffers appear while processing this data (as a result of setting hint bits or
performing an UPDATE), they are removed from the ring, returned to the
main cache, and will be subsequently evicted according to the general rules.
The buffer removed from the ring is replaced by another buffer from the
main cache. Such a strategy is designed for scenarios where the data is
mainly read, not modified.
If another process scans the same table, it attaches to the existing buffer
ring and later reads the pages that have been evicted before.
Vacuum also uses a ring, its size is set by the vacuum_buffer_usage_limit
parameter. The default value of the parameter is quite small (32 pages),
since slowing down vacuuming is usually less critical than slowing down
user processes (an exception is aggressive vacuuming, where it can use the
entire cache).
For bulk write operations such as COPY or CREATE TABLE AS SELECT,
the ring is quite large (usually 2048 pages, but not more than 1/8 of the
buffer cache).
18
Local Cache
Temporary Tables
visible to only one session, so there is no point in using shared cache
exist within one session, so their loss in a crash is acceptable
Features
no locks required
memory is allocated on demand within temp_buffers
standard eviction algorithm
buffer ring mechanism is not used
Temporary tables are an exception to the general rule. Since temporary data
is visible to only one process, there is no need for them in the shared buffer
cache. Moreover, temporary data exist only within one session, so it does
not need to be protected against failures.
A lightweight local cache is used for temporary data.
Since the local cache is accessible to only one process, it does not require
locks. Memory is allocated on demand (within the limit set by the
temp_buffers parameter), since temporary tables are not used in all
sessions. The local cache uses a standard eviction algorithm.
At the physical level, temporary tables are stored similarly to regular tables.
The temp_tablespaces parameter specifies the tablespaces where
temporary objects (temporary tables and their indexes) will be created when
CREATE command does not explicitly specify a tablespace.
The temp_tablespaces parameter value can contain a list of tablespace
names. When this list contains more than one name, PostgreSQL randomly
selects one from the list when creating each temporary object. However, for
subsequent objects created within the same transaction, the tablespaces are
sorted sequentially. If the list contains an empty string (default value),
PostgreSQL will use the default tablespace for the current database.
20
Cache Prewarming
pg_prewarm extension
manual prewarming of the OS cache
manual prewarming of the buffer cache
automatic prewarming of the buffer cache upon server restart
After a server restart, the buffer cache is empty, and it takes some time for it
to prewarm, i.e., to get filled with the current, actively used data. Sometimes
it can be useful to immediately read the contents of certain tables into the
cache.
The pg_prewarm extension allows us to read manually some tables either
into the OS cache, or the DBMS buffer cache.
The extension also allows us to restore automatically the contents of the
buffer cache after a server restart. To do this, we need to add the extension
library to shared_preload_libraries parameter.
22
Takeaways
All data access runs through the buffer cache
Rarely used pages are evicted, frequently used pages remain
Buffer cache reduces I/O, but requires logging
23
Practice
1. Create a table and insert a number of rows into it. How many
a) pages on the disk, b) buffers in the cache does the table
occupy?
2. Find out the number of dirty buffers in the cache at the moment.
Use CHECKPOINT command to perform a checkpoint. How
many dirty buffers are left now?
3. Load the pg_prewarm extension library, restart the server and
check that the contents of the buffer cache are restored.
To check the contents of the buffer cache, use the pg_buffercache
extension:
