PostgreSQL HA and PITR for a Payment Ledger
A fintech runs a customer ledger on Exoscale DBaaS for PostgreSQL in DE-FRA-1. The service uses a single-node Startup plan with 4 vCPU, 16 GB RAM, and 300 GB storage. Twenty application instances open up to 450 connections at peak, and p95 query latency is 80 ms. Last month a zone maintenance event made the database unavailable for 22 minutes. The business now requires RTO ≤ 5 minutes and RPO ≤ 1 minute for ledger writes. The team also sees connection storms after deploys, causing authentication timeouts. They are considering an HA plan, a read replica for reporting, and PgBouncer. The ledger schema uses serializable transactions for transfers and has a 40 GB hot table. Backups currently complete nightly, but the team has never tested a restore. Compliance requires seven years of monthly backups and point-in-time recovery for the last seven days. The budget allows one plan upgrade and one additional component, not both a read replica and a separate pooler cluster.
1. Which DBaaS plan and topology do you recommend, and what does it not solve?
Read the worked answer
Upgrade to an HA DBaaS plan in DE-FRA-1 rather than adding a read replica. The primary requirement is RTO and RPO for writes, not reporting offload. An HA plan provides a standby and automated failover, which is the closest managed capability to the five-minute RTO. A read replica would help reporting but would not improve write failover and would consume the single additional component budget.
Use the remaining budget for PgBouncer deployed on the application hosts or a small pooler tier, not a separate highly available pooler cluster. That reduces the 450 connections to a manageable number without adding a second distributed system to operate.
Be explicit about the residual risk: Exoscale DBaaS is zonal. HA protects against host failure but may not protect against a full zone maintenance event or a control-plane outage affecting both nodes. If the business truly requires five-minute RTO across zone loss, this design may not meet it; you need a cross-zone application queue or a documented exception. Do not claim HA solves a zone-wide outage without evidence.
2. Design the backup and point-in-time recovery setup to meet the stated RPO and compliance retention.
Read the worked answer
Enable the HA plan's automated backups and PITR, and confirm the PITR window covers at least seven days. RPO ≤ 1 minute depends on continuous WAL archiving, so verify the actual archive interval and test a restore to a timestamp one minute before a simulated bad write. Do not accept a nightly logical dump as the only recovery mechanism; that would give an RPO of up to 24 hours.
For the seven-year monthly retention, export a logical backup monthly with pg_dump, encrypt it, and store it in an Object Storage bucket in an approved Swiss or EU zone with versioning and a retention policy. Keep the monthly exports outside the DBaaS backup retention window, because DBaaS backups will expire. Record checksums and restore instructions with each export.
Test a full restore quarterly into a separate DBaaS instance, not just a table-level check. Measure actual restore time against the five-minute RTO; if restore takes longer, the RTO is not met even if failover is fast. Document the difference between HA failover and restore from backup.
3. How would you fix the connection storms and serializable transaction contention?
Read the worked answer
Deploy PgBouncer in transaction pooling mode between the application and PostgreSQL. Size default_pool_size to roughly 20–30 server connections per database and cap max_client_conn well above the application's peak. This reduces 450 direct connections to a small number of backend connections and prevents authentication storms after deploys. Because transaction pooling does not preserve session state, confirm the application does not rely on session-level advisory locks, prepared statements, or SET commands outside a transaction; if it does, use session pooling for those paths.
Set idle_in_transaction_session_timeout and statement_timeout to kill stuck transactions. For serializable transfers, keep transactions short and implement retry with exponential backoff on serialization failures. Do not hold a serializable transaction open while waiting for user input. Consider partitioning the 40 GB hot table only if contention is proven by waits and lock data, not by size alone. Monitor PgBouncer pool saturation and PostgreSQL lock waits. The judgement is to fix connection count first, then transaction design, rather than adding a read replica that does not address write contention.