PostgreSQL 16 pg_stat_io: Separating storage pressure from query latency
Use the I/O statistics view introduced in PostgreSQL 16 to design a practical baseline and diagnose a slow report.
What became observable in 2023
PostgreSQL 16 introduced pg_stat_io, a view for examining I/O activity by dimensions such as backend type and I/O context. A slow report can involve storage requests, concurrent writes or CPU work. The view supplies another way to distinguish those explanations. It does not attribute every operation to a particular statement or prove that each database-level read resulted in a physical device operation.
Read deltas in context
Capture the counters before and after a controlled operation. Match their dimensions so you compare each backend type, object and context with its starting value. Cumulative counters include unrelated work in the same window and may have been reset. Record that possibility. Combine operation counts with elapsed time. A high read count and low latency can coexist when the time is being spent elsewhere.
SELECT backend_type, object, context, reads, writes, stats_reset
FROM pg_stat_io
ORDER BY backend_type, object, context;Build an experiment around a slow report
Suppose the same report is slower on Monday mornings. Use an identical parameter set in a controlled environment and capture the query plan. Then compare running the report alone with running it while maintenance is active. Record elapsed time, plan buffer information and deltas from pg_stat_io. Avoid clearing production caches or resetting production statistics to simplify a test. The point is a reproducible comparison.
Correlate query and operating-system signals
The view does not identify the report query by name. Use its execution plan and, where available, statement-level statistics such as pg_stat_statements. Align observations with platform metrics when looking for physical storage latency and the operating system cache. Verify which I/O timing settings are enabled and what overhead they add before interpreting timing values.
Turn the observation into a testable decision
When the slowdown only overlaps maintenance, reschedule that work in a controlled trial and measure again. If the report also runs slowly alone, inspect the plan and distribution of the data. Track report latency, the I/O delta and maintenance completion time together across several runs. This keeps an improvement in one short window from being mistaken for a lasting fix. Record the database version, report parameters and observation window beside each sample. If a query plan changes between trials, document it as a separate variable.
Comments
No comments yet. Be the first to contribute.
Discussion question from the editor Which signal would make you investigate storage before rewriting the query?
Reading “PostgreSQL 16 pg_stat_io: Separating storage pressure from query latency” raised this question for me: long-term maintenance cost is important.
Editor note: Security and failure modes require evidence beyond a launch post. We will revisit the assessment as the source is updated.
Features are only part of the story: security and privacy deserve their own review.
I would compare the announcement against independent benchmarks before making a decision.
✎ Write a comment