⚡ ~/naveed Interview Prep
⚡ Portfolio Home 👤 About Naveed Bio & Credentials 📫 Contact Naveed Get In Touch ✍️ Engineering Blog Deep Dives 🎯 Interview Hub 1,000+ Scenarios ☸️ Kubernetes Mastery Hub 24 Modules 🎮 DevOps Arcade & Quizzes Subnet Blitz ⚡ 🗺️ DevOps Roadmaps PDFs & Guides 🤖 Morpheus Analysis AI Quant ↗ 🛠️ Developer Tools Utilities 🧪 Labs & Experiments 📄 Interactive CV & Certs 🔗 All Links & Socials ⚡ Join The Dispatch (Weekly SRE Newsletter) →
← Back to All Databases & Storage Interview Questions Scenario 47 of 54 in Databases & Storage
Senior SRE / Platform Engineer Database Replication Lag & Failover High Availability
🎯 Target Role / Context: Senior SRE / Platform Engineer Interview Scenario

Q: An analytics team runs a 45-minute analytical SQL query on a PostgreSQL streaming read replica. Suddenly, replication lag on the replica spikes to 3.5 GB, causing the replica to serve stale data to user-facing APIs. Shortly after, the analytics query is abruptly terminated with 'ERROR: canceling statement due to conflict with recovery'. How do you diagnose replication lag vs query conflicts and configure hot_standby_feedback and max_standby_streaming_delay?

Triage replication lag spikes on streaming read replicas caused by long-running analytical queries and configure hot_standby_feedback without bloating primary database tables.

#PostgreSQL #Replication Lag #Read Replica #WAL Replay #hot_standby_feedback #SRE
🎙️ Candidate Opening & Architectural Context
"PostgreSQL streaming replication applies Write-Ahead Logs (WAL) sequentially on read replicas. When the primary database executes a VACUUM that removes dead rows, the WAL records the deletion. If a read replica is currently executing an analytical query that still needs those rows according to MVCC rules, a recovery conflict arises. The replica must either pause WAL replay (causing replication lag) or cancel the running query."
Advertisement
⚡ Recommended Practice Lab

Want to master this scenario in a live sandbox? KodeKloud's PostgreSQL Database Administration & High Availability Course covers this exact problem with hands-on terminal drills.

🛠️ Production Runbook & Step-by-Step Resolution

Direct Answer: PostgreSQL streaming replication applies Write-Ahead Logs (WAL) sequentially on read replicas. When the primary database executes a VACUUM that removes dead rows, the WAL records the deletion. If a read replica is currently executing an analytical query that still needs those rows according to MVCC rules, a recovery conflict arises. The replica must either pause WAL replay (causing replication lag) or cancel the running query.

⚡

Situation: Replica Lag Surges and Analytical Queries Crash

⚡

Task: Reconcile WAL Replay Speed with Analytical Query Execution

Advertisement
⚡

Action: Configure Feedback Signals & Dedicated Analytics Replicas

⚡

Result: Zero Recovery Cancellations & Under-100ms API Lag

💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Replication replay conflicts occur when WAL cleanup on the primary conflicts with active snapshots on the replica. Enable hot_standby_feedback cautiously, and always isolate high-throughput client read traffic from heavy analytical workloads."
⚡ 60-Second Elevator Pitch Talking Points
  • Replication lag on replicas is often caused by paused WAL replay waiting for long queries to release rows.
  • Replay conflicts trigger query cancellations when max_standby_streaming_delay is exceeded.
  • Separate read-only replica pools for short API reads versus long BI queries to avoid mutual interference.
⛔ Critical Candidate Mistakes to Avoid in This Scenario

Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:

  1. Applying exclusive-lock DDL table migrations on live production databases during peak traffic windows when configuring PostgreSQL Streaming Replication DDL migrations that acquire an exclusive table lock block all concurrent queries, queuing connections until the database connection pool exhausts.
  2. Allowing high-frequency queries to trigger full table scans without appropriate composite indexing when configuring PostgreSQL Streaming Replication Sequential table scans saturate database CPU and disk I/O, evicting frequently queried data from the buffer cache and causing cascading timeouts.
  3. Disabling referential integrity constraints during bulk ETL imports without post-import consistency validation when configuring PostgreSQL Streaming Replication Skipping relational consistency checks risks introducing orphaned records and referential integrity violations into production tables.
  4. Relying on database snapshot backups that have never been verified through automated restoration drills when configuring PostgreSQL Streaming Replication Backups that have never been tested with automated restore verification frequently fail during real disaster recovery scenarios.
Advertisement
Want more Databases & Storage scenarios?
Explore our complete collection of scenario-based Databases & Storage interview runbooks.
Browse All Databases & Storage Questions →