Zero-Downtime Column Addition with Expand-Contract

Hello! Welcome to your next lesson on designing high-load distributed systems.

In our last session, we analyzed the theoretical stages of the expand-contract pattern for zero-downtime schema migrations. We established the "what" and "why" of this crucial technique. Today, we transition from theory to practice.

Your experience managing high-throughput payment and trading systems underscores the non-negotiable requirement for continuous availability. A simple schema change cannot be allowed to lock a critical table and halt operations. This lesson provides the practical implementation details to prevent that.

Our learning outcome is to implement a zero-downtime column addition using the expand-contract pattern. We will focus on the specific PostgreSQL commands and application logic strategies that make this possible, moving beyond a simple column addition to a more realistic column modification scenario, which is a common requirement in evolving systems.

1. Recap and Problem Framing

As we discussed, the expand-contract pattern breaks a schema change into backward-compatible steps:

1. Expand: Add the new schema elements.
2. Migrate: Transition application logic and data to the new schema.
3. Contract: Remove the old schema elements.

Let's consider a common, practical problem: an orders table with a primary key id of type INTEGER. As your service grows, you are approaching the 2.1 billion record limit and risk a catastrophic failure from integer overflow. You need to change the id column, and all foreign keys referencing it, to BIGINT.

A naive ALTER TABLE orders ALTER COLUMN id TYPE BIGINT; would rewrite the entire table and its indexes, acquiring an ACCESS EXCLUSIVE lock for what could be hours or days on a large table—an unacceptable downtime. We will now implement the solution using the expand-contract pattern.

2. Implementation Strategy: Application vs. Database

The core of the "expand" phase is ensuring that once the new column is created, all new and updated data is written to both the old and new columns. This is known as dual writing. There are two primary ways to implement this:

• Application-Level Dual Writing: The application code is modified to write to both columns. This keeps the logic within the service layer, which aligns well with a philosophy where the database is a pure persistence layer. It gives application developers full control but requires coordinated deployments across all services that write to the table.
• Database-Level Dual Writing: A database trigger is created to automatically copy data from the old column to the new one on every INSERT or UPDATE. This centralizes the logic in the database, making the change transparent to applications. It's simpler if many different services write to the table, as it requires no application code changes for the dual-write step.

Your choice between these depends on your system's architecture. Given your background, you can likely see the trade-offs: the trigger approach is a clean, atomic change at the data layer, while the application approach aligns with microservice principles where business logic resides outside the database.

For this lesson, we will focus on the trigger-based approach as it's a powerful and self-contained technique.

3. Step-by-Step Implementation in PostgreSQL

We will now walk through the process of changing a column's type from INT to BIGINT with zero downtime. The following article provides an excellent, detailed guide for this exact scenario.

Let's break down the steps from the article.

Step 1: Expand the Schema (Add Column & Trigger)

First, we add the new, nullable column. Let's call it id_bigint.

-- Add the new column. This is a fast metadata change.
ALTER TABLE sem_id ADD COLUMN id_bigint BIGINT NULL;

Adding a NULL-able column is extremely fast because PostgreSQL doesn't need to rewrite the table; it just updates its catalog. While it does require a brief ACCESS EXCLUSIVE lock, the duration is milliseconds.

Next, we set up the dual-writing mechanism using a trigger to ensure all new or updated rows are synchronized.

-- Create a function that the trigger will execute.
CREATE OR REPLACE FUNCTION update_bigint_id()
RETURNS TRIGGER AS $$
BEGIN
    -- Copy the value from the old column to the new one.
    NEW.id_bigint = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger to fire before any insert or update.
CREATE TRIGGER bigint_id_update
BEFORE INSERT OR UPDATE ON sem_id
FOR EACH ROW EXECUTE PROCEDURE update_bigint_id();

Note: The resource only shows BEFORE INSERT, but for a complete solution, you should also include OR UPDATE to handle modifications to existing rows.

At this point, the "expand" phase is active. All new writes are correctly populating both columns.

Step 2: Migrate Historical Data (Backfill)

The trigger handles new data, but we still need to copy the historical data from id to id_bigint. As the article notes, a single UPDATE would lock the table for writes. Instead, we do it in small batches.

The backfill is performed by a script that repeatedly runs a batched UPDATE statement until no more rows need updating.

-- Run this command in a loop (e.g., in a script)
-- until it reports "UPDATE 0".
UPDATE sem_id
SET id_bigint = id
WHERE id_bigint IS NULL
-- Process only a small batch at a time to avoid long locks.
AND ctid IN (
    SELECT ctid
    FROM sem_id
    WHERE id_bigint IS NULL
    LIMIT 1000
);

Note: Using ctid is a very efficient way to select physical rows for batch updates. A simple LIMIT on its own is not safe for UPDATE statements.

After the backfill completes, id and id_bigint are fully synchronized.

Step 3: The Atomic Cutover (Contract)

This is the final, critical step where we switch from the old column to the new one. This must be done in a single, short transaction to ensure atomicity.

Here is the transaction, with explanations for each step:

BEGIN;

-- 1. Lock the table in a mode that prevents other schema changes
-- but still allows concurrent reads and writes (SELECT, INSERT, UPDATE, DELETE).
LOCK TABLE sem_id IN SHARE ROW EXCLUSIVE MODE;

-- 2. Stop dual writing. The new column is now the source of truth.
DROP TRIGGER bigint_id_update ON sem_id;

-- 3. Drop the old column. This is a fast metadata operation.
ALTER TABLE sem_id DROP COLUMN id;

-- 4. Rename the new column to the original name. Also a fast metadata op.
ALTER TABLE sem_id RENAME COLUMN id_bigint TO id;

-- (Additional steps for sequences, indexes, and constraints would go here)

COMMIT;

This entire transaction executes in milliseconds because all operations are metadata changes, not data rewrites. The SHARE ROW EXCLUSIVE lock is key, as it's much less restrictive than ACCESS EXCLUSIVE but provides the necessary protection for the cutover.

4. Handling Constraints and Dependencies

In a real system, columns have constraints like NOT NULL, PRIMARY KEY, and FOREIGN KEY. Applying these naively can cause downtime. PostgreSQL provides features to handle this gracefully.

Here’s a summary of these powerful techniques:

• Indexes: Instead of letting ADD PRIMARY KEY build an index (which would lock the table), you create it beforehand without locking:

  CREATE UNIQUE INDEX CONCURRENTLY sem_id_id_bigint_uniq ON sem_id(id_bigint);
  

  The CONCURRENTLY keyword allows the index to be built without blocking writes to the table. It takes longer but avoids downtime.

• NOT NULL Constraint: A NOT NULL check requires a full table scan. To avoid locking during this scan, you can add the constraint in two steps:

  -- 1. Add the constraint as "not valid". This is instant.
  ALTER TABLE sem_id ADD CONSTRAINT id_not_null CHECK (id IS NOT NULL) NOT VALID;
  
  -- 2. Validate it. This performs the scan but only requires a
  -- SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes.
  ALTER TABLE sem_id VALIDATE CONSTRAINT id_not_null;
  

• Foreign Keys: The same logic applies. You must first apply the expand-contract pattern to the referencing column in the dependent table, and then add the foreign key constraint using the NOT VALID/VALIDATE technique.

Activity: Assemble the Full Migration

Based on what we've covered, here is a consolidated sequence of operations to change a primary key column id from INT to BIGINT, rename it, and re-apply the primary key constraint, all with zero downtime.

Your task: Review the following script. For each numbered block, mentally confirm its purpose and why it's designed to prevent downtime.

-- Phase 1: Expand
-- 1. Add the new column
ALTER TABLE sem_id ADD COLUMN id_bigint BIGINT;

-- 2. Create the dual-write trigger
CREATE OR REPLACE FUNCTION update_bigint_id() RETURNS TRIGGER AS $$
BEGIN
    NEW.id_bigint = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER bigint_id_update
BEFORE INSERT OR UPDATE ON sem_id
FOR EACH ROW EXECUTE PROCEDURE update_bigint_id();

-- Phase 2: Migrate
-- 3. Backfill data in batches (run this in a script until it updates 0 rows)
UPDATE sem_id SET id_bigint = id WHERE id_bigint IS NULL AND ctid IN (SELECT ctid FROM sem_id WHERE id_bigint IS NULL LIMIT 1000);

-- 4. Create the new unique index without locking writes
CREATE UNIQUE INDEX CONCURRENTLY sem_id_id_bigint_pk ON sem_id (id_bigint);

-- Phase 3: Contract (The Cutover)
-- 5. Perform the atomic switchover
BEGIN;
LOCK TABLE sem_id IN SHARE ROW EXCLUSIVE MODE;
-- Drop old PK constraint and trigger
ALTER TABLE sem_id DROP CONSTRAINT sem_id_pkey;
DROP TRIGGER bigint_id_update ON sem_id;
-- Drop old column, rename new one
ALTER TABLE sem_id DROP COLUMN id;
ALTER TABLE sem_id RENAME COLUMN id_bigint TO id;
-- Use the pre-built index to create the new PK instantly
ALTER TABLE sem_id ADD PRIMARY KEY USING INDEX sem_id_id_bigint_pk;
COMMIT;

This script combines all the techniques we've discussed into a single, robust migration plan.

Conclusion

Today, we moved from the theory of the expand-contract pattern to a concrete, practical implementation for a common and critical database task.

Key Takeaways:

• Zero-downtime column changes are achieved by breaking the migration into non-locking or briefly-locking steps.
• Dual writing is the core of the "expand" phase and can be implemented in the application or via database triggers.
• Backfilling data must be done in small, non-locking batches to keep the system live.
• PostgreSQL provides powerful, low-lock features like CREATE INDEX CONCURRENTLY and ADD CONSTRAINT ... NOT VALID that are essential for these migrations.
• The final cutover is performed in a short, atomic transaction that consists only of fast metadata operations.

Preview of the Next Lesson

We've successfully modified a column. But what if the required change is far more complex, involving multiple columns, data transformations, or even changing the table's fundamental structure? For such scenarios, a column-by-column approach is too cumbersome.

In our next lesson, we will learn how to design a shadow table migration strategy for a complex schema change, outlining the trigger logic, backfill process, and cutover plan. This scales the expand-contract pattern up to the table level, enabling you to perform almost any schema modification with zero downtime.