Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

Confirm this is genuine write amplification (more physical bytes written per logical write than before, not just more traffic) rather than assume it from the timing alone, then check the two most common migration-triggered causes: a newly added index on a frequently updated column defeating an existing update-in-place optimization, or a row-size change that broke existing free-space assumptions. Protect the primary immediately by removing or throttling whatever is generating the extra writes, not by tuning storage under fire, and confirm the fix worked by re-measuring the same ratio that flagged the problem, not just watching the IOPS (input/output operations per second) graph look better.

Structured elaboration

Glossary

Write amplification: writing more total bytes to physical storage than the logical write itself requires, for example one small UPDATE triggering writes to the table's heap plus several index structures plus the write-ahead log. IOPS (input/output operations per second): a measure of how many discrete disk read or write operations storage is asked to perform.

Confirming it's actually amplification

Compare the rate of logical writes (transactions per second, or rows changed per second) against the rate of physical bytes written (WAL bytes per second, or OS-level disk write throughput). If the logical write rate is flat but physical bytes written per logical write jumped, that's amplification, not simply more traffic.

Common migration-triggered causes, most likely first

• A newly added index on a column that's updated frequently: every update to an indexed column now has to write both the heap row and maintain every index touching it. In Postgres specifically, updating an indexed column disables the heap-only-tuple (HOT) optimization for that update, which otherwise lets an update skip touching indexes entirely when no indexed column changed and the page has free space. This is directly checkable: compare n_tup_hot_upd against n_tup_upd in pg_stat_user_tables; a sudden drop in that ratio right after the migration is a strong, checkable signal, and it's demonstrated concretely below.
• A column type or width change (widening a column, or adding several nullable columns) that increased average row size enough that rows no longer fit in their page's existing free space, forcing more page splits, and combined with a low fill factor, more full-page writes.
• A backfill migration still running in the background, generating sustained extra write-ahead-log volume on top of normal traffic; check whether the spike tracks the backfill's own progress rather than just the moment the schema change deployed.
• Full-page writes after a checkpoint: many engines write a full copy of a page to the log the first time it's touched after a checkpoint, to protect against a torn page on crash. If the migration spread writes over a wider, more random set of pages (for example, via a new index), more distinct pages hit this full-page-write cost following each checkpoint, amplifying log volume independent of any single row's size.

Immediate steps to protect the primary

If the cause is a specific new index that isn't yet load-bearing for query performance, consider dropping it (or marking it unused) as an emergency measure, an explicit, reversible trade made deliberately, not silently. If it's a backfill job, pause it rather than let it keep competing with foreground traffic for the same I/O budget; a well-built backfill should be resumable, and if this one isn't, that's a finding to fix on its own. If the amplification is coming from application write volume itself, throttle at the statement or connection level as a stopgap. Do not reach for "add more IOPS" as the first move; it treats the symptom and hides whether the underlying amplification is actually fixed.

Confirming the mitigation worked

Re-measure the same ratio used to confirm the problem (physical bytes per logical write, and the HOT-update ratio if that was the cause) back at its pre-migration baseline. A lower IOPS graph can simply mean traffic dropped for an unrelated reason (off-peak hours); the underlying amplification can still be broken and will resurface at the next traffic peak.

Worked example

A real, executed demonstration of the HOT-update mechanism specifically, since it's the most directly checkable and most common migration-triggered cause. Postgres 16, a table with free space reserved per page (fillfactor=70) so HOT updates are actually possible:

CREATE TABLE hot_demo (id bigint PRIMARY KEY, note text, counter int NOT NULL DEFAULT 0)
  WITH (fillfactor=70);

3,000 random updates to counter, with no index on it yet:

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      2922 |          2271 |    77.7

77.7% of updates avoided touching any index, exactly what HOT is for. Then an index is added on counter, the very column being updated, statistics reset, and the same 3,000 random updates repeated:

CREATE INDEX idx_hot_demo_counter ON hot_demo (counter);

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      2969 |             0 |     0.0

The HOT-update rate drops from 77.7% to exactly 0.0%. Every single update now has to touch the new index too, because it's on the column changing, real, measured write amplification caused directly by adding an index, on identical data and an identical update pattern, changed by nothing except the index's existence.

Trade-offs and pitfalls

• Dropping a newly added index to relieve write pressure can silently reintroduce the slow-query problem the index was meant to fix. Only do this as a deliberate, communicated, temporary trade, with a plan to rebuild it (ideally online, off-peak) once the amplification's root cause is actually addressed.
• Lowering fill factor helps HOT-update eligibility going forward but does not retroactively fix already-full existing pages; a table that's already bloated usually needs a rebuild to benefit, which carries its own availability trade-offs.
• A backfill migration that isn't resumable and rate-limited is a recurring failure pattern; treat "can this be paused and resumed without redoing work" as a required property of any migration touching every row of a live table, not an afterthought.

Direct answer

pg_stat_statements tracks cumulative statistics per normalized query (the same query shape with different literal values counted as one entry), so ranking by mean execution time surfaces queries that are consistently slow every time they run, which is a different and often more actionable signal than ranking by total time, which is dominated by whatever runs most often rather than whatever is individually worst.

Structured elaboration

One important correction before the query itself: the column names in the question, total_time, mean_time, and max_time, are the pre-PostgreSQL-13 names. As of PostgreSQL 13, they were renamed to total_exec_time, mean_exec_time, and max_exec_time (with separate total_plan_time/mean_plan_time columns added alongside them, splitting out query-planning time from execution time, which didn't exist as a separate measurement before). I ran this against a real PostgreSQL 16 instance and confirmed the old names no longer exist; the query below uses the current names and aliases the output to match what was asked for, so the result still has the requested column labels.

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_time_ms,
       round(mean_exec_time::numeric, 2)  AS mean_time_ms,
       round(max_exec_time::numeric, 2)   AS max_time_ms
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 5;

The WHERE clause filters out the extension's own bookkeeping query from the results, since otherwise this query would show up ranking itself. pg_stat_statements must be loaded via shared_preload_libraries and its extension created (CREATE EXTENSION pg_stat_statements;) before it will collect anything; it's not on by default.

Interpreting the results. The query column shows the normalized query text with literal values replaced by placeholders ($1, $2, and so on), because pg_stat_statements groups executions of the same query shape together regardless of which specific values were passed; this is different from pg_stat_activity, which shows the literal text of whatever is currently running. calls tells you how many times that shape has executed since the last stats reset, which matters for prioritization: a query with a high mean_exec_time but only 3 calls total is a very different priority than one with a similarly high mean but 50,000 calls. max_exec_time catches queries whose average looks fine but that occasionally spike badly, often a sign of a plan that's sensitive to the specific parameter values passed (a classic cause is a skewed data distribution where one particular value matches far more rows than the planner's cached plan expected).

Prioritizing the ranked list. Ranking by mean_exec_time alone finds individually slow queries, but the actual optimization priority should weigh mean time against call volume: a query at 60ms mean called 50,000 times a day is consuming roughly 50 minutes of cumulative database time daily, while a query at 500ms mean called only 10 times a day costs about 5 seconds cumulative; the first is very likely the better use of tuning effort even though it ranks lower on mean time alone, so I'd look at both this query's mean_exec_time-ranked list and a separate total_exec_time-ranked list before deciding where to spend effort, not just the one the question asked for.

Worked example

I generated real, varied query activity against a seeded 2 million row orders table (a mix of an indexed point lookup, a full-table GROUP BY, an unindexed range scan with an ORDER BY and LIMIT, and a self-join), reset pg_stat_statements, ran each query a realistic number of times, then ran the query above. Actual output:

 query                                                                                                                 | calls | total_time_ms | mean_time_ms | max_time_ms
------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+-------------
 SELECT count(*) FROM orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id WHERE o1.id < o2.id AND o1.id < $1  |     1 |        110.08 |       110.08 |      110.08
 SELECT status, count(*), avg(amount_cents) FROM orders GROUP BY status                                               |     2 |        126.62 |        63.31 |       64.05
 SELECT * FROM orders WHERE amount_cents > $1 ORDER BY created_at DESC LIMIT $2                                       |     1 |         27.72 |        27.72 |       27.72
 SELECT id, status FROM orders WHERE id = $1                                                                          |     5 |          2.59 |         0.52 |        1.22
 SELECT count(*) FROM orders WHERE customer_id = $1                                                                   |     3 |          1.05 |         0.35 |        0.75

Reading this: the self-join tops the list by mean time at 110ms, but it was only called once, so on its own this doesn't say much about production priority, it says this specific shape of query is expensive per-call, worth knowing if it's on a hot path. The GROUP BY query is second at 63ms mean but was called twice, both calls close to the mean (64.05ms max versus 63.31ms mean), so this one is consistently, not occasionally, expensive, a more confident signal that it needs a covering index (an index that already includes every column the query needs, so the database can answer it from the index alone without a separate trip to the table) or a materialized rollup (a precomputed, stored summary table that's refreshed periodically, so the GROUP BY is computed once and then just read) if it runs often in production. The point lookup by id and the count by customer_id are both fast and unremarkable, exactly what you'd expect from indexed equality lookups against a 2 million row table.

Trade-offs and pitfalls

pg_stat_statements numbers are cumulative averages since the last reset (or since the extension was created), so a query that was fast for months and only recently started degrading will still show an overall mean pulled down by its historical fast runs, masking a real, recent regression; for that, look at max_exec_time alongside the mean, or better, sample the view periodically and diff it over time rather than treating one snapshot as the full picture. mean_time ranking, as the direct answer notes, can mislead if read without call volume: it will surface a rarely-run, individually expensive report query above a frequently-run query whose cumulative cost to the system is actually far higher, so treat this ranked list as one input to prioritization, not the final answer.

Direct answer

Five metrics cover the core questions a responder needs answered first: is the database slow (a high-percentile query or write latency), is it actually serving load correctly (throughput and error rate), is it running out of room (connection and CPU or memory saturation), is its working set still fitting in memory (buffer or cache hit rate), and, if replicas exist, is it falling behind (replication lag). Pick alert thresholds relative to the workload's own historical baseline and its SLO (service-level objective, an internal target for how the system should perform), not a generic industry number, and expect an OLTP workload (online transaction processing, many small, fast operations) and an OLAP workload (online analytical processing, fewer, larger, longer-running queries) to need different baselines for the same metric.

Structured elaboration

On a managed database

A managed relational database service typically removes OS shell access, but the same five categories are still visible, just organized through the provider's own console or API instead of OS commands: host-level metrics (CPU, memory, disk, network, exposed as instance-level monitoring), database-level metrics (connections, buffer hit ratio, via the engine's own views proxied through the console), query-level metrics (top SQL by load, via a performance-insight feature layered on top rather than raw direct access to the engine's own statistics views), and replication metrics (lag, as a first-class console metric since the provider manages the replica topology). Losing shell access doesn't mean losing observability; it means the same five things live in a different namespace. Know where each one lives before an incident, not during one.

OLTP versus OLAP baselines

The same five metrics need structurally different thresholds depending on workload shape. An OLTP system, many small, fast, latency-sensitive operations, should alert tight on p99 latency, since milliseconds matter, and treat connection saturation as urgent, since a starved OLTP system fails user-facing requests immediately; its throughput baseline is usually fairly steady and predictable. An OLAP system, fewer, larger, longer-running analytical queries, should alert loose on p99 latency, since a single query legitimately taking minutes is normal, but tight on sustained resource saturation, CPU, memory, or I/O held near capacity for an extended period is the actual OLAP health signal, since a single slow query is expected but the whole system staying pinned means queuing is building, and on queue depth (how many queries are waiting to run) rather than on individual query latency. Applying an OLTP-shaped p99-latency alert to a warehouse would page constantly on completely normal behavior, and applying an OLAP-shaped hourly-average view to an OLTP system would miss a real user-facing latency spike entirely.

Worked example

Turning the p99-latency alert condition into concrete numbers, showing why a baseline-relative threshold rather than a fixed one is the right shape:

p99_baseline_ms = 42   # trailing 7-day baseline
multiple = 3
threshold_ms = p99_baseline_ms * multiple
print(threshold_ms)

126

At a 42ms trailing baseline, the alert fires above 126ms. If legitimate growth naturally moves the baseline to, say, 55ms next quarter, the threshold recalculates to 165ms automatically, whereas a fixed ">100ms" rule set today would already be uncomfortably close to normal, healthy behavior by then and would need someone to remember to revisit it.

Trade-offs and pitfalls

• A fixed threshold copied from a blog post or another team's dashboard, rather than derived from this workload's own baseline, is the most common reason alerts are either constantly noisy (too tight for this workload's normal variance) or dangerously quiet (too loose, so real incidents don't page). Baseline-relative thresholds avoid this, but need enough historical data to compute a meaningful baseline; don't set them from one week of a system still finding its steady state right after launch.
• Alerting only on averages instead of a high percentile hides exactly the tail-latency problems users actually feel; a p99 spike can coexist with a perfectly healthy-looking average if it affects a small fraction of requests, which is often the fraction most likely to matter most.
• Five metrics is a floor, not a ceiling. This is the minimum viable health picture, not a complete observability strategy; deeper investigation, specific slow-query identification, lock waits, log growth, still needs its own dedicated tooling once one of these five flags a problem.

Direct answer

pg_stat_activity is a system view, one row per active database connection (called a backend), showing what that connection is doing right now: its process ID, current state, what it's waiting on if anything, when its current query started, where it connected from, and the query text itself. It's the first place I look to answer "what is the database doing at this exact moment," especially for finding queries that have been running too long or sessions that are blocking each other.

Structured elaboration

Reading the key columns.

• pid: the backend's process ID. This is the identifier you'd pass to pg_cancel_backend(pid) (asks the query to stop, like a graceful interrupt) or pg_terminate_backend(pid) (forcibly ends the whole connection) if you decide to intervene.
• state: the backend's overall status. active means it's currently executing a query. idle means it's connected but waiting for the application to send the next command. idle in transaction is the one worth watching closely: a transaction is open but nothing is currently running inside it, which usually means application code forgot to commit or roll back, and it can hold locks the whole time it sits idle.
• wait_event_type and wait_event: if the backend isn't just burning CPU, these say what it's blocked on. A Lock wait means it's waiting on another session (contention); an IO wait means it's waiting on a disk read or write; a Timeout wait with event PgSleep means, unsurprisingly, it's inside a pg_sleep() call.
• query_start: when the currently running statement started. Subtracting this from now() gives you how long the current query has been running, which is the basis for the "longer than 5 minutes" filter below.
• client_addr: the IP address the connection came from. If this is null, it means the connection came in over a local Unix socket on the database server itself, not that the information is missing; a lot of same-host tooling and some connection poolers connect this way.
• application_name: whatever the connecting application chose to set it to (most drivers let you set this in the connection string). It's the single most useful column for triage in a system with more than one application talking to the database, because it turns an anonymous process ID into "this is the reporting job" or "this is the checkout service."

The query.

SELECT pid,
       state,
       wait_event_type,
       wait_event,
       query_start,
       now() - query_start AS running_for,
       client_addr,
       application_name,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
  AND state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

pid <> pg_backend_pid() excludes the very session running this query, since that query obviously isn't "long-running" relative to itself. Filtering state <> 'idle' keeps the result set to sessions actually doing something (a genuinely idle connection sitting there for hours isn't the problem this query is hunting for; idle in transaction for a long time is, and it passes the filter because its state literally is idle in transaction, not idle).

Worked example

I ran this against a real PostgreSQL 16 instance seeded with 2 million rows. I started a background session that set its application_name to reporting-svc and ran SELECT pg_sleep(20), count(*) FROM orders WHERE status = 'paid', then queried pg_stat_activity from a second session two seconds in (using a 1-second threshold instead of 5 minutes purely so the demo doesn't require an actual 5-minute wait; the query shape is identical). The real output:

 pid | state  | wait_event | query_start                    | running_for     | client_addr | application_name | query
-----+--------+------------+---------------------------------+------------------+-------------+------------------+-------------------------------
  74 | active | PgSleep    | 2026-09-25 05:05:49.564188+00 | 00:00:02.085267 |             | reporting-svc    | SET application_name = 'reporting-svc'; select pg_sleep(20),

Reading this row: pid 74 is the one to act on if I decide to cancel it. state is active, so it's genuinely executing, not idle in a transaction. wait_event is PgSleep, confirming it's inside the sleep, not blocked on a lock or I/O (in a real slow query you'd instead see either no wait event at all, meaning it's CPU-bound, or an IO event like DataFileRead, meaning it's waiting on disk). client_addr is blank, which by the definition above means this connected over a local Unix socket, not that I'm missing data. application_name immediately tells me this is the reporting service, not, say, the checkout path, which changes how urgently I'd intervene.

Trade-offs and pitfalls

pg_stat_activity shows the currently executing statement, not the whole transaction's history, so a session that ran three fast statements and is now stuck idle in a fourth, uncommitted transaction will show idle in transaction with an old xact_start but a recent-looking query_start from its last completed statement; check xact_start as well as query_start when a session looks suspicious. The query text shown is the literal text sent by the client, including any parameter placeholders the driver already substituted, so very generic-looking queries from an object-relational mapper (ORM, a library that translates between database rows and application objects) can be hard to trace back to a specific code path from the text alone; application_name and a request or correlation ID embedded in a SQL comment (many ORMs support this) close that gap. Running this query itself has negligible cost, but reflexively killing every session with pg_terminate_backend the moment it shows up in a long-running query check is a mistake: a five-minute analytical report or a maintenance job (like a VACUUM) is legitimately long-running, and killing it mid-write can force a rollback that costs more time than letting it finish.

Direct answer

I'd put four categories on the dashboard: request-level health (queries per second, latency at the p50 and p99 percentiles, error rate), resource saturation (connections in use, cache hit ratio, lock wait time), replication and durability (replication lag, write-ahead log queue depth, backup freshness), and the specific failure modes that have actually paged people before. For alerting, I set thresholds using rolling windows and a warn/page split, not single static numbers, and I map every page to a first remediation step so the on-call engineer isn't starting from zero at 3am.

Structured elaboration

What goes on the dashboard, and why each one.

If the cluster runs on a storage engine with background compaction (an LSM-tree, log-structured merge-tree: a storage design that buffers writes in memory and periodically merges, or compacts, them into new sorted files on disk instead of updating in place; or a columnar engine, one that stores each column's values together instead of each row, common for BI, business intelligence, and analytics workloads) sitting next to the OLTP (online transaction processing: the primary's regular small, latency-sensitive reads and writes) primary, such as a Citus columnar table (Citus is primarily PostgreSQL's distributed sharding extension; Citus columnar is one table access method within it, added for compressed, append-mostly storage of historical partitions, not the extension's main purpose) or a ClickHouse/TimescaleDB continuous-aggregate sidecar (a separate analytics-oriented database kept in sync with the primary specifically to serve rollup queries without loading the OLTP primary), add compaction backlog and table bloat (wasted, dead space that accumulates in a table or index and isn't automatically shrunk back down) to the list; a growing compaction backlog degrades read latency the same way index bloat does on a classic B-tree engine, just through a different mechanism.

Setting thresholds without drowning in noise.

Three techniques matter more than picking the "right" number:

1. Use rolling windows, not instantaneous values. A single 5-second latency spike from garbage collection or a checkpoint is normal; the same value sustained for 5 minutes is not. Alert on "p99 latency over 500ms for 5 consecutive minutes," not on any single sample crossing 500ms.
2. Split warn from page. Warn (visible on a dashboard, maybe a low-priority ticket) catches trend problems early; page (wakes a human) is reserved for things that are both urgent and actionable right now. Example: cache hit ratio dropping below 95% is a warn (the working set is starting to outgrow memory, worth planning around); dropping below 85% is a page (you're now taking a meaningfully worse I/O hit on a large fraction of reads).
3. Prefer relative and anomaly-aware thresholds over hardcoded absolutes where the baseline varies by time of day or by deployment size. A fixed "QPS > 10,000" alert is wrong for a service whose normal peak is 50,000; a threshold expressed as a deviation from the same hour last week, or a simple rolling z-score, survives normal traffic-shape changes without needing to be hand-tuned every time the service grows.

Concrete threshold examples, with the reasoning attached (not just the numbers):

• Cache hit ratio: warn under 95%, page under 85%. Reasoning: below 95% you're starting to lose the benefit of memory caching on a meaningful slice of reads; below 85% the marginal cost of each additional point drops is large, because you've moved from "occasional disk read" to "disk read is now common."
• Replication lag: warn over 30 seconds, page over 300 seconds (5 minutes). Reasoning: 30 seconds of staleness is tolerable for most read-replica use cases and often self-heals; 5 minutes means a failover right now risks losing up to 5 minutes of committed writes on an asynchronous replica, and reads from that replica are stale enough to be user-visible.
• Long-running transactions: page anything open longer than 10 minutes holding row or table locks. Reasoning: a transaction open that long is either a bug (forgotten COMMIT), a batch job that should be broken into smaller pieces, or actively blocking other work; none of those are things you want to discover only when something else pages.
• I/O operations per second (IOPS) on the storage volume, if the cluster runs on cloud block storage (a virtual disk volume attached to the database server, such as AWS EBS or GCP Persistent Disk, as opposed to object storage): page at 90% of the provisioned IOPS ceiling, sustained for 5 minutes. Reasoning: cloud volumes throttle hard at the ceiling rather than degrading gracefully, so you want the page before you hit the wall, not after latency has already spiked.

Map every page to a first action, not just a threshold. An alert without a next step trains people to acknowledge and go back to sleep. Two of the most common:

• High I/O wait alert fires: first action is to check whether it's a specific query (via the active-session view) versus a broad pattern across all queries (pointing at the underlying storage), and whether a recent deploy or backfill job correlates.
• Replication lag alert fires: first action is to check whether the primary's write volume actually increased (a load-driven cause you may just need to ride out) versus the replica itself being resource-starved (a replica-side problem you can act on directly, like resizing or restarting a stuck apply process).

Worked example

Say the team's baseline is 2,000 QPS with p99 latency normally around 40ms. I'd set: p99 latency warn at 150ms sustained 5 minutes, page at 400ms sustained 5 minutes (roughly 4x and 10x baseline, chosen because a load test showed the application's own request timeout is 500ms, so 400ms is the last point where paging still gives someone time to act before users see timeouts, not after). Connections in use: warn at 70% of max_connections, page at 90%, because pool exhaustion is a hard cliff, not a gradual degradation, so you want the warning well before the cliff. If, after a week of this configuration, the warn-level connection alert has fired forty times with no corresponding incident, that's a signal the threshold is miscalibrated for this workload's normal variance, not that the metric is useless; the fix is widening the window or raising the threshold, not deleting the alert.

Trade-offs and pitfalls

The most common failure mode isn't missing an important metric, it's alerting on too many of them with static thresholds tuned for a different traffic level than the service has grown into, which trains the on-call rotation to ignore pages. The opposite failure, cutting alerts down to "just the essentials," risks the exact regression this question is trying to avoid: rediscovering a real incident an hour late because nothing caught it. Anomaly-aware and relative thresholds reduce false positives but are harder to reason about at 3am ("why did this page, the number doesn't look that bad") than a simple static threshold, so pair them with a runbook line that explains, in plain language, what the alert is actually detecting. A backup-freshness check that only verifies "the job ran" rather than "the resulting backup restores cleanly" is a common blind spot: it will show green right up until the day you actually need it and discover the backup was silently corrupt.