Photo by Taylor Vick on Unsplash
Online Schema Changes: Altering Tables Without Downtime
Adding a column to a table with millions of rows used to mean locking the entire table for minutes or hours, blocking every query until the operation completed. Modern databases now support online schema changes that allow reads and writes to continue while the table structure is being modified. The techniques that make this possible reveal fundamental tradeoffs between consistency, performance, and operational complexity.
The Locking Problem
Traditional schema changes acquire exclusive locks because they need to rewrite data files and update metadata atomically. Adding a column might require rewriting every row to include space for the new field, or at minimum updating the table’s schema version in a way that no query can use stale metadata. During this time, any query that tries to access the table must wait.
The problem compounds with table size. A schema change that takes 30 seconds on a small table might take 30 minutes on a production dataset, creating an unacceptable window where the application is effectively down.
Shadow Copying and Cutover
One common approach creates a new table with the desired schema, copies data from the old table in the background, and performs a quick atomic switch once copying completes. MySQL’s pt-online-schema-change and gh-ost tools popularized this pattern.
The trick is handling writes that arrive during the copy. These tools use triggers or binlog parsing to capture changes to the original table and replay them on the shadow table. When the copy finishes and the replay catches up, a brief lock swaps the table names, redirecting all queries to the new structure.
This works well for adding columns or indexes but requires careful handling of foreign keys and triggers, which must be recreated on the new table. The double-write overhead during copying also increases load, and the final cutover, while brief, still requires a lock that can queue up queries.
Multi-Version Schema Concurrency
Postgres takes a different approach for many operations by maintaining multiple schema versions simultaneously. When you add a column with a default value, recent versions write only metadata changes instead of rewriting the table. Rows store the old format, but queries transparently see the new column populated with its default.
This works because Postgres’s MVCC system already handles multiple versions of rows. Extending that concept to schema versions allows old transactions to see the old schema while new transactions see the updated one. The actual row rewriting happens lazily as rows are updated for other reasons, or during routine vacuuming.
The limitation is that not all schema changes support this path. Dropping a column still leaves the data on disk (Postgres marks it invisible rather than rewriting). Changes that require validating constraints or rebuilding indexes still need to scan the full table, though they can do so without blocking concurrent writes.
Transactional DDL and Dependency Tracking
Online schema changes become more complex when dealing with views, stored procedures, and dependent objects. Some databases handle DDL transactionally, allowing schema changes to be rolled back if they fail or if dependent objects break.
Postgres supports transactional DDL, meaning you can alter a table, create indexes, and update views in a single transaction that either fully succeeds or fully rolls back. This prevents inconsistent states where a table schema changes but a dependent view still references the old structure.
Non-transactional systems like MySQL historically required more careful orchestration, though recent versions have improved. The tradeoff is that transactional DDL requires more sophisticated locking to prevent concurrent schema changes from interfering, while non-transactional DDL can be faster for simple operations.
The Unavoidable Lock
Even the most sophisticated online schema change eventually needs a brief exclusive lock to commit the final metadata update. The difference is the duration. A well-designed system might hold an exclusive lock for milliseconds to update catalog tables, versus minutes to rewrite data.
The challenge is acquiring that brief lock when the table is under constant use. If queries continuously hold read locks, the schema change might wait indefinitely. Some systems use lock timeouts or query priority mechanisms to ensure the schema change eventually gets its brief exclusive window.
Choosing the Right Tool
Different workloads favor different approaches. Shadow copying works universally but doubles write load and storage requirements during the migration. Multi-version approaches are faster and more efficient but may not support all schema change types. The right choice depends on table size, write rate, acceptable downtime window, and the specific operation being performed.
Understanding these mechanisms helps when a schema change doesn’t behave as expected, or when choosing between database systems with different operational characteristics.