Monitoring and Slow Queries

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

Expert10 min read

Read these first

Database Security

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.

Redis 7: INFO and latency doctor

Java

redis-cli INFO stats
# instantaneous_ops_per_sec:12400
# evicted_keys:0          ← alarm if > 0 under memory pressure
# keyspace_hits:982341
# keyspace_misses:42103

redis-cli INFO memory
# used_memory_human:3.42G
# maxmemory_human:4.00G
# mem_fragmentation_ratio:1.08

redis-cli --latency-history -i 1

Redis alerts VaultCommerce uses

used_memory > 85% of maxmemory — scale up or tune TTLs before eviction starts.
evicted_keys rate > 0 — hot keys or insufficient memory; check cart/session TTL policies.
rejected_connections — hit maxclients; scale or fix connection leaks in app pools.
master_link_down_since_seconds — replication broken; reads may be stale.

Elasticsearch 8 cluster health

Java

curl -s localhost:9200/_cluster/health?pretty
# "status": "yellow" → unassigned replica shards — investigate before red

curl -s localhost:9200/_cat/indices/products-*?v&s=store.size:desc

CloudWatch alarms on ClusterStatus.red, JVMMemoryPressure > 85%, and search latency p99 from application metrics.

CloudWatch alarm patterns

Metric	Threshold	VaultCommerce action
RDS CPUUtilization	> 80% for 10 min	Check pg_stat_statements; scale or kill runaway query
RDS FreeableMemory	< 500 MB	Review shared_buffers, work_mem; consider larger instance
RDS ReplicaLag	> 300 sec	Pause heavy analytics; check network or I/O
ElastiCache DatabaseMemoryUsagePercentage	> 85%	Eviction risk — expand or trim TTLs
ES ClusterStatus	red	Page immediately — shard allocation failure

RDS CPUUtilization
Threshold> 80% for 10 min
VaultCommerce actionCheck pg_stat_statements; scale or kill runaway query
RDS FreeableMemory
Threshold< 500 MB
VaultCommerce actionReview shared_buffers, work_mem; consider larger instance
RDS ReplicaLag
Threshold> 300 sec
VaultCommerce actionPause heavy analytics; check network or I/O
ElastiCache DatabaseMemoryUsagePercentage
Threshold> 85%
VaultCommerce actionEviction risk — expand or trim TTLs
ES ClusterStatus
Thresholdred
VaultCommerce actionPage immediately — shard allocation failure

Every alarm links to a runbook section in the production checklist article.

On-call triage playbook

Symptom: API p99 latency up, errors normal

Check CloudWatch RDS DatabaseConnections — pool exhaustion? (connections.pending in HikariCP metrics)
Pull top queries from Performance Insights / pg_stat_statements
Run EXPLAIN (ANALYZE, BUFFERS) on the suspect query in a read replica session
If Redis-related: INFO stats, check hit ratio (hits / (hits + misses) should be > 95%)
If search-related: ES _cluster/health, thread pool rejections

Symptom: elevated 5xx on checkout

Postgres: SELECT * FROM pg_stat_activity WHERE state = 'active' AND wait_event IS NOT NULL
Look for lock waits on inventory or orders — isolation level or long transactions
Redis: SLOWLOG GET 10 for commands over 10 ms

Java

-- Blocking lock detection (Postgres 16)
SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       left(blocked.query, 80) AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE NOT blocked.granted;

Quick recall

Everything you need if you only revisit this box.

pg_stat_statements ranks queries by total and mean time — VaultCommerce's first stop for Postgres slowness.
Redis INFO: watch used_memory, evicted_keys, hit ratio, and rejected_connections.
Elasticsearch: _cluster/health and JVM pressure; red status pages immediately.
CloudWatch ties RDS, ElastiCache, and ES to actionable thresholds with runbook links.
Triage order: connections → slow queries → locks → cache hit ratio → shard health.

Test yourself

Answer these before moving on — recall is what makes it stick.