How We Do Online Schema Changes Without Downtime

Every time we need to add a column to a table with 50 million rows, there is a moment where a lesser architecture would require a maintenance window — a 'we'll be back soon' banner, a very apologetic email to tenants, and a scramble to complete the migration before the window expires. We have never sent that email. Our tenants — Aaladipattiyan selling karupatti sweets, TVS Electronics, Hyle Laban delivering dairy across Chennai — have never seen a checkout outage from a schema migration. Here is exactly how we keep it that way.

Why Naive Migrations Are Dangerous

A naive migration does the obvious thing: ALTER TABLE, update the column, move on. For a small table, this is fine. For a table with millions of rows — orders, order_items, events, inventory_movements — PostgreSQL takes an ACCESS EXCLUSIVE lock during the operation. Nothing can read from or write to that table while the migration runs. For a table with 50M rows, that lock can hold for 5 to 20 minutes.

Five minutes of checkout downtime on a platform serving multiple tenants is not an infrastructure incident. It is a revenue incident. It is the kind of thing that ends up in a post-mortem document titled 'What We Did Wrong.'

The Additive-Then-Destructive Pattern

The principle is simple: never remove or rename in the same deploy that adds. Every schema change is a three-phase operation spread across at least two deployments.

Phase 1: Add (Never Block)

Adding a new column to a Postgres table — with a default value defined at the column level, not backfilled — is a metadata-only operation in Postgres 11+. It takes milliseconds regardless of table size. We add the new column, deploy application code that writes to both the old and new column simultaneously, and the migration is 'done' from an availability standpoint. Zero downtime.

Phase 2: Backfill (Batched, Background)

If existing rows need the new data, we backfill in small batches — typically 1,000 rows per batch, with a short sleep between batches. This runs as a background job, not a migration. It never takes a lock that blocks reads or writes. It takes longer than a single ALTER TABLE, but it takes that time without touching availability.

Phase 3: Cleanup (Future Deploy)

Only after the new column is fully backfilled and application code has been reading from it for at least one full deployment cycle do we remove the old column. At this point the old column is unused, the removal is safe, and if anything goes wrong we can roll back cleanly.

Postgres-Specific Mechanics That Enable This

A few Postgres behaviors are essential to this pattern:

• NOT VALID constraints: Adding a foreign key constraint with NOT VALID acquires only a SHARE UPDATE EXCLUSIVE lock (non-blocking). A subsequent VALIDATE CONSTRAINT runs without a full-table lock in Postgres 14+.
• CONCURRENTLY indexes: CREATE INDEX CONCURRENTLY builds the index without locking the table. It takes longer and requires more resources, but it never blocks reads or writes.
• pg_stat_activity monitoring: Before any migration that could take an exclusive lock, we check for long-running queries and wait for them to complete rather than killing them abruptly.

The One Case We Handle Differently

Column type changes are the exception that cannot be made fully additive in Postgres. Changing a column from VARCHAR to UUID, or from INTEGER to BIGINT, requires a full table rewrite. Our approach: add a new column with the new type, backfill it from the old column with the cast applied, switch application code to write and read the new column, then eventually drop the old one. It is the same three-phase pattern, applied to type changes. More steps, same safety.

Why This Matters for Multi-Tenant Commerce

QuantumOS X3 shares infrastructure across tenants while maintaining data isolation per tenant. A migration that causes downtime does not affect one tenant — it affects all of them simultaneously. The additive-then-destructive pattern is not a nice-to-have; it is a non-negotiable property of a multi-tenant platform that takes its tenants' revenue seriously.

We ship schema changes weekly. Our tenants have never noticed.

Subscribe to the QuantumOS Dispatch — weekly insights for commerce operators who want to compound their advantages.