Pg_buffercache is a PostgreSQL contrib extension we can use to inspect the contents of shared buffers, such as how many pages are in cache and which tables are in cache.
In PostgreSQL 19 beta version 1, we can see that new functionalities are implemented in the pg_buffercache extension.
Before creating this extension, check that the extension is available in your postgres.
select * from pg_available_extensions where name = 'pg_buffercache';
Result :
name | default_version | installed_version | comment
----------------+-----------------+-------------------+---------------------------------
pg_buffercache | 1.6 | | examine the shared buffer cache
(1 row)
We can create the extension by using this command.
create extension pg_buffercache ;
Now, let’s check what the functionalities of this extension in postgres version 18 are.
We can inspect the functionality of this extension by using this command
cybrosys@postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
cybrosys@postgres=# \dx+ pg_buffercache
Objects in extension "pg_buffercache"
Object description
--------------------------------------------------
function pg_buffercache_evict_all()
function pg_buffercache_evict(integer)
function pg_buffercache_evict_relation(regclass)
function pg_buffercache_numa_pages()
function pg_buffercache_pages()
function pg_buffercache_summary()
function pg_buffercache_usage_counts()
type pg_buffercache
type pg_buffercache[]
type pg_buffercache_numa
type pg_buffercache_numa[]
view pg_buffercache
view pg_buffercache_numa
(13 rows)
Now check this in postgres version 19
cybrosys@postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 19beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
cybrosys@postgres=# \dx+ pg_buffercache
Objects in extension "pg_buffercache"
Object description
-------------------------------------------------------
function pg_buffercache_evict_all()
function pg_buffercache_evict(integer)
function pg_buffercache_evict_relation(regclass)
function pg_buffercache_mark_dirty_all()
function pg_buffercache_mark_dirty(integer)
function pg_buffercache_mark_dirty_relation(regclass)
function pg_buffercache_numa_pages()
function pg_buffercache_os_pages(boolean)
function pg_buffercache_pages()
function pg_buffercache_summary()
function pg_buffercache_usage_counts()
type pg_buffercache
type pg_buffercache[]
type pg_buffercache_numa
type pg_buffercache_numa[]
type pg_buffercache_os_pages
type pg_buffercache_os_pages[]
view pg_buffercache
view pg_buffercache_numa
view pg_buffercache_os_pages
(20 rows)
Now let’s start with the newly added view named pg_buffercache_os_pages.
select * from pg_buffercache_os_pages limit 10;
Result :
bufferid | os_page_num
----------+-------------
1 | 0
1 | 1
2 | 2
2 | 3
3 | 4
3 | 5
4 | 6
4 | 7
5 | 8
5 | 9
(10 rows)
Here, the buffer ID is the identifier of the buffer pages. We know that each page size is nearly 8 KB, and the OS page size in almost all Linux distributions is nearly 4 KB. So that’s why here we can see that each buffer page is mapped to two OS pages.
Now let’s get more information about the new functionalities provided by the new 4 functions in the pg_buffercache extension.
function pg_buffercache_mark_dirty_all()
function pg_buffercache_mark_dirty(integer)
function pg_buffercache_mark_dirty_relation(regclass)
function pg_buffercache_os_pages(boolean)
1. pg_buffercache_mark_dirty_all()
Checking the metadata about this function pg_buffercache_mark_dirty_all().
\df+ pg_buffercache_mark_dirty_all()
Result :
List of functions
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------
Schema | public
Name | pg_buffercache_mark_dirty_all
Result data type | record
Argument data types | OUT buffers_dirtied integer, OUT buffers_already_dirty integer, OUT buffers_skipped integer
Type | func
Volatility | volatile
Parallel | safe
Owner | cybrosys
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | pg_buffercache_mark_dirty_all
Description |
Check the count of current pages in cache that were dirty.
SELECT count(*)
FROM pg_buffercache
WHERE isdirty;
Result :
count
-------
1163
(1 row)
Now use the function to dirty all the pages.
SELECT pg_buffercache_mark_dirty_all();
Result :
pg_buffercache_mark_dirty_all
-------------------------------
(21,1163,0)
(1 row)
The first value indicates that 21 new pages are marked as dirty, and already we have 1163 shared buffer’s pages in dirty state, and 0 indicates there are no errors.
Now, check again the total count of pages that were dirtied
SELECT count(*)
FROM pg_buffercache
WHERE isdirty;
Result :
count
-------
1184
(1 row)
1163 + 21 = 1184.
Now you can see that the count has increased. This function is mainly used to mark all pages dirty in cache.
2. pg_buffercache_mark_dirty(integer)
Checking metadata about this function
\df+ pg_buffercache_mark_dirty
Result :
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------
Schema | public
Name | pg_buffercache_mark_dirty
Result data type | record
Argument data types | integer, OUT buffer_dirtied boolean, OUT buffer_already_dirty boolean
Type | func
Volatility | volatile
Parallel | safe
Owner | cybrosys
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | pg_buffercache_mark_dirty
Description |
Check some buffer IDs and their related buffercache metadata from pg_buffercache
SELECT bufferid,
relfilenode,
relblocknumber,
isdirty
FROM pg_buffercache
LIMIT 10;
Result :
bufferid | relfilenode | relblocknumber | isdirty
----------+-------------+----------------+---------
1 | 49470 | 0 | f
2 | 49285 | 0 | f
3 | 49322 | 0 | f
4 | 49322 | 1 | f
5 | 49322 | 2 | f
6 | 49322 | 3 | f
7 | 49322 | 4 | f
8 | 49322 | 5 | f
9 | 49322 | 6 | f
10 | 49310 | 0 | f
(10 rows)
Now let’s make a specific page mark to dirty by using the function pg_buffercache_mark_dirty.
SELECT pg_buffercache_mark_dirty(1);
Result :
pg_buffercache_mark_dirty
---------------------------
(t,f)
(1 row)
Now check the isdirty state of the buffer id 1.
SELECT bufferid,
relfilenode,
relblocknumber,
isdirty
FROM pg_buffercache
LIMIT 10;
Result :
bufferid | relfilenode | relblocknumber | isdirty
----------+-------------+----------------+---------
1 | 49470 | 0 | t
2 | 49285 | 0 | f
3 | 49322 | 0 | f
4 | 49322 | 1 | f
5 | 49322 | 2 | f
6 | 49322 | 3 | f
7 | 49322 | 4 | f
8 | 49322 | 5 | f
9 | 49322 | 6 | f
10 | 49310 | 0 | f
(10 rows)
Now you can see that the buffer ID is marked as dirty.
3. pg_buffercache_mark_dirty_relation
Checking the metadata
\df+ pg_buffercache_mark_dirty_relation
Result :
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------
Schema | public
Name | pg_buffercache_mark_dirty_relation
Result data type | record
Argument data types | regclass, OUT buffers_dirtied integer, OUT buffers_already_dirty integer, OUT buffers_skipped integer
Type | func
Volatility | volatile
Parallel | safe
Owner | cybrosys
Security | invoker
Leakproof? | no
Access privileges |
Language | c
Internal name | pg_buffercache_mark_dirty_relation
Description |
Create a sample table and insert values
CREATE TABLE av_test (
id int,
name text
);
CREATE TABLE
INSERT INTO av_test
SELECT g, md5(g::text)
FROM generate_series(1,10000) g;
INSERT 0 10000
Check the record counts.
SELECT count(*)
FROM av_test;
Result :
-[ RECORD 1 ]
count | 10000
Check the pages that are currently in cache.
SELECT count(*)
FROM pg_buffercache b
JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relname = 'av_test';
Result :
-[ RECORD 1 ]
count | 85
Use the function to entirely mark the relation pages as dirty.
SELECT pg_buffercache_mark_dirty_relation('av_test'::regclass);Result :
-[ RECORD 1 ]----------------------+---------
pg_buffercache_mark_dirty_relation | (3,85,0)
Here, the first value 3 indicates that 3 new pages are marked as dirty, and already 85 pages are in cache, and there are 0 errors during this process.
4. pg_buffercache_os_pages(boolean)
Checking the metadata.
\df+ pg_buffercache_os_pages
Result :
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------
Schema | public
Name | pg_buffercache_os_pages
Result data type | SETOF record
Argument data types | include_numa boolean, OUT bufferid integer, OUT os_page_num bigint, OUT numa_node integer
Type | func
Volatility | volatile
Parallel | safe
Owner | cybrosys
Security | invoker
Leakproof? | no
Access privileges | cybrosys=X/cybrosys +
| pg_monitor=X/cybrosys
Language | c
Internal name | pg_buffercache_os_pages
Description |
Now use this function like this
SELECT *
FROM pg_buffercache_os_pages(true)
LIMIT 5;
Result :
bufferid | os_page_num | numa_node
----------+-------------+-----------
1 | 0 | 0
1 | 1 | 0
2 | 2 | 0
2 | 3 | 0
3 | 4 | 0
(5 rows)
Here, Postgres is asking the os for numa node information for each os page.
Here bufferid is the PostgreSQL shared buffer ID, and os_page_num is the underlying operating system page number.
numa_node = numa node where that page residesA numa node is a group of cpu’s and memory that are physically closer to each other in a server.
numa stands for non-uniform memory access.
We can also check the shared buffers that are mapped to os pages with out numa node information like this.
SELECT *
FROM pg_buffercache_os_pages(false)
LIMIT 5;
Result :
bufferid | os_page_num | numa_node
----------+-------------+-----------
1 | 0 |
1 | 1 |
2 | 2 |
2 | 3 |
3 | 4 |
(5 rows)
In PostgreSQL 19, the newly added view named pg_buffercache_os_pages gives a view of how each shared buffer page is mapped to the os pages. And also it provides other new four functionalities, and they give good features to mark a specific relation page as dirty and mark an entire relation page as dirty, etc. So understanding postgres 19's new features is good for database administrators and also PostgreSQL enthusiasts.