DBA & Storage9 min read•September 2026•Kernelry Staff

Database Reliability, Replication & Failover Interview Scenarios

Stateful data storage remains the hardest component of production infrastructure to run reliably. Managing stateful systems requires understanding the mechanical reality of database engines: write-ahead logging (WAL), synchronous vs asynchronous replication lag, connection pool exhaustion, and zero-downtime schema migrations.

Architecture Blueprint

High-Availability Database Topology & Failover Flow

Visualizing client traffic flowing through PgBouncer connection pools, streaming WAL replication to replicas, and Patroni/etcd automated leader election.

Architecture Blueprint: Database Connection Pooling & Consensus FailoverHigh-Availability DB

The Core Concept

Production databases need high availability without single points of failure. SREs combine connection poolers (like PgBouncer) with streaming replication and failover consensus managers (like Patroni with etcd) to promote a standby replica automatically if the primary fails.

Key Rules of Thumb for Production Systems

•Connection poolers (PgBouncer) prevent process memory bloat: 10,000 app connections get multiplexed onto 50 real database backend connections.

•Asynchronous replication offers high write throughput but risks data loss during ungraceful failover; Synchronous replication guarantees zero data loss at the expense of write latency.

•Patroni uses etcd distributed consensus and Raft heartbeats to elect primary leaders, strictly preventing split-brain dual writes.

•Zero-downtime migrations require additive schema changes (add column, dual write, backfill, deprecate, drop). Never lock large tables with full ALTER TABLE rewrites.

Why Production Systems Demand This Depth

Stateless application pods are easy to replace, but databases store persistent company data. When a primary database fails, you need to prevent split-brain writes, minimize or eliminate data loss, and restore database availability as quickly as possible.

Common Diagnostic Pitfalls

•Allowing hundreds of microservice instances to open direct connections to PostgreSQL without a connection pooler (exhausting connection RAM).
•Failing to monitor replication lag, leading to applications reading stale data or severe WAL backlog filling the primary disk.
•Attempting automated failover without a distributed consensus quorum (etcd/Consul), causing split-brain where two nodes both accept writes.
•Running blocking schema migrations (e.g. adding a column with a default value on an old Postgres version) that locks the table for hours.

Best Practices for Scenario Drills

•Distinguish between logical replication and physical streaming replication (WAL).
•Explain how connection pooling modes work: Session pooling vs Transaction pooling in PgBouncer.
•Describe the 4-phase backward-compatible zero-downtime migration process for large databases.

Worked Production Scenarios

Study real-world outage patterns, diagnostic trees, and clarifying questions.

Triage and Mitigation of Critical PostgreSQL Replication Lag During Peak Traffic

Difficulty: senior

Scenario Prompt:

“During a high-traffic sales event, read replicas experience a 35-minute replication lag. Customers complain that newly placed orders do not appear on their profile dashboards. The primary database disk is at 91% capacity due to accumulated WAL segments. How do you triage and resolve this?”

Substantive Technical Concepts to Hit:

Root Cause Identification

Check pg_stat_replication on the primary and pg_stat_wal_receiver on replicas. Determine if the lag is network bandwidth bottleneck, long-running read queries holding locks on the replica (max_standby_streaming_delay), or I/O saturation.

Read Routing Workaround

Temporarily route critical read queries (like order confirmation lookups) to the primary database while leaving bulk analytics on replicas to alleviate user-facing inconsistencies.

WAL Segment Disk Pressure

If the primary disk fills up completely, PostgreSQL will crash. Ensure WAL archive storage (e.g. S3 WAL-G/pgBackRest) is healthy so old WAL can be safely pruned from primary storage.

Replica Concurrency Tuning

Increase max_parallel_maintenance_workers and inspect vacuum operations to prevent table bloat from slowing down WAL replaying.

Senior Clarifying Questions to Investigate:

Is the replication lag caused by long-running analytical queries blocking the replica replay process?
What is the replication slot status on the primary - are inactive slots holding back WAL segment cleanup?
Can we temporarily route critical read-your-own-writes traffic to the primary node?

Executing Zero-Downtime Large Table Schema Migrations in Production

Difficulty: senior

Scenario Prompt:

“You need to add a new indexed foreign key column to a 500-million row orders table in production. A standard ALTER TABLE and CREATE INDEX will lock the table for 45 minutes, causing a complete application outage. How do you execute this with zero downtime?”

Substantive Technical Concepts to Hit:

CREATE INDEX CONCURRENTLY

PostgreSQL allows CREATE INDEX CONCURRENTLY, which builds the index without taking an exclusive table write lock, allowing writes to continue uninterrupted.

Expand and Contract (Additive Migration)

Step 1: Add nullable column without constraints. Step 2: Deploy app version that dual-writes to both old and new columns. Step 3: Backfill historical rows in small batches. Step 4: Add NOT NULL / FK constraints with NOT VALID, then VALIDATE CONSTRAINT.

Lock Timeout Guards

Always set lock_timeout = '2s' in the migration transaction so that if a lock cannot be acquired immediately, it fails fast rather than queueing up and blocking all subsequent web traffic.

Batch Backfilling

Backfill old records in chunks of 5,000 rows with sleep intervals to avoid saturating disk I/O and creating massive replication lag.

Senior Clarifying Questions to Investigate:

What version of PostgreSQL is running, and does it support instant column addition with non-null defaults?
What is the current write transactions-per-second (TPS) rate on the orders table during off-peak hours?
Do we have tools like pg_repack or gh-ost / pt-online-schema-change available if table rewriting is unavoidable?

Interactive Scenario Drills

That's where we come in.

Explaining architectural trade-offs under time constraints is very different from reading about them.

Kernelry helps you rehearse technical questions, evidence-led debugging, safe next steps, and the clear communication teams expect. The drills adapt to your answer, so you practice the reasoning rather than memorize a company-specific script. During early access, free accounts include ten daily scenarios across our mixed question pool, with no credit card required.

Start Today's Free Drill

Back to All Blog Posts