Managed PostgreSQL: Control Plane and Data-Plane Topology

What I'm building and the one decision that shapes it

Every customer gets their own Postgres instance — one process, one tenant, never a shared server. That's the first and most consequential decision. Multi-tenanting inside a single Postgres (schemas or databases per tenant) would save memory, but it destroys the product: you can't give tenants their own extensions, their own versions, their own restore timelines, or real resource isolation, and one tenant's runaway query becomes your incident. Every serious DBaaS (RDS, Cloud SQL) landed on instance-per-tenant. So the problem becomes: run 300,000 small Postgres clusters cheaply and safely on shared hosts, with a control plane that provisions, heals, backs up, and restores them without humans in the loop.

The second shaping decision: the fleet is divided into cells — self-contained failure domains of roughly 50 hosts and ~2,000 database instances, each with its own consensus store, proxy fleet, and backup workers. A bug or an etcd outage takes down one cell, not the platform. I'll justify the cell size below.

Assumptions

Stating these rather than asking:

Single cloud provider, multiple regions; each customer database lives in one region. Cross-region DR is out of scope for v1 (it's an evolution item at the end).
The external block-storage layer gives me network-attached volumes with per-volume IOPS/throughput limits, snapshots, and the ability to detach/attach across hosts in the same zone. Think EBS-shaped.
The external compute orchestrator can schedule containers onto specific hosts with cgroup CPU/memory/IO limits, and can hard-kill a container on demand. Think Kubernetes-shaped, but I only rely on that narrow interface.
Plans: Free/Hobby (single node, no failover SLA, 7-day PITR), Standard (HA pair, <60s failover, 7-day PITR), Pro (HA with 2 standbys + read replicas, 35-day PITR).
"Stable connection URL" means the exact same hostname and port work before, during, and after failover, with no client-side changes and no DNS re-resolution required.

Scale estimates (rough, and they matter)

300k databases. Assume the classic long tail: 90% are under 10 GB (median maybe 1–2 GB — hobby projects, staging environments), ~9% are 10–500 GB, and the top ~1% (3,000 databases) average several hundred GB, with a few hundred whales at 1–4 TB. Back-of-envelope: tail ≈ 1 PB, middle ≈ 1 PB, whales ≈ 0.5–1 PB → call it **3 PB of primary data**, ×2 for HA standbys, plus backups in object storage.
Packing. A small instance needs 300–500 MB RSS (shared_buffers + backends + WAL machinery). On a 64 vCPU / 512 GB host we can pack ~100–150 small instances, far fewer mid-size ones. That puts the fleet at **3,000–5,000 hosts** → 60–100 cells per region-scale deployment.
Provisioning. Several thousand creates/day is ~1 every 20–30 seconds average. Trivial steady-state; the real load is bursts (a platform outage ends and 500 queued creates land at once) and restores, which are creates with terabytes attached.
Failover math. The 60-second promise decomposes as: detect (~~30s of missed heartbeats — can't go much lower without flapping), promote (~~2–5s), reroute (~1s), client reconnect (client-dependent). That budget dictates the heartbeat/TTL numbers later.
Consensus-store load. Each Postgres cluster's HA agent renews a leader lease every ~10s. 300k clusters against one consensus store would be ~30k writes/s of pure heartbeat — untenable and a global blast radius. 2,000 clusters per cell is ~200 writes/s per cell store. That's the calculation that sizes the cells.

Architecture

flowchart TB
    subgraph Global["Global control plane (per region)"]
        API[Public API / dashboard]
        CPDB[(Control-plane DB<br/>desired + observed state)]
        REC[Reconcilers<br/>provision / repair / backup / verify]
        PLACE[Placement service]
        API --> CPDB
        REC --> CPDB
        PLACE --> CPDB
    end

    subgraph Cell1["Cell (≈50 hosts, ≈2,000 DB clusters)"]
        PROXY[Proxy fleet<br/>L4 SNI routing]
        ETCD[(Consensus store<br/>etcd, 5 nodes across AZs)]
        subgraph HostA["Host (AZ-a)"]
            P1[Tenant primary<br/>Postgres + Patroni]
            PX[~100 more tenants]
        end
        subgraph HostB["Host (AZ-b)"]
            S1[Tenant sync standby<br/>Postgres + Patroni]
        end
        PROXY -- watches routes --> ETCD
        P1 -- leader lease --> ETCD
        S1 -- health/state --> ETCD
        P1 == streaming WAL ==> S1
    end

    CLIENT[Customer app] -- "db-abc123.pg.platform.com:5432 (TLS+SNI)" --> PROXY
    PROXY --> P1
    REC -- desired state / commands --> ETCD
    P1 -- WAL archive + base backups --> S3[(Object store: S3<br/>pgBackRest repos)]
    REC -.restore tests.-> VERIFY[Verification cell]
    VERIFY --> S3

Components

Global control plane (per region). A REST API, a control-plane database (a plain HA Postgres cluster we run ourselves, on dedicated hardware, deliberately not hosted on the product — the control plane must survive the product being down), and a set of reconcilers. Everything is desired-state: the API writes "database X should exist with plan P in region R," reconcilers converge reality toward it and record observed state. Reconciliation, not workflows-only, because with 300k databases something is always half-broken; a system that only acts on explicit requests rots.

Cells. Each cell contains ~50 hosts spread across 3 AZs, one 5-node leader-election/consensus store (etcd), a fleet of stateless proxies, and local backup/repair workers. Cells are the unit of blast radius, of capacity planning, and of etcd scalability.

Per-database HA. Each customer database is a Patroni-managed cluster: every Postgres container runs alongside an HA orchestrator agent for Postgres (Patroni) that uses the cell's etcd for leader election and stores cluster state there. I chose Patroni over building failover logic into the control plane because failover must work when the control plane is down or partitioned — the decision has to be made by a consensus quorum next to the data, not by a distant brain. (Rejected: repmgr — weaker fencing story, no DCS-based leader lease; a homegrown agent — this is the highest-stakes code in the product and Patroni has a decade of scar tissue we don't.)

Proxy fleet. Customers never connect to a Postgres host directly. More below.

Backups. A Postgres backup/WAL-archiving tool (pgBackRest) ships base backups and every WAL segment to an object store (S3), one repository per database.

Provisioning flow

POST /v1/databases → returns immediately with status: creating and the future connection URL (we can mint the hostname and credentials before any byte is provisioned — this is what makes URLs stable, see below).

The reconciler then:

Placement. The placement service picks a cell with headroom, then hosts: primary in AZ-a, standby in AZ-b, anti-affinity so no two members of one cluster share a host or AZ. Scoring uses observed utilization (P95 CPU, memory RSS, volume IOPS), not just requested size — because some tenants run at 100% forever, and we must never bet on averages for memory (more under Isolation).
Storage. Ask the block-storage layer for volumes with an IOPS/throughput cap matching the plan.
Compute. Ask the orchestrator to start the Postgres+Patroni container pinned to the chosen hosts, with cgroup limits.
Bootstrap. Patroni initializes the cluster, writes its state into the cell's etcd, brings up the standby via a base backup from the primary.
Backups armed. pgBackRest repo created in S3, archive_command wired, and — this is a hard gate — the first base backup must complete before we flip status to available. A database with no restorable backup isn't "available," it's a liability. (We tried the tempting shortcut in the design: mark ready first, back up async. Rejected: a disk failure in the gap means total data loss on a product whose whole pitch is "we own durability.")
Routing + creds. Write the route (db-abc123 → primary endpoint) into etcd; store the generated password in the secrets manager; mint a per-database server TLS cert.
Status → available. Target: under 2 minutes for an empty database; the base backup of an empty cluster is seconds.

Idempotent throughout — every step keys on the database ID, so a crashed reconciler retries safely.

The stable connection URL, and why it's a proxy

Each database gets db-abc123.pg.<region>.platform.com:5432. That DNS name resolves to the cell's proxy fleet (a handful of anycast/NLB IPs) and never changes. The proxy terminates nothing at the SQL level — it's an L4 routing proxy that reads the TLS SNI (the per-database hostname) from the ClientHello and pipes bytes to whatever etcd says is that database's current primary. Proxies watch etcd, so a route change propagates in milliseconds.

Why not the alternatives:

DNS re-pointing on failover — rejected. Client resolvers and JVM connection pools cache DNS with hostile TTLs; you don't control the client. Failover time becomes "whenever the customer's resolver feels like it."
Floating VIP — rejected. Doesn't cross AZs on most clouds, and I want the standby in another AZ.
A full SQL-aware proxy / connection pooler (PgBouncer) in the path — rejected as the routing layer. Pooling changes session semantics (prepared statements, advisory locks, SET), and I don't want to break customer apps by default. We offer pooling as an opt-in endpoint later; the mandatory path stays a dumb, fast L4 pipe (a thin Go proxy, ~sub-ms added latency; HAProxy can't do per-SNI dynamic routing across 300k backends cleanly, which is why every platform in this space ends up writing this component).

The proxy also gives us draining and fencing for free: on failover, proxies drop connections to the old primary the instant the route flips (clients get a clean connection reset and reconnect to the same URL, landing on the new primary), and even a zombie old primary receives no customer traffic because no customer can reach a Postgres host except through the proxy. Network policy enforces that: port 5432 on database hosts accepts connections only from the proxy fleet and cluster peers.

Replication mode: what each plan actually promises

This is where the data-loss contract lives, so I'll be explicit.

Standard/Pro (HA plans): synchronous replication, quorum-style. Pro runs synchronous_standby_names = ANY 1 (s1, s2) — commit waits for flush on at least one of two standbys. RPO on failover is zero for acknowledged commits. Cost: one cross-AZ round trip per commit, ~1–2 ms — acceptable for the product tier that advertises durability. The quorum form matters: with a single sync standby (Standard tier), that standby dying stalls all writes until Patroni reacts; Patroni handles this by dropping to async temporarily and flagging the cluster degraded — we surface that as an event because during that window the RPO guarantee is suspended. That honesty is the price of a two-node Standard tier; Pro's ANY-1-of-2 doesn't have the problem unless two standbys die.

Why not async for HA plans? Async failover means acknowledged transactions can vanish — RPO equals replication lag, typically milliseconds but unbounded under load (a bulk load can push lag to minutes). Losing customer-acknowledged commits during our automated failover is the one thing a managed database must not do silently. We rejected async-by-default, but we expose it as an explicit per-database toggle for latency-obsessed customers, with the data-loss implication in the console in plain words.

Free/Hobby: single node, no standby. On host death: detach the block volume, reattach to a healthy host, restart Postgres, crash-recover. Recovery in minutes, RPO zero if the volume survived; if the volume died, restore from backup with RPO up to ~1 minute (WAL archiving cadence, below). We considered giving free tier an async standby — rejected on cost: it doubles the fleet for tenants paying nothing, and volume-reattach recovery is good enough for the tier's promise.

Failover: detection, promotion, fencing, split-brain

Patroni settings tuned to the 60-second budget: ttl=30s, loop_wait=10s, retry_timeout=10s.

sequenceDiagram
    participant P as Old primary (Patroni)
    participant E as etcd (cell)
    participant S as Sync standby (Patroni)
    participant PR as Proxy fleet
    participant C as Client
    Note over P: Host dies / partitions
    P--xE: leader lease not renewed
    Note over E: lease expires after TTL (≤30s)
    S->>E: observes expiry, wins election<br/>(only sync standby is eligible)
    S->>S: pg_ctl promote (~2s)
    S->>E: writes new route for db-abc123
    E-->>PR: watch fires (ms)
    PR->>PR: kill connections to old primary
    C->>PR: reconnect, same URL
    PR->>S: routed to new primary
    Note over P: If merely partitioned:<br/>can't renew lease → self-demotes;<br/>watchdog kills Postgres if agent hangs

Worst case ≈ 30s detection + 5s promotion + ~1s rerouting: inside 60s with margin for client reconnect/backoff.

Split-brain prevention is layered, because any single fence fails eventually:

Lease-based demotion. A primary that can't renew its etcd lease demotes itself to read-only before the lease TTL lets anyone else promote. This is Patroni's core invariant.
Watchdog. If the Patroni process itself hangs (so it can neither renew nor demote), a kernel watchdog timer (softdog) it was petting expires and hard-resets the node. This closes the "agent frozen, Postgres happily accepting writes" hole.
Proxy fencing. Even a zombie primary gets zero customer traffic — routes live in etcd, the same quorum that elected the new leader, so the routing view and the election can't disagree. Direct connections are blocked by network policy.
Control-plane hard kill. The repair reconciler notices the stale member and tells the compute orchestrator to kill the container and, for belt-and-suspenders on shared-nothing violations, detach its volume.

Only sync standbys are eligible for automatic promotion (Patroni enforces this) — promoting a lagging async replica would silently discard commits, which we just promised not to do.

Rejoin. The old primary comes back, discovers it's on a divergent timeline, and Patroni runs pg_rewind against the new primary to rejoin as a standby — minutes, not the hours a full re-clone would take on a big database. If rewind fails (it sometimes does), the repair reconciler rebuilds the standby from the latest pgBackRest backup instead.

Planned switchovers (host maintenance, resizes, minor upgrades) go through the control plane calling Patroni's switchover API: checkpoint, brief pause, promote, reroute — a few seconds of connection blip instead of 30s of detection. Same fencing path, so it's also our continuously-exercised failover test.

Backups and point-in-time recovery

Two data streams per database, both to S3 via pgBackRest, encrypted per-tenant:

WAL archiving, continuously. archive_command pushes each 16 MB WAL segment as it fills; archive_timeout=60s forces a segment switch on quiet databases so the archive never trails by more than a minute. That 60s is the single-node RPO floor. For HA plans it barely matters (the sync standby holds unarchived WAL), but we keep it uniform. We monitor archive lag as a first-class SLO — a database whose WAL isn't archiving is a sev-2 even though the customer sees nothing wrong, because its PITR contract is silently eroding.
Base backups. Weekly full + daily differential for normal databases. For whales (roughly >500 GB), pgBackRest fulls take hours and hammer IOPS the tenant is already saturating — so whales get block-storage snapshots as the base (crash-consistent, incremental at the block layer, minutes regardless of size, bracketed by pg_backup_start/stop so WAL replay makes them consistent) with the same WAL archive on top. Two base-backup mechanisms is real operational cost; we pay it because the alternative is either hammering whales nightly or stretching their restore window.

PITR mechanics. "Restore to 2026-08-19 14:03:22" = provision a new database (same flow as create), lay down the last base backup before T, set recovery_target_time = T, replay WAL from the archive, promote at the target, hand back a fresh URL. Restores never overwrite the original in place — the original is evidence, and in-place restore turns a bad timestamp guess into a second disaster. Retention: WAL + backups kept 7 or 35 days per plan; expiry is pgBackRest retention plus a control-plane sweep.

Restore time estimate (framed as such): tail databases in minutes; a 4 TB whale from snapshot in tens of minutes plus WAL replay — replay speed is the whale restore bottleneck, which is one more reason whales get daily snapshot bases (short WAL chains to replay).

Proving restores work. A backup that's never been restored is a hope, not a backup. A dedicated verification cell per region does nothing but restores:

Every base backup gets an automatic pgbackrest verify (checksum the repo) — cheap, catches bit rot and truncated uploads.
A rotating ~1% daily sample gets a full PITR to a random timestamp inside its window, then pg_amcheck plus a row-count/pg_dump --schema-only sanity pass, then teardown.
Scheduling guarantees every database gets a full restore test at least every 90 days, whales included (whale restore tests are also how we keep our restore-time numbers honest instead of estimated).
Each database carries a customer-visible last_verified_restore timestamp. If verification fails, we page ourselves and immediately take a fresh base backup — the failure means the PITR chain has a hole right now.

Read replicas and replication lag

Read replicas are asynchronous streaming standbys with their own stable hostname (db-abc123-replica-1...), never promotion candidates, and on Pro they cascade from the sync standby rather than the primary so a fleet of replicas doesn't multiply the primary's WAL-sender load.

Lag handling, in order of what actually goes wrong:

Surface it. replica_lag_bytes/seconds in the API and dashboard. We can't hide async semantics, so we instrument them.
Protect the primary from the replica. Replication slots retain WAL for a slow replica — unbounded, that fills the primary's disk, which is a worse outage than a stale replica. max_slot_wal_keep_size caps retention (order of 10–50 GB by plan); a replica that blows past it gets invalidated and the repair reconciler rebuilds it from the S3 backup, fetching missed WAL from the archive instead of the primary. The tradeoff is deliberate: sacrifice the replica to save the primary, every time.
Query conflicts. Long replica reads vs. vacuum: default hot_standby_feedback=off (bloat on the primary is the greater evil), a modest max_standby_streaming_delay, and the knob exposed for customers who prefer fewer cancellations.

Tenant isolation on shared hosts

The threat model is two-headed: noisy neighbors (the common case) and actual security isolation (the table stakes).

Security: one Postgres process per tenant in its own container, dedicated volume, per-tenant encryption keys for backups, per-database TLS certs, superuser withheld (customers get a CREATEDB/CREATEROLE-ish role; superuser plus shared kernel is an escape-hatch factory), extensions from an allowlist only. Containers with seccomp/AppArmor; the sensitive-tenant evolution path is microVMs (Firecracker-class) at ~10–20% density cost, not needed for v1.

Noisy neighbors, resource by resource:

Memory: never oversubscribed. Postgres under memory pressure means the OOM killer executing databases at random. Sum of memory limits ≤ host RAM, full stop. This is the binding constraint in packing, and it's why the "runs at 100% continuously" customer is fine — their 100% is inside a limit we provisioned for.
CPU: oversubscribed ~2–3× on shared tiers, cgroup CPU quota as the ceiling, and the placement service uses observed P95 utilization so ten flat-out tenants don't land on one host. Pro tiers get dedicated (unshared) CPU allocations.
Disk IO: the real noisy-neighbor vector, capped at the volume. Per-volume IOPS/throughput limits from the block-storage layer mean a saturating tenant queues against their own cap, not the host's. This is precisely why network-attached volumes beat local NVMe here, despite local NVMe's better raw latency — local disk would make both IO isolation and volume-reattach recovery our problem. (Local NVMe stays on the menu as a premium dedicated-host tier later.)
Connections/WAL: per-instance max_connections by plan; archive uploads rate-limited per tenant so one bulk load can't starve the cell's uplink.

The whale problem. A few hundred customers at 1–4 TB dominate storage and IOPS, and they break every average the packing logic relies on. Don't fight it — segregate it: whales get dedicated cells with at most a handful of tenants per host (or the host to themselves), no CPU oversubscription, snapshot-based backups, and their own capacity planning. A whale in a shared cell distorts bin-packing, monopolizes the backup workers' window, and turns its host's every maintenance into a negotiated event. The graduation is automated: the control plane watches size/IOPS trends and migrates a growing tenant to a whale cell via standby-build-then-switchover — a few seconds of connection blip, no customer action, same URL (the URL is the proxy's, not the host's — this is the stable-URL decision paying rent a second time).

Control-plane data model and API

Control-plane Postgres, the interesting tables (abridged):

databases   (id, org_id, plan, pg_version, region, cell_id, status,
             hostname, sync_mode, pitr_window_days, created_at)
clusters    (database_id, patroni_scope, desired_topology jsonb,
             observed_topology jsonb, last_reconciled_at)
instances   (id, database_id, role,           -- primary|sync_standby|replica
             host_id, volume_id, az, status, lag_bytes)
hosts       (id, cell_id, az, cpu_alloc, mem_alloc, observed_p95_cpu, status)
cells       (id, region, kind,                -- shared|whale|verification
             etcd_endpoints, proxy_lb, capacity_score)
backups     (id, database_id, kind,           -- full|diff|snapshot
             s3_prefix, started_at, finished_at, verified_at, status)
restore_tests (id, database_id, target_time, result, checked_at)
events      (id, database_id, type, payload jsonb, at)  -- customer-visible audit

API surface (REST, everything async with status polling / webhooks):

POST   /v1/databases                      create
GET    /v1/databases/{id}                 status, endpoints, lag, last_verified_restore
PATCH  /v1/databases/{id}                 resize plan / toggle sync_mode
DELETE /v1/databases/{id}                 soft-delete; backups kept 7 days after
POST   /v1/databases/{id}/replicas        add read replica
POST   /v1/databases/{id}/restores        {target_time} → new database id
POST   /v1/databases/{id}/failover        customer-triggered switchover (their fire drill)
GET    /v1/databases/{id}/backups         list, with verification timestamps

Split-brain of the control plane itself is prevented the boring way: reconcilers take per-database advisory locks in the control-plane DB, and every mutation to a cell goes through etcd transactions conditioned on current state.

Version upgrades

Minor versions (security patches): rolling — upgrade the standby, planned switchover, upgrade the old primary. Seconds of blip, fleet-wide waves per cell, automated.
Major versions: customer-initiated with a deadline (we force-upgrade EOL versions after long notice). Mechanism: build a new cluster on the target version, logical replication from the old, then a cutover that flips the proxy route — seconds of downtime and instant rollback (old cluster intact) at the cost of double resources during migration and logical replication's caveats (sequences, DDL freeze during sync). In-place pg_upgrade --link is the fallback for whales where double storage is impractical; it's minutes of hard downtime and no easy rollback, which is why it's the fallback and not the default.

Failure modes, tradeoffs, evolution

What breaks and what happens: host death → Patroni failover (<60s) or volume-reattach (free tier, minutes). AZ loss → sync standbys in other AZs promote; the cell's etcd (5 nodes / 3 AZs) keeps quorum; proxies are stateless behind an LB. Cell etcd quorum loss → databases keep serving (Patroni can't elect, so topology freezes; primaries stay primary) but failover is unavailable in that cell until quorum returns — degraded HA, page immediately. Control-plane outage → running databases and automatic failovers are untouched (that's why failover authority lives in the cells); only provisioning/restores queue. S3 outage → serving unaffected; WAL archiving buffers on local disk with alerts, PITR window stops advancing. Proxy fleet is the scary shared component in each cell — it's stateless, horizontally scaled, and deploys canary-by-cell, because a bad proxy release is the closest thing this design has to a cell-wide customer outage.

Tradeoffs I'm consciously making: 1–2 ms commit latency and 2× instance cost for sync HA (zero-RPO failover is the product). An extra network hop on every query for stable URLs and fencing (sub-ms; DNS games would be cheaper and worse). Two base-backup mechanisms (whale snapshots + pgBackRest) for one restore contract. Cells add per-cell overhead (5 etcd nodes each) and cross-cell migration machinery — the price of bounded blast radius, and worth it at 300k databases.

Evolution: cross-region async replicas and region-level DR for Pro; opt-in pooled endpoints (PgBouncer) for serverless-style clients; microVM isolation as a compliance tier; storage/compute disaggregation (Aurora/Neon-style page servers) if whale economics demand it — deliberately not v1, because vanilla Postgres semantics on plain volumes is the most debuggable system we can operate at this fleet size, and the operator's 3 a.m. experience is part of the product.

Interview follow-ups (Codex as interviewer)

The original design above is unchanged. Codex read the prompt and the design, then asked the follow-ups below; answers and any design revisions follow each question.

Round 1

Q1 (Codex): When a cell loses etcd quorum but its primaries remain reachable, Patroni cannot renew their 30-second leases and should demote them, yet you claim they keep serving; give the exact partition timeline and mechanism that prevents both split-brain and a cell-wide write outage.

You caught a real contradiction. Default Patroni demotes a primary that can't renew its DCS lease — so with my stated config, etcd quorum loss means every primary in the cell goes read-only within 30 seconds. That's a cell-wide write outage I claimed wouldn't happen. The fix is Patroni's DCS failsafe mode (failsafe_mode: true, Patroni 3.0+), and here's the timeline: t0, etcd quorum lost. t0 to t0+TTL, each primary fails lease renewal and invokes failsafe: it polls every other member of its own cluster over the Patroni REST API. If all members respond — confirming the primary can see them and none is promoting — it keeps running as leader, re-verifying every loop_wait (10s). If it can't reach a member, it demotes conservatively within the TTL.

Split-brain is prevented because promotion requires etcd: no standby can win an election while quorum is down, so at most one writable primary exists per cluster — the incumbent. The failure matrix: etcd down + cluster members mutually reachable → primaries keep serving, failover unavailable (degraded HA, page). etcd down + primary partitioned from its standby → primary demotes, nobody promotes → that cluster is read-only until quorum returns. That last cell is the honest cost: you cannot have both "no split-brain" and "writes continue" when the arbiter and the peer are both unreachable. Proxies keep serving from their last-known in-memory route table when their etcd watch drops — routes are frozen, not erased.

Design change: enable Patroni failsafe_mode: true fleet-wide; proxies explicitly serve stale routes on etcd watch loss; runbook distinguishes "failover frozen" (acceptable minutes) from "primaries demoted" (sev-1).

Q2 (Codex): A database hostname permanently resolves to one cell's proxies, whose routes come from that cell's etcd; trace a whale's cross-cell migration packet by packet and explain how the same URL survives failure or retirement of the old cell without DNS re-resolution or a new global routing dependency.

This also lands. As written, db-abc123 resolves to cell 7's proxy IPs, so a cross-cell whale migration or cell retirement forces a DNS change — exactly the client-cache dependency I rejected. Revision: the proxy fleet becomes regional, not per-cell. DNS for every database in a region resolves to one regional anycast/NLB layer fronting a shared proxy fleet. Proxies maintain a full region route table: watch connections to all ~60–100 cell etcds (route keys only) plus a control-plane-fed db→cell assignment map. That's ~300k entries in a hash map — trivial memory — and the failover fast path is unchanged: route flips still come from the owning cell's etcd in milliseconds.

Packet trace for the whale migration: control plane builds a standby in the whale cell (streaming from the source), then executes switchover. Route entries carry an epoch (monotonic per database, issued by the control plane); during cutover both cells briefly hold a route, highest epoch wins, so a proxy that hears the cells in either order converges correctly. Client → anycast IP → proxy reads SNI → looks up db-abc123 → epoch-max route says (whale cell, new primary IP:port) → splice. Old connections are killed at cutover; clients reconnect to the same hostname and land in the new cell. Old cell retirement is now a control-plane operation with no DNS event.

I'm paying for this: the per-cell proxy was my blast-radius story, and a regional fleet is a region-wide shared component. Mitigation: shard the regional fleet into ~10 proxy groups, each anycast group serving a deterministic hash-slice of hostnames, deployed canary-by-group. A bad proxy release or poisoned route map takes out a tenth of a region's connections, not all of them.

Design change: regional sharded proxy fleet, epoch-numbered routes, db→cell map as a new control-plane-published artifact.

Q3 (Codex): Standard HA has one synchronous standby, but you drop to asynchronous replication when it fails while still claiming zero RPO; if the primary then dies, what precisely is eligible for promotion, which acknowledged commits may disappear, and what availability-versus-durability contract do you actually enforce?

Concede the contract as I stated it. "RPO zero" plus "drop to async when the standby dies" is two promises that contradict during the degraded window, and I hid that in a clause. The honest contract, now explicit: RPO = 0 for any single failure. For a specific double failure — sync standby lost, then primary lost before a replacement standby catches up — RPO is bounded by WAL archive lag, ≤ ~60 seconds in steady state, and only if the primary's volume is also unrecoverable. Precisely what's eligible for promotion: only a standby that held sync status at the moment of failure (Patroni enforces this via the sync-state key in etcd). During the degraded window there is no such standby, so automatic promotion is disabled — the repair path is the free-tier one: detach the primary's volume, reattach, crash-recover (RPO 0, volume survived) or PITR from archive (RPO ≤ archive lag, volume dead). The commits that can vanish are exactly those acknowledged after the degrade event and after the last archived WAL segment, in the volume-loss case only.

The availability-vs-durability choice during degrade is real, so I stop making it unilaterally: default stays "degrade to async, keep accepting writes, emit a customer-visible durability_degraded event, repair reconciler builds a new standby at max priority." Customers who want the other side get a per-database strict toggle (Patroni synchronous_mode_strict): writes stall rather than degrade.

Design change: SLA text rewritten as single-failure-RPO-0 with the double-failure bound stated; strict sync toggle added to PATCH /v1/databases; auto-promotion explicitly disabled while degraded.

Q4 (Codex): PostgreSQL clients commonly send an SSLRequest before any TLS ClientHello, so a pure L4 proxy cannot see SNI until it has already selected a backend and completed PostgreSQL's SSL negotiation; how does your proxy route legacy clients without terminating or understanding the PostgreSQL protocol?

The premise is half right and the conclusion doesn't follow. Yes, the standard Postgres handshake is: client sends an 8-byte SSLRequest, server answers 'S', then the TLS ClientHello flows. But that doesn't hide SNI from the proxy — it just means the proxy must answer the SSLRequest itself. Sequence: accept TCP; read first packet; if SSLRequest, reply 'S'; the client then sends its ClientHello — now the proxy parses SNI from it, picks the backend from the route table, opens a connection to the backend, replays an SSLRequest, waits for the backend's 'S', then splices the buffered ClientHello and everything after. TLS stays end-to-end client↔Postgres (per-database cert); the proxy never terminates it and never sees plaintext. So it's not a pure L4 proxy — it's L4 plus an 8-byte protocol shim, which I should have said outright; it's the component every platform in this space writes. Postgres 17's sslnegotiation=direct clients (ClientHello first) are handled by peeking the first bytes and branching.

The case that genuinely can't be routed is sslmode=disable — no TLS, no SNI. We reject it: TLS is mandatory on the public endpoint, connection strings ship with sslmode=require, and a plaintext startup packet gets a clean Postgres error message saying so.

Design change: document the handshake shim and mandatory-TLS policy explicitly; no mechanism change.

Q5 (Codex): Suppose AI agents burst-create 20,000 databases, use them for minutes, and delete them repeatedly: where are admission control, quotas, idempotency, rapid credential issuance, capacity reservation, cancellation of in-flight provisioning, secure reclamation, and protection against seven days of backup/tombstone accumulation implemented?

My provisioning section sized for thousands/day of durable databases and this workload breaks two of its assumptions: the base-backup-before-available gate is pointless overhead for a database that lives five minutes, and 7-day backup retention on deletes turns churn into an S3/tombstone accumulator. Revision, by mechanism. Admission: per-org token buckets at the API (creates/hour, concurrent-creating, total-DB caps by plan), 429 + Retry-After beyond them, and a weighted-fair provisioning queue so one agent org's burst can't starve interactive creates. Idempotency: POST /v1/databases takes an Idempotency-Key header (agents retry aggressively); internally every reconciler step was already keyed on database ID. Cancellation: DELETE during creating just flips desired state; the reconciler checks desired state between steps and unwinds whatever exists — same idempotent machinery running backwards.

The real product answer is an ephemeral tier. plan: ephemeral means: no HA, PITR off (no repo, no archive_command, no backup gate), a TTL the agent sets at create, hard-delete on expiry or DELETE with hours — not 7 days — of grace. Creates are served from a warm pool: pre-provisioned empty Postgres containers with volumes attached, sitting in dedicated churn cells; a create claims one, mints creds and a route, and returns available in a few seconds instead of two minutes. The pool is refilled by a reconciler against a demand forecast; churn cells are segregated so 20k creates/deletes a day never distort bin-packing or backup windows for long-lived tenants. Reclamation: every volume is encrypted with a per-volume key, so secure delete is crypto-erase (destroy the key), plus cred/cert revocation, route removal, and hostname quarantine — a released hostname isn't reusable for 30 days, so a stale agent connection string can never land on a stranger's new database.

Design change: ephemeral plan + warm pools + churn cells + org-level rate limits + Idempotency-Key + crypto-erase and hostname quarantine.

Q6 (Codex): Design the promised pooled endpoint as an actual product: where do poolers run, what limits isolate 300,000 tenants, which pooling mode supports prepared statements, advisory locks, and session state, how are credentials rotated, and what happens to queued clients and in-flight transactions during failover?

Placement first: one lightweight pooler process per opted-in database, not a shared multi-tenant pooler. A shared PgBouncer serving thousands of tenants is a config-reload storm and a shared-fate component — one tenant's 10k-client stampede degrades everyone. An idle PgBouncer/pgcat process is a few MB; per-tenant processes ride the same isolation story as everything else. They run on dedicated pooler hosts inside the tenant's cell, addressed as db-abc123-pooler.pg.<region>...:5432 through the same SNI proxy — the pooler gets its own route entry and the same failover treatment.

Mode and semantics, honestly: transaction pooling is the default — that's the whole point for serverless/agent clients. Protocol-level prepared statements work (PgBouncer ≥ 1.21 and pgcat track and replay them across server connections); advisory locks, SET session state, LISTEN/NOTIFY, and temp tables do not, and the docs say so in a table, not a footnote. Session pooling is selectable but buys little. Limits: max_client_conn per plan on the client side; the pooler's server-side pool counts against the plan's max_connections, so pooled and direct connections share one budget and a tenant can't double-dip. Auth: the pooler validates client SCRAM credentials via auth_query against the tenant's own database using a dedicated auth role — rotate the password in Postgres and the pooler picks it up on next auth, no config push.

Failover: the pooler watches the same etcd route as the proxy. On a flip it drops its server-side connections; in-flight transactions error exactly as on the direct path — no pretending otherwise, a transaction pooler cannot replay an aborted transaction safely. But queued clients and idle client connections survive: the pooler holds them, opens fresh server connections to the new primary, and drains the queue. That's a concrete product improvement over the direct path, where every client connection resets.

Design change: pooled endpoint moves from "evolution" into the design: per-tenant pooler processes, transaction-mode default, auth_query credential flow, shared max_connections budget, own route entry.

Round 2 — pushback

Q7 (Codex): archive_timeout=60s only forces WAL rotation—it does not bound upload lag during S3 failure or backlog. What mechanism stops acknowledged writes or blocks failover before unarchived WAL exceeds 60 seconds, and what RPO do you actually guarantee if the primary volume is then lost?

Correct — archive_timeout rotates segments; it says nothing about whether archive_command is succeeding. Nothing in the design stops acknowledged writes when archiving falls behind, and that's deliberate: blocking commits on S3 health would make object storage a synchronous dependency of every single-node database — trading a rare, bounded durability gap for a common availability outage, on the tier with the weakest durability promise. So the honest guarantee is: single-node volume-loss RPO equals current archive lag — ≤60s under healthy archiving, unbounded during an archive outage. We already treat archive lag as a first-class SLO; the concrete numbers now: alert at 5 minutes of lag, page at 15, and the customer-facing PITR window visibly stops advancing. For HA plans, failover never depends on the archive at all — the sync standby holds every acknowledged byte of WAL, so promotion loses nothing regardless of archive backlog; the archive-lag exposure is only single-node volume loss or the HA double failure from Q3.

What we buy down is the correlated-failure window: archive_command gets a secondary archive target — a bucket in a different region (different provider for the paranoid tier) that the archiver fails over to when the primary target errors, with pgBackRest repo reconciliation once the primary target recovers. An S3 regional outage then degrades archive latency instead of halting the PITR clock. Local WAL spooling with disk-usage alerts (already in the design) covers the gap where both targets are down; if spool disk approaches full on a single-node database, we take the least-bad action — throttle, then stop accepting writes on that database before we corrupt it — but that's a per-database last resort at ~90% spool, not a fleet policy.

Design change: secondary WAL archive target with automatic failover and repo reconciliation; archive-lag alert/page thresholds (5/15 min) made explicit; SLA wording corrected to "volume-loss RPO = archive lag, ≤60s under healthy archiving."

Q8 (Codex): Epoch-numbered routes in two independent cell etcd clusters are not atomic: some proxies and existing sessions can still reach the old primary while others route to the promoted one. What authoritative fencing step makes the old primary unwritable before the new primary accepts writes during cross-cell migration?

Right — the epoch gives eventual convergence, not a fence. Routing layers can never be the correctness mechanism when they read from two independent stores; the fence has to be on the database itself. Cross-cell migration is a planned switchover, so we control the order, and the cutover is two-phase: fence, then promote. Phase one: the control plane writes a fenced tombstone (with the new epoch) for the tenant into the source cell's etcd — source-cell proxies' watches fire in milliseconds, killing existing sessions and refusing new ones — and simultaneously tells the source Patroni to demote: checkpoint, flush, drop to read-only, confirm. Demotion is the authoritative step: a demoted Postgres rejects writes at the server, no matter which stale route a proxy holds. Phase two, only after the demotion ack and the target standby confirming replay up to the source's final flush LSN: promote in the target cell, publish the epoch-bumped route, clients reconnect and land on the new primary. A proxy with a stale view during the overlap routes a client to a read-only ex-primary or a fenced route — they get an error and reconnect; ugly for seconds, never split-brained.

Failure handling in the window: if the source can't confirm demotion (crashed or hung mid-migration), we do not promote — the migration aborts, the tombstone is retracted, and the source cell keeps ownership; a stuck demotion escalates to the orchestrator hard-killing the container plus volume detach (the same fencing ladder as unplanned failover), and only after that forced fence do we allow promotion. The invariant, stated once: the target primary never accepts its first write until the source is provably unwritable — by demotion ack, or by hard kill and volume detach when the ack won't come.

Design change: cross-cell cutover specified as two-phase — source-cell fenced tombstone + confirmed demotion (or hard-kill + volume detach on timeout) before target promotion; abort-on-timeout leaves ownership with the source cell.

What changed, summarized

Patroni failsafe_mode: true fleet-wide so etcd quorum loss doesn't demote healthy primaries; proxies serve last-known routes on watch loss.
Proxy fleet is now regional and sharded (~10 hash-sliced groups), not per-cell; routes carry per-database epochs; control plane publishes a db→cell map. Fixes cross-cell migration and cell retirement without DNS changes.
Standard-tier durability contract rewritten: RPO 0 for single failures; the double-failure bound (≤ archive lag, volume-loss case) stated explicitly; per-database strict sync toggle added; auto-promotion disabled during the degraded window.
Proxy documented as L4 + 8-byte Postgres handshake shim (answers SSLRequest, then reads SNI); TLS mandatory on the public endpoint.
New ephemeral tier for agent workloads: warm pools in dedicated churn cells, seconds-fast creates, TTL + hard delete, PITR off; per-org rate limits and Idempotency-Key on create; crypto-erase reclamation and 30-day hostname quarantine.
Pooled endpoint promoted from "evolution" to designed feature: per-tenant pooler processes, transaction-mode default, auth_query credential rotation, pooled+direct connections share one max_connections budget.
Secondary WAL archive target with automatic failover; explicit archive-lag alert/page thresholds; volume-loss RPO restated as "= archive lag," not a flat 60s.
Cross-cell cutover is two-phase: source-cell fence tombstone + confirmed demotion (or hard-kill + volume detach) before target promotion.

Industry practice and further reading

Added after the interview rounds: how real systems handle the hard parts above, with verified sources (checked 2026-08-19).

How industry does it

The stable-URL-via-proxy decision is almost exactly what GitHub built for MySQL. Their MySQL High Availability at GitHub post rejects VIP+DNS failover for the same reasons I did — a failed primary that won't release its VIP, VIPs that can't cross datacenters, clients caching DNS — and lands on orchestrator/raft for election, Consul as the route store, and GLB/HAProxy behind an anycast writer hostname that never changes. Swap orchestrator for Patroni, Consul for etcd, and GLB for the SNI proxy and it's the same machine, with their measured failover at 10–13 seconds. The cautionary companion is their October 21, 2018 post-incident analysis: a 43-second cross-country partition, Orchestrator promoted West Coast primaries exactly as configured, both coasts took writes, and reconciling the split cost 24 hours. That incident is why this design keeps failover authority in a quorum next to the data, restricts promotion to sync standbys, and treats cross-cell moves as fenced, planned operations rather than something an automated brain does across a partition.

The Q1 fix I described from memory is real and documented: Patroni's DCS Failsafe Mode has the primary poll every member in the permanent /failsafe key over POST /failsafe when its lease update fails, keep running only if all respond, and demote if any member is unreachable — and replicas treat the failsafe ping itself as proof of a live primary, so they won't race for leadership while etcd is down. The doc matches the failure matrix in my Q1 answer clause for clause.

Industry genuinely splits on the sync-replication question, and it's worth being honest that my design is the stricter camp. Render's own High Availability docs use asynchronous replication, trigger failover after 30 seconds of primary unavailability (my detection budget exactly), keep the same URL after failover, and state plainly that "a small number of the most recent writes" can be lost on automatic failover. PlanetScale's Postgres architecture is the other camp: primary plus two replicas across three AZs with commits confirmed by at least one replica — the ANY-1-of-2 quorum shape I gave the Pro tier. So the design's sync-by-default is a real product choice with a real competitor on each side, not settled practice.

On pooling I diverged from the most visible published system. Supabase built Supavisor, a single multi-tenant Elixir pooler cluster they benchmarked to a million client connections, because every Supabase database gets pooled access as platform plumbing. I chose per-tenant pooler processes instead (Q6) to avoid a shared-fate component — defensible, but Supavisor is proof the shared approach works at scale if you're willing to build a distributed system to do it. Their motivation section is also the best short writeup of why Postgres connections are expensive enough to make poolers a product requirement.

The backup stack is the least controversial part: pgBackRest with weekly fulls plus continuous WAL archiving is precisely what Crunchy Data — who employ pgBackRest's maintainers and run it under Crunchy Bridge — recommend in their introduction to Postgres backups, down to the delta-restore trick my repair reconciler uses to rebuild failed standbys. The road I deliberately didn't take is Neon's: Architecture decisions in Neon explains their storage/compute split — safekeepers running consensus for WAL durability, pageservers materializing pages on demand, old page versions retained in S3 so PITR and branching become cheap reads instead of restores. That's the "storage disaggregation if whale economics demand it" evolution item made concrete, and their post is candid about the price: you've replaced Postgres's storage layer with a distributed system you now operate.

One more data point that the control plane is the product: Fly.io shipped "This Is Not Managed Postgres" as an actual docs page — you run your own upgrades, off-site backups, and monitoring — and spent years absorbing the support cost of that stance before launching Managed Postgres in July 2025 with automatic failover, backups, and pooling. Everything hard in this design (the backup gate, the verification cell, the repair reconcilers) is the part Fly initially declined to build, and eventually had to.

Updates from post-training information

Clerk's February 19, 2026 postmortem (after my training window) is a correction to this design's detection story. An automatic ANALYZE flipped a query plan — the planner sampled a column as 100% NULL when it was 99.9996% — and the resulting plan ate the database. Their failover never fired because "the database was technically still online, just degraded." My detection is Patroni heartbeats: it catches dead hosts and partitions, not a primary that answers heartbeats while serving queries a thousand times too slowly. The customer-triggered POST /failover endpoint covers the manual escape, but the design should add what Clerk committed to after the incident: alerting on query-plan flips and latency-based degradation signals that can trigger the failover path, not just page a human. A gray failure is still a failure to the customer.