Skip to main content
Version: Next

pg_buffercache

The pg_buffercache module provides a means for examining what is happening in the shared buffer cache in real time.

The Apache Cloudberry pg_buffercache module is based on the PostgreSQL pg_buffercache module. In addition to the PostgreSQL views and functions, which report on the instance that you are connected to, it provides gp_-prefixed views that collect the same information from the coordinator and from all primary segments.

Install and register the module​

pg_buffercache is built and installed by a default Apache Cloudberry build. Register the extension in each database in which you want to use it:

CREATE EXTENSION pg_buffercache;

By default, access to the views and functions of this module is restricted to superusers and to members of the pg_monitor role.

Views and functions for a single instance​

These objects report on the shared buffers of the instance that runs the query. When you query them in a normal session, that instance is the coordinator.

ObjectDescription
pg_buffercache view, pg_buffercache_pages() functionOne row for each shared buffer.
pg_buffercache_summary view, pg_buffercache_summary() functionOne row that summarizes the state of the shared buffer cache.
pg_buffercache_usage_counts view, pg_buffercache_usage_counts() functionOne row for each usage count value.

The pg_buffercache view has the following columns:

ColumnTypeDescription
bufferidintegerID of the buffer, in the range from 1 to shared_buffers.
relfilenodeoidFilenode number of the relation.
reltablespaceoidTablespace OID of the relation.
reldatabaseoidDatabase OID of the relation.
relforknumbersmallintFork number within the relation.
relblocknumberbigintPage number within the relation.
isdirtybooleanWhether the page is dirty.
usagecountsmallintClock-sweep access count.
pinning_backendsintegerNumber of backends that pin this buffer.

The pg_buffercache_summary view has the following columns:

ColumnTypeDescription
buffers_usedintegerNumber of used shared buffers.
buffers_unusedintegerNumber of unused shared buffers.
buffers_dirtyintegerNumber of dirty shared buffers.
buffers_pinnedintegerNumber of pinned shared buffers.
usagecount_avgdouble precisionAverage usage count of the used shared buffers.

The pg_buffercache_usage_counts view has the following columns:

ColumnTypeDescription
usage_countintegerA possible buffer usage count.
buffersintegerNumber of buffers with this usage count.
dirtyintegerNumber of dirty buffers with this usage count.
pinnedintegerNumber of pinned buffers with this usage count.

The summary and usage count views are much cheaper to query than pg_buffercache.

Cluster-wide views​

ViewDescription
gp_buffercacheThe pg_buffercache rows of the coordinator and of every primary segment.
gp_buffercache_summaryThe pg_buffercache_summary row of the coordinator and of every primary segment: one row per instance.
gp_buffercache_usage_countsThe pg_buffercache_usage_counts rows of the coordinator and of every primary segment.
gp_buffercache_summary_aggregatedA single row that aggregates gp_buffercache_summary over all instances: the sum of each buffer counter and the average of usagecount_avg.
gp_buffercache_usage_counts_aggregatedA single row that contains the sums of the columns of gp_buffercache_usage_counts, including the usage_count column itself. It is not a breakdown per usage count.

The first three views have the columns of the corresponding pg_ view, preceded by a gp_segment_id column. The value of gp_segment_id is -1 for the coordinator.

Examples​

Show the buffer cache summary of each instance:

SELECT * FROM gp_buffercache_summary;

Show the totals for the whole cluster:

SELECT * FROM gp_buffercache_summary_aggregated;

Count the dirty buffers on each instance:

SELECT gp_segment_id, count(*) AS dirty_buffers
FROM gp_buffercache
WHERE isdirty
GROUP BY gp_segment_id
ORDER BY gp_segment_id;

Module documentation​

See pg_buffercache in the PostgreSQL documentation for more information about the pg_buffercache view. The summary and usage count functions were added to PostgreSQL in version 16 and are described in the PostgreSQL 16 documentation.