PostgreSQL extensions — pg_buffercache
Some time ago, I’ve decided to prepare series of short notes about interesting and most common used PG extensions. I called it #PostgreSQL Thursday’s .
pg_buffercache can be pretty useful for performance analysis of queries, it allows to have a look at how much a data is cached. In the case of data cached for a given relation, your query do not need to access data directly on disk to retrieve the data and can directly rely on the cache, so the data fetching is simply faster.
postgres=# create extension pg_buffercache ;
CREATE EXTENSIONpostgres=# \d pg_buffercache
View "public.pg_buffercache"
Column | Type | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
bufferid | integer | | |
relfilenode | oid | | |
reltablespace | oid | | |
reldatabase | oid | | |
relforknumber | smallint | | |
relblocknumber | bigint | | |
isdirty | boolean | | |
usagecount | smallint | | |
pinning_backends | integer | | |
The view created after installation of the extension called pg_buffercache has several columns.
- bufferid, the block ID in the server buffer cache
- relfilenode, which is the folder name where data is located for relation
- reltablespace, Oid of the tablespace relation uses
- reldatabase, Oid of database where location is located
- relforknumber, fork number within the relation
- relblocknumber, age number within the relation
- isdirty, true if the page is dirty
- usagecount, page LRU (least-recently used) count
- pinning_backends, information about buffer pins
Few more words explanation, using 128MB of shared_buffers with 8kB of block size, there are 16,384 buffers, so pg_buffercache has the same number of 16,384 rows.
postgres=# SELECT CASE WHEN c.reldatabase IS NULL THEN ''
WHEN c.reldatabase = 0 THEN ''
ELSE d.datname
END AS database,
count(*) AS cached_blocks
FROM pg_buffercache AS c
LEFT JOIN pg_database AS d
ON c.reldatabase = d.oid
GROUP BY d.datname, c.reldatabase
ORDER BY d.datname, c.reldatabase;database | cached_blocks
----------+---------------
postgres | 275
| 17
| 16092
(3 rows)
Below, simple query provides the number of buffers used by each relation of the current database.
SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;relname | buffers
---------------------------------+---------
pg_attribute | 34
pg_proc | 25
pg_class | 17
pg_operator | 14
pg_depend_reference_index | 12
pg_depend | 11
pg_proc_oid_index | 9
pg_proc_proname_args_nsp_index | 9
pg_type | 8
pg_attribute_relid_attnum_index | 8
I’ve been an Architect, a DevOps, a DataOps, and a DBA. I’m focused on databases, real-time data and streaming technologies. I work with Apache Kafka, RabbitMQ, Elastic Stack and PostgreSQL. After hours I love to share my knowledge and experience.
