Home / Blog / Zero-Downtime PostgreSQL Major Upgrades, Orchestrated by HeliosProxy
Architecture 11 min read

Zero-Downtime PostgreSQL Major Upgrades, Orchestrated by HeliosProxy

The hardest operational task on Postgres is a major-version upgrade. The version cuts are not frequent — once every 12-18 months for most installations — but each one requires planning a coordinated rollout across primaries and replicas, deciding between pg_upgrade and logical replication, picking a downtime window, drafting a rollback plan, and accepting that some fraction of the load may behave differently on the new version due to planner changes, type tightening, or extension drift.

HeliosProxy v0.4 puts the orchestration inside the proxy. This post walks through how, with a worked example for a fictional PG-12 → PG-17 cut.

Why This Is Hard

  1. Coordination. Primary, sync standby, async replicas, read-replicas in other regions, an analytical replica, possibly a logical-replication subscriber feeding a search index. All need to move; some can move slowly; some can't.
  2. Validation. The cut is irreversible in practice. You want to know, before flipping traffic, that the new backend produces equivalent results for your actual workload.
  3. Rollback. If the new version misbehaves under real load, you want to revert in seconds.

The Stage Model

Upgrades are described as a sequence of stages in heliosproxy.toml. Each stage has a name, a tick schedule, and a SQL body. The proxy executes the SQL on a tick — against either backend, depending on the stage's role — and tracks progress in the journal.

[upgrade]
enabled          = true
from_backend     = "pg12-prod"
to_backend       = "pg17-candidate"
shadow_window    = "24h"
cut_threshold    = 0

# Stage 1: backfill historical data into the new backend
[[upgrade.stages]]
name     = "backfill"
tick     = "30s"
target   = "to"
sql      = """
INSERT INTO pg17.events
SELECT * FROM pg12.events
WHERE id > (SELECT COALESCE(MAX(id), 0) FROM pg17.events)
LIMIT 10000
"""

# Stage 2: continuous reconciliation
[[upgrade.stages]]
name     = "reconcile"
tick     = "15s"
target   = "both"
sql      = "SELECT * FROM helios_diff_window('events', '5m')"

# Stage 3: pre-cut verification
[[upgrade.stages]]
name     = "verify"
tick     = "5m"
target   = "to"
sql      = "SELECT COUNT(*) FROM pg17.events"

Stages are not magic; they are SQL on a tick. The orchestration is in coordinating which stage runs where, what counts as success, and when to advance — not in hiding what the SQL does.

Shadow Execution

While the upgrade is in flight, the shadow-execution comparator runs the same client traffic against both backends and diffs the result. Configurable diff modes:

  • rows — row-by-row equality.
  • aggregates — compare aggregate values only (looser; tolerates ordering differences).
  • row_count — compare cardinality only (loosest).

Divergent queries are recorded in the journal with their query IDs. Cut only when the divergent count is at or below your cut_threshold for the duration of shadow_window.

Chaos Rehearsal

An upgrade plan that has only ever been validated on the happy path is not a plan. v0.4's chaos-mode admin endpoint injects controlled failures so you find issues in pre-prod, not during the cut window:

POST /api/chaos
{
  "type":      "latency",
  "target":    "pg17-candidate",
  "amount_ms": 250,
  "duration":  "5m",
  "fraction":  0.1
}

Inject latency, error rates, network partitions; rehearse the upgrade orchestrator's response.

The Cut

When the shadow window has been clean for the configured duration, traffic moves. The proxy points all routing decisions at the new backend; the old backend stays online for rollback. Time-travel replay is the rollback path: if the new backend misbehaves, replay the cut window's traffic against the old backend, point routing back, and you have lost zero data.

What This Replaces

  • pg_upgrade works in place but requires downtime.
  • Logical replication works but requires schema compatibility and significant operational overhead.
  • Bespoke runbooks — what most teams actually do. Document a sequence of steps, run them carefully, hope.

The proxy orchestrator is not a replacement for any of these in isolation. It is a coordinator that uses them as primitives.

The PG-12 EOL Wedge

This is the practical reason this matters now. The migration wave for everyone running PG-12 in production has already started. Most existing tooling assumes you accept downtime; the alternatives are bespoke. HeliosProxy v0.4 offers a third path — orchestrated, validated, rollback-ready.

Boundaries

  • Cross-extension drift — if you depend on extensions that don't exist on the new major (or behave differently), the orchestrator does not magically fix that.
  • Logical-replication-driven flow — the v0.4 stages are SQL-on-a-tick. A logical-replication-managed flow is on the roadmap.
  • Cross-region coordination — one orchestrator per region with an external sequencer.

Earlier in This Series

HeliosProxy is open source under Apache 2.0. Repository: github.com/dimensigon/HDB-HeliosDB-Proxy.

← Back to Blog