Skip to main content
PreparationZone Logo
PrepZone

Monitoring and Slow Queries

pg_stat_statements, Redis INFO, CloudWatch alarms, and the on-call triage playbook.

Pass checkpoint below

Read these first

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_keys climbing 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
A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

Postgres 16: pg_stat_statements

Enable the extension and load it on startup:

Java
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.

Java
-- 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.