Database Replication and High Availability Cheat Sheet

Updated 2026-05-15

Next Topic: Database Schema Design Patterns Cheat Sheet

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

Every HA design starts with a single question—how long the primary waits for its replicas before declaring a commit durable. These modes trace that spectrum from fully asynchronous (fastest, but exposed to data loss on failover) through synchronous, semi-synchronous, and quorum variants, then branch into the structural choices of logical versus physical replication, cascading topologies, and multi-primary writes. Read it as the menu of consistency-versus-performance trade-offs you'll keep returning to throughout the rest of the sheet.

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