Zero downtime Postgres upgrades

Original Article ↗ Hacker News Discussion ↗

Engineers dissect a real-world attempt to upgrade a large PostgreSQL database on AWS with effectively zero user-visible downtime, relying on logical replication, dual-connected applications, and careful cutover orchestration. Commenters debate whether such complexity is justified compared to taking a short, planned outage—raising tradeoffs between availability and consistency, operational simplicity versus specialized tooling, and how much reliability customers can reasonably expect. The thread also surfaces alternative strategies such as AWS blue/green deployments, snapshot-based approaches, and using Postgres as a general-purpose backbone versus introducing more specialized services.

Hosted solutions vs custom process

  • Some note Heroku and AWS already support scaling and follower databases, but others clarify followers can lag badly on busy DBs and backups/replicas can get slow or stuck.
  • Aurora’s newer minor-upgrade and blue/green features are praised, but support depends on engine version and some report flaky experiences, so not universally trusted yet.

How often and how far to upgrade

  • Debate over “big-bang” upgrades vs frequent small ones.
  • One side: each major upgrade has similar availability risk, so deferring just stacks work; upgrading across many versions at once increases risk.
  • Other side: “if it isn’t broken, don’t fix it” plus real downtime cost leads teams to wait, then invest heavily in a robust, one-time process.

Postgres as central infrastructure

  • Criticism: using one RDBMS for everything (logging, queues, scheduling, business data) creates a single point of failure and pushes the tech beyond its intended model.
  • Counterpoint: many successful systems are “Postgres/MySQL + Redis”; fewer moving parts and one well-understood system can be better than many specialized services.
  • Several argue it’s engineering, not pure CS: consolidate until Postgres no longer fits, then peel off workloads like logging to other systems.

Zero downtime vs acceptable downtime

  • Strong disagreement on whether true zero downtime is worth it.
  • Many claim a short, announced maintenance window (e.g., 10–15 minutes every couple of years) is fine for nearly all SaaS and cheaper than complex “zero-downtime” engineering.
  • Others, especially with global or infrastructural customers, say any outage is effectively their customers’ outage and hurts trust or competitiveness.
  • Several stress consistency and clear communication over 100% availability, noting even hospitals and hyperscalers accept planned downtime.

Migration techniques and risks

  • Approaches discussed:
    • Logical replication table-by-table (safer but tedious and IO-heavy).
    • Snapshot + replication slot + advancing LSN to create logical replicas quickly; some experts warn of subtle corruption/data-loss risks and logical replication bugs.
    • pglogical, custom tools (e.g., pg_easy_replicate), and GitLab/Instacart-style zero-downtime cutovers.
  • Large-table challenges, sequence synchronization, and ID strategies (UUIDv4/v7, KSUID, HiLo) are recurring themes.
  • Several emphasize rehearsals, verification (checksums, canaries), and having at least a conceptual rollback path, even if not fully symmetric.