Database Incident Communication — Database & SQL Exercises

1 / 5

During an active incident, the on-call engineer posts in the incident channel: "We are seeing 45-second replication lag on the read replica." What does this mean for read-heavy services currently pointing at that replica?

Replication lag vocabulary:

Option C is the correct operational impact. Replication lag is the delay between when a transaction commits on the primary and when that change is applied on the replica.

Term	Definition
Replication lag	Delay between primary commit and replica apply
Stale read	A read that returns outdated data because the replica has not yet applied recent writes
Read-after-write consistency	Guarantee that a user can always read their own recent write — broken under replication lag
Replica promotion	Elevating a replica to primary — during high lag, this risks losing recently committed data

Incident communication vocabulary for replication lag:

"We are routing all read traffic back to the primary to avoid stale reads until lag is resolved."
"The replica is currently 45 seconds behind — services that require read-after-write consistency should not query it."
"Lag has been stable at 45 seconds for 10 minutes — investigating whether this is WAL volume, network throughput, or replica I/O saturation."

Common causes to investigate: long-running transactions on primary, high WAL volume, replica I/O bottleneck, network link saturation between primary and replica.

2 / 5

The application logs show recurring deadlocks on the orders table. Which incident message most effectively communicates this situation to an on-call team that includes both engineers and a product manager?

Deadlock incident communication vocabulary:

Option B is the model incident message because it covers five elements: what is happening (deadlocks), why (lock acquisition order conflict), who is affected (product manager can understand "3–5% of checkout requests failing"), current state (being retried), and action plan (consistent lock ordering fix).

Deadlock vocabulary:

Term	Plain English definition
Deadlock	Two transactions each hold a lock the other needs — neither can proceed; the database kills one
Victim transaction	The transaction the database rolls back to resolve the deadlock
Lock acquisition order	The sequence in which a transaction requests locks — inconsistent order across transactions causes deadlocks
Deadlock retry	Application-level logic to re-execute a rolled-back transaction after a deadlock

Standard deadlock fix vocabulary for PRs: "The fix ensures both the checkout and inventory update transactions acquire the orders row lock before the inventory row lock, eliminating the circular wait condition."

3 / 5

An SRE reports: "The database connection pool is exhausted — all 100 connections are active, new queries are queuing, and response time is climbing." Which action is the correct immediate mitigation for connection pool exhaustion?

Connection pool exhaustion vocabulary and mitigation:

Option C is the correct immediate mitigation. Restarting the primary (Option A) causes additional downtime and risks data loss — it is never the first response. Increasing max_connections alone (Option B) does not free stuck slots and must account for PostgreSQL's per-connection memory overhead. Adding a read replica (Option D) does not help when write connections are exhausted.

Correct mitigation steps:

Action	Why
Kill idle/blocked connections (`pg_terminate_backend`)	Frees slots immediately without downtime
Reduce pool max_size in app config	Prevents the pool from over-requesting connections relative to the DB limit
Enable PgBouncer (connection pooler)	Multiplexes many app connections into fewer physical DB connections
Add `idle_in_transaction_session_timeout`	Auto-kills transactions idle in an open BEGIN — the most common exhaustion culprit

Key terms:

Connection pool — a cache of pre-established database connections shared across application threads
Pool exhaustion — all connections are active; new requests queue or fail with "too many connections"
PgBouncer / pgpool — middleware that multiplexes many logical connections into fewer physical database connections
idle in transaction — a connection holding an open BEGIN with no active query; holds locks and wastes a connection slot indefinitely

4 / 5

During a database incident, the primary server fails and the monitoring system automatically promotes the standby to primary in 12 seconds. The on-call engineer needs to communicate this to stakeholders. Which term correctly describes this process, and which message is most appropriate?

Database failover vocabulary:

Option D is correct on both counts. A failover is an automatic or manual response to a primary failure, in contrast to a switchover which is a planned, controlled role change.

Term	Triggered by	Data loss risk?
Failover	Primary failure (unplanned)	Possible if standby was lagging at failure time
Switchover	Planned maintenance (controlled)	No — standby is caught up before role swap
Promotion	The act of elevating a replica to primary	—
RTO (Recovery Time Objective)	Max acceptable time to restore service	—
RPO (Recovery Point Objective)	Max acceptable data loss (measured in time)	—

Why Option D's message is correct:

Names the event precisely: "unexpected failure" (vs. vague "went down")
Gives the exact time: "15:42 UTC" — critical for incident timeline reconstruction
States the current status clearly: "database is now operational"
Provides the next update time — prevents stakeholders from flooding the channel with "what's happening?"

5 / 5

After a database crash, a DBA says: "We need to check the WAL to determine the last committed transaction before the crash so we can assess any data loss." What is the WAL and why is it critical for database recovery?

WAL vocabulary and crash recovery:

Option C is the correct and precise definition. The Write-Ahead Log (WAL) is the foundation of database durability — the D in ACID.

WAL crash recovery process:

After a crash, PostgreSQL enters recovery mode at startup
It reads the WAL from the last checkpoint forward
Redo: committed transactions whose data pages were not yet written to disk are replayed and applied
Undo: partial (uncommitted) transactions are rolled back
The database reaches a consistent state and opens for connections

Term	Definition
WAL (Write-Ahead Log)	Append-only log of all changes, written before data pages — ensures durability
Checkpoint	A point where all dirty pages up to that LSN are guaranteed written to disk; limits WAL replay on recovery
LSN (Log Sequence Number)	Position in the WAL stream — used to identify exactly where replication or recovery is up to
WAL archiving	Saving WAL segments to external storage for point-in-time recovery (PITR)
PITR (Point-in-Time Recovery)	Restoring the database to an exact moment using a base backup + WAL segments