Definition
- Query latency – track p50, p95, and p99 response times from
pg_stat_statementsor the performance schema, not the average which hides the slow tail. - Throughput – measure transactions or queries per second; rising throughput with stable latency is healthy.
- Connections – monitor active, idle, and pending connections in the pool (HikariCP) and the server.
- CPU and I/O – watch saturation signals; high CPU with low I/O suggests inefficient queries, high I/O suggests cache misses.
- Cache hit ratio – a healthy OLTP buffer pool sits above 99 percent; a falling ratio warns the working set is spilling to disk.
- Slow-query log and EXPLAIN ANALYZE – find the specific offending statements and read their actual execution plans.
Measuring the National Bank of India database is like running a health check on the bank's busiest counter. Time each customer (query latency), count how many are served per minute (throughput), check the queue length (connections), see how hard the staff and machinery work (CPU and I/O), and check how often the answer was already on the clerk's desk versus fetched from the back vault (cache hit ratio).
Understanding the Concepts
What is query latency and why measure percentiles?
Query latency is the time a single query takes to complete. The p50, p95, and p99 latencies tell the real story because an average hides the slow tail that hurts customers.
-- PostgreSQL: top 10 slowest query patterns by p99 time
SELECT query, calls, mean_exec_time, max_exec_time,
stddev_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY max_exec_time DESC
LIMIT 10;
query | calls | mean_exec_time | max_exec_time
-------------------------------+-------+----------------+--------------
SELECT * FROM transactions ... | 45000 | 12.3 ms | 8450.2 ms
UPDATE accounts SET balance... | 22000 | 5.1 ms | 3200.8 ms
Rajesh reviews this weekly at National Bank of India to catch regressions before they affect customers.
How do you measure throughput?
Throughput is transactions or queries per second. Rising throughput with stable latency is healthy; latency climbing as throughput rises signals a bottleneck.
-- PostgreSQL: transactions committed and rolled back
SELECT datname,
xact_commit AS commits_total,
xact_rollback AS rollbacks_total,
tup_returned AS rows_returned,
tup_fetched AS rows_fetched
FROM pg_stat_database
WHERE datname = 'national_bank';
Monitor the delta over time intervals to compute per-second rates.
How do you monitor connections?
Every database has a connection limit. Track active, idle, and waiting connections.
-- PostgreSQL: connection breakdown by state
SELECT state, COUNT(*) AS count
FROM pg_stat_activity
WHERE datname = 'national_bank'
GROUP BY state;
state | count
-------------------+------
active | 12
idle | 45
idle in transaction| 3
package com.bank.monitoring;
import com.zaxxer.hikari.HikariDataSource;
public class PoolMonitor {
public void logPoolStats(HikariDataSource ds) {
System.out.println("Active: " + ds.getHikariPoolMXBean().getActiveConnections());
System.out.println("Idle: " + ds.getHikariPoolMXBean().getIdleConnections());
System.out.println("Waiting: " + ds.getHikariPoolMXBean().getThreadsAwaitingConnection());
}
}
A pool exhausted with threads waiting is a classic cause of latency spikes.
How do you check CPU and disk I/O utilization?
High CPU with low I/O often means inefficient queries doing in-memory work. High I/O means the working set no longer fits in memory.
# Check disk I/O on the database server
iostat -x 1 5
# Check CPU utilization
top -bn1 | head -5
-- PostgreSQL: I/O statistics per table
SELECT relname,
heap_blks_read AS disk_reads,
heap_blks_hit AS cache_hits,
ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2)
AS hit_ratio_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 5;
What is the cache hit ratio and why does it matter?
The cache hit ratio measures how often a requested page was in the buffer pool versus read from disk. A healthy OLTP system sits above 99 percent.
-- PostgreSQL: overall buffer cache hit ratio
SELECT
SUM(heap_blks_hit) AS cache_hits,
SUM(heap_blks_read) AS disk_reads,
ROUND(100.0 * SUM(heap_blks_hit) /
NULLIF(SUM(heap_blks_hit) + SUM(heap_blks_read), 0), 2) AS hit_ratio
FROM pg_statio_user_tables;
cache_hits | disk_reads | hit_ratio
-----------+------------+----------
98452310 | 142580 | 99.86
A falling ratio warns that the working set is spilling to disk, which is an early signal to add memory.
How do you use the slow-query log?
The slow-query log captures statements exceeding a threshold. Enable it and set the threshold.
-- PostgreSQL: enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();
-- MySQL: enable slow query log
-- SET GLOBAL slow_query_log = 1;
-- SET GLOBAL long_query_time = 0.5;
For each offender found in the log, run EXPLAIN ANALYZE to see the actual plan.
How do you read an execution plan with EXPLAIN ANALYZE?
The execution plan shows row estimates versus actuals, whether an index or sequential scan was used, and where time is spent.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT t.txn_id, t.amount, a.account_name
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.created_at > '2026-01-01'
AND t.amount > 50000;
Hash Join (cost=... rows=... actual time=2.1..45.3 rows=1200)
-> Seq Scan on transactions (actual rows=500000) -- Problem!
-> Hash (actual rows=10000)
-> Seq Scan on accounts
The Seq Scan on transactions with 500000 rows signals a missing index on (created_at, amount).
How do you monitor lock and wait statistics?
Lock waits and deadlocks reveal concurrency contention.
-- PostgreSQL: find blocked queries and the blocker
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
AND gl.database = bl.database
AND gl.relation = bl.relation
AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
WHERE blocked.pid != blocking.pid;
What monitoring stack does a senior engineer use?
Pankaj sets up a complete observability pipeline for National Bank of India.
Application (Spring Boot + Micrometer)
│
▼
Prometheus (scrapes metrics every 15s)
│
▼
Grafana (dashboards: p99 latency, pool saturation, cache ratio)
│
▼
AlertManager (alerts on sustained p99 > 500ms, pool pending > 5)
package com.bank.config;
import io.micrometer.core.instrument.MeterRegistry;
import org.springframework.stereotype.Component;
@Component
public class DbMetrics {
public DbMetrics(MeterRegistry registry) {
// HikariCP auto-exposes pool metrics via Micrometer
// pg_stat_statements metrics via a custom collector
}
}
Alert on sustained p99 latency, pool saturation, replication lag, and falling cache hit ratio rather than on averages.
Visual Overview
┌─────────────────────────────────────────────────────────┐
│ Database Performance Metrics │
├─────────────────┬───────────────────┬───────────────────┤
│ Query Layer │ Resource Layer │ Concurrency Layer │
├─────────────────┼───────────────────┼───────────────────┤
│ p50/p95/p99 │ CPU utilization │ Active connections│
│ latency │ Disk I/O (IOPS) │ Idle connections │
│ Throughput (TPS)│ Cache hit ratio │ Pending (waiting) │
│ Slow query log │ Memory pressure │ Lock waits │
│ EXPLAIN ANALYZE │ WAL write rate │ Deadlock count │
└─────────┬───────┴─────────┬─────────┴─────────┬─────────┘
│ │ │
└────────┬────────┘ │
▼ │
┌─────────────────┐ │
│ Prometheus + │◄───────────────────┘
│ Grafana │
│ (correlate all)│
└─────────────────┘
Key Takeaway
Measuring database performance requires looking across three dimensions at once: query, resource, and concurrency. On the query side, track latency as percentiles (p50, p95, p99), not the average, because the average hides the slow tail that actually hurts customers. For the worst statements, read the actual execution plan with EXPLAIN ANALYZE to see estimates versus reality.
On the concurrency side, track active, idle, and pending connections. An exhausted connection pool with threads waiting is a classic cause of latency spikes that looks nothing like a CPU problem. On the resource side, watch CPU, disk I/O, and the buffer-cache hit ratio, which should sit very high in a healthy OLTP system.
At the National Bank of India this paid off during salary-day peak, when the transfer API slowed but CPU looked fine. The real signal was the connection pool: pending connections spiked while the pool was exhausted, so the fix was pool sizing and transaction scoping, not more database power. Separately, the account-statement query kept appearing in the slow-query log, and EXPLAIN ANALYZE revealed a missing index.
The practice Pankaj always describes is to correlate everything in a proper stack: Micrometer exporting to Prometheus, dashboards in Grafana, and an APM tracing slow requests end to end. Alert on sustained p99 latency, pool saturation, and replication lag rather than on averages, because those are the signals that predict customer pain.
Remember one sentence: measure latency as percentiles, throughput, connections, CPU, I/O, cache hit ratio, and the slow-query log together, and alert on percentiles and saturation rather than averages.
---