Designing and Practicing Universal High‑Availability for MySQL and PostgreSQL

The talk, originally presented by senior DBA Zhao Feixiang from Airwallex, focuses on designing and implementing universal high‑availability (HA) solutions for MySQL and PostgreSQL databases.

1. HA Principles

HA means keeping a service continuously available through redundancy. A single instance is a single point of failure; HA requires at least two independent nodes. The discussion abstracts a generic HA model consisting of three layers:

Data Synchronization Module : ensures state replication between primary and secondary instances using logs (binlog for MySQL, WAL for PostgreSQL). It handles full‑copy bootstrapping and incremental log shipping.

HA Management Module : monitors node health, decides when to fail over, and orchestrates role changes. It can be centralized, peer‑to‑peer, or distributed.

Unified Access Module : provides a stable endpoint (VIP, DNS, or proxy) so applications do not need to change connection strings during failover.

2. Generic HA Architecture

The three modules map to storage HA (data sync), compute HA (management), and service HA (access). This abstraction applies to any database, but storage HA is the most complex because databases are stateful.

3. Typical HA Solutions

Common open‑source components such as keepalived (VIP failover) and haproxy (health check & load balancing) form a classic HA stack. The diagram shows how these components interact with the underlying database resources.

4. MySQL HA Practices

Data Sync : MySQL uses binary logs for asynchronous, semi‑synchronous, and synchronous replication. Asynchronous replication offers highest performance but risks data loss; semi‑synchronous reduces that risk by waiting for at least one replica to acknowledge; full synchronous waits for all replicas, impacting latency.

MHA : A centralized manager (MHA Manager) monitors MySQL instances and triggers failover scripts. It supports both async and semi‑sync replication.

MySQL InnoDB Cluster : Combines Group Replication, MySQL Router, and MySQL Shell to provide an integrated HA solution with automatic failover and load balancing. Single‑primary mode is recommended for stability.

5. PostgreSQL HA Practices

Data Sync : PostgreSQL’s WAL (Write‑Ahead Log) can be configured for logical or physical replication (set wal_level=logical for logical). Physical streaming replication offers low latency; logical replication allows selective table sync.

pgpool‑II : Provides connection pooling, load balancing, and HA management. It uses asynchronous streaming replication underneath and can manage VIP failover.

Patroni : A distributed HA manager that stores cluster state in etcd/ZooKeeper/Consul and uses haproxy for routing. It offers higher automation and resilience but requires more components.

6. Cloud HA and Trends

Cloud‑based HA (e.g., RDS) often requires wal_level=logical for PostgreSQL and may use shared storage or streaming replication. Cloud providers differ in failover time and read/write split handling. VIP/keepalived is usually unavailable in managed services; instead, built‑in load balancers or proxies are used.

Future HA trends continue to emphasize the three‑layer model, with more automation, distributed consensus, and tighter integration of data sync, management, and access layers.

7. Q&A Highlights

HA monitoring focuses on instance liveness and connectivity; health checks may run simple queries.

Choosing sync vs. async vs. semi‑sync depends on business priorities: data consistency vs. performance.

Patroni is suitable for complex, highly automated environments; pgpool‑II fits simpler scenarios.

MySQL InnoDB Cluster is the recommended HA solution for MySQL 8+, while other options (MHA, Orchestrator) remain viable.

Financial systems typically prefer synchronous replication to avoid data loss.

Overall, the presentation provides a comprehensive framework for evaluating and implementing HA for relational databases, with practical guidance for MySQL and PostgreSQL in both on‑premises and cloud contexts.