Why this matters
- A checkout latency spike from a missing index on
orders(user_id)affects revenue before CPU alerts fire — query-level visibility catches it first. - Redis memory at 95% with
evicted_keysclimbing means session loss and cart abandonment — INFO fields tell you before users complain. - Production interviews expect you to name specific tools and the triage order: symptom → engine metrics → slow query → fix → verify.
Clustered (PK)
Table rows (sorted by PK)
Row 1id=1
Row 2id=2
Row 3id=3
Non-clustered (email)
email index→ row pointer
Table heapunordered rows
Postgres 16: pg_stat_statements
Enable the extension and load it on startup:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top 10 queries by total time (VaultCommerce reporting DB)
SELECT
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
left(query, 120) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
VaultCommerce's worst offender was a dashboard query doing a sequential scan on orders — 2.4M calls/day, 180 ms mean. Adding (user_id, created_at) index dropped mean to 4 ms.
-- After deploying an index, reset stats to measure improvement
SELECT pg_stat_statements_reset();
RDS exports pg_stat_statements metrics to CloudWatch via Performance Insights. Set alarms on db.load.avg and db.wait_event for IO:DataFileRead.
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 4 sections
- Redis 7: INFO and latency doctor
- Elasticsearch 8 cluster health
- CloudWatch alarm patterns
- On-call triage playbook
Test yourself
Answer these before moving on — recall is what makes it stick.