When managing large PostgreSQL databases, understanding why queries are slow or why some operations consume more resources than others can be challenging. Standard monitoring tools like pg_stat_statements provide valuable insights about execution counts and total time, but they don’t tell you how much CPU or I/O resources each query consumes.
This is where the PostgreSQL extension pg_stat_kcache comes into play. It bridges the gap between PostgreSQL query statistics and operating system-level resource usage, giving you a granular view of how queries impact your system.
What is pg_stat_kcache?
pg_stat_kcache is a PostgreSQL extension that tracks kernel-level statistics, including CPU usage, disk I/O, and block-level access. By monitoring these metrics, database administrators and developers can identify resource-heavy queries and optimize them effectively.
It answers questions such as:
- Which databases or queries are consuming the most CPU?
- Which queries cause heavy disk reads or writes?
- Are queries I/O-bound or CPU-bound?
This information is essential for performance tuning, especially in systems with large datasets or high workloads.
Why Use pg_stat_kcache?
Standard PostgreSQL statistics are powerful but limited to logical database metrics, such as execution time and row counts. pg_stat_kcache complements these metrics by providing physical system-level metrics, enabling you to:
- Identify CPU-bound Queries
Queries consuming excessive CPU can be optimized with indexing, query rewriting, or materialized views. - Identify I/O-bound Queries
Heavy disk reads or writes may indicate the need for caching, better indexing, or table partitioning. - Make Informed Resource Management Decisions
Understanding which queries stress system resources helps in planning hardware upgrades or optimizing database configuration.
How pg_stat_kcache Works
pg_stat_kcache hooks into PostgreSQL’s statistics collector and uses Linux kernel counters to measure query-level resource usage. It tracks:
- CPU Time
- exec_user_time – Time spent in user mode
- exec_system_time – Time spent in kernel mode
- Disk I/O
- exec_reads / exec_reads_blks – Reads in rows/blocks
- exec_writes / exec_writes_blks – Writes in rows/blocks
- Other Metrics
- Swaps, signals, messages, and page faults
These metrics are stored in views: pg_stat_kcache (database-level aggregation) and pg_stat_kcache_detail (query-level details).
Installing pg_stat_kcache
Step 1: Install Required Packages
On Ubuntu/Debian:
sudo apt-get update
sudo apt-get install postgresql-17 postgresql-client-17 postgresql-contrib-17
(Replace 17 with your PostgreSQL version.)
Step 2: Create the Extension
CREATE EXTENSION pg_stat_kcache;
If you see this error:
ERROR: required extension "pg_stat_statements" is not installed
HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.
This happens because pg_stat_kcache depends on pg_stat_statements. The reason is simple:
pg_stat_kcache collects kernel-level statistics for each query,
But to link those statistics to specific queries, it needs the query identifiers and execution tracking that pg_stat_statements provides.
So, PostgreSQL suggests using the CASCADE option to automatically install the required extension
CREATE EXTENSION pg_stat_kcache CASCADE;
Step 3: Verify Installation
\dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_kcache | 2.3.0 | public | Kernel statistics gathering
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
Using pg_stat_kcache
Database-Level Metrics
To see the top databases consuming CPU and I/O:
SELECT datname,
exec_user_time,
exec_system_time,
exec_reads,
exec_writes,
exec_reads_blks,
exec_writes_blks,
stats_since
FROM pg_stat_kcache
O
Sample output:
datname | exec_user_time | exec_system_time | exec_reads | exec_writes | exec_reads_blks | exec_writes_blks | stats_since
------------+---------------------+-----------------------+------------+-------------+-----------------+------------------+----------------------------------
averigo_18 | 0.46842872700000016 | 0.36874199999999985 | 6320128 | 0 | 770 | 0 | 2025-09-20 09:15:23.988591+05:30
powa | 0.02456399999999999 | 0.05191700000000002 | 0 | 0 | 0 | 0 | 2025-09-20 09:59:39.599399+05:30
demo | 0.04134399999999999 | 0.009487000000000002 | 0 | 0 | 0 | 0 | 2025-09-20 11:26:33.206214+05:30
postgres | 0.001728169 | 0.0016090000000000037 | 57344 | 0 | 7 | 0 | 2025-09-20 09:14:57.938665+05:30
The output shows CPU and I/O usage per database since the last statistics reset.
exec_user_time and exec_system_time indicate the total CPU time spent in user and kernel mode, respectively.
exec_reads / exec_writes represent the number of rows read or written by the database.
exec_reads_blks / exec_writes_blks show the number of disk blocks read or written.
stats_since marks when these statistics began accumulating.
Practical Use Cases with PostgreSQL 17
1. Identify CPU-Heavy Databases
SELECT datname,
exec_user_time + exec_system_time AS total_cpu_time
FROM pg_stat_kcache
ORDER BY total_cpu_time DESC
LIMIT 5;
- Purpose: Find CPU-intensive databases.
- Optimization Tips: Indexing, query rewriting, caching, or query partitioning.
2. Identify I/O-Heavy Databases
SELECT datname,
exec_reads + exec_writes AS total_io_ops,
exec_reads_blks + exec_writes_blks AS total_io_blocks
FROM pg_stat_kcache
ORDER BY total_io_blocks DESC
LIMIT 5;
- Purpose: Detect I/O-bound workloads.
- Optimization Tips: Use caching, indexing, table partitioning, or adjust autovacuum.
3. Per-Query CPU and I/O Analysis
SELECT s.query,
k.rolname,
k.exec_user_time,
k.exec_system_time,
k.exec_reads,
k.exec_writes
FROM pg_stat_statements s
JOIN pg_database d ON s.dbid = d.oid
JOIN pg_stat_kcache_detail k
ON k.datname = d.datname
AND k.rolname = (SELECT rolname FROM pg_roles WHERE oid = s.userid)
ORDER BY k.exec_user_time + k.exec_system_time DESC
LIMIT 10;
- Purpose: Find queries that consume the most CPU or I/O per database and per role.
- Optimization Tips: Rewrite expensive queries, add indexes, or partition large tables.
Best Practices
- Monitor Large Queries: Focus on queries with high resource usage.
- Combine with Other Tools: Use alongside pg_stat_statements, PoWA, or pgBadger for comprehensive monitoring.
- Understand OS-Level Impact: High CPU or I/O may require hardware or configuration tuning beyond query optimization.
Conclusion
pg_stat_kcache is a powerful PostgreSQL extension that gives administrators and developers a clear view of how queries consume system resources. By connecting database-level statistics with operating system metrics, it allows you to identify resource-intensive queries, optimize performance, and make smarter decisions about hardware and query design.
For large-scale databases or high-performance analytical systems, pg_stat_kcache turns performance monitoring from guesswork into actionable, data-driven insights, enabling precise and effective tuning.