Database High Availability During Maintenance

Maintaining high availability during scheduled relational database maintenance requires a combination of resilient architecture, automated failover mechanisms, and disciplined schema migration practices. Web development and database administration teams prevent downtime by decoupling the application layer from the physical database, leveraging read replicas, executing controlled rolling failovers, and applying backward-compatible database updates. This article outlines the essential technical strategies teams use to perform database upgrades, patching, and schema changes without disrupting the end-user experience.

Multi-AZ Architectures and Automated Failover

The foundation of zero-downtime maintenance is a multi-node deployment, such as an active-passive setup across multiple Availability Zones (AZs).

  • Primary-Replica Topology: The primary node handles read and write traffic, while one or more secondary nodes continuously replicate data synchronously or asynchronously.
  • Controlled Switchover: To patch or resize the database, teams apply updates to the standby replica first. Once the replica is updated and healthy, an automated or manual switchover promotes the replica to the new primary. The old primary is then demoted, patched, and returned to the cluster as a replica. This process reduces downtime to a brief interruption of a few seconds rather than hours.

Database Proxies and Connection Management

Even with rapid switchovers, dropping active database connections can produce application errors. Teams insert a database proxy or connection pooler between the application and the database to mask failovers.

  • Tools: Proxies like AWS RDS Proxy, PgBouncer (for PostgreSQL), or ProxySQL (for MySQL) maintain persistent connections with the application layer.
  • Connection Queuing: During the brief seconds when a failover occurs, the proxy pauses incoming queries and queues transactions rather than returning connection drop errors to the web application. Once the new primary node accepts traffic, the queued queries execute normally.

The Expand-and-Contract Pattern for Schema Migrations

Large schema modifications—such as adding constraints, modifying column types, or indexing massive tables—can lock tables and stall traffic. Teams avoid this using the Expand-and-Contract (or Parallel Run) pattern:

  1. Expand: Add new columns, tables, or indices alongside the existing structure without altering existing application code. Write operations populate both the old and new structures simultaneously.
  2. Transition: Migrate existing historical data in small, non-blocking background batches.
  3. Contract: Point application read queries to the new schema structure. Once verified, deploy code that removes references to the old structure, then safely drop the obsolete columns or tables.

Read-Only Fallbacks and Graceful Degradation

In scenarios where maintenance requires write operations to pause briefly, web teams design the application to degrade gracefully instead of failing outright:

  • Split Read/Write Traffic: Web frameworks route all read requests to read replicas and write requests to the primary. If the primary must be temporarily locked or restarted, the site continues serving read-only traffic (such as browsing products or reading articles).
  • Asynchronous Queueing: User actions that require writes (like form submissions or cart updates) are accepted by the application, placed into an asynchronous message queue (e.g., RabbitMQ, Apache Kafka, or AWS SQS), and processed once the database is fully writable again.