Introduction and applicable scenarios

pg_buffercache is an official standard extension (contrib module) that provides developers and DBAs with a real-time window into the internal state of the PostgreSQL shared buffer. Through it, we can precisely understand the distribution, usage frequency, and state of data blocks in memory, making it a key tool for performance analysis and optimization.

Typical applicable scenarios include:

Identifying hot data: quickly locate which tables and indexes in the current database are frequently accessed and whose data pages occupy the highest proportion in memory.

Assessing cache utilization: analyze the usage of shared_buffers to determine whether its size is appropriate and whether there is a large amount of free or inefficiently used cache space.

Monitoring dirty and pinned pages: monitor the proportion of dirty pages to understand checkpoint pressure. Simultaneously, observe pinned buffers to detect potential long-running queries or connection issues.

Assisting SQL and index tuning: combined with tools like pg_stat_statements, analyze whether the data accessed by high-cost SQL statements efficiently utilizes the cache.

Precautions

Queries on pg_buffercache require reading the shared buffer. Frequent execution may cause a slight performance impact on the system. It is recommended to analyze in a test environment or a maintenance window.

Create and delete extension

Create extension

Execute the following command in the database that requires analysis to enable pg_buffercache.

Delete extension

If this feature is no longer needed, execute the following command to uninstall it. After uninstallation, related views and functions will be removed.

Extension usage

View the quantity of buffers occupied by each object

The following query counts the tables and indexes that occupy the most memory buffers in the current database, which is the most direct method to identify "hotspot objects".

Application scenario: identify objects with excessively high memory usage, determine whether indexes are fully cached, and verify if hot data effectively resides in memory.

View overall buffer status

This function provides a quick, low-cost snapshot of the global state of the shared buffer

Application scenario: quickly assess the overall pressure of the buffer to determine whether there is a buffer shortage or a dirty page backlog issue.

View the distribution of buffer usage counts

This function counts the buffer distribution by usage_count. PostgreSQL manages buffers using a clock scanning algorithm with a count range of 0 to 5, where higher values indicate more frequent page access.

Application scenarios:

There are many buffers with a usage count of 0 to 1, indicating frequent buffer eviction and possible memory pressure.

Buffers with a usage count of 4 to 5 represent the real hot data and should have priority to remain resident in memory.

Dirty pages concentrate in buffers with high usage counts, indicating frequent updates to hot data.

RDS for PostgreSQL