Senior LevelPostgreSQL
How does pg_stat_kcache measure OS-level CPU user/system time and file system read/write bytes per query?
❓ Interview Prompt: Explain `pg_stat_kcache` extension, hooking into `pg_stat_statements`, gathering kernel `rusage` metrics, and OS disk I/O bytes.
While 'pg_stat_statements' tracks database execution metrics, 'pg_stat_kcache' gathers operating system kernel metrics per query via the 'rusage' system call:
Key OS Metrics:
•
'user_time' / 'system_time': CPU user mode vs kernel system mode execution time.
•
'reads_bytes' / 'writes_bytes': Physical filesystem disk read/write bytes executed by the OS kernel!
Code Implementation Example
sql
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;
-- Query top queries by physical OS filesystem disk read bytes
SELECT query, reads_bytes / 1024 / 1024 AS os_read_mb, user_time, system_time
FROM pg_stat_kcache()
ORDER BY reads_bytes DESC
LIMIT 5;💡Measuring physical OS disk reads per query via pg_stat_kcache.
🎯 Interview Key Takeaway
Use pg_stat_kcache alongside pg_stat_statements to measure exact OS-level CPU time and filesystem read/write bytes.
#pg_stat_kcache#OS Metrics#rusage#Disk I/O#Senior
Back to all PostgreSQL Questions→