High-availability relational database

Problem

Applications that need transactions, joins, and a strict schema cannot treat the database as a single box. One instance is a single point of failure: a crash, a disk, or a host reboot stops writes and locks the product.

Manual failover is slow and easy to get wrong. Direct connections to one host also collapse under connection storms. The platform needs a relational cluster that elects a new primary, keeps replicas in sync, and absorbs client connections without pinning a single address.

System Design

This project is a primary-replica relational cluster. The primary takes writes; replicas stream the same data. If the primary disappears, remaining members elect a replacement without an operator.

A connection pool sits in front. Applications talk to one endpoint. The pool tracks the current primary, spreads read traffic when it is safe, and sheds dead members. Persistent volumes keep data across restarts. Credentials live in secrets.

Architecture Decisions

• Streaming replication so replicas stay close to the primary
• Automatic failover instead of a manual promote
• Connection pooling so apps do not open a session per request on the database
• Health checks so a dead node leaves the pool
• Persistent volumes and regular backups
• Repeatable scripts for deploy, status, failover drills, and teardown

Tradeoffs

• Replica reads can lag a moment behind the primary
• Writes still go through one primary at a time
• More nodes mean more disk, more ops, and more backup surface
• Pooling adds a hop — traded for connection control and a stable endpoint
• Real high availability needs at least three members on separate hosts

Production Behavior

Deploy the cluster, point the pool at the members, and let applications use a single endpoint. When a node fails, the cluster elects a new primary and the pool stops sending traffic to the dead member.

Run a failover drill after deploy. Check replica lag and backup restore before calling it production-ready. Built for transactional workloads that must survive a node loss without rewriting the application.