Database Replication and High Availability Cheat Sheet

Back to DatabasesUpdated 2026-05-15

Database replication and high availability are critical components of production database architectures, ensuring data durability, continuous service availability, and disaster recovery capabilities. Modern HA solutions span from simple primary-replica setups to sophisticated multi-datacenter clusters with automatic failover, balancing the competing demands of consistency, availability, and partition tolerance. Understanding replication modes, failover mechanisms, and operational trade-offs is essential—whether deploying PostgreSQL streaming replication, MySQL Group Replication, or distributed consensus-based systems like Patroni with etcd.

What This Cheat Sheet Covers

This topic spans 12 focused tables and 135 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Replication Types and ModesTable 2: PostgreSQL Streaming Replication ConfigurationTable 3: Patroni HA Cluster ConfigurationTable 4: PostgreSQL Logical ReplicationTable 5: MySQL Replication ModesTable 6: MySQL Group Replication ConfigurationTable 7: Galera Cluster ConfigurationTable 8: Connection Pooling with pgBouncerTable 9: Global Load Balancing and FailoverTable 10: Monitoring Replication and LagTable 11: Recovery Objectives and PatternsTable 12: Read-Heavy Architecture Patterns

Table 1: Replication Types and Modes

Type	Example	Description
Asynchronous replication	PostgreSQL default MySQL default	Primary commits transactions without waiting for replicas to acknowledge; offers best performance but potential data loss on failover if commits not yet replicated
Synchronous replication	PostgreSQL `synchronous_commit=on` `synchronous_standby_names` set	Primary waits for at least one replica to write and flush WAL before commit returns; guarantees zero data loss for committed transactions but increases latency
Semi-synchronous replication	MySQL with semisync plugins	Primary waits for at least one replica acknowledgment after writing to binary log; middle ground between async and sync with configurable wait point (AFTER_SYNC or AFTER_COMMIT)
Quorum-based synchronous	PostgreSQL `ANY 2 (s1, s2, s3)`	Commit succeeds when any N replicas acknowledge; provides flexibility in multi-replica environments and survives individual replica failures
Priority-based synchronous	PostgreSQL `FIRST 2 (s1, s2, s3)`	Commit waits for first N replicas by priority; if higher-priority replica fails, next in line automatically becomes synchronous

Table 1: Replication Types and Modes

Type	Example	Description
Asynchronous replication	PostgreSQL default MySQL default	Primary commits transactions without waiting for replicas to acknowledge; offers best performance but potential data loss on failover if commits not yet replicated
Synchronous replication	PostgreSQL `synchronous_commit=on` `synchronous_standby_names` set	Primary waits for at least one replica to write and flush WAL before commit returns; guarantees zero data loss for committed transactions but increases latency
Semi-synchronous replication	MySQL with semisync plugins	Primary waits for at least one replica acknowledgment after writing to binary log; middle ground between async and sync with configurable wait point (AFTER_SYNC or AFTER_COMMIT)
Quorum-based synchronous	PostgreSQL `ANY 2 (s1, s2, s3)`	Commit succeeds when any N replicas acknowledge; provides flexibility in multi-replica environments and survives individual replica failures
Priority-based synchronous	PostgreSQL `FIRST 2 (s1, s2, s3)`	Commit waits for first N replicas by priority; if higher-priority replica fails, next in line automatically becomes synchronous