Database High Availability: Beyond Cloud Provider Toggles to Distributed Systems Reality

ate async executeWithRetry<T>(query: () => Promise<T>, attempt = 0): Promise<T> { if (this.isCircuitOpen()) throw new Error('Circuit breaker open: database unreachable');

try {
  return await query();
} catch (err: any) {
  const isRetryable = err.code === 'ECONNRESET' || err.code === 'ETIMEDOUT' || err.message.includes('connection closed');
  
  if (isRetryable && attempt < this.MAX_RETRIES) {
    this.circuitBreaker.failures++;
    this.circuitBreaker.lastFailure = Date.now();
    
    const delay = this.BASE_DELAY * Math.pow(2, attempt) + Math.random() * 500;
    await new Promise(res => setTimeout(res, delay));
    return this.executeWithRetry(query, attempt + 1);
  }
  
  throw err;
}

}

private isCircuitOpen(): boolean { const now = Date.now(); if (this.circuitBreaker.failures >= this.circuitBreaker.threshold) { if (now - this.circuitBreaker.lastFailure > this.circuitBreaker.resetTime) { this.circuitBreaker.failures = 0; // Half-open state return false; } return true; } return false; }

async query(text: string, params?: any[]) { return this.executeWithRetry(() => this.pool.query(text, params)); }

async acquire(): Promise<PoolClient> { return this.executeWithRetry(() => this.pool.connect()); } }

Application-level resilience prevents connection storms during failover. The circuit breaker stops requests when the database is unreachable, allowing the cluster to stabilize. Exponential backoff with jitter distributes retry pressure across the cluster.

### Step 4: Connection Pooling & Read/Write Splitting

Deploy PgBouncer or Odyssey in front of the database cluster. Configure `server_reset_query` and `server_check_query` to detect failed connections before they reach the application. Route reads to replicas, writes to the Patroni-managed leader endpoint.

```ini
# pgbouncer.ini
[databases]
* = host=leader-endpoint port=5432

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
server_reset_query = DEALLOCATE ALL
server_check_query = SELECT 1

During failover, PgBouncer detects leader changes via health checks, closes stale connections, and establishes new ones to the promoted standby. This eliminates application-side connection timeouts.

Pitfall Guide

1. Assuming Multi-AZ Equals Zero Downtime Multi-AZ deployments distribute nodes across failure domains but do not eliminate failover latency. DNS propagation, connection draining, and leader election timeouts still occur. Mitigation: Implement health-check-based routing (e.g., HAProxy, Envoy) instead of DNS TTL-dependent failover.

2. Misconfiguring Synchronous Replication Under Network Jitter Forcing synchronous replication on unstable networks causes write stalls. The primary blocks until the standby acknowledges WAL flush. Mitigation: Use quorum sync mode, monitor pg_stat_replication.sync_state, and set explicit synchronous_commit thresholds based on network SLAs.

3. Ignoring Connection Pool Exhaustion During Failover When the leader steps down, all active connections drop. If the application retries instantly without backoff, the new leader receives a connection storm, triggering OOM or max_connections limits. Mitigation: Enforce exponential backoff, limit retry concurrency, and configure PgBouncer max_db_connections with headroom.

4. Skipping Controlled Failover Drills Theoretical HA configurations rarely match production behavior. Unplanned failovers expose configuration drift, missing permissions, and monitoring gaps. Mitigation: Schedule monthly chaos tests using patronictl switchover. Verify RTO/RPO metrics post-failover.

5. Overloading Read Replicas for Analytical Queries Heavy analytical workloads on replicas increase replication lag. If lag exceeds the application's tolerance, read-your-writes consistency breaks, and failover promotion may restore stale data. Mitigation: Isolate analytical workloads to dedicated read nodes or materialized views. Monitor pg_replication_lag and alert at >5s.

6. Treating Backups as High Availability Backups enable disaster recovery, not high availability. Restore operations take minutes to hours and cannot satisfy RTO < 30s. Mitigation: Maintain separate DR pipelines with cross-region replication. HA handles node failures; DR handles region/catastrophic failures.

7. Application-Level Silent Failures Catching database errors and returning empty responses or cached data masks availability degradation. Users experience stale data without alerts. Mitigation: Fail fast. Return explicit 503 Service Unavailable when the circuit breaker trips. Route to fallback endpoints only after explicit SLA violation.

Best practice from production: Implement a dedicated HA health endpoint that reports replication lag, leader status, and connection pool saturation. Integrate this into your alerting pipeline. HA is observable or it is broken.

Production Bundle

Action Checklist

• Define explicit RTO and RPO SLAs before architecture selection
• Deploy Patroni + etcd/Consul for deterministic leader election
• Configure synchronous replication with quorum mode for 3+ node clusters
• Implement PgBouncer/Odyssey with transaction pooling and health checks
• Add application-level retry logic with exponential backoff and circuit breaking
• Isolate analytical workloads from HA-critical read replicas
• Schedule monthly controlled failover drills and verify metrics
• Monitor replication lag, leader election latency, and connection pool saturation

Decision Matrix

Configuration Template

# docker-compose.yml for Patroni + etcd + PostgreSQL HA
version: '3.8'
services:
  etcd1:
    image: bitnami/etcd:3.5
    environment:
      - ETCD_NAME=etcd1
      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      - ETCD_INITIAL_CLUSTER_STATE=new
    ports: ["2379:2379", "2380:2380"]

  etcd2:
    image: bitnami/etcd:3.5
    environment:
      - ETCD_NAME=etcd2
      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      - ETCD_INITIAL_CLUSTER_STATE=new
    ports: ["2381:2379", "2382:2380"]

  etcd3:
    image: bitnami/etcd:3.5
    environment:
      - ETCD_NAME=etcd3
      - ETCD_INITIAL_CLUSTER=etcd1=http://etcd1:2380,etcd2=http://etcd2:2380,etcd3=http://etcd3:2380
      - ETCD_INITIAL_CLUSTER_STATE=new
    ports: ["2383:2379", "2384:2380"]

  patroni1:
    image: zalando/patroni:latest
    environment:
      - PATRONI_NAME=pg1
      - PATRONI_POSTGRESQL_DATA_DIR=/home/postgres/pgdata
      - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
      - PATRONI_ETCD3_HOSTS=etcd1:2379,etcd2:2379,etcd3:2379
      - PATRONI_SCOPE=ha_cluster
      - PATRONI_RESTAPI_CONNECT_ADDRESS=patroni1:8008
    ports: ["5432:5432", "8008:8008"]
    depends_on: [etcd1, etcd2, etcd3]
    volumes:
      - pg1_data:/home/postgres/pgdata

  patroni2:
    image: zalando/patroni:latest
    environment:
      - PATRONI_NAME=pg2
      - PATRONI_POSTGRESQL_DATA_DIR=/home/postgres/pgdata
      - PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
      - PATRONI_ETCD3_HOSTS=etcd1:2379,etcd2:2379,etcd3:2379
      - PATRONI_SCOPE=ha_cluster
      - PATRONI_RESTAPI_CONNECT_ADDRESS=patroni2:8008
    ports: ["5433:5432", "8009:8008"]
    depends_on: [etcd1, etcd2, etcd3]
    volumes:
      - pg2_data:/home/postgres/pgdata

volumes:
  pg1_data:
  pg2_data:

-- pg_hba.conf snippet for replication
host    replication     replicator    10.0.0.0/8        scram-sha-256
host    all             all           10.0.0.0/8        scram-sha-256

Quick Start Guide

1. Deploy etcd cluster: Run the etcd services from the template. Verify quorum with etcdctl endpoint health --cluster.
2. Initialize Patroni nodes: Start patroni1 and patroni2. Patroni will automatically initialize PostgreSQL, configure streaming replication, and register with etcd.
3. Validate leader election: Run patronictl -c patroni.yml list ha_cluster. Confirm one node shows Leader and the other Replica.
4. Test failover: Execute patronictl switchover ha_cluster --candidate pg2. Verify leadership transfers within 5–10 seconds and replication resumes.
5. Connect application: Point your TypeScript resilience layer to the Patroni REST API endpoint or load balancer. Monitor pg_stat_replication and application retry metrics.