Essential PostgreSQL Monitoring Extensions: pg_buffercache, pg_stat_statements, pgstattuple, pg_prewarm

PostgreSQL ships with powerful extensions for monitoring and optimizing database performance. This guide covers four essential extensions: pg_buffercache for buffer pool analysis, pg_stat_statements for query tracking, pgstattuple for table bloat detection, and pg_prewarm for cache warming.

1. pg_buffercache: Analyze Buffer Pool Usage

The pg_buffercache extension provides real-time visibility into PostgreSQL's shared buffer cache. It shows which tables and indexes are consuming buffer memory and helps identify caching inefficiencies.

Installation:

CREATE EXTENSION pg_buffercache;

Check buffer usage by table:

SELECT
  c.relname AS table_name,
  pg_size_pretty(count(*) * 8192) AS buffered_size,
  round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers')::integer, 2) AS buffer_percent
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relkind IN ('r', 'i')
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 10;

This query reveals which objects consume the most buffer space, helping you understand memory utilization patterns.

Check buffer hit ratio:

SELECT
  count(*) FILTER (WHERE usagecount > 0) AS used_buffers,
  count(*) FILTER (WHERE usagecount = 0) AS unused_buffers,
  round(100.0 * count(*) FILTER (WHERE usagecount > 0) / count(*), 2) AS usage_percent
FROM pg_buffercache;

2. pg_stat_statements: Track Query Performance

pg_stat_statements tracks execution statistics for all SQL statements. It's essential for identifying slow queries, high-frequency queries, and optimization opportunities.

Installation:

-- Add to postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

Find slowest queries by total time:

SELECT
  substring(query, 1, 80) AS query_preview,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Find most frequently executed queries:

SELECT
  substring(query, 1, 80) AS query_preview,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Reset statistics:

SELECT pg_stat_statements_reset();

3. pgstattuple: Detect Table and Index Bloat

pgstattuple provides detailed statistics about table and index physical storage, including dead tuple counts and free space. Use it to identify bloated tables needing maintenance.

Installation:

CREATE EXTENSION pgstattuple;

Check table bloat:

SELECT
  table_len AS total_bytes,
  tuple_count AS live_tuples,
  dead_tuple_count AS dead_tuples,
  round(100.0 * dead_tuple_len / nullif(table_len, 0), 2) AS dead_tuple_pct,
  round(100.0 * free_space / nullif(table_len, 0), 2) AS free_space_pct
FROM pgstattuple('your_table_name');

Check index bloat:

SELECT
  avg_leaf_density,
  leaf_fragmentation
FROM pgstatindex('your_index_name');

A healthy index has leaf density above 90% and low fragmentation. Values below this threshold suggest the index needs rebuilding.

Quick bloat estimate (faster for large tables):

SELECT
  approx_tuple_count AS estimated_rows,
  dead_tuple_estimate AS estimated_dead_tuples,
  round(100.0 * dead_tuple_estimate / nullif(approx_tuple_count, 0), 2) AS dead_pct
FROM pgstattuple_approx('your_table_name');

4. pg_prewarm: Warm Up the Buffer Cache

pg_prewarm loads table and index data into the buffer cache or OS cache. Use it after server restarts to restore cache state and avoid cold-start performance degradation.

Installation:

CREATE EXTENSION pg_prewarm;

Prewarm a table into buffer cache:

SELECT pg_prewarm('your_table_name', 'buffer');

Prewarm an index:

SELECT pg_prewarm('your_index_name', 'buffer');

Prewarm with different modes:

-- Load into PostgreSQL buffer cache
SELECT pg_prewarm('orders', 'buffer');

-- Load into OS page cache only (faster, less memory pressure)
SELECT pg_prewarm('orders', 'prefetch');

-- Read data but don't advise OS (useful for testing)
SELECT pg_prewarm('orders', 'read');

Automate cache restoration on startup:

-- Add to postgresql.conf:
-- shared_preload_libraries = 'pg_prewarm'
-- pg_prewarm.autoprewarm = true

-- This saves buffer contents on shutdown and restores them on startup

Combining Extensions for Effective Monitoring

These extensions work best together. Use pg_stat_statements to identify problem queries, pg_buffercache to verify caching behavior, pgstattuple to detect bloat, and pg_prewarm to optimize cold starts.

Example monitoring workflow:

-- 1. Find slow queries
SELECT query, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 5;

-- 2. Check if related tables are cached
SELECT relname, count(*) * 8192 AS cached_bytes
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relname = 'orders'
GROUP BY relname;

-- 3. Check for bloat
SELECT dead_tuple_count, free_space
FROM pgstattuple('orders');

-- 4. Prewarm critical tables after maintenance
SELECT pg_prewarm('orders');
SELECT pg_prewarm('orders_pkey');

Conclusion

These four PostgreSQL extensions provide essential visibility into database performance. pg_buffercache reveals memory usage, pg_stat_statements tracks query performance, pgstattuple detects bloat, and pg_prewarm ensures fast restarts. Together, they form a solid foundation for PostgreSQL monitoring and optimization.