Prompt and scope
The workload has slower scans, rising read latency, and mixed OLTP and analytical traffic. PostgreSQL 18 adds more I/O visibility, including byte-oriented pgstatio data and per-backend statistics. Use those views with query plans, wait events, and operating-system measurements to form a falsifiable diagnosis rather than tuning one parameter by instinct.
This is a data question because the core skill is database observability, workload reasoning, and safe performance experimentation.
What interviewers assess
First, can you identify the dimensions of pgstatio rows and avoid summing incompatible contexts? Backend type, object, context, and operation describe different sources of I/O.
Second, do you separate cumulative counters from a time window? A rate requires two samples with a known interval, and resets or restarts must be recorded.
Third, can you connect database evidence to a query? EXPLAIN ANALYZE with I/O timing and buffers shows whether a plan performs reads, writes, or prefetch; it does not replace system-level evidence.
Fourth, can you distinguish cache misses, checkpoint pressure, vacuum work, and storage saturation?
Fifth, can you change one variable, protect correctness, and validate the result against a representative workload?
Questions to clarify first
- Is the slowdown a single query, a workload class, or the entire instance?
- Did schema, statistics, volume, query mix, or PostgreSQL version change?
- What are the read/write latency, throughput, and queue-depth signals at the storage layer?
- Were statistics reset, the instance restarted, or a failover performed during comparison?
- Is the workload bounded by CPU, memory, I/O, locks, or client concurrency?
- What correctness and latency SLOs constrain a remediation?
30-second answer framework
“I would establish a before-and-after window, record restarts and statistics resets, then sample pgstatio by backend type, object, context, and operation. I would correlate deltas with EXPLAIN ANALYZE I/O timings, buffer usage, wait events, checkpoint and vacuum activity, and OS latency. I would classify the bottleneck, change one reversible control, replay a representative workload, and compare throughput, tail latency, correctness, and resource headroom.”
Step-by-step answer
Step 1: Establish a comparable window
Capture PostgreSQL version, workload shape, query IDs, restart time, statistics-reset time, and storage topology. Take two pgstatio samples far enough apart to expose rates, and retain the raw snapshots so a later reset is not mistaken for improvement.
SELECT backend_type, object, context, reads, read_bytes,
writes, write_bytes, read_time, write_time
FROM pg_stat_io;The exact columns and permissions depend on the target major version; pin the documentation and query used by the monitoring agent.
Step 2: Attribute I/O by dimensions
Compare backend types and contexts separately. Client backends, checkpointer, background writer, autovacuum workers, and maintenance operations imply different remedies. Relation data, indexes, and temporary files also have different physical behavior. Avoid one global “I/O is high” number.
Step 3: Correlate with the slow query
Run a representative EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) where safe, and enable I/O timing only with awareness of its measurement cost. Compare actual rows, read blocks, hit blocks, prefetch, and elapsed time. A plan with many reads may be expected for a scan; the question is whether the read rate and latency violate the workload budget.
Step 4: Separate competing causes
High reads with low device latency suggests cache pressure or a plan/data-size change. High read latency and queue depth suggests storage saturation. Checkpoint-heavy writes, vacuum activity, or temporary-file growth can contend with foreground queries. Wait events and CPU utilization help distinguish I/O waits from lock or executor bottlenecks.
Step 5: Form a falsifiable hypothesis
State a measurable claim such as “the new partition scan exceeds cache capacity and causes random reads” or “checkpoint write bursts delay foreground reads.” Choose a counterfactual test: a plan change, controlled cache warm-up, checkpoint pacing adjustment, index or partition correction, or workload isolation.
Step 6: Apply a reversible remediation
Change one control at a time, with a rollback value and an observation interval. Do not raise memory, workers, or checkpoint settings beyond host capacity. If the root cause is a query or data-layout regression, fix that before masking it with larger caches.
Step 7: Validate and preserve evidence
Replay a representative mix, compare p50 and tail latency, throughput, error rate, read/write bytes, wait events, and OS metrics. Confirm query results and replication behavior remain correct. Keep both windows and the decision record so a later restart or statistics reset is visible.
Model answer
“I would first capture version, restart and reset times, workload mix, storage metrics, and two pgstatio snapshots. I would compare deltas by backend type, object, context, and operation, then connect the suspected query to EXPLAIN ANALYZE buffers, I/O timing, WAL, wait events, and checkpoint or vacuum activity. I would not treat cumulative counters or one cache-hit ratio as a diagnosis.
After classifying cache pressure, storage latency, checkpoint contention, vacuum work, or plan regression, I would make one reversible change and replay a representative workload. Success requires lower tail latency with stable correctness, throughput, resource headroom, and replication behavior. The evidence and rollback value stay in the incident record.”
Common mistakes
- Summing every pgstatio row → incompatible dimensions mislead → group by backend, object, context, and operation.
- Comparing counters without a window → rates are invented → take timed deltas and record resets.
- Treating cache hit ratio as proof → storage latency and plan shape are hidden → correlate with bytes, timings, waits, and OS data.
- Running EXPLAIN ANALYZE on production blindly → workload is disturbed → use a safe replica or controlled sample.
- Changing many settings together → causality disappears → change one reversible variable.
- Ignoring vacuum and checkpoints → background work is blamed on queries → attribute backend contexts separately.
- Fixing symptoms with more memory → host pressure worsens → validate capacity and query/data-layout causes first.
Follow-up questions
Follow-up 1: Is pgstatio new in PostgreSQL 18?
The view predates 18, while PostgreSQL 18 adds further I/O visibility such as byte-reporting columns and per-backend statistics. Always use the documentation for the deployed major version.
Follow-up 2: How do you compute a rate?
Take two snapshots with timestamps, subtract the counters, divide by elapsed time, and annotate any restart or statistics reset between them.
Follow-up 3: Does high read_bytes prove a bad query?
No. A large scan may be intentional. Compare plan expectations, row counts, latency, cache state, and workload SLOs before labeling it a regression.
Follow-up 4: Why inspect backend_type?
Foreground clients, checkpointer, background writer, autovacuum, and maintenance work create different contention patterns and remediation options.
Follow-up 5: When is EXPLAIN I/O timing unsafe?
On a busy production path, measurement overhead can distort latency. Use a replica, sampled query, or controlled window and state the limitation.
Follow-up 6: What proves the fix worked?
Repeated representative workload runs show improved tail latency and throughput without correctness, replication, or capacity regressions, with before-and-after evidence retained.