Schema Migrations: Add, Rename and Backfill Without Downtime

A schema is never finished. Part 10 added five columns to a table that had four rows; this part adds them to a table with 200,000 rows that an app is reading and writing, and times each change. Some are instant on both engines. Some rewrite every row, and one of them is instant on PostgreSQL and a full rewrite on the SQL Server edition most people run. The clock and the lock tell you which is which, and the expand-and-contract pattern lets you rename a column without breaking a single running request.

How each change is measured

Every change runs inside a transaction, and right after the statement the script asks the engine three questions: how long did it take, what lock is it holding on the table, and how much has it written to the log. On SQL Server that is a small procedure over sys.dm_tran_locks and sys.dm_tran_database_transactions; on PostgreSQL it is \timing, pg_locks and one more tell-tale, pg_relation_filenode('appointments'), the number of the file the table lives in. A new file number means the table was rewritten. Both scripts also begin with a lock timeout, SET lock_timeout = '5s' and SET LOCK_TIMEOUT 5000, because every change here needs the strongest lock at least briefly, and an instant change that queues behind one long report blocks every query that arrives after it.

Adding columns

Section 2 adds a nullable column. Both engines finish in about a millisecond, SQL Server writes one kilobyte of log, PostgreSQL keeps its file number. The metadata changed; the rows did not. Section 3 adds a NOT NULL column with a default, twice:

ALTER TABLE appointments ADD COLUMN channel varchar(10) NOT NULL DEFAULT 'app';
Time: 0.289 ms

SELECT attname, atthasmissing, attmissingval FROM pg_attribute ...
 attname | atthasmissing | attmissingval
---------+---------------+---------------
 channel | t             | {app}

ALTER TABLE appointments ADD COLUMN import_id uuid NOT NULL DEFAULT gen_random_uuid();
Time: 240.514 ms

PostgreSQL 11 and later store a constant default once, in the catalogue, and hand it to every old row on read; the column exists for 200,000 rows in a quarter of a millisecond. A default that differs per row cannot be stored once, so gen_random_uuid() rewrites the table into a new file, 240 ms. SQL Server Express, which LocalDB reports itself as, writes every row for both:

step ms table_lock log_kb
3a. add NOT NULL DEFAULT 'app', run 1 367.0 Sch-M 39394
3b. add NOT NULL DEFAULT NEWID(), run 1 398.6 Sch-M 35151

Microsoft's documentation for ALTER TABLE says the constant-default case is metadata-only on Enterprise edition; on the edition measured here it is a rewrite of the same order as the random one, and the table doubled in size from 15 to 31 MB as the widened rows split their pages. If your migration adds a required column with a default to a big table on Standard or Express, plan it as a rewrite.

Renaming a column without breaking the app

Section 4a renames reason to visit_reason in one statement. It is instant, and the next query that says reason fails, column "reason" does not exist on PostgreSQL and Invalid column name 'reason' on SQL Server. Every running copy of the app that still uses the old name breaks at that instant, which is fine at 2 a.m. with one server and not fine otherwise. The safe rename is four steps across three deployments: expand, backfill, switch, contract. Section 4b adds the new column beside the old one. Section 4c fills it in batches, each its own transaction:

CREATE PROCEDURE backfill_visit_reason(batch_size integer) LANGUAGE plpgsql AS $$
DECLARE
    from_id bigint := 0;
    max_id  bigint;
    n       bigint;
    total   bigint := 0;
    t0      timestamptz := clock_timestamp();
    tb      timestamptz;
BEGIN
    SELECT max(id) INTO max_id FROM appointments;
    WHILE from_id < max_id LOOP
        tb := clock_timestamp();
        UPDATE appointments SET visit_reason = reason
        WHERE id > from_id AND id <= from_id + batch_size AND visit_reason IS NULL;
        GET DIAGNOSTICS n = ROW_COUNT;
        total := total + n;
        COMMIT;                                 -- each batch is its own transaction
        RAISE NOTICE '  ids %-%: % rows, % ms', from_id + 1, from_id + batch_size, n,
            round(extract(epoch FROM clock_timestamp() - tb) * 1000);
        from_id := from_id + batch_size;
    END LOOP;
    RAISE NOTICE 'backfill: % rows in % ms', total,
        round(extract(epoch FROM clock_timestamp() - t0) * 1000);
END
$$;

Twenty batches of 10,000 copied 200,000 rows in 2,098 ms on PostgreSQL and 619 ms on SQL Server, and no batch held a lock for more than a fraction of a second. The visit_reason IS NULL condition makes it safe to run again after a crash: the second run found 0 rows and finished in under 100 ms. Then the switch is a deployment, the app reads and writes the new column, and section 4e contracts, dropping the old column in a couple of milliseconds. One number from the SQL Server run changes the advice: its lock escalation counter went from 0 to 20 during the backfill, one escalation per batch, because a statement that touches more than about 5,000 rows trades its row locks for a table lock. Batches of 10,000 are fine on PostgreSQL, which has no escalation; on SQL Server keep them under 5,000. A second surprise is the cost on PostgreSQL's side: every updated row is a new row version, the table grew from 23 to 47 MB, and VACUUM makes that space reusable without shrinking the file.

Widening a type

Part 5 said to choose bigint for ids because changing it later is a rewrite. Section 5 changes a 200,000-row int column to bigint: 227 ms and 33 MB of log on SQL Server, 214 ms and a new file number on PostgreSQL, both holding the strongest lock throughout. Both scripts change it back and forward again to show the second run costs the same; the one asymmetry is that SQL Server narrowed the column back to int in 72 ms with 1 KB of log, checking the values rather than rewriting them. On a table with a foreign key pointing at the column, every referencing column must change too, each a rewrite of its own table. That is the cost Part 5 was pricing.

Adding an index while the app runs

A plain CREATE INDEX holds a shared lock for the build: reads continue, writes wait, 48 ms here and minutes on a table of real size. Section 6 tries the non-blocking form on each engine:

Msg 1712, Level 16, State 3, Line 4
Online index operations can only be performed in Enterprise edition of SQL Server or Azure SQL Edge.

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

SQL Server's WITH (ONLINE = ON) is an Enterprise feature. PostgreSQL's CREATE INDEX CONCURRENTLY is on every edition, refuses to run inside a transaction, and outside one built the same index in 54 ms while writes could continue. If a concurrent build fails, it leaves an invalid index behind that has to be dropped; the script's final listing shows all three indexes with indisvalid = t.

How EF Core migrations map to this

EF Core scaffolds AddColumn, RenameColumn, AlterColumn and CreateIndex from the model change and emits the statements above through its provider; a migration runs in one transaction by default, which is the wrong shape for a batched backfill, so the backfill goes through migrationBuilder.Sql(...) with the transaction suppressed or into a separate job, and the expand and contract steps become two migrations shipped with two deployments. The provider options exist for the index case, IsCreatedOnline() for SQL Server and IsCreatedConcurrently() for Npgsql, with the edition and transaction rules above. Run dotnet ef migrations script --idempotent before every production deploy, read the SQL, and paste it into the migration SQL explainer if you want each statement's cost spelled out. EF Core was not run for this part; the mapping is described, the SQL is what was measured.

Frequently asked

How do I add a NOT NULL column to a large table without downtime?

In PostgreSQL 11 and later a constant default is stored in the catalogue and the change is instant. In SQL Server it is instant only on Enterprise edition; on Standard, Express and LocalDB every row is rewritten, so add the column as nullable, backfill in batches, then add the NOT NULL constraint.

How do I rename a database column safely?

Expand, backfill, switch, contract: add the new column beside the old one, copy the values in small batches with a re-runnable UPDATE, deploy the app version that uses the new column, then drop the old one. A one-step rename breaks every running request that uses the old name.

Why should a backfill run in batches?

One UPDATE of every row holds locks and log space for the whole run and blocks the app. Batches of a few thousand rows each commit quickly and let other work through. On SQL Server keep batches under about 5,000 rows, because larger statements escalate row locks to a table lock.

Next: Part 12, search, views and the finished schema, the last part, with every table from the series in one script.