Online schema migration vs maintenance window

The decision

You must change the shape of a large table, for example add a column, change a type or split the table. A maintenance window stops traffic for a planned time, runs the change in one step and starts again. An online migration keeps the app running. It uses the expand and contract method. First add the new shape next to the old one and copy the data in small batches. Then move readers and writers over, and remove the old shape. A third way mixes them: do the slow work online, and use a window of seconds for the final switch. The forces are customer impact, risk, and the engineering time you spend.

Choose a maintenance window when

• The table is small, for example under 10 GB, so the change takes minutes.
• Your users sit in one time zone and you have a real quiet hour.
• Your contract or service goal allows planned downtime, and the window fits inside it.
• The change is rare, and the team has not built online tools.
• The change cannot be done in steps, for example a move to a new database engine with no way to copy changes.

Choose an online migration when

• The window would be longer than your monthly downtime allowance (see the numbers).
• Users are in many time zones, so no hour is quiet.
• The table is large and the change would rewrite it.
• You migrate often, so the tools pay back.
• You need a way back without restoring a backup.

Choose a hybrid (online preparation, a very short switch) when

• The slow part is copying data, but the switch itself can be done in seconds.
• You can pause writes for a few seconds at low risk, for example by holding requests in a queue.

The numbers that change the answer

Window length (assumptions plus calculation). Assumption: the table has 500,000,000 rows of 400 bytes. Size = 500,000,000 x 400 = 200,000,000,000 bytes = 200 GB. Assumption: a rewrite runs at 100 MB a second. Time = 200,000 MB / 100 = 2,000 seconds = 33 minutes. Assumption: index rebuilds double it, so 67 minutes.

Downtime allowance (calculation). A 30-day month has 30 x 24 x 60 = 43,200 minutes.

Money lost in the window (assumptions plus calculation). Assumption: revenue is Rs 5 crore (50,000,000) a day. Per minute that is 50,000,000 / 1,440 = Rs 34,722. Assumption: at night traffic is 8% of the average. Loss per minute = 34,722 x 0.08 = Rs 2,778. For 67 minutes: Rs 186,111. This is small. The cost that matters is the broken goal, support work and trust, not the lost sales.

Engineer time (assumptions). An engineer costs Rs 3,000,000 a year, or 3,000,000 / 48 working weeks = Rs 62,500 a week. Window: 0.6 weeks (about 3 days) = Rs 37,500. Online migration with five small releases and a copy job: 3 weeks = Rs 187,500. The online way costs about Rs 150,000 more, once. If you migrate 10 times a year, you build the tools once and later migrations cost less.

Copy time online (assumption plus calculation). The copy runs slowly so it does not hurt live traffic. At 5 MB a second: 200,000 MB / 5 = 40,000 seconds = 11.1 hours.

Cited facts (PostgreSQL). Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock, unless the manual says otherwise. Adding a column with a constant default does not rewrite the table, and a volatile default does. A standard index build blocks writes. CREATE INDEX CONCURRENTLY does not, but it scans the table twice, takes longer, and can leave an invalid index if it fails. With NOT VALID, ADD CONSTRAINT does not scan the table. The later VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock (see Sources). Changing the type of a column normally rewrites the whole table and its indexes.

The lock queue (cited rule plus known behaviour). The manual says an ACCESS EXCLUSIVE lock conflicts with locks of all modes. A transaction that wants a conflicting lock waits until it is released. So an ALTER must wait for every running query on the table. While it waits, new queries on that table can queue behind it. The manual page we read does not describe this queue in words, so test it on your own version. The setting lock_timeout aborts a statement that waits too long for a lock.

What breaks with each choice

Maintenance window.

• The change runs past the window. Rolling back a half-finished change can take as long again. Fallback: a time limit with a go or no-go checkpoint, and a tested restore.
• The window hides risk: you cannot test the real data size at 3 am for the first time.
• The goal is broken. At 99.9% the 67 minutes use the whole month and 24 minutes more (67 - 43.2 = 23.8).

Online migration.

• An ALTER waits behind a long query. New queries can queue behind the ALTER, so one small change can stop the app (see the lock queue above). Fallback: set a short lock_timeout for the session and retry.
• Old and new code run together, so each release must work with both shapes. Fallback: expand and contract with feature flags.
• The copy and the live writes disagree. Fallback: a verification query that compares counts and checksums before the switch.
• The copy hurts live traffic. Fallback: throttle by watching replica lag and latency.

Hybrid. The few seconds of paused writes become minutes if the switch step fails. Fallback: a script that was rehearsed on a copy of production and a way to resume writes at once.

What it costs

• People: about Rs 37,500 for a window and Rs 187,500 for a first online migration, both assumptions.
• Reversible-first path. Start with a window for small tables. Write the online steps for any table over 10 GB, or any service with a goal of 99.9% or better. Rehearse on a copy of production. Move to online-by-default when you migrate more than about once a month (Assumption). Move also when one window would use more than half your monthly allowance (here 67 minutes is 155% of 43.2).
• Changing your mind: a window plan can become an online plan before the date, at the cost of the extra weeks. After a destructive step, such as dropping the old column, there is no way back. So make that step last, and wait a week.

How each level answers

The question is: "Change the type of a column on a 200 GB table."

SDE-1: Take the site down, run the change and start again. Or add a new column and copy.

SDE-2: I add a new column, write to both, copy old rows in batches, move reads, then drop the old column. The site stays up.

Senior: The rewrite is about 67 minutes. At 99.9% we have only 43.2 minutes a month, so a window does not fit. I go online with a lock timeout, a throttled copy of about 11 hours, and a check before the switch. The fallback is to keep the old column for a week.

Staff: I compare Rs 186,111 of lost sales, which is small, with the broken goal and the trust we lose, which is large. I pay the extra Rs 150,000 of engineer time once and put the steps in a shared tool so each later migration is cheaper. I own the runbook and the rule: no destructive step in the same release as the switch. I measure window minutes used, failed migrations and rollbacks. I revisit it when the data doubles or the goal changes.

Say it in the interview

For 200 GB I would go online with expand and contract. I would add the new column, write both, copy in batches, switch reads, and drop the old column a week later. A window would be about 67 minutes, which is more than the 43.2 minutes a 99.9% goal allows in a month. For a small table or one quiet hour, I would take a window to save engineer time.

Common mistakes

• Saying "online means no risk". Online work has its own risks, such as lock queues and a copy that disagrees with live writes.
• Counting only lost sales. At night the sales loss is small, but the goal, support load and customer trust matter more.
• Dropping the old column in the same release. It removes your way back.
• Never rehearsing. The first run on real data size should not be the real one.

Sources

• PostgreSQL manual, ALTER TABLE: an ACCESS EXCLUSIVE lock unless noted; a non-volatile default is stored in metadata without a rewrite, a volatile default rewrites the table; changing a column type normally rewrites the table; NOT VALID constraints and VALIDATE CONSTRAINT (a SHARE UPDATE EXCLUSIVE lock). https://www.postgresql.org/docs/current/sql-altertable.html (opens in a new tab) (checked 26 Oct 2026)
• PostgreSQL manual, CREATE INDEX: a concurrent build does not block writes, scans the table twice, takes longer, and may leave an invalid index. https://www.postgresql.org/docs/current/sql-createindex.html (opens in a new tab) (checked 26 Oct 2026)
• PostgreSQL manual, Explicit Locking: ACCESS EXCLUSIVE conflicts with locks of all modes; a transaction seeking a conflicting lock waits for it to be released. https://www.postgresql.org/docs/current/explicit-locking.html (opens in a new tab) (checked 26 Oct 2026)
• PostgreSQL manual, client connection settings: lock_timeout aborts a statement that waits longer than the set time for a lock. https://www.postgresql.org/docs/current/runtime-config-client.html (opens in a new tab) (checked 26 Oct 2026)
• The table size, copy speeds, revenue, night traffic share, salary and weeks of work are assumptions. The other numbers are calculations shown above.