Using the Expand and Contract Pattern in PostgreSQL

When you change the structure of a PostgreSQL database while application instances are running, the expand and contract pattern lets old and new application versions work at the same time. You add the new database structure alongside the old one, keep both versions compatible while you move data and switch which column the code reads, then remove the old structure after no supported code depends on it.

The pattern is a sequence for deploying changes, not a PostgreSQL feature with a fixed recipe. PostgreSQL provides operations such as adding a column and updating rows. The order in which you deploy application versions determines when each operation is safe. The example below replaces full_name with display_name while the service continues handling requests. This approach avoids switching everything at once, but it does not guarantee zero downtime.

Add the new column, then deploy compatible writers

In this example, customers.full_name stores the name shown to users. Add display_name without a default so existing rows have a NULL value until the migration fills them:

ALTER TABLE customers
  ADD COLUMN display_name text;

According to PostgreSQL 18, existing rows return NULL for a column added without a default. A constant default does not require PostgreSQL to update every existing row during the ALTER TABLE. A volatile default, such as clock_timestamp(), can require PostgreSQL to work on each row. Check the table modification documentation for the PostgreSQL version you run and the exact change you plan to make.

PostgreSQL needs a table lock even for changes that avoid rewriting every row; the lock limits what other operations can do with the table. PostgreSQL documents ACCESS EXCLUSIVE as the default lock for ALTER TABLE, with exceptions for specific forms of the command. The command can wait for conflicting activity, and the lock can block other work while PostgreSQL holds it. Check the lock behavior for your exact command and test it on a representative database before running it in production. See the ALTER TABLE reference.

Next, deploy a version that still reads full_name but writes to both columns in the same transaction. A transaction groups database changes so PostgreSQL applies them together:

UPDATE customers
SET full_name = $1,
    display_name = $1
WHERE id = $2;

Apply the same rule when inserting rows. Older instances that write only full_name can keep running during this rollout because that column remains in place. Keep reading full_name until every older instance that writes only that column has stopped. Otherwise, an older instance can update full_name after you copy its value to display_name, leaving the two columns out of sync.

Check every piece of code that writes to the database. This includes background jobs and scheduled tasks, as well as scripts, direct SQL clients, and code that handles requests. If you cannot make all of them write both columns, use a compatibility mechanism such as a database trigger, or wait to switch reads to display_name. PostgreSQL’s MVCC documentation explains how concurrent transactions see database snapshots, or views of the data at a point in time, and how PostgreSQL handles row updates. MVCC does not make separate application writes atomic. Assign both columns in one statement and transaction.

Backfill existing rows in batches

After all code that writes only the old column has stopped, copy its existing values into display_name as a backfill. For a large table, update a limited number of rows in each transaction instead of keeping one transaction open for the whole migration.

Assuming id is the primary key, a batch can look like this:

WITH batch AS (
  SELECT id
  FROM customers
  WHERE display_name IS NULL
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1000
)
UPDATE customers AS c
SET display_name = c.full_name
FROM batch
WHERE c.id = batch.id;

Commit each batch separately and repeat the statement. The value 1000 is an example starting point, not a PostgreSQL recommendation. Adjust it based on production load, then check application latency and replication lag as the updates run.

PostgreSQL’s UPDATE documentation shows a similar approach that updates rows in repeated batches. It explains that large updates can add to table bloat, where obsolete row versions take up space, delay changes reaching replicas, and cause lock contention. When a batch uses SKIP LOCKED, run a final pass without it to include rows the batch skipped because another transaction held their locks. The backfill creates new versions of updated rows. PostgreSQL’s VACUUM documentation explains how vacuum reclaims space from obsolete versions.

Verify the data before switching reads

Before deploying code that reads display_name, check for missing values and mismatches:

SELECT
  count(*) FILTER (WHERE display_name IS NULL) AS missing,
  count(*) FILTER (
    WHERE display_name IS DISTINCT FROM full_name
  ) AS mismatched
FROM customers;

For a direct copy, both counts should be zero before you switch the code to read display_name. If the new field transforms the old value, compare display_name with the expected result of that transformation instead of requiring the values to match exactly. Also account for every piece of code that can still change either column. A successful backfill check confirms row values only at the time of the check.

Deploy the new read path after verification, and continue writing both columns. That keeps full_name available to older code during the rollback window. If a rollback returns reads to full_name, dual writes ensure that column still receives current values.

Remove the old column after the rollback window

Once supported code no longer reads full_name and you no longer need to roll back to a version that depends on it, stop writing the old column. Drop full_name in a later schema change:

ALTER TABLE customers
  DROP COLUMN full_name;

Dropping the column ends compatibility with code that expects it. PostgreSQL also notes that dependent objects, such as views, can prevent a drop unless you remove those dependencies. Review dependencies before running the command; CASCADE removes dependent objects as well, so do not use it as a shortcut for that review. See the column removal guidance.

Stop writing to the old column in one deployment, then drop it in a later one. This gives each deployment time to settle before you make the irreversible change. Keep the old column until the rollback window has passed. Before you drop it, check that no supported code or database object depends on it.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com