PostgreSQL Streaming Replication, WAL & Standby Patterns for HA

PostgreSQL Streaming Replication, WAL & Standby Patterns for HA

postgres replication is the single most under-explained primitive in the modern data stack — every team runs it, almost no one can sketch the WAL → wal_sender → wal_receiver pipeline on a whiteboard, and the moment an interviewer asks "what happens to an in-flight transaction when synchronous_commit = remote_apply and the standby goes away?" the room goes quiet. Postgres ships one of the cleanest replication stories in any open-source database, but the configuration surface is wide, the failure modes are subtle, and the interview probes are exactly where the trade-offs live. If you cannot say, in one sentence, why postgres streaming replication tails the write-ahead log instead of the data files, you are guaranteed to be slow on every Postgres HA question that follows.

This guide is the senior-DE walkthrough you wished existed the first time you stood up a primary plus two replicas and an interviewer asked "how would your pipeline survive a primary loss in under 30 seconds?" It walks through the durability primitive (postgres wal, the write-ahead log that every commit hits before any data page), the streaming mechanics (pg_basebackup cloning, wal_sender / wal_receiver streaming connections, wal_level, max_wal_senders, wal_keep_size), the four-level durability ladder (synchronous replication modes off / on / remote_write / remote_apply plus quorum sync since PG10), the hot standby read-scale story (replication conflicts, hot_standby_feedback, max_standby_streaming_delay), and the failover layer that ties it all together (patroni with etcd / Consul leader election, walg archive to S3, STONITH fencing, postgres failover in seconds rather than minutes, and the postgres ha deployment shape senior interviewers actually expect to hear). Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library → for the schema + transaction probes that touch replication, rehearse on ETL practice problems → for the cross-system durability patterns that show up around Postgres CDC, and sharpen the query-tuning axis with the optimization library → for the index-bloat + vacuum probes that surface in any hot-standby discussion.

---

On this page

• Why streaming replication + WAL is the HA foundation
• WAL + streaming replication mechanics
• Sync vs async replication
• Hot standby + read scale
• Failover + Patroni + WAL-G
• Cheat sheet — Postgres HA recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why streaming replication + WAL is the HA foundation

Postgres durability is the WAL, replication is just "tail the WAL" — every HA story is a consequence of that one design choice

The one-sentence invariant: every modification in Postgres writes a WAL record before any data page is touched, and replication is simply other servers tailing that WAL stream. Once you internalise that single fact, the entire postgres ha interview surface collapses to a sequence of consequences. Standbys are not "the database streamed" — they are "the log of changes streamed," replayed on the standby's data files. PITR is not a magic restore button — it is "replay WAL from a base backup up to a target time." Failover is not "switch the IP" — it is "promote a standby that has replayed the WAL up to a known point and stop the dead primary from accidentally accepting writes."

Four axes that interviewers actually probe.

• Sync mode. synchronous_commit controls how much durability you wait for at commit time. off returns immediately (no WAL fsync), on (default) waits for local fsync, remote_write waits for at least one standby's OS receive, remote_apply waits for at least one standby to have replayed the change. Each step up is a step in latency and a step in RPO.
• Hot standby. A standby in hot_standby = on mode accepts read-only queries while it replays WAL. The trade-off: a long-running analytics query on the standby can block WAL apply, lagging the standby. hot_standby_feedback = on makes the primary delay vacuum to protect the standby — at the cost of slower bloat reclaim on the primary.
• Failover. When the primary dies, something must promote a standby. Manual pg_ctl promote works but takes humans in the loop. Patroni, Stolon, and pg_auto_failover automate this via distributed consensus (etcd / Consul / Kubernetes leases). The target RTO is 10–30 seconds for a well-tuned cluster.
• PITR + DR. Streaming replication alone does not protect against logical corruption (an accidental DROP TABLE propagates to every standby in seconds). A WAL archive — typically WAL-G to S3 — plus a base backup gives you the point-in-time recovery story you need for "restore to 12:00 yesterday."

Why WAL is the durable truth.

• Every transaction writes WAL records to the in-memory WAL buffer and then to disk at commit (or sooner, per wal_writer_delay). The data pages are written later, asynchronously, by the checkpointer and background writer.
• On crash recovery, Postgres replays WAL from the last checkpoint LSN forward to bring the data files back to a consistent state. That same replay is what a standby does continuously.
• WAL is therefore the source of truth for durability. Data files are a derived materialisation; WAL is the canonical log.

The 2026 reality — what changed since 2022.

• PostgreSQL 16 / 17 added logical replication failover-safety (slots that follow physical failover), parallel WAL apply on standbys, and dramatic improvements in pg_basebackup performance via server-side compression.
• Patroni 3.x has become the de-facto standard HA stack for Postgres in Kubernetes — Zalando, Stackgres, and CrunchyData PG Operator all ship Patroni under the hood.
• WAL-G has largely replaced pgBackRest for new clusters in 2026: faster parallel WAL push, native S3 / GCS / Azure support, delta backups via LSN, and built-in encryption.
• Disaggregated storage — Aurora / AlloyDB / Crunchy Bridge externalise WAL to a shared log layer; the open-source Postgres analogue is still streaming replication, and that is what 95% of teams run.

What interviewers listen for.

• Do you say "WAL is the durable truth; replication tails it" in the first sentence? — senior signal.
• Do you separate synchronous_commit levels as a spectrum not a binary? — senior signal.
• Do you mention hot_standby_feedback as a vacuum-vs-query trade-off unprompted? — senior signal.
• Do you push back on "just turn on synchronous_commit" with "depends on RPO vs latency targets"? — senior signal.

Worked example — the WAL → standby pipeline end-to-end

Detailed explanation. The classic whiteboard question — "draw the path of a single UPDATE statement from the application to the standby." Most candidates draw the application talking to Postgres and a standby magically receiving rows. Senior candidates draw eight components: client → backend → WAL buffer → WAL file → wal_sender → network → wal_receiver → standby data files. Every Postgres HA decision lives in that pipeline.

Question. Trace the journey of one UPDATE orders SET status='paid' WHERE id=42 from the application all the way to a hot standby that another reader can query. Identify which configuration parameter controls each step.

Input.

step	actor	controls
1	application	BEGIN; UPDATE …; COMMIT;
2	backend	writes WAL record to WAL buffer
3	WAL writer	flushes to disk, controlled by synchronous_commit
4	wal_sender	streams WAL to standby, controlled by wal_level, max_wal_senders
5	wal_receiver	writes WAL to standby disk
6	startup process	replays WAL into data files
7	reader on standby	sees the change once apply LSN ≥ commit LSN

Code.

-- Primary (postgresql.conf)
wal_level                = replica          -- WAL records carry enough info for replication
max_wal_senders          = 10               -- up to 10 standbys can stream
wal_keep_size            = '1GB'            -- retain at least 1GB of WAL for slow standbys
synchronous_commit       = on               -- default: wait for LOCAL fsync only
archive_mode             = on
archive_command          = 'wal-g wal-push %p'

-- Standby (postgresql.conf)
hot_standby              = on               -- accept read-only queries while replaying
primary_conninfo         = 'host=primary.db user=replicator application_name=standby1'
primary_slot_name        = 'standby1_slot'  -- physical replication slot

-- Standby (standby.signal) — empty file triggers standby mode at startup

Step-by-step explanation.

1. Client → backend. The application sends UPDATE orders SET status='paid' WHERE id=42; over libpq. A backend process binds to the session and locks the row.
2. Backend → WAL buffer. The backend constructs a WAL record (XLOG_HEAP_UPDATE, contains old + new tuple positions and the new tuple bytes) and appends it to the shared WAL buffer in memory. The data page is dirtied in shared_buffers but not flushed.
3. WAL buffer → WAL file. At COMMIT, the backend signals the WAL writer to flush the buffer up through the commit LSN. synchronous_commit = on (default) makes the backend wait for fsync on the WAL file. synchronous_commit = off returns immediately, leaving up to wal_writer_delay (200 ms default) of writes unflushed.
4. wal_sender → wire. A dedicated wal_sender process per standby reads WAL files (or the in-memory buffer for warm catch-up) and pushes new records over the replication connection. wal_level = replica is the minimum; logical adds row-level decoding metadata.
5. wal_receiver → standby disk. On the standby, the wal_receiver process reads incoming WAL records and writes them to the standby's pg_wal directory. synchronous_commit = remote_write waits for this step to ack before the primary's commit returns.
6. startup → data files. The standby's startup process replays the WAL records into shared_buffers / data files. synchronous_commit = remote_apply waits for this step. The standby is now logically caught up.
7. Reader on standby. Any read-only session with hot_standby = on can now see status='paid' on row 42. The visibility lag from primary commit to standby visibility is 0 ms (remote_apply) to several seconds (default async).

Output.

LSN	primary	standby (async)	standby (remote_apply)
commit LSN written	t=0 ms	t=0 ms (not yet visible)	t=0 ms (still waiting)
WAL on standby disk	—	t=15 ms	t=15 ms
Replayed on standby	—	t=25 ms	primary commit returns at t=25 ms
Reader sees row	t=0 ms (primary read)	t=25 ms (standby)	t=25 ms (standby, but commit blocked until now)

Rule of thumb. Async replication (synchronous_commit = on, the default) gives you ~10–50 ms lag for free. Sync replication (remote_apply) gives you 0 RPO but adds the round-trip to your commit latency. If your interviewer asks "is replication lag a Postgres bug?" — no, it is a design parameter you control with synchronous_commit.

Worked example — why every change goes through WAL first

Detailed explanation. A common interview probe — "why does Postgres write WAL before the data page? Couldn't you just write the data page and skip the log?" The answer is the most fundamental concept in any log-structured database: WAL ensures crash recovery is bounded by the WAL since the last checkpoint, not by the entire database size.

Question. Explain why a single UPDATE writes WAL before touching the data file, what would go wrong if WAL did not exist, and how this enables replication for free.

Input.

scenario	what gets written
With WAL (Postgres default)	WAL record first, dirty page in shared_buffers, page flushed later at checkpoint
Without WAL (hypothetical)	Data page direct to disk on every commit
Crash mid-update with WAL	Replay WAL from last checkpoint LSN forward
Crash mid-update without WAL	Torn page on disk; unrecoverable without per-page fsync

Code.

-- Inspect WAL behaviour
SHOW wal_level;                              -- replica (default in PG14+) or logical
SHOW fsync;                                  -- on (do not turn off in production)
SHOW synchronous_commit;                     -- on (default)
SHOW checkpoint_timeout;                     -- 5min (default)
SHOW max_wal_size;                           -- 1GB (default; triggers checkpoint earlier than timeout)

-- See current WAL position
SELECT pg_current_wal_lsn();                 -- e.g. 0/16B8E40 (LSN = log sequence number)

-- See WAL files on disk
SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;

Step-by-step explanation.

1. Every change (INSERT, UPDATE, DELETE, DDL, vacuum, hint bit flip) writes a WAL record describing the change. The record contains the LSN, the page, the bytes to change, and metadata for replay.
2. The corresponding data page is updated in shared_buffers but not flushed to disk. The page is now "dirty."
3. At commit time, the backend forces an fsync on the WAL file up to the commit's LSN. This guarantees the change is durable on disk as a WAL record, even though the data page is still in memory.
4. Periodically (every checkpoint_timeout or when WAL exceeds max_wal_size), the checkpointer flushes all dirty pages to disk and records a CHECKPOINT marker in WAL. After a successful checkpoint, WAL before the marker is no longer needed for crash recovery.
5. On crash, the recovery process reads the last checkpoint LSN and replays WAL forward from there. Recovery time is bounded by checkpoint_timeout (typically 5 min) — not by database size.
6. The "free" benefit: because WAL fully describes every change, a standby just replays the WAL stream. No separate log, no separate format. Replication is a byproduct of the durability design.

Output.

Property	With WAL	Without WAL
Crash recovery time	Bounded by checkpoint interval (~5 min)	Unbounded (must verify every page)
Replication primitive	Free (stream WAL to standby)	Custom log required
PITR	Free (archive WAL + replay to target LSN/time)	Not possible
Torn-page protection	full_page_writes = on writes full page at first dirty after checkpoint	Per-page fsync needed
Cost	One sequential write per commit	Random writes everywhere

Rule of thumb. WAL is not "an optimisation Postgres added for replication" — it is the foundation of durability. Replication, PITR, and crash recovery are all consequences of having a sequential log of changes. If you understand WAL, you understand 80% of Postgres HA.

Worked example — the four "must-answer" axes in a 30-second answer

Detailed explanation. Senior interviewers often open with: "talk me through how you'd architect Postgres HA for a payment system." The candidates who ramble lose; the candidates who hit the four axes (sync mode, hot standby, failover, PITR) in 30 seconds win. This is the elevator-pitch shape every senior DE should be able to deliver cold.

Question. Deliver the 30-second elevator pitch for Postgres HA architecture. Cover sync mode, hot standby, failover, and PITR with one decision per axis.

Input.

Axis	Decision	Reason
Sync mode	remote_apply to one in-region standby	0 RPO for the local DC
Hot standby	Two read replicas, hot_standby_feedback=on for analytics one	Read scale + protect long analytics queries
Failover	Patroni + etcd, RTO target 30s	Automated, no humans
PITR	WAL-G to S3, daily full + continuous WAL	1 minute RPO for accidental DELETE

Code.

# Conceptual deployment shape — 4 nodes
nodes:
  - role: primary
    sync: synchronous_standby_names = 'ANY 1 (standby_local, standby_remote)'
  - role: standby_local            # same DC, sync replica
    sync: receives remote_apply ack
  - role: standby_analytics        # same region, async, hot_standby_feedback=on
    sync: hot_standby
  - role: standby_dr               # remote region, async
    sync: hot_standby

archive:
  tool: wal-g
  destination: s3://payments-wal-archive/
  schedule: daily base backup + continuous WAL push

orchestration:
  tool: patroni
  consensus: etcd cluster, 3 nodes across AZs
  rto_target: 30s

Step-by-step explanation.

1. Sync mode. synchronous_standby_names = 'ANY 1 (standby_local, standby_remote)' means at least one of the two named standbys must ack each commit. Combined with synchronous_commit = remote_apply, this gives 0 RPO across the local DC.
2. Hot standby. Both read replicas accept SELECTs. The analytics one runs with hot_standby_feedback = on so a 30-minute report does not get cancelled by a vacuum on the primary.
3. Failover. Patroni watches the primary via etcd leases. If the primary dies (lease not renewed in ttl seconds, default 30s), Patroni picks the standby with the highest replay LSN, runs pg_ctl promote, and updates the etcd key so clients reconnect via the new primary endpoint.
4. PITR. WAL-G pushes every WAL segment to S3 continuously plus runs a daily base backup. Accidental DROP TABLE at 11:42? Restore the daily base backup, then recovery_target_time = '2026-06-22 11:41:00'. Recovery is bounded by the size of WAL since the last base backup.

Output.

Failure mode	What protects against it	RPO	RTO
Primary crash, local DC alive	sync standby + Patroni promote	0	30s
Local DC outage	async DR standby + manual promote	seconds	5 min
Accidental DROP / UPDATE	WAL-G PITR	1 min	30 min – 2 hr
Region-wide outage	DR standby in another region	seconds	depends on DNS / app re-pointing

Rule of thumb. A senior Postgres HA answer covers four axes in 30 seconds. Sync mode = your RPO decision. Hot standby = your read scale decision. Failover = your RTO decision. PITR = your "human / corruption / regulator" decision. Miss any one and the architecture has a hole.

Senior interview question on Postgres HA design

A senior interviewer often opens with: "design the Postgres HA topology for a financial ledger service that handles 5K TPS, must lose no committed transactions on a primary failure, has a 30-second RTO target, and must survive an entire-region outage. Walk me through the trade-offs."

Solution Using a 4-axis decision framework

Axis-by-axis answer — financial ledger HA design

1. Sync mode    → synchronous_commit = remote_apply
                  synchronous_standby_names = 'ANY 1 (sa, sb)'
                  Local-DC quorum sync; commit blocks until one standby applies.

2. Hot standby  → standby_local (sync), standby_analytics (async, feedback=on)
                  Read scale, plus a heavy-analytics replica with vacuum delay.

3. Failover     → Patroni + etcd, ttl=30s, loop_wait=10s
                  Automated leader election; STONITH via cloud API isolating
                  the dead primary's NIC.

4. PITR + DR    → WAL-G to S3 (cross-region), daily base backup
                  DR standby in region B, async, manual promote on full-region loss.

Step-by-step trace.

Failure scenario	Detection	Recovery action	RPO	RTO
Primary OS crash	etcd lease lost in 30s	Patroni promotes standby_local	0 (sync ack received)	< 30s
Primary disk loss	etcd lease lost; standby_local has all committed WAL	Patroni promotes standby_local	0	< 30s
Local DC network partition	etcd quorum lost on dead side	STONITH fences old primary; new primary promoted	0	30–60s
Region A total outage	Operator paged	Manual promote of standby_dr in region B	seconds	5–15 min (DNS)
Accidental DROP TABLE at 11:42	App errors	WAL-G restore to 11:41:30	30s	30 min – 2 hr

After this 4-axis pass, the architecture is documented and every failure mode has a numbered runbook step. The remaining 5% — non-Postgres concerns like DNS, app retry policy, observability — defaults to the team's existing platform.

Output:

Component	Choice	Why
Primary + sync standby	remote_apply, ANY 1 quorum	0 RPO, only 1 standby must ack
Analytics replica	hot_standby_feedback = on	Protect long reports from vacuum
Failover orchestrator	Patroni + etcd, 30s ttl	Automated, distributed consensus
WAL archive	WAL-G to S3 cross-region	PITR + DR in one tool
DR standby	Async, region B	Survives entire-region outage

Why this works — concept by concept:

• Sync mode is your RPO knob — synchronous_commit = remote_apply is the only setting that gives true 0 RPO. on (the default) is async; remote_write waits for OS-receive on the standby but the standby could still die before applying.
• Quorum sync (ANY 1) — since PG10, synchronous_standby_names = 'ANY 1 (a, b)' means "any one of a or b must ack." This avoids the single-point-of-failure problem of FIRST 1 while keeping latency bounded by the fastest standby.
• Patroni + etcd — distributed consensus is the only way to safely automate failover. A standby promoting itself based on local heartbeat alone risks split-brain.
• hot_standby_feedback — a long-running analytics query on the standby reads tuples that the primary's vacuum might want to remove. hot_standby_feedback = on makes the standby tell the primary "do not vacuum tuples older than X yet." Trade-off: primary bloat grows.
• WAL-G to cross-region S3 — gives you PITR (replay WAL to a point in time) and DR (replay WAL into a region B standby) in one tool. The off-cluster archive is the only protection against a logical bug that propagates to every standby.
• Cost — one extra sync standby = ~5–15 ms added commit latency. One async DR standby = ~0 commit cost but ~1 standby's worth of cloud spend. The combined cost is roughly 3× a single-primary deployment for full HA + DR.

SQL
Topic — SQL
Postgres HA & transaction design problems

Practice →

ETL Topic — ETL Replication & CDC pipeline problems

Practice →

---

2. WAL + streaming replication mechanics

postgres wal is the durable log; wal_sender + wal_receiver ship it to standbys — every Postgres HA primitive sits on this pipeline

The mental model in one line: Postgres writes every change to WAL first, the wal_sender process tails WAL into a replication connection, the standby's wal_receiver writes incoming WAL to disk, and the standby's startup process replays it into the data files. Once you see those four processes — backend → wal_sender → wal_receiver → startup — every postgres streaming replication question becomes a question about where in that pipeline a failure or a config knob applies.

The four core processes.

• Backend. The per-connection process that executes SQL on the primary. Writes WAL records to the shared WAL buffer at every change; signals the WAL writer to flush at commit time (subject to synchronous_commit).
• wal_sender. One per replication connection on the primary. Reads WAL from buffer (warm) or files (cold catch-up), streams via the replication protocol to each standby. Bounded by max_wal_senders (default 10).
• wal_receiver. One per replication connection on the standby. Reads WAL from the network and writes to pg_wal/ on the standby's disk. Acks reception (LSN-write) and apply (LSN-apply) back to the primary; the primary uses these to honour synchronous_commit = remote_write / remote_apply.
• Startup process (recovery). On the standby, replays WAL records into shared_buffers / data files. Same code path as crash recovery — Postgres reuses it for streaming. Always runs while the standby is in recovery mode.

The role of replication slots.

• A physical replication slot is a named pointer on the primary that tracks "the lowest LSN this standby still needs." The primary will not delete WAL beyond a slot's LSN, even under WAL-recycling pressure.
• Without a slot, a slow standby can fall off wal_keep_size and need a fresh pg_basebackup to catch up. With a slot, the primary keeps WAL forever (or until you drop the slot) — at the cost of unbounded disk if the standby is dead and the slot is forgotten.
• Rule: every standby in production should use a named physical slot, and your monitoring should alert on pg_replication_slots.wal_status = 'unreserved' or pg_wal growth above a threshold.

pg_basebackup — cloning the primary.

• The bootstrap step for every new standby: a consistent file-level copy of the primary's data directory at a specific LSN, streamed over the replication protocol.
• Equivalent to "tar the data directory while writing all in-flight WAL into the tarball" — the result is a directory you can start as a standby with standby.signal.
• PG13+ supports server-side compression (gzip / lz4 / zstd), parallel base-backup workers, and a manifest file for integrity verification.

The key config knobs (primary side).

• wal_level — replica (default) is enough for physical replication; logical adds row-level decoding metadata for logical replication and CDC tools (Debezium).
• max_wal_senders — how many simultaneous streaming connections you allow. Set to (# standbys) + headroom (typically +2 for ad-hoc base backups).
• wal_keep_size — minimum WAL retained on the primary regardless of checkpoint pressure. Useful when you don't want to use replication slots; typical: 1–10 GB.
• archive_mode + archive_command — when on, every completed WAL segment is shipped to an off-cluster archive (typically WAL-G to S3). This is the foundation of PITR + DR.

The key config knobs (standby side).

• primary_conninfo — libpq connection string to the primary. Includes application_name (used to match synchronous_standby_names) and credentials.
• primary_slot_name — the named slot the standby uses. Prefer this over wal_keep_size for any production standby.
• hot_standby — on lets the standby accept read-only queries (covered in section 4).
• recovery_target_* — for PITR scenarios; specifies a target LSN, time, or named transaction to recover up to.

Common interview probes on the mechanics.

• "What's the difference between wal_level = replica and wal_level = logical?" — logical adds extra info for row-level decoding; physical replication only needs replica.
• "What happens if a standby falls behind beyond wal_keep_size?" — primary recycles the WAL, standby errors at restart, needs a fresh base backup. Replication slots prevent this at the cost of unbounded WAL on the primary.
• "Why use a replication slot?" — guarantees the primary keeps the WAL the standby still needs, even after recycling pressure.
• "What does pg_basebackup -X stream do?" — streams WAL concurrently with the data copy so the resulting backup is consistent up to the end-of-backup LSN.

Worked example — setting up a standby with pg_basebackup in 4 commands

Detailed explanation. Every Postgres HA setup begins with cloning the primary. pg_basebackup is the official tool — it speaks the replication protocol, ships data files plus in-flight WAL, and leaves you a directory that can start as a standby with a single signal file. The 4-command recipe below is the literal sequence senior DEs type, and the one most interviewers expect to hear cited.

Question. Show the exact 4 commands to bring up a new physical replica streaming from primary.db to a new host. Cover the replication slot, the base backup, the standby config, and the start command.

Input.

field	value
Primary host	primary.db
New standby host	standby1.db
Replication user	replicator
Slot name	standby1_slot
PG_DATA dir	/var/lib/postgresql/16/main

Code.

# 1. On the primary — create the replication slot ahead of time
psql -h primary.db -U postgres -c \
  "SELECT pg_create_physical_replication_slot('standby1_slot');"

# 2. On the standby — clone the primary (slot-based, with server-side compression)
PGPASSWORD=secret pg_basebackup \
  -h primary.db -U replicator -D /var/lib/postgresql/16/main \
  -X stream -S standby1_slot \
  --compress=server-zstd:9 -P -v -R

# 3. On the standby — verify the auto-generated standby config
cat /var/lib/postgresql/16/main/postgresql.auto.conf
# primary_conninfo = 'host=primary.db user=replicator application_name=standby1 ...'
# primary_slot_name = 'standby1_slot'
ls /var/lib/postgresql/16/main/standby.signal   # empty file present → standby mode

# 4. Start the standby — it will connect, stream WAL, replay, then accept reads
sudo systemctl start postgresql@16-main
psql -h standby1.db -U postgres -c "SELECT pg_is_in_recovery();"
# expected: t  (true — we're a streaming standby)

Step-by-step explanation.

1. Create the slot first. Creating it before the base backup means the primary retains all WAL from the slot's creation onward — so the new standby never falls off the back of the WAL window during the base backup itself.
2. pg_basebackup -X stream -S slot -R. -X stream opens a second replication connection that streams in-flight WAL concurrently with the data copy, so the backup is consistent at end-of-backup LSN. -S tells the in-flight stream to use the named slot. -R writes postgresql.auto.conf with primary_conninfo and primary_slot_name plus creates the standby.signal file.
3. Inspect. postgresql.auto.conf ends up with the replication connection string and slot name; the standby.signal empty file tells PG to start in standby mode rather than promote to primary.
4. Start. On boot, the standby connects to the primary via primary_conninfo, requests streaming from the slot's LSN, replays the in-flight WAL captured during base backup, then begins live streaming. pg_is_in_recovery() = true confirms standby mode.

Output.

Check	Expected
pg_is_in_recovery() on standby	t
pg_stat_replication on primary	one row, application_name=standby1, state=streaming
pg_replication_slots on primary	standby1_slot, active=t, wal_status=reserved
pg_last_wal_receive_lsn() on standby	non-null, increasing
pg_last_wal_replay_lsn() on standby	non-null, ≤ receive LSN

Rule of thumb. Always use a named slot for production standbys; always pass -X stream to pg_basebackup so the backup is self-consistent; always inspect pg_stat_replication on the primary before declaring the standby healthy. If state is not streaming, you have a TLS, auth, or connectivity problem to fix before going further.

Worked example — replication slot vs wal_keep_size

Detailed explanation. Interviewers love this probe — "you have a standby that goes offline for 6 hours. When it comes back, the primary has recycled WAL. What happens?" The answer depends entirely on whether the standby uses a replication slot or relies on wal_keep_size. Knowing the trade-off cold is a senior signal.

Question. Compare the behaviour of a 6-hour-offline standby (a) without a slot and wal_keep_size = 1GB, and (b) with a named slot. Show the failure mode in (a) and the protection in (b), plus how to monitor (b) so the slot doesn't fill the primary's disk.

Input.

scenario	primary	standby
(a) wal_keep_size = 1GB, no slot	Recycles WAL beyond 1 GB	Offline 6 hr; 30 GB of WAL generated meanwhile
(b) named slot, no wal_keep_size	Retains all WAL since slot LSN	Offline 6 hr; primary keeps 30 GB of WAL until standby is back

Code.

-- (a) WITHOUT a slot — primary recycles when WAL > wal_keep_size
SHOW wal_keep_size;   -- '1GB'
-- After 6 hr offline, WAL needed is 30GB → primary deleted what we need.
-- Standby errors on reconnect:
--   FATAL: requested WAL segment 000000010000000200000035 has already been removed
-- Recovery: rebuild via pg_basebackup. Hours.

-- (b) WITH a slot — primary keeps WAL until slot moves
SELECT slot_name, active, restart_lsn, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name = 'standby1_slot';
--  slot_name      | active | restart_lsn | wal_status | lag
--  standby1_slot  | f      | 0/A000000   | reserved   | 32 GB

-- Monitor & alert when slot lag exceeds threshold
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes_behind
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 50 * 1024 * 1024 * 1024;  -- 50GB

Step-by-step explanation.

1. In scenario (a), the primary has only wal_keep_size = 1GB of guaranteed retention. After 6 hours offline and 30 GB of new WAL, the segments the standby needs have already been recycled. On reconnect, the wal_sender returns requested WAL segment has already been removed and the standby cannot catch up — you must rebuild it from a fresh base backup.
2. In scenario (b), the primary holds the slot's restart_lsn. As long as the slot exists, the primary will not delete WAL beyond that LSN — even if the disk fills. After 6 hours, the slot's lag is 32 GB and pg_wal/ has grown by 32 GB.
3. When the standby reconnects, it resumes streaming from restart_lsn and the slot's restart_lsn starts advancing as the standby catches up. Once caught up, lag drops to near-zero and the primary recycles the no-longer-needed WAL.
4. The slot's danger is the forgotten dead standby. If standby1 is decommissioned but its slot is not dropped, pg_wal/ grows forever. The monitoring query above flags any slot whose lag exceeds a threshold so you catch this before disk pressure.

Output.

Scenario	Standby behaviour	Primary disk	Recovery
(a) No slot, wal_keep_size = 1GB	Cannot catch up (WAL recycled)	Bounded at ~1 GB	Rebuild via pg_basebackup (hours)
(b) Named slot	Catches up streaming	Grew by 32 GB; recycles after catch-up	Automatic
(b) but standby never returns	n/a	Grows forever	Drop the slot (pg_drop_replication_slot)

Rule of thumb. Use replication slots for production standbys; monitor slot lag religiously. A slot is the only way to guarantee a slow / offline standby can catch up without a full rebuild — and the only thing that makes it dangerous (unbounded WAL growth) is also the easiest thing to monitor.

Worked example — wal_level choices and what they enable

Detailed explanation. Postgres ships three wal_level values — minimal, replica (default), logical. Each adds metadata to WAL records, enabling different features. The interview probe: "why might you switch from replica to logical, and what's the cost?"

Question. List the three wal_level values, what each enables, and the cost. Recommend a default for a 2026 production system.

Input.

wal_level	What's logged	Enables
minimal	Just enough for crash recovery	Crash recovery; no streaming replication
replica (default since PG10)	Enough for physical replication	Streaming replication, hot standby, PITR
logical	Adds row-level decoding info	Logical replication, CDC (Debezium), pglogical

Code.

-- Inspect & change wal_level (requires restart!)
SHOW wal_level;            -- replica
ALTER SYSTEM SET wal_level = 'logical';
-- pg_ctl restart   (yes, restart, not reload — wal_level is not reloadable)

-- After restart, logical replication slots become possible
SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput');

-- Create a publication and a subscription (PG10+)
-- On primary:
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- On subscriber:
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary.db user=replicator dbname=app'
  PUBLICATION orders_pub;

Step-by-step explanation.

1. minimal. Lowest WAL volume; only logs what's needed to recover from a crash. Cannot run streaming replication — the wal_sender process won't have enough metadata. Useful only for single-node systems with no replication needs and a focus on cheap WAL.
2. replica. Default since PG10. Logs everything physical replication needs (block-level changes plus full-page writes after checkpoint). Costs ~10–20% more WAL than minimal and is the right answer for 99% of production systems.
3. logical. Adds row-level decoding metadata (old + new row images, depending on REPLICA IDENTITY). Enables logical replication (subset of tables, version-skew across publisher/subscriber, multi-master patterns) and CDC tools like Debezium. Costs another ~5–15% WAL volume.
4. Switching cost. wal_level is not reloadable — requires a full restart. Plan for a maintenance window when changing.

Output.

Use case	Recommended wal_level
Single-node, no replication	minimal
Standard HA + read replicas	replica
HA + read replicas + CDC to Kafka via Debezium	logical
Multi-master / bidirectional logical replication	logical
2026 default for new clusters	replica (upgrade to logical only if CDC is in scope)

Rule of thumb. Default to replica for any new cluster. Move to logical only when you have a concrete CDC or logical-replication use case — the extra WAL volume is real and is paid by every write, even reads that never use logical decoding.

Senior interview question on WAL mechanics

A senior interviewer might ask: "Your pg_wal/ directory has grown to 200 GB and your disk is filling up. Walk me through the diagnosis and the safe remediation. What's the worst thing you could do?"

Solution Using pg_replication_slots + pg_stat_archiver + archive_command diagnosis

-- Step 1: Which slot or standby is holding back WAL?
SELECT
  slot_name,
  active,
  wal_status,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS bytes_retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Step 2: Is the WAL archiver stuck?
SELECT
  archived_count,
  failed_count,
  last_archived_wal,
  last_archived_time,
  last_failed_wal,
  last_failed_time
FROM pg_stat_archiver;

-- Step 3: Compare WAL on disk to retention requirement
SELECT
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_lsn,
  (SELECT count(*) FROM pg_ls_waldir()) AS wal_files,
  (SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir()) AS wal_size_on_disk;

-- Step 4: SAFE remediation — drop the dead slot if confirmed
-- ONLY after confirming the standby is decommissioned!
SELECT pg_drop_replication_slot('forgotten_slot');

-- Step 5: Fix the broken archive_command
-- e.g. switch from a failing custom script to WAL-G:
ALTER SYSTEM SET archive_command = 'wal-g wal-push %p';
SELECT pg_reload_conf();

Step-by-step trace.

Step	Finding	Action
1	forgotten_slot retains 150 GB, inactive	Confirm standby is decommissioned
2	archive_command last failed 6 hr ago, 6000 failed pushes	S3 credentials expired
3	pg_wal = 200 GB; only 50 GB needed for current standby	Confirms slot + archive are the two culprits
4	Drop forgotten_slot (after confirming it's truly orphaned)	Frees 150 GB after next checkpoint
5	Fix archive_command (rotate WAL-G credentials)	Resumes archive pushing

After the fix, pg_wal/ shrinks at the next checkpoint as recycled segments are deleted. Disk recovers without an outage.

Output:

Cause	Symptom	Remediation
Inactive replication slot	pg_replication_slots.active = f, growing lag	Drop the slot (only if standby is gone)
Broken archive_command	pg_stat_archiver.failed_count rising	Fix command / credentials; WAL won't be recycled until archive succeeds
Slow standby	active=t but huge lag	Add bandwidth, parallelise apply (PG16+), or rebuild standby
wal_keep_size set too high	High but bounded retention	Lower the setting; reload config

Why this works — concept by concept:

• pg_replication_slots is the diagnosis ground truth — every byte of held-back WAL has a name. restart_lsn tells you the LSN the slot is holding; the diff to current WAL tells you the bytes retained.
• archive_command failures hold WAL too — Postgres will not recycle a WAL segment until it has been successfully archived (when archive_mode = on). A broken archive command holds all WAL since the last successful push.
• Dropping an active slot is destructive — pg_drop_replication_slot on an active slot kills the standby's streaming connection and forces a rebuild. Always check active and pg_stat_replication first.
• SAFE order matters — diagnose first, fix the archive_command first, drop the slot last. The worst thing you could do is delete WAL files manually from pg_wal/ — that breaks replication and crash recovery simultaneously.
• Cost — diagnosis is O(1) — three system view queries. Fixing the archive command is O(seconds) — a reload, not a restart. Dropping a slot frees space at the next checkpoint, not immediately.

SQL
Topic — SQL
Postgres WAL & system view diagnostics

Practice →

ETL Topic — ETL WAL archive & PITR pipeline problems

Practice →

---

3. Sync vs async replication

synchronous replication in Postgres is a ladder of four levels — durability versus commit latency, with quorum sync as the modern default

The mental model in one line: synchronous_commit is a four-rung ladder from off (fast, lossy) → on (default, async to standby) → remote_write (sync to standby OS) → remote_apply (sync to standby applied) — and synchronous_standby_names controls which standbys count toward the ack. Every synchronous replication question in a senior interview reduces to "which rung, which standbys, what's your RPO target?"

The four levels of synchronous_commit.

• off. Commit returns before the WAL is fsync'd locally. Up to wal_writer_delay (200 ms default) of writes can be lost on crash. Used for bulk-load and "performance > durability" workloads only.
• on (default). Commit waits for the WAL to be fsync'd on the primary's disk. No standby is involved. This is async replication: the primary commits, the standby catches up "soon" (typically 10–50 ms).
• remote_write. Commit waits for the primary fsync plus the WAL to be received by at least one standby in synchronous_standby_names. The standby's OS has it in its disk cache; if the standby crashes before fsync, the data is lost on the standby (but the primary still has it).
• remote_apply. Commit waits for the primary fsync plus the WAL to be applied on the standby — i.e. the standby's data files reflect the change. The strongest guarantee: a read on the standby right after the commit returns will see the change.

synchronous_standby_names — which standbys count.

• The parameter is a string like 'FIRST 1 (a, b, c)' or 'ANY 2 (a, b, c)'.
• FIRST N (list) — the first N standbys in the list order that are connected must ack. Useful for "this one primary standby in this DC is the sync standby; the rest are async."
• ANY N (list) — quorum sync since PG10. Any N out of the named standbys must ack. The fastest-responding standbys win. This is the modern default for multi-AZ deployments.
• The parameter value uses each standby's application_name, which is set in the standby's primary_conninfo.

Latency vs durability — the central trade-off.

• Async (synchronous_commit = on) — primary commit latency ~1–3 ms (local fsync). Standby lag 10–50 ms typical. RPO = whatever WAL hasn't replicated when the primary dies (typically tens of milliseconds).
• Sync (remote_write) — adds the network RTT plus standby disk write. ~5–10 ms commit latency on a same-DC standby; ~30–100 ms on cross-region. RPO = 0 (primary won't ack until standby has it).
• Sync (remote_apply) — adds the standby apply time. ~10–30 ms commit latency in-region. RPO = 0 plus the standby is read-after-commit consistent.

Quorum sync — the pattern that solved the FIRST-1 problem.

• Before PG10, you wrote synchronous_standby_names = 'standby_a'. If standby_a went down, every primary commit blocked. Operationally fragile.
• PG10+ ships ANY 1 (a, b) — at least one of a or b must ack. If a is down, b's ack is enough; if b is down, a's ack is enough. The primary blocks only if both are down.
• Pattern: ANY 1 (sa, sb) for "one of two same-DC standbys must ack" — the standard high-availability shape in 2026.

synchronous_commit per-transaction overrides.

• The parameter is settable per-session and per-transaction. A bulk-load job can SET synchronous_commit = off for the duration, while the same primary still gives remote_apply to financial transactions.
• Pattern: financial transactions stay at remote_apply; bulk loads, analytics writes, and audit logs run at on or off.

Common interview probes on sync vs async.

• "What's the difference between remote_write and remote_apply?" — remote_write waits for OS-receive; remote_apply waits for the standby's startup process to replay the WAL into data files.
• "What happens if synchronous_standby_names includes a standby that goes offline?" — with FIRST 1, every commit blocks until it returns. With ANY 1 (a, b), commits succeed as long as one of a or b is alive.
• "Why not just always use remote_apply?" — adds 5–30 ms to every commit. For OLTP at 5K TPS, that's a 10–30% throughput hit.
• "What's the per-transaction trick?" — SET LOCAL synchronous_commit = off for the duration of a bulk-insert transaction; the durability requirement varies by workload.

Worked example — measuring the latency cost of remote_apply

Detailed explanation. Senior interviewers love a concrete number. "How much latency does remote_apply add to a commit?" The honest answer is "depends on RTT and standby disk speed," but you should be able to give a quick measurement strategy that produces a real number for a real cluster.

Question. Show how to measure the per-commit latency cost of switching from synchronous_commit = on to synchronous_commit = remote_apply on a 2-node cluster (primary + in-region standby). Use a synthetic workload.

Input.

field	value
Primary	primary.db
Standby	standby.db (same AZ, ~1 ms RTT)
Workload	10K INSERT INTO bench (v) VALUES ($1)
Connection	single libpq, autocommit

Code.

# 1. Baseline at on (async to standby)
psql -h primary.db -U bench -c "ALTER SYSTEM SET synchronous_commit = 'on';
                                SELECT pg_reload_conf();"

pgbench -h primary.db -U bench -c 1 -j 1 -T 30 -f insert.sql
# tps = 4800 (avg ~0.21 ms / txn)

# 2. Switch to remote_apply, named standby
psql -h primary.db -U bench <<SQL
ALTER SYSTEM SET synchronous_commit = 'remote_apply';
ALTER SYSTEM SET synchronous_standby_names = 'standby1';
SELECT pg_reload_conf();
SQL

pgbench -h primary.db -U bench -c 1 -j 1 -T 30 -f insert.sql
# tps = 1800 (avg ~0.56 ms / txn)

# 3. (Optional) Quorum sync — ANY 1 of two standbys
psql -h primary.db -U bench -c "ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby1, standby2)';
                                SELECT pg_reload_conf();"

pgbench -h primary.db -U bench -c 1 -j 1 -T 30 -f insert.sql
# tps = 2100 (avg ~0.48 ms / txn — fastest standby wins each commit)

Step-by-step explanation.

1. Establish a baseline at the default. synchronous_commit = on only waits for local primary fsync; this is your "no sync overhead" number.
2. Switch the cluster to remote_apply with a single standby. Every commit now waits for primary fsync + network RTT + standby fsync + standby apply.
3. Measure again. The throughput drop and the average per-transaction latency rise reflect the added round-trip cost. In the example, 0.21 → 0.56 ms = ~0.35 ms added per commit (consistent with ~1 ms RTT + apply time).
4. (Optional) Switch to quorum sync. With two same-AZ standbys, the fastest one wins each commit. Throughput recovers because the slowest standby no longer blocks commits.
5. Decide based on the throughput-vs-durability trade-off. For 5K TPS workloads, remote_apply with quorum sync usually costs 20–30% throughput for 0 RPO.

Output.

Config	TPS	Avg latency / txn	RPO
synchronous_commit = on	4800	0.21 ms	~tens of ms
remote_apply + single standby	1800	0.56 ms	0
remote_apply + ANY 1 of two	2100	0.48 ms	0

Rule of thumb. Measure on your real cluster — the numbers above are illustrative but the shape is universal: each rung up the ladder costs latency proportional to RTT + standby disk. Quorum sync (ANY 1 (a, b)) is the cheapest way to keep RPO=0 without single-standby blocking risk.

Worked example — FIRST vs ANY in synchronous_standby_names

Detailed explanation. The interviewer probe — "you've configured synchronous_standby_names = 'standby_a'. Standby_a goes offline at 2am. What happens to your application?" If you can't answer this in two sentences, you don't understand sync replication. Quorum syntax exists precisely to avoid this failure mode.

Question. Compare FIRST 1 (a), FIRST 1 (a, b), and ANY 1 (a, b) behaviour when standby a goes offline. Show which one keeps the application up.

Input.

Setting	When a is alive	When a is offline, b alive
FIRST 1 (a)	a acks	Primary blocks on every commit
FIRST 1 (a, b)	a acks (b is next-in-line)	b acks (primary uses next standby in list)
ANY 1 (a, b)	First to ack wins	b acks

Code.

-- Pattern 1 — naive sync
ALTER SYSTEM SET synchronous_standby_names = 'standby_a';
-- Risk: if standby_a goes down, every commit blocks until it returns.
-- This is the most common production HA mistake.

-- Pattern 2 — FIRST 1 with a fallback list
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby_a, standby_b)';
-- If a is connected, only a's ack counts (b runs async).
-- If a disconnects, the next in list (b) takes over and acks.

-- Pattern 3 — Quorum sync (modern default)
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby_a, standby_b)';
-- Whichever of a or b acks first counts. Both must be down for primary to block.

SELECT pg_reload_conf();

-- Inspect which standby is currently sync vs async
SELECT application_name, sync_state, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
-- application_name | sync_state | ...
-- standby_a        | sync       | ...    (or 'quorum' for ANY pattern)
-- standby_b        | async      | ...    (or 'quorum')

Step-by-step explanation.

1. FIRST 1 (a) — the original pre-PG10 syntax. Exactly one standby can ack, and only that named standby. If a is down, the primary blocks every transaction until a returns. Operationally brittle; do not use in production HA.
2. FIRST 1 (a, b) — PG9.6+ failover-list syntax. As long as a is connected, only a counts. The moment a disconnects, b becomes the sync standby. Asymmetric (always prefers a); useful when standbys are not equivalent (e.g. one is in the same rack, one is in another DC).
3. ANY 1 (a, b) — PG10+ quorum sync. The first to ack each commit wins; both must be down for the primary to block. Symmetric; the modern default for multi-AZ deployments where standbys are interchangeable.
4. sync_state in pg_stat_replication — tells you whether each connected standby is currently sync, potential, async, or quorum. Use this to confirm your config is actually in effect.

Output.

Config	a down	b down	Both down
FIRST 1 (a)	Primary BLOCKS	n/a	Primary BLOCKS
FIRST 1 (a, b)	b acks	a acks	Primary BLOCKS
ANY 1 (a, b)	b acks	a acks	Primary BLOCKS
ANY 2 (a, b, c)	b, c ack	a, c ack	Primary blocks (only 1 alive)

Rule of thumb. Use ANY N (list) for the modern HA pattern. Set N to the minimum number of standbys whose ack you need (typically 1 in two-standby setups, 2 in three-standby setups). The "naive single name" pattern is the most common Postgres HA outage cause — don't ship it.

Worked example — per-transaction synchronous_commit for bulk loads

Detailed explanation. Suppose your cluster runs synchronous_commit = remote_apply (0 RPO for OLTP). At 2 AM, a nightly ETL inserts 50 million rows. At remote_apply, that takes 4 hours. Switching to synchronous_commit = off for the duration cuts it to 40 minutes — and the durability requirement is genuinely lower (audit log re-derivable from upstream sources).

Question. Show how to lower synchronous_commit per-transaction without changing the cluster-wide setting. Cover both session-level and transaction-level overrides.

Input.

Setting	Value	Effect
Cluster synchronous_commit	remote_apply	Every commit waits for standby apply
Bulk job target	off	No sync wait at all for these 50M rows

Code.

-- Pattern 1 — session-level override (whole connection)
\c app_db
SET synchronous_commit = off;
\copy big_facts FROM '/tmp/facts.csv';     -- runs at off; rest of cluster unchanged
SET synchronous_commit = DEFAULT;          -- restore for safety

-- Pattern 2 — single-transaction override
BEGIN;
SET LOCAL synchronous_commit = off;        -- LOCAL scope: just this transaction
INSERT INTO big_facts (...) SELECT ...;
COMMIT;
-- Outside the BEGIN/COMMIT, this session is back to cluster default

-- Pattern 3 — function-scoped override (set on the function)
CREATE OR REPLACE FUNCTION ingest_audit(payload jsonb) RETURNS void AS $$
BEGIN
  INSERT INTO audit_log (payload, ts) VALUES (payload, now());
END;
$$ LANGUAGE plpgsql
SET synchronous_commit = 'off';            -- function-scoped; reverts on return

-- Verify which level is in effect for the current transaction
SHOW synchronous_commit;

Step-by-step explanation.

1. Session-level (SET). Sets the value for the rest of the current connection. Useful for ETL jobs that connect, run their workload at lower durability, and disconnect. Reverts to the cluster default when the connection ends.
2. Transaction-local (SET LOCAL). Sets the value only for the current transaction; reverts on commit/rollback. Safer than session-level when other code shares the connection — for example, in pgbouncer transaction pooling, SET persists across transactions but SET LOCAL does not.
3. Function-scoped (SET on the function definition). Applies whenever the function is called, reverts on return. Useful when a specific code path should always have lower durability (audit log, async event queue).
4. The override is a downgrade per transaction. The wider cluster still gives remote_apply to every other transaction.

Output.

Workload	Cluster default	Override	Effective level
Financial transactions	remote_apply	none	remote_apply
Nightly bulk load	remote_apply	SET synchronous_commit = off	off
Audit log inserts	remote_apply	SET synchronous_commit = off on function	off
Read-only queries	remote_apply	irrelevant (no commit)	n/a

Rule of thumb. Set the cluster default to the strictest level any transaction needs, then downgrade per-workload where the durability requirement is genuinely lower. Never set the cluster default to off and then try to upgrade per-transaction — you can't take back the writes that already shipped without sync wait.

Senior interview question on sync replication and RPO

A senior interviewer might ask: "you run synchronous_commit = remote_write with synchronous_standby_names = 'ANY 1 (sa, sb)'. The primary crashes hard right after committing a transaction. What's your RPO? Walk me through exactly what happens to that last transaction."

Solution Using a step-by-step ack trace + recovery walkthrough

Trace — last committed transaction T, primary crash immediately after ack

t0  Backend on primary writes WAL record for T to WAL buffer
t1  Backend forces fsync on primary's WAL up to T.lsn  (synchronous_commit=remote_write step 1)
t2  wal_sender ships WAL bytes for T over the wire to standby sa
t3  Standby sa's wal_receiver writes T.lsn to sa's pg_wal (OS recv done)
t4  sa's wal_receiver acks "write_lsn = T.lsn" back to primary
t5  Primary returns COMMIT OK to the client                  ← client thinks T is durable
t6  Primary's kernel/CPU dies. All in-memory state gone.

What standbys hold at this moment:
  - sa: T.lsn is on disk (wal_receiver wrote it), but startup process hasn't replayed yet
  - sb: T.lsn may or may not have arrived (we only needed ONE of (sa, sb) to ack)

Recovery — Patroni promotes sa (highest replay LSN among healthy standbys):
  - sa replays remaining WAL through T.lsn, then is promoted with pg_ctl promote
  - sa is now the new primary
  - T is fully durable. RPO = 0 for this transaction.

What we did NOT promise:
  - remote_write only guarantees OS recv on standby, not apply
  - If sa's OS had crashed between t3 and a kernel fsync, T could have been
    lost on sa but the client got OK. RPO > 0 in that narrow window.
  - remote_apply (instead of remote_write) closes this window completely.

Step-by-step trace.

step	t	event	T durable on?
t0	0 ms	Backend writes WAL buffer	primary RAM
t1	1 ms	Primary fsync	primary disk
t3	2 ms	sa receives & writes T	primary disk + sa disk
t4	2.1 ms	sa acks write_lsn	primary knows sa has it
t5	2.2 ms	Primary acks OK to client	client believes T is durable
t6	2.3 ms	Primary hard crash	primary lost, sa intact
t7	promote	Patroni promotes sa, replays through T.lsn	sa is new primary, T preserved

After promotion, the application reconnects to sa (now primary) and the client's view that T was committed is preserved.

Output:

Aspect	Value
RPO for T (the just-committed transaction)	0 (assuming sa's OS did not also crash)
Narrow loss window	Between remote_write ack and standby's OS fsync (~tens of ms)
remote_apply would close this	Yes — adds apply step before client OK
Failover RTO	10–30 s with Patroni + etcd
Application sees	Brief reconnect, then T visible on new primary

Why this works — concept by concept:

• remote_write semantics — guarantees the standby has the WAL bytes in OS-receive (likely cached, possibly not fsync'd on the standby). For 99.9% of failure modes this is enough; the standby's OS is unlikely to also crash in the same millisecond as the primary.
• Quorum sync (ANY 1) — at t4, only one of sa, sb needed to ack. If sa had been down, sb's ack would have closed the loop instead. The standby chosen for promotion is the one with the highest replay LSN, regardless of which one acked first.
• remote_apply vs remote_write — remote_apply waits one more step (standby's startup process replays into data files) before the ack returns to the primary. This adds 1–10 ms per commit and gives true 0 RPO with no narrow window.
• Patroni picks the highest-LSN standby — not just any standby. Patroni queries each standby's pg_last_wal_receive_lsn() before promoting; the one ahead becomes the new primary. This is how postgres failover preserves data without manual checking.
• Cost — remote_write ≈ +1 RTT to commit latency. remote_apply ≈ +1 RTT + standby apply time (typically another 1–5 ms). Both are O(1) per commit.

SQL
Topic — SQL
Transaction durability & RPO probes

Practice →

Optimization Topic — optimization Latency vs durability tuning problems

Practice →

---

4. Hot standby + read scale

hot standby turns every replica into a read-scale node — but hot_standby_feedback is the vacuum-vs-query trade-off that breaks many production clusters

The mental model in one line: a standby with hot_standby = on accepts read-only queries while it replays WAL; long-running queries can conflict with WAL apply, so hot_standby_feedback = on makes the primary delay vacuum to protect those queries — at the cost of bloat on the primary. Once you internalise this, every read-replica question reduces to "where did you put the analytics workload, and how did you protect it from replication conflict?"

The two-line concept.

• hot_standby = on lets the standby serve SELECTs while in recovery mode. The standby is read-only — no inserts, no DDL, no SELECT FOR UPDATE.
• Read scale comes from routing reads to standbys (often via PgBouncer or HAProxy) and writes to the primary. Two standbys + a primary effectively triples read capacity.

Replication conflict — the dark side of hot standby.

• The standby replays WAL by applying changes to data pages. A long-running SELECT on the standby may be reading rows that the primary's vacuum has marked as removable — the standby's apply wants to remove those rows too, but the SELECT still references them.
• Postgres' resolution: cancel the query after max_standby_streaming_delay (default 30s). The client sees ERROR: canceling statement due to conflict with recovery. Painful.

hot_standby_feedback = on — the protection.

• The standby sends its oldest active transaction's snapshot back to the primary. The primary delays VACUUM so it doesn't reclaim tuples still needed by the standby.
• Trade-off: the primary accumulates bloat (dead tuples) while the standby's long query runs. A 1-hour analytics query on the standby = 1 hour of vacuum delay on the primary.
• Use only on standbys where long reads are expected (analytics replica). Keep hot_standby_feedback = off on standbys serving short OLTP reads.

max_standby_streaming_delay — the timeout.

• Default: 30s. If WAL apply is blocked by an active query for more than 30s, Postgres cancels the query.
• Set to -1 to wait forever (no query is ever cancelled — apply may lag indefinitely). Set higher (e.g. 5min) for analytics replicas to let bigger reports run without conflict.
• The right pattern: dedicate one standby to analytics with a long max_standby_streaming_delay + hot_standby_feedback = on; keep others tight for low replication lag.

Routing reads — PgBouncer / HAProxy patterns.

• PgBouncer at the connection-pool layer. Two instances (one pointing to the primary for writes, one to a virtual IP that round-robins across standbys). Application code routes reads vs writes by which pool it uses.
• HAProxy with health checks reading pg_is_in_recovery(). The "primary" backend has only the node where pg_is_in_recovery() = false; the "standby" backend has the rest. Failover automatically updates the routing.
• Patroni's REST API exposes /master, /replica, /sync endpoints — HAProxy can use these for sub-second routing changes during a failover.

Reads on standbys — semantic caveats.

• Read-after-write is not guaranteed unless synchronous_commit = remote_apply. A user who just submitted a form and is immediately routed to a standby may not see their own update.
• Sequence usage. nextval() works on the primary only; standbys are read-only.
• SELECT FOR UPDATE. Not allowed on standbys; throws an error.
• Temp tables. Not allowed on standbys.

Common interview probes on hot standby.

• "What's the trade-off of hot_standby_feedback = on?" — primary bloat in exchange for protecting standby queries from cancellation.
• "What happens when a standby query conflicts with WAL apply?" — query is cancelled after max_standby_streaming_delay (default 30s), unless hot_standby_feedback is on.
• "Can I write to a standby?" — no, standby is strictly read-only.
• "How do I guarantee read-after-write consistency on a standby?" — set synchronous_commit = remote_apply and route reads to a sync standby; otherwise the standby may lag.

Worked example — routing reads via PgBouncer to a read replica

Detailed explanation. A common interview design — "you've added a read replica; how does the application use it?" The naive answer is "the application opens two connections — one to primary, one to standby." The senior answer routes via PgBouncer (or HAProxy) so the application doesn't care which node is which.

Question. Set up a PgBouncer config that exposes two databases — app_write (always primary) and app_read (round-robins across the two standbys). Show the application code change.

Input.

field	value
Primary	primary.db:5432
Standby 1	standby1.db:5432
Standby 2	standby2.db:5432
PgBouncer host	pgb.db:6432

Code.

; /etc/pgbouncer/pgbouncer.ini
[databases]
app_write = host=primary.db port=5432 dbname=app
app_read  = host=primary.db port=5432 dbname=app target_session_attrs=read-only
; (Behind the scenes, app_read uses libpq's target_session_attrs=read-only
;  to round-robin across hosts and skip the primary.)
; Alternative: use HAProxy / Patroni REST for actual load balancing.

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type   = md5
auth_file   = /etc/pgbouncer/userlist.txt
pool_mode   = transaction
max_client_conn = 5000
default_pool_size = 50

# Application code
import psycopg

write_pool = psycopg.connect("host=pgb.db port=6432 dbname=app_write user=app")
read_pool  = psycopg.connect(
    "host=standby1.db,standby2.db port=6432 dbname=app_read "
    "target_session_attrs=read-only "    # libpq picks a read-only node
    "user=app"
)

# Route reads vs writes
def get_user(user_id):
    with read_pool.cursor() as cur:
        cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cur.fetchone()

def update_user(user_id, name):
    with write_pool.cursor() as cur:
        cur.execute("UPDATE users SET name = %s WHERE id = %s", (name, user_id))
        write_pool.commit()

Step-by-step explanation.

1. PgBouncer exposes two logical databases — app_write (primary) and app_read (read-only). The application sees two connection strings; doesn't know which node is which.
2. target_session_attrs=read-only is a libpq feature (PG10+) that round-robins across the host list and connects only to nodes where pg_is_in_recovery() = true. Built-in read-replica routing.
3. The application opens one pool per role. All SELECTs go through read_pool; all writes go through write_pool.
4. During a failover, Patroni updates the primary endpoint; PgBouncer reloads (or runs behind HAProxy that re-checks); the application sees a brief connection error and reconnects.
5. The pattern scales — adding a third read replica is standby3.db,… in the host list; no application change needed.

Output.

Aspect	Value
Reads	Round-robin across standbys
Writes	Always to primary
Failover impact	Brief reconnect; application code unchanged
Adding a read replica	Edit host list, reload PgBouncer
Read-after-write consistency	NOT guaranteed (standby lag)

Rule of thumb. Route reads through target_session_attrs=read-only or an HAProxy backend that filters by pg_is_in_recovery(). The application code knows about read vs write; it does not know about node identity.

Worked example — replication conflict and hot_standby_feedback

Detailed explanation. The interview probe — "you run a 30-minute analytics report on a standby. Every now and then it fails with ERROR: canceling statement due to conflict with recovery. What's happening and how do you fix it?" This is the classic hot-standby gotcha, and the answer touches hot_standby_feedback, max_standby_streaming_delay, and vacuum tuning on the primary.

Question. Show the configuration changes to let a 30-minute analytics query run on a standby without cancellation. Cover both hot_standby_feedback and max_standby_streaming_delay. Explain the cost paid on the primary.

Input.

field	value
Query	30-min analytics rollup on standby_analytics
Default behaviour	Cancelled after 30s
Target	Query runs to completion

Code.

-- On the analytics standby
ALTER SYSTEM SET hot_standby_feedback = on;
ALTER SYSTEM SET max_standby_streaming_delay = '1h';
SELECT pg_reload_conf();

-- Verify on the standby
SHOW hot_standby_feedback;          -- on
SHOW max_standby_streaming_delay;   -- 1h

-- On the primary, watch for vacuum delay
SELECT pid, usename, state, xact_start, query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

-- Watch bloat on the primary's high-churn tables
SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS pct_dead
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Step-by-step explanation.

1. hot_standby_feedback = on on the analytics standby. The standby's wal_receiver tells the primary the oldest active transaction's snapshot. The primary's vacuum now respects that and won't reclaim tuples newer than that snapshot.
2. max_standby_streaming_delay = '1h' raises the conflict-cancellation timeout. Even if a conflict does occur (e.g. an ALTER TABLE on the primary), the standby waits up to 1 hour for the query to finish before cancelling.
3. Bloat on the primary. While the 30-min query runs, the primary's vacuum stops reclaiming dead tuples. On a high-churn table, 30 minutes can mean millions of dead tuples accumulating. After the query ends, vacuum runs and reclaims — but the bloat is temporary.
4. The trade-off. Pick one standby as the analytics target. Other standbys keep hot_standby_feedback = off for low lag. The primary pays vacuum delay only for the analytics workload, not for every standby.
5. Monitoring. Watch n_dead_tup / n_live_tup on the primary's hot tables. If the ratio stays high after the analytics query ends, vacuum isn't catching up — tune autovacuum_vacuum_cost_limit higher.

Output.

Setting	Standby query	Primary cost
hot_standby_feedback = off, default delay	Cancelled at 30s	None
hot_standby_feedback = on, delay 1h	Completes	Vacuum delayed for the query's duration
Tight autovacuum tuning after	n/a	Bloat reclaimed faster post-query

Rule of thumb. One analytics standby with hot_standby_feedback = on; other standbys with it off. The cost of vacuum delay is real but bounded by the analytics query duration. If you turn it on cluster-wide, every long query on any standby blocks vacuum everywhere.

Worked example — pg_stat_replication and lag monitoring

Detailed explanation. Senior interviewers expect a real monitoring story. "How do you monitor replication lag?" The answer is two views — pg_stat_replication on the primary and pg_stat_wal_receiver on the standby — plus a handful of LSN-diff calculations and a Prometheus exporter.

Question. Write the SQL that exposes replication lag in bytes and in seconds for every connected standby. Show the alert thresholds you'd set.

Input.

Metric	Source	Threshold
Bytes behind	pg_stat_replication	> 100 MB warn, > 1 GB page
Seconds behind	pg_stat_replication.replay_lag	> 5 s warn, > 60 s page

Code.

-- On the primary — one row per connected standby
SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn,  write_lsn))  AS send_to_write_bytes,
  pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn))  AS write_to_flush_bytes,
  pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) AS flush_to_replay_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

-- application_name | sync_state | total_lag_bytes | replay_lag
-- standby_local    | sync       | 0 bytes          | 00:00:00.001
-- standby_analytics| async      | 12 MB            | 00:00:02.4
-- standby_dr       | async      | 48 MB            | 00:00:05.1

-- Alert query — flag any standby > 1 GB behind
SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 1024 * 1024 * 1024;

Step-by-step explanation.

1. sent_lsn — how far WAL has been written to the wire toward this standby.
2. write_lsn — how far the standby has acked OS-receive.
3. flush_lsn — how far the standby has fsync'd to disk.
4. replay_lsn — how far the standby's startup process has replayed into data files. This is the LSN visible to readers on the standby.
5. replay_lag (seconds) — interval since the primary committed the last replayed transaction. A near-zero value means the standby is caught up.
6. total_lag_bytes — pg_current_wal_lsn() − replay_lsn on the primary's clock. The most actionable single number for "how far behind."

Output.

Metric	Healthy	Warn	Page
total_lag_bytes	< 10 MB	100 MB – 1 GB	> 1 GB
replay_lag (seconds)	< 1 s	5–60 s	> 60 s
state	streaming	catchup	disconnected
sync_state matches config	sync for sync standby	async when expected sync	mismatch

Rule of thumb. Monitor both bytes and seconds. Bytes catches stuck WAL on the wire; seconds catches slow replay. A standby with 0 bytes lag but rising replay_lag is suffering from slow apply (CPU-bound replay) — different fix than network bandwidth.

Senior interview question on hot standby workload split

A senior interviewer might ask: "You have one primary and three standbys. Workloads: a payments OLTP service that needs read-after-write consistency, an analytics dashboard that runs 5-minute rollups, and a regulatory query that runs an hour every night. How do you map workloads to standbys?"

Solution Using per-standby configuration + routing

Workload-to-standby mapping

standby_sync   — synchronous_commit = remote_apply target
                 hot_standby_feedback = off
                 max_standby_streaming_delay = 30s (default)
                 Workload: read-after-write OLTP reads (payments service)

standby_async  — async streaming, no feedback
                 hot_standby_feedback = off
                 max_standby_streaming_delay = 30s
                 Workload: short-lived dashboards (sub-second queries)

standby_analytics — async streaming, feedback ON
                 hot_standby_feedback = on
                 max_standby_streaming_delay = 1h
                 Workload: 5-min rollups + nightly hour-long regulatory query

Routing (PgBouncer / HAProxy)
  payments-read   → standby_sync   (target_session_attrs=read-only)
  dashboard-read  → standby_async  (target_session_attrs=read-only)
  analytics-read  → standby_analytics  (direct connection string)

Step-by-step trace.

Workload	Standby	Why
Payments read-after-write	standby_sync	Sync replica; remote_apply ensures the standby has the write applied before the client sees commit OK
Dashboard quick reads	standby_async	Async standby is fine for sub-second queries; no need for sync overhead
5-min rollup	standby_analytics	Has hot_standby_feedback, so the 5-min query won't conflict with vacuum
Nightly hour-long regulatory query	standby_analytics	Same standby; max_standby_streaming_delay = 1h lets it complete

After the mapping, the analytics standby pays the vacuum cost (acceptable for one node), the sync standby pays the latency cost on the primary's commits (acceptable for OLTP correctness), and the async standby stays low-lag for dashboards.

Output:

Standby	Sync mode	Feedback	Max delay	Workload
standby_sync	remote_apply target	off	30s	Payments OLTP reads
standby_async	async	off	30s	Quick dashboards
standby_analytics	async	on	1h	5-min rollups + nightly long query

Why this works — concept by concept:

• One standby per workload class — different workloads have different conflict-tolerance and durability needs. Mixing them on one standby forces compromises.
• hot_standby_feedback is opt-in per standby — exactly the standby that runs long queries pays the vacuum cost. Other standbys stay free.
• remote_apply target via synchronous_standby_names — sync ack from standby_sync only; other standbys run async. Sync overhead is paid once per commit, not per standby.
• max_standby_streaming_delay scales with query duration — set per standby to the longest query you expect on that node. Avoid -1 (infinite) because it can mask a stuck standby.
• Routing is library + proxy — application code routes via target_session_attrs=read-only or explicit pool selection. No bespoke node-identity logic in app code.
• Cost — three standbys ≈ 3× a single-primary deployment for compute, plus the vacuum-delay budget on the analytics node and the sync-commit budget on the sync node. Both costs are O(1) per commit, O(1) per query.

SQL
Topic — SQL
Read-replica routing & consistency probes

Practice →

Optimization Topic — optimization Vacuum & bloat tuning problems

Practice →

---

5. Failover + Patroni + WAL-G

patroni plus walg is the 2026 default — distributed consensus elects a leader, STONITH fences the dead primary, WAL-G archives WAL and base backups to S3 for PITR + DR

The mental model in one line: Patroni runs alongside every Postgres node, holds a lease in etcd / Consul / Kubernetes, promotes the highest-LSN standby when the lease expires, and WAL-G pushes WAL + base backups to S3 for PITR. Once you see the two layers — consensus for failover, archive for recovery — every postgres failover interview question reduces to "what does Patroni do, and what does WAL-G do, and how do they cooperate?"

Patroni — what it actually does.

• A Python daemon that runs as a sidecar to every Postgres node. Manages Postgres' lifecycle (start, stop, promote, demote, reinit).
• Uses a distributed configuration store (DCS) — typically etcd, sometimes Consul, sometimes Kubernetes leases — to coordinate leader election.
• The primary holds a leader lease in the DCS that it renews every loop_wait seconds (default 10s). If the lease expires (default ttl 30s), Patroni considers the primary dead and starts election.
• Election picks the standby with the highest pg_last_wal_replay_lsn() (or the one configured as the preferred failover target). Patroni runs pg_ctl promote on the winner, updates the DCS, and the rest of the cluster reconfigures.

WAL-G — what it actually does.

• A Go binary that pushes WAL segments (wal-push) and base backups (backup-push) to object storage (S3 / GCS / Azure / Swift).
• Used as the archive_command in postgresql.conf: archive_command = 'wal-g wal-push %p'. Every completed WAL segment is shipped off-cluster.
• Provides wal-fetch and backup-fetch for restore. PITR is a backup-fetch of the latest base backup followed by replaying WAL via wal-fetch up to the target time/LSN.
• Optional encryption (libsodium, OpenPGP), parallel uploads, delta backups (only changed pages since last full).

The split-brain problem and STONITH.

• Split brain happens when the network partitions and both sides think the other is dead. Each side promotes its standby; the application connects to both; data diverges. Catastrophic.
• Prevention 1: DCS quorum. Patroni only promotes if it can read/write to a majority of the DCS nodes. A network-partitioned former primary cannot reach the DCS quorum, so it gracefully demotes itself.
• Prevention 2: STONITH (Shoot The Other Node In The Head). External fencing — Patroni calls a cloud API (e.g. EC2 stop-instance, K8s pod deletion) to forcibly kill the dead primary before promoting. Belt-and-braces on top of DCS quorum.
• Prevention 3: synchronous replication. A standby that wasn't sync-ack'd cannot have committed transactions the new primary is missing. Quorum sync + DCS quorum together close the split-brain window almost completely.

Patroni vs Stolon vs pg_auto_failover.

• Patroni — most popular, etcd / Consul / K8s DCS, Python, used by Zalando, CrunchyData, Stackgres. The 2026 default.
• Stolon — Go, embedded DCS or external etcd, similar feature set, smaller community.
• pg_auto_failover — by Citus Data (now part of Microsoft), uses a monitor node instead of full DCS, simpler ops but less proven at scale. Good for small clusters.

pg_basebackup vs WAL-G — when to use which.

• pg_basebackup ships with Postgres, runs from one node to another, no object storage required. Best for standby provisioning — bootstrapping a new replica.
• WAL-G is for archiving and DR — base backups + continuous WAL push to object storage. Best for PITR and cross-region DR. New standbys can also bootstrap from WAL-G via backup-fetch (faster than pg_basebackup for multi-TB clusters because S3 has more aggregate bandwidth than the primary's NIC).
• Modern 2026 pattern: pg_basebackup for initial cluster bootstrap; WAL-G for every subsequent base backup and all WAL archival.

Failover timing — the 12-second cluster.

• A well-tuned Patroni cluster targets 10–30 seconds RTO. The breakdown for a 12-second failover:
  • t = 0: primary crashes (kernel panic, hardware fault, OOM).
  • t = 0–10s: Patroni waits for the lease to expire (ttl = 10s, loop_wait = 5s).
  • t = 10–11s: Patroni queries every standby's LSN; picks the leader.
  • t = 11–12s: pg_ctl promote runs; new primary is open for writes.
  • t = 12s: HAProxy / Patroni REST updates routing; clients reconnect to the new primary.
• Tightening ttl below 10s risks false positives (network blips trigger spurious failovers). 10–30s is the sweet spot.

Common interview probes on failover.

• "What prevents split-brain?" — DCS quorum + STONITH + sync replication (defence in depth).
• "How does Patroni pick the new primary?" — highest pg_last_wal_replay_lsn() among connected standbys.
• "What is WAL-G for?" — WAL archive and base backup to object storage; enables PITR and cross-region DR.
• "What's the RTO target for Patroni?" — 10–30 seconds; depends on ttl and standby promote time.
• "What is STONITH and why do you need it?" — forcibly kill the dead primary via an external mechanism (cloud API, IPMI, K8s API) so it cannot accidentally accept writes after a network partition.

Worked example — minimal Patroni config for a 3-node cluster

Detailed explanation. Senior interviewers often ask you to sketch the Patroni config for a typical cluster. The answer is a YAML file with three sections — Postgres, Patroni, and DCS — and a small etcd cluster running alongside. Knowing the literal config shape is a strong signal that you've operated this in production.

Question. Write a minimal Patroni YAML for a 3-node Postgres cluster (1 primary + 2 standbys) using etcd as the DCS. Cover ttl, loop_wait, synchronous_mode, and WAL-G as the archive_command.

Input.

field	value
Nodes	pg1 (primary), pg2, pg3
DCS	etcd cluster on etcd1, etcd2, etcd3
Archive	s3://prod-pg-archive/ via WAL-G
Sync target	ANY 1 of (pg2, pg3)
RTO target	30s

Code.

# /etc/patroni/patroni.yml — one file per node, edit `name` and `connect_address`
scope: pg-cluster
name: pg1                       # change per node: pg1 / pg2 / pg3

restapi:
  listen: 0.0.0.0:8008
  connect_address: pg1.internal:8008

etcd3:                          # PG/Patroni cluster's source of truth
  hosts: etcd1.internal:2379,etcd2.internal:2379,etcd3.internal:2379

bootstrap:
  dcs:
    ttl: 30                     # leader lease seconds; failover after this
    loop_wait: 10               # heartbeat interval
    retry_timeout: 10
    maximum_lag_on_failover: 1048576   # 1 MB — don't promote standbys lagging more
    synchronous_mode: true              # require sync standby
    synchronous_mode_strict: false      # allow degrade to async if no sync standby
    postgresql:
      use_pg_rewind: true               # fast re-join via pg_rewind
      parameters:
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby: 'on'
        synchronous_commit: remote_apply
        synchronous_standby_names: 'ANY 1 (pg2, pg3)'
        archive_mode: 'on'
        archive_command: 'wal-g wal-push %p'
        restore_command: 'wal-g wal-fetch %f %p'

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pg1.internal:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir:  /usr/lib/postgresql/16/bin
  authentication:
    replication:
      username: replicator
      password: <secret>
    superuser:
      username: postgres
      password: <secret>
  create_replica_methods:
    - wal_g                     # bootstrap new standbys via WAL-G backup-fetch
    - basebackup
  wal_g:
    command: wal-g backup-fetch /var/lib/postgresql/16/main LATEST
    no_master: 1                # don't need primary online to bootstrap
  basebackup:
    max-rate: '100M'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false

Step-by-step explanation.

1. scope + name. scope is the cluster identifier shared by all nodes; name is per-node. Patroni writes leader/member keys into etcd under /$scope/.
2. etcd3 (or etcd for v2 API). The DCS endpoints. Patroni requires majority connectivity to read/write — that's what prevents split-brain.
3. ttl + loop_wait. Leader lease semantics. The primary renews every loop_wait (10s); after ttl (30s) without renewal, the lease is gone and failover starts. The 30s figure is the upper bound on RTO from this layer.
4. synchronous_mode: true + synchronous_standby_names: 'ANY 1 (pg2, pg3)'. At least one of pg2 or pg3 must ack; Patroni only fails over to a sync-ack'd standby (so no committed transactions are lost).
5. archive_command: 'wal-g wal-push %p'. Every WAL segment goes to S3 via WAL-G. The same command on every node — when a standby is promoted, archive continues seamlessly.
6. create_replica_methods: [wal_g, basebackup]. When a node needs a fresh data directory (after a long outage), Patroni first tries WAL-G backup-fetch (fast from S3), falling back to pg_basebackup if that fails.
7. use_pg_rewind: true. When a former primary rejoins after failover, pg_rewind brings its data dir into line with the new primary by rewriting changed pages — far faster than a full re-clone.

Output.

Behaviour	Configured by
30s RTO ceiling	ttl: 30
0 RPO for committed transactions	synchronous_mode: true + remote_apply + ANY 1
Continuous WAL to S3	archive_command = 'wal-g wal-push %p'
Fast standby re-join after failover	use_pg_rewind: true
New standby bootstrap	create_replica_methods: [wal_g, basebackup]

Rule of thumb. Patroni YAML is one of the few things in Postgres HA that must be exactly right. Keep it in source control, deploy via Ansible / Salt / Helm, and treat changes as you would code changes — review, test in staging, roll forward carefully.

Worked example — WAL-G archive + PITR end-to-end

Detailed explanation. PITR is the disaster scenario where everything else has gone wrong — a developer ran DELETE FROM orders without a WHERE clause, the change replicated to every standby in seconds, and the only protection is the WAL archive. The recipe — fetch a base backup, replay WAL up to a target time — is something every senior DE should be able to type from memory.

Question. Walk through the exact commands to recover the database to 11:30 AM after an accidental DELETE FROM orders at 11:42 AM. Assume WAL-G with daily base backups and continuous WAL push.

Input.

field	value
Disaster time	11:42 AM (DELETE FROM orders)
Target recovery time	11:30 AM (12 minutes before disaster)
Archive	s3://prod-pg-archive/
Last full backup	last night at 02:00

Code.

# 1. Stop the cluster (Patroni)
patronictl -c /etc/patroni/patroni.yml pause pg-cluster   # disable failover during recovery
systemctl stop patroni                                      # on each node

# 2. Provision a fresh data dir (delete the corrupted current data)
rm -rf /var/lib/postgresql/16/main/*

# 3. Restore the latest base backup BEFORE the disaster
wal-g backup-fetch /var/lib/postgresql/16/main LATEST

# 4. Configure recovery target (PG12+: recovery params in postgresql.conf, signal file)
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf <<EOF
restore_command         = 'wal-g wal-fetch %f %p'
recovery_target_time    = '2026-06-22 11:30:00+00'
recovery_target_action  = 'promote'
recovery_target_inclusive = false
EOF
touch /var/lib/postgresql/16/main/recovery.signal

# 5. Start Postgres; it will replay WAL up to 11:30 AM, then promote
sudo systemctl start postgresql@16-main

# 6. Verify
psql -h pg1.internal -U postgres -c "SELECT pg_is_in_recovery();"  -- should be 'f'
psql -h pg1.internal -U postgres -c "SELECT count(*) FROM orders;"  -- should match 11:30 AM state

# 7. Reinitialise standbys from the new primary (they're now branched from old timeline)
patronictl -c /etc/patroni/patroni.yml reinit pg-cluster pg2
patronictl -c /etc/patroni/patroni.yml reinit pg-cluster pg3

# 8. Resume Patroni
patronictl -c /etc/patroni/patroni.yml resume pg-cluster

Step-by-step explanation.

1. Pause Patroni. During PITR you must stop the cluster from running normal HA logic. patronictl pause disables failover so Patroni doesn't try to "fix" the situation while you're restoring.
2. Fresh data dir. Delete the corrupted state. The orders table — and everything else from the disaster — is wiped.
3. wal-g backup-fetch LATEST. Pulls the most recent base backup (from last night) into the data dir. This is the starting state for replay.
4. Recovery target config. Tell Postgres to replay WAL up to recovery_target_time = '11:30:00', then promote (stop applying WAL and accept writes). recovery_target_inclusive = false means "stop before the transaction at that time, not after." recovery.signal is the file that puts PG into recovery mode.
5. Start Postgres. PG reads the signal file, calls restore_command (wal-g wal-fetch) for each WAL segment from the base backup's LSN forward, applies them, and when it hits the target time, promotes itself. The orders table now has its 11:30 AM state.
6. Verify. pg_is_in_recovery() = false means promotion succeeded. Count rows in orders to confirm the disaster is undone.
7. Reinit standbys. The new primary is on a new timeline (Postgres tracks this with a timeline ID). Existing standbys cannot stream from a different timeline; they must be re-cloned via wal-g backup-fetch (faster than pg_basebackup in a recovery scenario).
8. Resume. Patroni is back in charge; HA is restored.

Output.

Step	Time	Result
Pause Patroni	<5s	HA frozen
Restore base backup	depends on size	~10 GB / minute via S3 + zstd
Replay WAL to 11:30 AM	depends on volume	~1 GB / minute typical
Promote new primary	<5s	Pre-disaster state restored
Reinit standbys	depends on size	Each standby ~equal to base-backup restore
Resume Patroni	<5s	Cluster healthy

Rule of thumb. PITR is rare and stressful. Practice it twice a year with a non-production restore drill. The first time you do it under fire is the worst time to discover that your archive_command was broken three days ago and the WAL you need isn't in S3.

Worked example — a Patroni failover in 12 seconds

Detailed explanation. The 30-second whiteboard ask — "draw a Patroni failover timeline. What happens in each second from the moment the primary dies to the moment clients are writing again?" Senior DEs should be able to sketch this cold, with timings.

Question. Walk through a 12-second Patroni failover. Annotate each phase — detection, election, promotion, routing.

Input.

field	value
Patroni ttl	10s
Patroni loop_wait	5s
HAProxy health check interval	1s
Cluster	1 primary + 2 standbys, ANY 1 sync

Code.

Patroni failover timeline (12 seconds total)
============================================

t = 0.0s  Primary pg1 hard-crashes (kernel panic).
          - HAProxy starts health-check failures on pg1 (every 1s).
          - Patroni daemon on pg1 is dead — cannot renew etcd lease.

t = 0.0–5.0s
          - Patroni on pg2 / pg3 check etcd every loop_wait (5s).
          - Lease is still valid until ttl expires.
          - pg2 / pg3 stay as standbys.

t = 5.0s  pg2 / pg3 see no leader heartbeat; start watching etcd lease.
          - Patroni queries etcd: "is leader still alive?"
          - etcd reports leader key with TTL countdown.

t = 10.0s Leader lease TTL expires in etcd.
          - etcd deletes the leader key.
          - pg2 and pg3 race to become leader (etcd CAS).

t = 10.0–11.0s
          - Each candidate queries every other standby's LSN
            (HTTP REST call to Patroni REST API).
          - pg2 (synchronous standby) has replay_lsn = primary's last commit.
          - pg3 (async) has slightly behind LSN.
          - pg2 wins the race (higher LSN OR explicit sync preference).

t = 11.0–11.5s
          - pg2 acquires the leader key in etcd (CAS write).
          - Patroni on pg2 runs `pg_ctl promote`.
          - pg2 exits recovery, becomes writable.

t = 11.5–12.0s
          - HAProxy health check on pg2 transitions from "replica" to "primary".
          - Routing rules update: writes go to pg2, reads round-robin across pg2 + pg3.
          - Clients reconnect (driver retries the failed connection).

t = 12.0s First write commits on the new primary pg2.
          RTO achieved = 12s from kernel panic to first write committed.

Background (t > 12s):
  - Patroni on pg3 reconfigures pg3 as standby of pg2 (uses pg_rewind if needed).
  - When pg1 returns, Patroni runs pg_rewind on it and re-attaches as standby.

Step-by-step explanation.

1. t = 0s. Hardware fault. The primary's Patroni daemon stops renewing the etcd lease. HAProxy's first health check fails ~1s later.
2. t = 0–5s. Standbys' Patroni daemons check etcd every loop_wait (5s). They see the lease still has TTL, so they wait.
3. t = 5–10s. Standbys watch the lease TTL count down. The next loop_wait cycle, they re-check.
4. t = 10s. Lease TTL expires; etcd deletes the leader key. Standbys see this and start the leader election.
5. t = 10–11s. Each standby calls every other standby's REST API to get its replay_lsn. The candidate with the highest LSN (or the configured sync standby) wins. pg2 was the sync standby with ANY 1 (pg2, pg3) and has the highest LSN.
6. t = 11–11.5s. pg2 writes its identity to etcd's leader key (compare-and-swap; only one node wins). Patroni runs pg_ctl promote on pg2.
7. t = 11.5–12s. HAProxy detects pg2 is now primary (its health check sees pg_is_in_recovery() = false). Application traffic routes to pg2.
8. t = 12s. First write commits. The failover is observable from the application as a ~1 connection drop + retry.

Output.

Phase	Duration	Cumulative
Detection (lease expire)	10 s	10 s
Election (LSN compare)	1 s	11 s
Promotion (pg_ctl promote)	0.5 s	11.5 s
Routing update (HAProxy)	0.5 s	12 s
Total RTO	—	12 s

Rule of thumb. ttl dominates RTO. Drop ttl to 15s for tighter RTO (at the risk of false positives on network blips). Below 10s the false-positive rate becomes painful. 15–30s is the production sweet spot.

Senior interview question on failover and DR

A senior interviewer might frame this as: "Walk me through your end-to-end Postgres HA + DR architecture for a multi-region SaaS. Cover the failover orchestrator, the archive, the DR site, and the runbook for a region-wide outage."

Solution Using Patroni + etcd + WAL-G + cross-region standby

End-to-end Postgres HA + DR architecture
========================================

Region A (primary):
  - pg-a1 (primary)
  - pg-a2 (sync standby, same AZ, remote_apply target)
  - pg-a3 (async standby, different AZ, hot_standby_feedback=on for analytics)
  - etcd cluster across 3 AZs (etcd-a1, etcd-a2, etcd-a3)
  - Patroni on every PG node, scope=pg-cluster
  - HAProxy fronting writes (to /master endpoint) and reads (to /replica endpoint)
  - WAL-G archive_command pushing WAL + base backups to s3://prod-pg/ (region A)
  - WAL-G cross-region copy via S3 replication → s3://prod-pg-dr/ (region B)

Region B (DR):
  - pg-b1 (async standby, restoring from s3://prod-pg-dr/)
  - Manual standby (no Patroni; not part of the same scope)
  - Standby promotes via `pg_ctl promote` only when operator confirms region A is dead

Runbook for region-A outage:
  1. Confirm region A is unreachable (status page, multiple probes).
  2. Stop the application's writes (feature flag, app config).
  3. Promote pg-b1: `pg_ctl promote` (RTO depends on WAL replay lag, typically 30s–2min).
  4. Update application DB endpoint to region B (DNS / config / service mesh).
  5. Re-enable writes; serve from region B.
  6. When region A returns, rebuild it as a new DR site (do not reuse old data — diverged timelines).

Step-by-step trace.

Failure	Detection	Action	RPO	RTO
Primary OS crash (region A)	etcd lease expiry, 30s	Patroni promotes pg-a2	0 (sync ack)	12–30s
AZ outage (region A AZ1)	etcd loses majority in AZ1; pg-a2 / pg-a3 still healthy	Patroni promotes from another AZ	0 (if sync standby in surviving AZ)	30s
Network partition (split brain risk)	DCS quorum lost on dead side; STONITH via cloud API	New primary in majority partition; old primary fenced	0	30s
Region A full outage	Operator confirms via status page	Manual promote of pg-b1 in region B; DNS cutover	seconds (S3 replication lag)	5–15 min
Accidental DROP TABLE at 11:42	Application errors	WAL-G PITR to 11:41 (from region B archive if A is also down)	<1 min	30 min – 2 hr

After this 5-axis pass, the architecture has named runbooks for every failure mode. The remaining 5% — DNS TTLs, application retry behaviour, observability — is decided per-service.

Output:

Layer	Tool	Function
In-region HA	Patroni + etcd	0 RPO, 30s RTO automated failover
Same-region read scale	HAProxy + 2 standbys	Reads on standbys via target_session_attrs
WAL archive	WAL-G to S3	PITR + DR (continuous WAL push)
Cross-region DR	S3 cross-region replication + manual standby	Survives full region outage
Split-brain prevention	DCS quorum + STONITH + sync replication	Defence in depth
RPO target	0 (in-region) / seconds (cross-region)	Tunable per-workload
RTO target	12–30s (in-region) / 5–15 min (cross-region)	Tunable via ttl

Why this works — concept by concept:

• Patroni + etcd for in-region — automated, fast, distributed-consensus-safe. The only Postgres HA orchestrator with both maturity and a community in 2026.
• WAL-G for archive — continuous WAL push to S3 gives both PITR (replay to target time) and DR (warm-start a region-B standby from the archive).
• Cross-region S3 replication — the archive itself replicates to region B asynchronously. RPO for region-wide failure ≈ S3 replication lag (typically seconds).
• Manual promote in region B — DR failover is not automated. The risk of a false-positive cross-region failover (network partition between regions) is too high. Humans confirm; humans execute.
• STONITH is the last-resort fence — even with DCS quorum, in rare cases the dead primary might be alive and unreachable. A cloud-API call to forcibly kill it ensures it cannot accept writes after the partition heals.
• Quorum sync — ANY 1 (pg-a2, pg-a3) means in-region commits are 0 RPO as long as at least one standby is alive. Single-standby naming would have been brittle.
• Cost — three nodes in region A + one in region B + an etcd cluster + an S3 archive = ~4× a single-primary baseline. The cost is justified by 30s RTO and 0 RPO; downgrade by removing the DR standby if your RPO can tolerate region-loss data loss.

SQL
Topic — SQL
Postgres HA & failover design problems

Practice →

ETL
Topic — ETL
DR & cross-region replication patterns

Practice →

---

Cheat sheet — Postgres HA recipes

• WAL is the durable truth. Every change writes WAL before any data page is touched; replication is just other servers tailing the WAL. Crash recovery, PITR, and replication are all consequences of having a sequential change log.
• pg_basebackup 4-command standby setup. (1) On primary: SELECT pg_create_physical_replication_slot('name'). (2) On standby: pg_basebackup -h primary -U replicator -D $PGDATA -X stream -S name -R --compress=server-zstd:9. (3) Inspect postgresql.auto.conf + standby.signal. (4) systemctl start postgresql and verify with SELECT pg_is_in_recovery().
• wal_level defaults. replica for HA only; logical only when CDC / logical replication is in scope (extra WAL cost). minimal is single-node only.
• synchronous_commit ladder. off (lossy, ~tens of ms RPO) → on (default, async, primary fsync only) → remote_write (sync to standby OS recv) → remote_apply (sync to standby applied; 0 RPO + read-after-write).
• synchronous_standby_names patterns. Use ANY N (a, b, c) for quorum sync (PG10+). Avoid the naive single-name pattern — it makes every primary commit block when that one standby is down.
• hot_standby_feedback = on rule. On exactly the standby that runs long analytics queries. Off elsewhere. Cost: vacuum on primary delayed while queries run.
• max_standby_streaming_delay. Default 30s. Raise to 1h on the analytics standby. Avoid -1 (infinite) — it masks stuck standbys.
• Replication slots. Use named physical slots for every production standby. Monitor pg_replication_slots.wal_status and slot lag in bytes. Drop a slot only after confirming the standby is gone.
• WAL-G as the modern archive. archive_command = 'wal-g wal-push %p' + daily wal-g backup-push. Restores via wal-g backup-fetch LATEST (faster than pg_basebackup for large clusters).
• Patroni minimum config. ttl: 30, loop_wait: 10, synchronous_mode: true, synchronous_standby_names: 'ANY 1 (b, c)', archive_command: 'wal-g wal-push %p', use_pg_rewind: true. Run etcd on 3 AZs.
• STONITH is non-negotiable. Always pair Patroni with an external fencing mechanism (cloud API, IPMI, K8s API) — DCS quorum alone is not enough in edge cases.
• Failover budget (12s target). Detection 10s (ttl) + election 1s + promotion 0.5s + routing 0.5s = 12s RTO. Tighten ttl only if your network has < 1s false-positive rate.
• PITR drill. Practice twice a year. The runbook is: pause Patroni → wipe data dir → wal-g backup-fetch LATEST → set recovery_target_time + recovery.signal → start PG → verify → reinit standbys → resume Patroni.
• pg_rewind for fast re-join. After failover, the old primary's data dir has diverged. use_pg_rewind: true in Patroni's bootstrap config rewrites only the changed pages — minutes vs hours vs full re-clone.
• Routing reads via target_session_attrs=read-only. PG10+ libpq feature; round-robins across standbys, skips the primary. Built-in, no proxy required for simple cases.
• Monitor lag in bytes AND seconds. pg_stat_replication.replay_lag (interval) catches slow apply. pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) (bytes) catches stuck WAL on the wire.
• Read-after-write consistency on standby. Only guaranteed with synchronous_commit = remote_apply to that specific standby. Otherwise the standby may lag the primary's commit.
• Cross-region DR shape. Async standby in region B + cross-region S3 replication of the WAL archive. Promote manually only when region A is confirmed dead. Acceptable RPO depends on S3 replication lag (typically seconds).

Frequently asked questions

Streaming replication vs logical replication — which one should I use?

Use postgres streaming replication (physical replication) for high-availability, read replicas, and disaster recovery — it ships the WAL byte-for-byte from primary to standby, replays it into identical data files, and supports hot standby reads. Use logical replication (PG10+, wal_level = logical, publications + subscriptions) when you need to replicate a subset of tables, span major Postgres versions, support multi-master or fan-out topologies, or feed CDC tools like Debezium / pglogical. Streaming replication is faster, more bandwidth-efficient, and simpler operationally. Logical replication is more flexible but adds 5–15% WAL overhead and is slower per row. For HA, streaming is the default; for CDC, use logical alongside streaming on the same cluster.

synchronous_commit levels — what's the actual trade-off?

synchronous_commit is a four-rung durability ladder. off — commit returns before local WAL fsync (~tens of ms RPO, fastest). on (default) — commit waits for primary fsync only; standby is async (10–50 ms typical lag). remote_write — commit waits for primary fsync + standby OS-receive (0 RPO if standby's OS doesn't also crash). remote_apply — commit waits for primary fsync + standby actually-applied (0 RPO + read-after-write consistency on that standby). Each rung up adds ~1 RTT to commit latency, typically 1–10 ms in-region. For financial transactions, use remote_apply with ANY 1 (a, b) quorum sync. For bulk loads and audit logs, downgrade per-session with SET LOCAL synchronous_commit = off.

How do I prevent or reduce postgres replication lag?

First, diagnose by reading pg_stat_replication on the primary — look at sent_lsn, write_lsn, flush_lsn, replay_lsn and the lag intervals (write_lag, flush_lag, replay_lag). The four common causes: (1) network bandwidth — bytes-behind grows but seconds-behind doesn't catch up; fix with bigger pipes or compression on the WAL stream; (2) standby CPU for replay — seconds-behind grows, bytes-behind doesn't; enable parallel WAL apply in PG16+; (3) replication conflict when hot_standby_feedback = off — long queries cancelled and apply continues, but during the conflict apply is paused; either turn feedback on or shorten conflict-prone queries; (4) slow disk on standby — pg_stat_io on the standby shows high WAL-write latency. The hard rule: monitor both bytes and seconds, alert when either crosses your threshold, and have a runbook for each cause.

pg_basebackup vs WAL-G — when do I use which?

Use pg_basebackup to provision the first standby of a small-to-medium cluster — it ships with Postgres, requires no extra infra, streams a consistent snapshot of the data dir, and is the simplest way to bootstrap a node. Use walg (WAL-G) for everything ongoing: continuous WAL archive to S3 (archive_command = 'wal-g wal-push %p'), daily / hourly base backups to S3 (wal-g backup-push), PITR (wal-g backup-fetch LATEST + restore_command = 'wal-g wal-fetch %f %p'), and cross-region DR via S3 replication. WAL-G is faster than pg_basebackup for multi-TB clusters because S3 has more aggregate bandwidth than a single primary's NIC, supports delta backups, parallel uploads, and encryption. Modern 2026 pattern: pg_basebackup for the very first standby; WAL-G for every backup and archive thereafter.

Patroni vs Stolon vs pg_auto_failover — which orchestrator?

Patroni is the 2026 default — Python daemon, uses etcd / Consul / Kubernetes leases as DCS, mature feature set, used by Zalando / CrunchyData / Stackgres / many cloud Postgres operators. Pick Patroni unless you have a specific reason not to. Stolon is the Go alternative — embedded DCS option, similar feature set, smaller community; pick it if your team is more comfortable with Go than Python. pg_auto_failover is by Citus (now Microsoft) — uses a separate monitor node instead of a full DCS, simpler ops, less proven at large scale; pick it for small clusters (2–3 nodes) where the Patroni learning curve is unwelcome. All three solve the same problem (postgres failover automation); Patroni has the largest installed base and the deepest documentation.

How does Postgres handle automatic failover with Patroni?

Patroni runs as a sidecar on every Postgres node and holds a leader lease in a distributed configuration store (etcd / Consul / K8s). The primary renews the lease every loop_wait seconds (default 10s). If the primary crashes and stops renewing, the lease expires after ttl seconds (default 30s). Standbys' Patroni daemons detect the expired lease, query each other's pg_last_wal_replay_lsn() via Patroni's REST API, and the standby with the highest LSN (or the configured sync standby) writes a CAS update to the DCS to claim leadership — only one wins. The winner runs pg_ctl promote, exits recovery, and starts accepting writes. HAProxy / Patroni REST endpoints update routing, clients reconnect, and writes resume. Total RTO is typically 12–30 seconds. Split-brain is prevented via DCS quorum (a primary that can't see a majority of DCS nodes demotes itself), STONITH (forcibly kills the old primary via cloud API), and synchronous replication (only a sync-ack'd standby can be promoted, so no committed transactions are lost).

Practice on PipeCode

• Drill the SQL practice library → for the schema, transaction, and isolation probes that touch replication semantics.
• Rehearse on the ETL practice library → for the cross-system durability and CDC patterns that surround Postgres replication.
• Sharpen the query-tuning axis with the optimization library → for the index-bloat, vacuum, and hot-standby-feedback trade-offs.
• For the full picture, browse Pipecode.ai → — Leetcode for data engineering with 450+ DE-focused problems graded against real-time scoring.

Lock in Postgres HA muscle memory

Postgres docs explain WAL. PipeCode drills explain the decision — when synchronous_commit is worth the latency, when hot_standby_feedback saves a long-running analytics query, when Patroni's 12-second failover wins. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →