What does wait_event_type = Lock usually indicate?
Sessions are blocked by other transactions.
DBA action: identify blockers and kill idle-in-transaction sessions.
What does wait_event_type = IO mean?
Sessions are waiting on storage operations.
DBA action: determine read vs write vs WAL pressure.
What does wait_event_type = NULL mean?
The session is actively running on CPU.
DBA action: inspect query logic and execution plans.
What is the difference between blocking and a deadlock?
Blocking resolves when one transaction finishes.
A deadlock is a circular dependency and Postgres kills one transaction automatically.
What is the most dangerous pg_stat_activity state?
idle in transaction.
It holds locks, blocks vacuum, and causes cascading failures.
What are all of the pg_stat_activity states?
Lock
IO
LWLock (Lightweight Lock)
Client
Timeout
Activity
CPU (NULL wait_event_type)
What does rising commit latency usually indicate?
WAL or write-path pressure.
DBA action: check WAL waits and write-heavy workloads.
What does IO + WALWrite / WALSync indicate?
Commits are waiting on WAL flush.
DBA action: throttle write bursts, break large transactions.
What does IO + DataFileRead usually mean?
Read amplification from bad plans, bloat, or cold cache.
DBA action: inspect top queries and their plans.
What does a rising checkpoints_req counter mean?
Forced checkpoints due to WAL pressure.
DBA action: increase max_wal_size and reduce write bursts.
What does checkpoint_completion_target control?
How evenly dirty pages are flushed between checkpoints.
Higher values smooth IO and reduce spikes.
What does VACUUM actually do?
Removes dead tuples and marks space reusable.
It does not shrink tables or compact data.
What is bloat?
Extra space caused by dead tuples or unused pages.
Leads to increased IO and cache inefficiency.
What is fragmentation?
Live data spread across many pages, reducing locality.
Causes gradual IO creep even with healthy vacuum.
Why can IO increase even when vacuum is running?
Vacuum cleans tuples but does not compact data.
Fragmentation causes more pages per query.
What blocks vacuum from removing dead tuples?
Long-running transactions, especially idle-in-transaction sessions.
What does high shared_blks_read mean?
Blocks were read from storage, not cache.
Repeated high reads indicate IO amplification.
What does high shared_blks_hit mean?
Data was served from memory cache.
This is fast and healthy.
When do you run EXPLAIN (ANALYZE, BUFFERS)?
When a query dominates latency or IO.
It confirms plan behavior and read/write cost.
What does “plan regression” mean?
Same query, different execution strategy, much more work.
Often caused by stale stats or data skew.
What is the safest first fix for a suspected plan regression?
Run ANALYZE.
It’s fast, safe, and often resolves the issue.
Do Aurora readers guarantee zero-lag reads?
No. Replication lag still exists.
Route read-after-write traffic to the writer.
Does failover fix performance problems?
No. Failover is for recovery, not tuning.
Bad plans and bloat survive failover.
What is Performance Insights best used for?
Fast incident orientation: who is slow and why.
DB Load + wait types guide next steps.
