Day 6: Evolve the Product Schema Safely with ALTER TABLE — And Migrate Existing Data Without Downtime.

Day 6: Evolve the Product Schema Safely with ALTER TABLE — And Migrate Existing Data Without Downtime

In Day 5, we explored the critical role of transactions in preserving data integrity, demonstrating how a timely ROLLBACK can rescue a system from accidental data destruction. However, databases are not static monuments. As your retail platform grows, your business requirements will change.

Today, we face a classic production milestone: our catalog needs to support a new globally unique Stock Keeping Unit (SKU) code for every product.

In a staging environment with ten rows, this is a one-line SQL statement. In a production environment sustaining hundreds of queries per second, a naive database alteration is a self-inflicted denial-of-service attack.

Today, you will learn how databases handle schema changes under the hood, observe how naive alterations trigger catastrophic lock queues, and implement a zero-downtime, multi-phase migration pattern to evolve your schema safely.

The Production Stakes: The Lock Queue Avalanche

In 2020, GitLab experienced a multi-hour degradation because a database migration took an exclusive lock on a heavily used table. Under high traffic, this single lock blocked all incoming application queries, rapidly exhausted the database connection pool, and brought down the user-facing application.

To understand why this happens, we must look at how relational databases manage concurrent access to their system catalogs.

When you execute any query, the database acquires a lock on the target table to ensure the table's structure does not change mid-query. PostgreSQL uses a hierarchy of lock modes:

1. Row Share / Row Exclusive Locks: Acquired by standard SELECT, INSERT, UPDATE, and DELETE operations. These locks are highly compatible with one another, allowing thousands of clients to read and write to the same table concurrently.

2. Access Exclusive Locks: Acquired by structural alterations like ALTER TABLE. This lock is completely incompatible with all other lock modes. While an AccessExclusiveLock is held, no other transaction can read from or write to that table.

If you execute a naive migration:

The database must acquire an AccessExclusiveLock. If the table contains millions of rows, the database must write the default value to every single row on disk. During this rewrite, every incoming customer search, cart addition, and checkout request targeting the products table is queued behind your migration.

Within seconds, your application's connection pool saturates. Upstream services begin timing out, and your customers see 503 Service Unavailable errors.

The Safe Alternative: The Multi-Phase Online Migration

To evolve our schema without downtime, we must decompose the migration into small, non-blocking steps. This pattern is known as the Online Schema Migration.

Instead of forcing the database to perform a massive, synchronous rewrite under an exclusive lock, we split the work into a sequence of operations that minimize lock holding times and defer validation.

We map our migration into four distinct phases:

Phase 1: Add a Nullable Column

First, we add the column as nullable without a default value. This operation only updates the database's metadata catalogs. It does not rewrite any data on disk, meaning the AccessExclusiveLock is held for mere milliseconds.

Phase 2: Low-Impact Batch Backfilling