Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

Don't start by assuming the analytics jobs cause the spikes just because they share an hourly schedule. Build a three-step methodology: instrument write latency and every candidate resource at fine enough granularity to catch a multi-minute spike, run a natural experiment (move or pause the job) to test causality rather than infer it from a coincidence, and only then pick a mitigation that matches the specific resource the job turns out to contend for.

Structured elaboration

Instrumentation and sampling

• Capture write latency as a percentile distribution (p50, p99, p99.9, meaning the value that only the slowest 50%, 1%, or 0.1% of requests respectively exceed), not an average, at 1-minute or finer resolution. Hourly or even 5-minute rollups can average a 90-second stall down to invisible.
• Tag every latency sample by region. P99 write latency spikes affecting "global users" can be driven by a single slow region pulling the global percentile up while every other region is healthy; segmenting by region rules that in or out before you even look at the analytics job.
• Instrument both client-perceived latency (including cross-region network round trip) and server-side commit latency separately, so a network routing problem doesn't get mistaken for a database problem.
• At the database layer, sample pg_stat_activity and pg_locks (or your engine's equivalents) every 10 to 30 seconds during the analytics job's known window, and less frequently outside it, to catch lock waits or connection pressure in the act rather than after the fact.
• At the OS/infrastructure layer, sample disk queue depth and I/O throughput, CPU run queue, and network egress on the primary's host at the same resolution.

Validating causality, not just coincidence

• Time-lag cross-correlation: compute the correlation between the job's on/off schedule and the p99 series at several time lags. A real causal relationship shows a clear correlation peak near a small, consistent lag (the time it takes contention to manifest as latency); a coincidental "same hour, different cause" pairing shows no clean peak.
• The strongest test is a natural experiment: deliberately shift or skip the analytics job's schedule for a few cycles and watch whether the latency spike moves with it. If the job moves 30 minutes and the spike doesn't move with it, causation is refuted right there, regardless of how well the original schedules lined up.
• Rule out confounders sharing the same "top of the hour" slot: backup jobs, log rotation, connection-pool recycling, and TLS certificate renewal are common hourly/cron neighbors that produce the exact same false-positive correlation.
• If causal, you should be able to find a resource-contention fingerprint that lines up with the spike window: for example, writer queries visibly waiting in pg_locks on something the analytics job holds, or disk queue depth rising in lockstep with both.

Deciding the mitigation once causality is confirmed

The right fix depends entirely on which resource is actually contended, so diagnose that before picking one:

• I/O contention (the job reads or writes heavily and competes with OLTP, online transaction processing, meaning the primary's own regular small reads and writes, for write fsyncs): move the job to a read replica, or throttle its I/O.
• Lock contention (the job holds a long read lock, or triggers maintenance against a hot table): run it at a snapshot-isolated read level against a replica so it can't block writers at all.
• Connection-pool exhaustion (the job opens many connections and starves the OLTP pool): give it its own bounded connection pool or role.
• Replication-apply pressure (the job runs against a replica and its side effects add load back toward the primary): move it to a dedicated analytics replica or a change-data-capture-fed warehouse instead of querying anything in the primary's write path.

Reach for the fix that matches the confirmed mechanism. Throwing more hardware at the primary before you know which resource is contended just relocates the spike to whichever resource you didn't address.

Worked example

The technique for step two, testing whether the spike's timing actually tracks the job rather than just sharing an hour, is a lagged cross-correlation. Mechanically: pick a candidate lag, say 2 minutes, shift the job's on/off schedule by that many minutes, then compute the standard correlation coefficient (a single number between -1 and 1 measuring how closely two series move together) between the shifted job-schedule series and the latency series. Repeat that across a range of candidate lags and see which one produces the highest correlation; a sharp, high peak at one specific lag, one that stays at the same lag even after you deliberately move the job, is the signature of real causation rather than coincidence. Below is a small, fully pinned illustration of the method (a hand-built synthetic series, not real production data) showing how a genuine causal relationship keeps its correlation peak at the same lag even after the job's schedule is deliberately shifted, which is exactly the signature you'd look for in the real natural experiment.

import statistics

minutes = list(range(140))
# week 1: job runs minutes 60-74. week 2: job deliberately SHIFTED to minutes 90-104.
job_week1 = [1 if 60 <= m < 75 else 0 for m in minutes]
job_week2 = [1 if 90 <= m < 105 else 0 for m in minutes]

def synth_p99(job_on, lag=2, base=40, spike=260, decay=6):
    out = []
    start = job_on.index(1) + lag
    end = len(job_on) - 1 - job_on[::-1].index(1)
    for m in minutes:
        if start <= m <= end:
            out.append(base + spike)
        elif m > end:
            d = spike * (0.6 ** ((m - end) / decay))
            out.append(base + d if d > 1 else base)
        else:
            out.append(base)
    return out

def xcorr(job_on, p99, lags=range(-10, 11)):
    jm, pm = statistics.mean(job_on), statistics.mean(p99)
    js, ps = statistics.pstdev(job_on), statistics.pstdev(p99)
    result = {}
    for lag in lags:
        num, cnt = 0.0, 0
        for i in range(len(job_on)):
            j = i - lag
            if 0 <= j < len(job_on):
                num += (job_on[j] - jm) * (p99[i] - pm)
                cnt += 1
        result[lag] = (num / cnt) / (js * ps) if js > 0 and ps > 0 else 0.0
    return result

for label, job_on in [("week1 (job at :60-:74)", job_week1), ("week2 (job shifted to :90-:104)", job_week2)]:
    p99 = synth_p99(job_on)
    corr = xcorr(job_on, p99)
    best = max(corr, key=lambda k: corr[k])
    print(f"{label}: peak correlation r={corr[best]:.3f} at lag={best} min")

Running this prints:

week1 (job at :60-:74): peak correlation r=0.892 at lag=2 min
week2 (job shifted to :90-:104): peak correlation r=0.891 at lag=2 min

The peak stays at the same 2-minute lag both times, even though the job moved 30 minutes later in the second series. That consistency, the spike tracking the job wherever it goes, is the signature you're looking for in the real natural experiment; a coincidental hourly neighbor would not follow the job when it moves.

Trade-offs and pitfalls

• Dashboards built on hourly or coarse averages will hide the exact spike you're trying to find; insist on percentile latency at sub-minute resolution before trusting any "it looks fine" reading.
• Correlation from a shared schedule is the single most common false lead in this kind of investigation; treat "same hour" as a hypothesis to test, never as evidence on its own.
• Reacting with a broad mitigation (bigger instance, more replicas) before confirming causality wastes effort and can mask the real cause, which then resurfaces later under a different label.
• A global p99 can hide a single bad region; always confirm whether the spike is global or regional before designing a fix, since the two point at very different mitigations.
• Pausing the job "to see if it helps" without checking whether the deferred work simply lands later doesn't prove much; compare like-for-like windows, not just "did today's number improve."

Direct answer

A duplicate-primary-key report on a replicated MySQL cluster right after a crash and restart narrows to three hypotheses: binlog corruption from the unclean shutdown, a transaction that was only partially replicated before the crash, or an actual split-brain (including the classic cause of a cloned server_uuid). GTIDs (global transaction identifiers), the binary log, and SHOW REPLICA STATUS each leave a different, checkable fingerprint for each hypothesis, so the investigation is a matter of reading the right view in the right order rather than guessing.

Structured elaboration

Step 1: immediate triage

On every replica, run SHOW REPLICA STATUS\G (this replaced SHOW SLAVE STATUS in MySQL 8.0.22 and later; MySQL 8.4 only recognizes the new name). Check Last_SQL_Error and Last_IO_Error: a real duplicate-key error surfaces here verbatim if the applier thread hit it while replaying a row. Compare Retrieved_Gtid_Set against Executed_Gtid_Set: a gap means the replica has received transactions it hasn't applied yet.

Step 2: rule split-brain in or out first

Check read_only/super_read_only on every node to confirm which one was actually acting as the source at crash time. Then check SELECT @@server_uuid on every node in the topology. A surprisingly common root cause of "duplicate primary keys after a crash" is a replica provisioned by copying the source's data directory without regenerating auto.cnf, so two servers share the same server_uuid. GTID-based replication identifies transactions by server_uuid:transaction_number; if two servers share a UUID, their GTID sets can look mutually consistent to the replication machinery while the actual row data on each has quietly diverged, since nothing in the GTID bookkeeping can tell the two servers apart.

Step 3: check for binlog corruption from the unclean shutdown

An unclean crash can leave the last binary log file with a partially written event. Check the server's error log around the crash timestamp for messages about a bad binlog magic number or truncation during crash recovery. Inspect the tail of the binlog with SHOW BINLOG EVENTS IN 'file' FROM <pos> and look at whether the last GTID event before the crash has a matching commit marker. If a GTID appears in gtid_executed despite no commit marker at end of file, that's the corruption signature.

Step 4: check for an incompletely replicated transaction

On the affected replica, Relay_Log_File and Relay_Log_Pos from SHOW REPLICA STATUS show exactly where the SQL (applier) thread stopped. SHOW RELAYLOG EVENTS IN '<relay-log-file>' FROM <Relay_Log_Pos> shows the next event it was about to run when it crashed. Compare that GTID's row content on the replica against the same GTID's row content on the source; a mismatch there is incomplete-apply, not corruption.

Step 5: reconcile and validate

Do not let a replica that's failing on a specific GTID just keep retrying; it will fail on the same conflicting insert forever. Either fix the row manually to match the source's intent and mark exactly that one GTID as done (SET GTID_NEXT='<uuid:n>'; BEGIN; COMMIT;, a targeted, auditable skip, not a blunt skip-counter), or, once you don't trust that node's data, re-provision it from a fresh, verified source clone and let it catch up cleanly, usually the safer choice. Either way, validate afterward with an actual data checksum between source and every replica (for example Percona Toolkit's pt-table-checksum, or a manual per-range hash comparison), because GTID-set equality alone only proves the same set of transactions was applied, not that the resulting data is identical, exactly the gap a cloned-server_uuid bug hides inside.

Worked example

Real output from a MySQL 8.4.11 instance started with GTID mode on:

SELECT @@server_uuid;
-- c9bf8a93-b89e-11f1-824f-92bfb8bc99e6

SELECT @@gtid_executed;
-- c9bf8a93-b89e-11f1-824f-92bfb8bc99e6:1-10

(Note on the file names below: MySQL 8.4's default binlog base name, when `--log-bin` is enabled without an explicit basename, is `binlog`, not the older `mysql-bin` convention many pre-8.0 installs used; the names below reflect that current default. If your server's config sets an explicit basename, e.g. `log-bin=mysql-bin`, you'll see that name instead, same mechanics either way.)

SHOW BINARY LOGS;
-- binlog.000001 | 181
-- binlog.000002 | 2996534
-- binlog.000003 | 1495

SHOW BINLOG EVENTS IN 'binlog.000003' LIMIT 10; against real inserted rows produces the actual GTID event structure a forensic read would examine:

binlog.000003     4 Format_desc   ... Server ver: 8.4.11, Binlog ver: 4
binlog.000003   127 Previous_gtids ... c9bf8a93-b89e-11f1-824f-92bfb8bc99e6:1-6
binlog.000003   198 Gtid          ... SET @@SESSION.GTID_NEXT= 'c9bf8a93-...:7'
binlog.000003   275 Query         ... CREATE DATABASE IF NOT EXISTS troubleshoot
binlog.000003   412 Gtid          ... SET @@SESSION.GTID_NEXT= 'c9bf8a93-...:8'
binlog.000003   491 Query         ... CREATE TABLE accounts (...)
binlog.000003   763 Gtid          ... SET @@SESSION.GTID_NEXT= 'c9bf8a93-...:9'
binlog.000003   842 Query         ... BEGIN
binlog.000003   925 Table_map     ... table_id: 85 (troubleshoot.accounts)
binlog.000003   998 Write_rows    ... table_id: 85 flags: STMT_END_F

Each logical transaction gets exactly one Gtid event assigning it a GTID before its actual row-change events, and Previous_gtids records everything the log already knows about. That structure is exactly what step 3's inspection looks for: a transaction whose Write_rows event has no matching commit boundary at end of file is the corruption signature. SHOW REPLICA STATUS\G against a topology with an actual replica would additionally expose Retrieved_Gtid_Set, Executed_Gtid_Set, Relay_Log_File, and Relay_Log_Pos for steps 1 and 4; the standalone instance used here has no replica configured, so those columns aren't populated. For offline forensics on an archived binlog file rather than a live server, mysqlbinlog reads the same event stream from disk (it isn't bundled in this minimal container image, so it's described here rather than executed, but SHOW BINLOG EVENTS above returns the identical information from a running server).

Trade-offs and pitfalls

• Don't reach for a blunt skip-counter (skipping N binlog events) as a first response; it can skip past legitimate transactions that came after the one causing the error, making the replica diverge further instead of recovering.
• Re-provisioning a replica from the source feels slow under incident pressure but is often both faster and safer than manual row-level surgery once you can't be certain how far the divergence extends. Manual fixes are for when you've proven the scope is a single row or transaction.
• Checking only GTID-set equality and declaring the incident resolved is exactly the gap that lets a cloned-server_uuid bug hide; always independently checksum the actual data too.

Direct answer

The triage workflow is the same shape regardless of which managed provider's tool is in use, AWS Performance Insights, Google Cloud SQL Insights, or an equivalent: start from the alert, look at database load broken down by wait event to see where time is actually being spent rather than just that it's high, identify the specific SQL statement contributing most to that wait, then correlate the timing precisely with application-side traces and logs to confirm the database-side finding actually explains the user-visible symptom before committing to a fix.

Structured elaboration

Glossary

A wait event is a label the database engine attaches to a session describing what it's currently waiting on, a lock, a disk read, CPU scheduling, or a network round trip to the client, rather than just reporting that a query took some number of milliseconds. It tells you where inside that time the delay actually happened. Database load (what AWS's tooling calls it) roughly means average active sessions, the average number of sessions simultaneously doing work or waiting at any instant. A load of 1.0 means, on average, one session was active; a load consistently above the provisioned compute capacity means sessions are queuing behind each other, not just running individually slower.

Triage workflow, starting from an alert about increased latency

1. Open the load-over-time view for the alert's window and confirm load actually rose. This rules out the alert measuring something the tool doesn't corroborate, a real possibility if the alerting metric is measured differently, for example client-side including network time, than the database-side load metric.
2. Break the elevated load down by wait event. Most of these tools stack the load chart by category: is the extra load mostly CPU (genuine compute-bound work), I/O (waiting on storage), lock waits (contention), or client and network waits (the database itself is idle, waiting on the application to send its next statement, a strong signal the bottleneck isn't in the database at all)? This single breakdown usually narrows the investigation from "everything's possible" down to one or two categories immediately.
3. Drill into the top SQL for that window, filtered by the dominant wait-event category from step 2. The tool ranks statements by their actual contribution to load, identifying the specific query or small set of queries responsible, rather than a vague "the database is slow."
4. Correlate the timing precisely with application traces: pull the application's own request traces for the same window and confirm the specific slow database call in the trace matches the specific SQL statement from step 3, in rough duration and rough volume. This matters because a database-side finding that doesn't line up with the actual user-facing symptom means a real problem has been found, just not necessarily the one the alert was about, for example the top-load query might be an unrelated background batch job.
5. Identify and deploy a mitigation matched to the confirmed category and query: a lock-wait-dominated finding points at concurrency or transaction-scoping fixes, an I/O-dominated finding points at an index, a query-shape fix, or a resource-tier change, a CPU-dominated finding points at query efficiency or scaling compute. Choose based on what steps 2 through 4 actually showed, not a default "add an index" reflex.

This is a procedural workflow, not a symptom-specific one: the same five steps apply whether the underlying cause turns out to be a lock, a missing index, or an unrelated noisy-neighbor job. The real value of these managed tools is that they make steps 2 and 3 possible at all without shell or OS access to the instance.

Worked example

A concrete illustration of how the arithmetic of a wait-event breakdown is actually read, using clearly labeled, pinned illustrative numbers rather than a claimed live measurement:

cpu, io_wal, lock, other = 1.1, 3.8, 1.0, 0.3   # average active sessions, by wait category
total = cpu + io_wal + lock + other
print(round(total, 2), round(100 * io_wal / total, 1))

6.2 61.3

A total database load of 6.2 average active sessions during the alert window, with I/O wait tied to WAL (write-ahead log, the sequential durability log Postgres writes to before a transaction can be reported committed) sync accounting for 61.3% of it, immediately points the investigation toward a fsync- and WAL-bound bottleneck: transactions are stalling while they wait for their commit records to sync to disk. On a self-managed instance you'd confirm that directly against raw WAL statistics views, but on a managed instance the wait-event breakdown from the provider's own dashboard is the lens available instead, since shell access to run that raw view directly isn't available there.

Trade-offs and pitfalls

• Stopping at "top SQL by load" without the application-trace correlation step risks fixing the wrong query; the highest-load query in a window isn't always the one actually causing the user-visible symptom the alert was raised for, especially on a busy shared instance.
• A wait-event breakdown dominated by client or network wait gets mistaken for a database problem more often than it should. It usually means the application is the slow party, not sending its next statement promptly, for example doing unrelated work mid-transaction; don't spend effort tuning the database for a bottleneck that actually lives in the application tier.
• These tools sample and retain data at a specific granularity and retention window, which varies by provider and pricing tier. Confirm the tool actually captured detail for the exact alert window before trusting a "nothing unusual" reading; a too-coarse sampling interval can miss a genuine but brief spike entirely.

Direct answer

I confirm bloat (wasted space: dead row or index-entry versions that still occupy disk pages but no longer hold anything live) with direct measurement rather than inference from symptoms alone, using PostgreSQL's pgstattuple extension to see exactly how full the index's pages actually are, since "performance got worse" has several other possible causes and I don't want to rebuild the wrong thing. Once confirmed, REINDEX CONCURRENTLY is the default remediation, because it rebuilds the index without holding the exclusive lock a plain REINDEX or DROP/CREATE would, so normal reads and writes against the table keep working the whole time.

Structured elaboration

Why indexes bloat in the first place. PostgreSQL uses multi-version concurrency control (MVCC): an UPDATE doesn't modify a row in place, it writes a new row version and marks the old one dead, and the same happens to that row's index entries, a new one gets added, the old one becomes dead weight. VACUUM (run automatically by autovacuum, Postgres's built-in background process that watches tables and runs VACUUM on them for you on a schedule, or run manually) reclaims that dead space for reuse within the same table or index, but it doesn't shrink the file back down on disk in the common case; the freed space stays allocated to that index, available for future rows, but not returned to the filesystem. A workload with heavy UPDATE or DELETE traffic and infrequent or falling-behind autovacuum activity accumulates dead space faster than it's reclaimed, which is what bloat actually is: not corruption, just historical dead versions and freed-but-unreturned space diluting the live, useful data in the index's pages.

Confirming bloat, not just assuming it. pgstattuple (a PostgreSQL extension: CREATE EXTENSION pgstattuple;) gives direct, page-level measurement instead of relying on plan cost estimates or a hunch:

SELECT * FROM pgstattuple('idx_sessions_user_id');   -- physical stats: free space, dead tuples
SELECT * FROM pgstatindex('idx_sessions_user_id');   -- btree-specific: avg_leaf_density, leaf_fragmentation

pgstatindex's avg_leaf_density is the most direct bloat signal for a B-tree index (the tree-shaped structure Postgres uses for a normal index; a "leaf page" is one of the disk pages at the very bottom of that tree, the ones that actually hold the index entries pointing back at table rows, as opposed to the upper pages that just route a search down to the right leaf): it's the percentage of each leaf page actually occupied by live index entries, versus free space. A healthy, freshly-built index typically sits around 90%; a meaningfully bloated one drops well below that. leaf_fragmentation (how out-of-physical-order the leaf pages have become relative to their logical scan order) is a second, independent signal: a high value means even a full index scan is doing more random I/O than it should, on top of whatever the density number already shows.

Worked example

I reproduced real index bloat on a seeded PostgreSQL 16 table (200,000 rows, one index on a user_id column), then measured and remediated it, rather than describing the mechanism abstractly.

Baseline, right after building the index and running a single VACUUM:

avg_leaf_density: 90.73   leaf_fragmentation: 0   index_size: 1568 kB

I then disabled autovacuum on the table (ALTER TABLE sessions SET (autovacuum_enabled = false);, simulating a table falling behind its normal maintenance) and ran six full-table UPDATEs that each touched the indexed column, deliberately generating dead index entries with nothing reclaiming them:

avg_leaf_density: 83.27   leaf_fragmentation: 49.97   index_size: 12 MB

Real, measured bloat: the index grew from 1,568 KB to 12 MB, roughly 8x, on a table whose live row count never changed, and pgstattuple on the underlying table confirmed the mechanism directly: 200,072 dead tuples against 200,000 live ones (a dead-to-live ratio over 100%), with the table itself reporting 65.68% free space. That figure lands close to 1x, not the roughly 6x you might expect from six full-table updates with nothing reclaiming space in between: even with autovacuum disabled, Postgres still opportunistically prunes already-dead, no-longer-visible tuples out of a heap page whenever a later update on that same page needs room to write its next version, so each round's update tends to clear out the previous round's dead version as it goes, leaving only the most recent round's dead tuples still sitting there uncollected by the time VACUUM finally runs. I confirmed this directly by reproducing the same six-update sequence against a fresh PostgreSQL 16 instance and measured 200,043 dead tuples, the same roughly-1x result, not 6x. This is exactly the shape of evidence that confirms bloat as the cause rather than, say, a data-volume increase or a plan regression: the row count is unchanged, but the physical structure carrying those rows has ballooned.

Remediation: REINDEX INDEX CONCURRENTLY idx_sessions_user_id;. This completed cleanly with no error and no lock held against normal table access, and the measurements afterward confirmed a full recovery:

avg_leaf_density: 90.72   leaf_fragmentation: 0   index_size: 1568 kB

Back to within a rounding error of the original baseline, on all three measured dimensions, with the rebuild done live.

Rebuilding with minimal impact on live traffic: comparing the three real options.

Trade-offs and pitfalls

REINDEX CONCURRENTLY avoids the exclusive lock, but it isn't free: it requires roughly double the disk space during the rebuild (the old and new copies of the index briefly coexist), it takes noticeably longer wall-clock time than a plain REINDEX because it has to do extra work to stay concurrent-safe, and if it's interrupted partway through, it can leave behind an invalid index (visible via pg_index.indisvalid = false) that needs to be manually dropped rather than automatically retried, so check that flag after running it rather than assuming success from a lack of error output. pgstattuple's functions do a real, if usually brief, scan of the object being measured, so running them against a very large table or index during an active incident is itself a small additional load; that cost is worth paying to confirm the diagnosis before committing to a rebuild, but it's not literally free. The single biggest pitfall is skipping confirmation entirely and reindexing on a hunch: if the actual cause of the slowdown was a plan regression, a missing index elsewhere, or genuine data growth rather than bloat, a REINDEX changes nothing about the real problem and burns a maintenance window or a chunk of I/O budget for no benefit, which is exactly why measuring first with pgstattuple/pgstatindex matters more than jumping straight to the fix.

Direct answer

For a cluster this size, I track replication health at three levels at once: raw lag (both in time and in bytes, since they catch different failure modes), the primary's write pressure that's driving replication in the first place, and the replica's own capacity to keep up. I set alert thresholds using rolling windows and a warn/page split rather than a single static number, and I map every alert to a specific first action, because at this scale, a noisy alert that nobody trusts is worse than no alert at all.

Structured elaboration

Normalizing the workload numbers first. The question states reads in queries per second (100,000 QPS) and writes in a different unit, per minute (20,000 writes/minute). Before reasoning about capacity I put both on the same basis:
$20{,}000 \text{ writes/min} \div 60 = 333.3 \text{ writes/sec (TPS, transactions per second)}$
So the actual write rate the replication stream has to carry is roughly 333 transactions per second, a small fraction of the 100,000 QPS read rate, which matters directly: replication lag (how far behind a replica's applied data is from the primary's, measured either as elapsed time or as bytes of unreplayed write-ahead log) is driven by write volume and write-ahead log (WAL) generation rate, not by read volume at all, so this cluster's replication load is far lighter than its 100k QPS headline number might suggest at first glance.

Key metrics to track.

Automated versus operational responses. Automated responses are appropriate for reversible, low-risk actions: automatically routing read traffic away from a replica whose lag has crossed the page threshold (a circuit breaker, so stale reads don't silently reach users) is safe to automate, because routing back once lag recovers is equally safe and reversible. Operational (human-driven) responses are appropriate for anything that changes capacity or topology: resizing a replica, adding a new one, or resyncing from scratch, because those carry real cost and risk that a human should weigh, not a script.

False positives and cost trade-offs. A rolling 3-to-5-minute window on the time-based lag threshold absorbs normal, brief lag blips (a large single transaction landing on the primary will always cause a short lag spike on replay, that's expected, not a failure) without needing a human to look at every one. Tracking both time-based and byte-based lag costs almost nothing extra (both come from the same pg_stat_replication view) but catches different failure shapes: a replica that's fully stalled will show byte lag growing immediately while replay_lag's time-based measurement, which is computed from message round-trips, can take a little longer to reflect a total stall. The cost trade-off worth naming explicitly at this scale: an unnecessarily tight page threshold on a 100k QPS cluster, where normal replication lag naturally has more variance simply because there's more absolute traffic, will page on noise constantly and burn the team's trust in the alert; the thresholds above assume you've validated them against several weeks of this specific cluster's real baseline, not picked them from a generic template.

Worked example

At 333 write transactions per second (the normalized rate from above), if the primary generates roughly 1 KB of WAL per average transaction (a reasonable rough figure for a typical OLTP (online transaction processing: many small, individual read/write operations, as opposed to large analytical scans) row-level write; actual figures vary by workload and should be measured, not assumed), that's about 333 KB/sec of WAL the replication stream has to carry, or roughly 28 GB/day. If a replica's network path or disk write throughput can sustain, say, 50 MB/sec, that's nowhere near a bottleneck at 333 KB/sec, meaning under steady state this cluster's write volume alone is unlikely to be the cause of lag; a lag incident on this cluster is far more likely to be caused by a replica-side resource contention event (a competing read workload, or an underlying storage hiccup) than by the write rate simply outrunning capacity, which changes where I'd look first during an actual incident: replica health metrics before primary write-volume graphs.

Trade-offs and pitfalls

Alerting on lag alone, without also tracking the primary's write-volume trend, produces alerts that can't distinguish "the replica is broken" from "the primary is just busier than usual right now," which leads directly to the wrong first action (resizing a healthy replica because the primary had a legitimate traffic spike, or vice versa). Automating traffic failover (automatically redirecting read traffic away from a replica that's fallen behind, without a human approving each move) away from a lagged replica is safe only if there's enough remaining read capacity elsewhere to absorb the diverted traffic; doing this blindly on a cluster running close to capacity on all replicas can turn one replica's lag into a broader capacity incident. Tracking replication-slot retained WAL is easy to skip because it rarely matters, until the one time a replica silently disconnects and nobody notices the primary's disk filling up from retained WAL it can no longer discard, which is exactly the kind of slow-building, easy-to-miss failure a dedicated alert on that specific metric exists to catch.