{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"altering-a-live-table","title":"Changing a table that is in use","url":"https://addaly.com/learn/data-and-sql/altering-a-live-table","position":61,"of":66,"minutes":9,"answer":"Add nullable, backfill in batches, then constrain, because adding NOT NULL or an expression default rewrites or scans a live table under the strongest lock.","keyIdea":"Add nullable, backfill in batches, then constrain, because adding NOT NULL or an expression default rewrites or scans a live table under the strongest lock; set a lock timeout so a queued ALTER cannot take the site down behind one long report."},"body":{"format":"markdown","text":"## The schema is not finished, and the table is busy\n\nThe `orders` table has forty million rows, a web application reading from it every second, and it needs a new column. `ALTER TABLE orders ADD COLUMN channel text;` looks like nothing. Whether it is nothing depends on what the database has to do to every existing row, and on who is holding a lock at the moment you run it.\n\n## What is instant and what rewrites the table\n\nAdding a nullable column with no default is a metadata change in every major engine: the catalogue is updated, existing rows are untouched and read as NULL for the new column, and it completes in milliseconds regardless of table size.\n\nAdding a column with a constant `DEFAULT` used to rewrite every row in Postgres, minutes to hours on a big table. Since Postgres 11 a constant default is also metadata only, with the default applied on read for old rows. A default that is an expression, such as `DEFAULT now()`, still rewrites. Changing a column's type rewrites unless the new type is a strict widening, `varchar(50)` to `text` for instance. Adding `NOT NULL` to an existing column scans the whole table to prove that no NULL exists, and holds a lock while it does.\n\nThe pattern for a new required column on a live table is therefore three steps rather than one:\n\n```sql\nALTER TABLE orders ADD COLUMN channel text;             -- instant, nullable\n-- backfill in batches of 10,000 with UPDATE ... WHERE channel IS NULL LIMIT-style loops\nALTER TABLE orders ADD CONSTRAINT orders_channel_nn CHECK (channel IS NOT NULL) NOT VALID;\nALTER TABLE orders VALIDATE CONSTRAINT orders_channel_nn;   -- scans without the heavy lock\nALTER TABLE orders ALTER COLUMN channel SET NOT NULL;       -- instant, since the check proves it\n```\n\n`NOT VALID` adds the rule for new rows immediately; `VALIDATE` checks old rows while allowing reads and writes to continue. Postgres 12 and later recognises the validated check and sets `NOT NULL` without rescanning.\n\n> Figure: Adding a required column to a table that is read every second. Timeline: 00:00, SET lock_timeout = '3s', so a queued ALTER cannot take the site down behind one long report; 00:00, ADD COLUMN channel text: nullable, no default, a catalogue change of a few milliseconds; 00:05–02:00, Backfill in batches of ten thousand, each committing on its own so others get a turn; 02:05, ADD CONSTRAINT ... CHECK (channel IS NOT NULL) NOT VALID: new rows are covered immediately; 02:06, VALIDATE CONSTRAINT: the old rows are scanned while reads and writes continue; 02:20, SET NOT NULL: instant, because the validated check has already proved there are no NULLs. The danger is not the ALTER, which takes milliseconds. It is the queue behind it: a long report holds a share lock, the ALTER waits, and every new query waits behind the ALTER. The site looks down. A lock timeout means the change fails instead, and you try again when the report has finished.\n\n## The lock queue\n\n`ALTER TABLE` takes the strongest lock there is, `ACCESS EXCLUSIVE`, which waits for every current reader and writer to finish, and blocks every new one until it is done. On a busy table the danger is not the ALTER itself but the queue behind it: a long-running report holds a share lock, the ALTER waits behind it, and every ordinary query that arrives waits behind the ALTER. The site appears to be down. It is not; it is queued behind a metadata change that would have taken two milliseconds if it had been allowed to start.\n\nSet a lock timeout before any schema change on a live database: `SET lock_timeout = '3s';`. If the ALTER cannot get its lock in three seconds, it fails, nothing is queued, and you try again when the report has finished. Creating an index has its own version of the problem and its own fix, `CREATE INDEX CONCURRENTLY`, which builds the index without blocking writes, at the cost of taking longer and needing to be run outside a transaction.\n\n## Rename nothing that is in use\n\nRenaming a column breaks every query, view and application that uses the old name at the instant the rename commits. The safe sequence is additive: add the new column, have the application write both, backfill, switch reads to the new column, stop writing the old one, and drop it in a later release. It is more steps and it means no moment at which anything is broken. The same applies to dropping a column: first confirm nothing reads it, which a quick grep of the code and a look at the views that depend on it will tell you.\n\n## Migrations are files, not commands\n\nA schema change typed into a GUI against production is a change nobody can review, repeat on the test database, or reverse. The discipline is a numbered file per change, `0042_add_orders_channel.sql`, applied in order, with a table in the database recording which have run. The free tools that do exactly this are Flyway, Liquibase, dbmate and sqitch, and most application frameworks ship their own; the earlier lesson on Postgres transactions is why they can apply a multi-statement migration atomically there and not in MySQL. The file is also the documentation: six months later, the reason a column exists is the commit message on the migration that added it.\n\n## The habit\n\nBefore a schema change, answer three questions in writing: does this rewrite the table, what lock does it take, and what breaks if the old name disappears. Then set a lock timeout, and run it from a migration file that has already run on a copy."},"check":{"question":"A team runs `ALTER TABLE orders ADD COLUMN channel text` on a busy production table during the day. The statement itself should take milliseconds, yet the site stops responding for four minutes. What is the most likely mechanism?","options":[{"text":"Adding a text column rewrites every row to make space for it, and forty million rows take four minutes.","correct":false,"tempting":"A nullable column with no default is metadata only; existing rows are not touched."},{"text":"The ALTER waited behind a long-running query for its exclusive lock, and every new query queued behind the ALTER until that query finished.","correct":true},{"text":"The application's connection pool refreshed its cached column list and reconnected every client at once, overwhelming the server for the duration of the reconnect storm.","correct":false,"tempting":"Clients adapt to a new column without reconnecting; the outage pattern here is a lock queue, not a reconnection storm."},{"text":"Postgres automatically rebuilt every index on the table to include the new column, holding a lock throughout.","correct":false,"tempting":"Indexes contain only the columns they were defined on; a new column is not added to them."}]},"prev":{"slug":"concurrent-writes","title":"Two people, one row: lost updates and how the database prevents them","url":"https://addaly.com/learn/data-and-sql/concurrent-writes"},"next":{"slug":"views-and-saved-questions","title":"Views: a saved question, not a saved answer","url":"https://addaly.com/learn/data-and-sql/views-and-saved-questions"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Changing a table that is in use', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/altering-a-live-table (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Changing a table that is in use","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"Add nullable, backfill in batches, then constrain, because adding NOT NULL or an expression default rewrites or scans a live table under the strongest lock; set a lock timeout so a queued ALTER cannot take the site down behind one long report.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"timeline","title":"Adding a required column to a table that is read every second","events":[{"when":"00:00","what":"SET lock_timeout = '3s', so a queued ALTER cannot take the site down behind one long report"},{"when":"00:00","what":"ADD COLUMN channel text: nullable, no default, a catalogue change of a few milliseconds"},{"when":"00:05–02:00","what":"Backfill in batches of ten thousand, each committing on its own so others get a turn"},{"when":"02:05","what":"ADD CONSTRAINT ... CHECK (channel IS NOT NULL) NOT VALID: new rows are covered immediately"},{"when":"02:06","what":"VALIDATE CONSTRAINT: the old rows are scanned while reads and writes continue"},{"when":"02:20","what":"SET NOT NULL: instant, because the validated check has already proved there are no NULLs"}],"caption":"The danger is not the ALTER, which takes milliseconds. It is the queue behind it: a long report holds a share lock, the ALTER waits, and every new query waits behind the ALTER. The site looks down. A lock timeout means the change fails instead, and you try again when the report has finished."},"alt":"Figure: Adding a required column to a table that is read every second. Timeline: 00:00, SET lock_timeout = '3s', so a queued ALTER cannot take the site down behind one long report; 00:00, ADD COLUMN channel text: nullable, no default, a catalogue change of a few milliseconds; 00:05–02:00, Backfill in batches of ten thousand, each committing on its own so others get a turn; 02:05, ADD CONSTRAINT ... CHECK (channel IS NOT NULL) NOT VALID: new rows are covered immediately; 02:06, VALIDATE CONSTRAINT: the old rows are scanned while reads and writes continue; 02:20, SET NOT NULL: instant, because the validated check has already proved there are no NULLs. The danger is not the ALTER, which takes milliseconds. It is the queue behind it: a long report holds a share lock, the ALTER waits, and every new query waits behind the ALTER. The site looks down. A lock timeout means the change fails instead, and you try again when the report has finished."}},"blocks":[{"t":"h2","id":"the-schema-is-not-finished-and-the-table-is-busy","runs":[{"text":"The schema is not finished, and the table is busy"}]},{"t":"p","runs":[{"text":"The "},{"text":"orders","code":true},{"text":" table has forty million rows, a web application reading from it every second, and it needs a new column. "},{"text":"ALTER TABLE orders ADD COLUMN channel text;","code":true},{"text":" looks like nothing. Whether it is nothing depends on what the database has to do to every existing row, and on who is holding a lock at the moment you run it."}]},{"t":"h2","id":"what-is-instant-and-what-rewrites-the-table","runs":[{"text":"What is instant and what rewrites the table"}]},{"t":"p","runs":[{"text":"Adding a nullable column with no default is a metadata change in every major engine: the catalogue is updated, existing rows are untouched and read as NULL for the new column, and it completes in milliseconds regardless of table size."}]},{"t":"p","runs":[{"text":"Adding a column with a constant "},{"text":"DEFAULT","code":true},{"text":" used to rewrite every row in Postgres, minutes to hours on a big table. Since Postgres 11 a constant default is also metadata only, with the default applied on read for old rows. A default that is an expression, such as "},{"text":"DEFAULT now()","code":true},{"text":", still rewrites. Changing a column’s type rewrites unless the new type is a strict widening, "},{"text":"varchar(50)","code":true},{"text":" to "},{"text":"text","code":true},{"text":" for instance. Adding "},{"text":"NOT NULL","code":true},{"text":" to an existing column scans the whole table to prove that no NULL exists, and holds a lock while it does."}]},{"t":"p","runs":[{"text":"The pattern for a new required column on a live table is therefore three steps rather than one:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE orders ADD COLUMN channel text;             -- instant, nullable\n-- backfill in batches of 10,000 with UPDATE ... WHERE channel IS NULL LIMIT-style loops\nALTER TABLE orders ADD CONSTRAINT orders_channel_nn CHECK (channel IS NOT NULL) NOT VALID;\nALTER TABLE orders VALIDATE CONSTRAINT orders_channel_nn;   -- scans without the heavy lock\nALTER TABLE orders ALTER COLUMN channel SET NOT NULL;       -- instant, since the check proves it"},{"t":"p","runs":[{"text":"NOT VALID","code":true},{"text":" adds the rule for new rows immediately; "},{"text":"VALIDATE","code":true},{"text":" checks old rows while allowing reads and writes to continue. Postgres 12 and later recognises the validated check and sets "},{"text":"NOT NULL","code":true},{"text":" without rescanning."}]},{"t":"h2","id":"the-lock-queue","runs":[{"text":"The lock queue"}]},{"t":"p","runs":[{"text":"ALTER TABLE","code":true},{"text":" takes the strongest lock there is, "},{"text":"ACCESS EXCLUSIVE","code":true},{"text":", which waits for every current reader and writer to finish, and blocks every new one until it is done. On a busy table the danger is not the ALTER itself but the queue behind it: a long-running report holds a share lock, the ALTER waits behind it, and every ordinary query that arrives waits behind the ALTER. The site appears to be down. It is not; it is queued behind a metadata change that would have taken two milliseconds if it had been allowed to start."}]},{"t":"p","runs":[{"text":"Set a lock timeout before any schema change on a live database: "},{"text":"SET lock_timeout = '3s';","code":true},{"text":". If the ALTER cannot get its lock in three seconds, it fails, nothing is queued, and you try again when the report has finished. Creating an index has its own version of the problem and its own fix, "},{"text":"CREATE INDEX CONCURRENTLY","code":true},{"text":", which builds the index without blocking writes, at the cost of taking longer and needing to be run outside a transaction."}]},{"t":"h2","id":"rename-nothing-that-is-in-use","runs":[{"text":"Rename nothing that is in use"}]},{"t":"p","runs":[{"text":"Renaming a column breaks every query, view and application that uses the old name at the instant the rename commits. The safe sequence is additive: add the new column, have the application write both, backfill, switch reads to the new column, stop writing the old one, and drop it in a later release. It is more steps and it means no moment at which anything is broken. The same applies to dropping a column: first confirm nothing reads it, which a quick grep of the code and a look at the views that depend on it will tell you."}]},{"t":"h2","id":"migrations-are-files-not-commands","runs":[{"text":"Migrations are files, not commands"}]},{"t":"p","runs":[{"text":"A schema change typed into a GUI against production is a change nobody can review, repeat on the test database, or reverse. The discipline is a numbered file per change, "},{"text":"0042_add_orders_channel.sql","code":true},{"text":", applied in order, with a table in the database recording which have run. The free tools that do exactly this are Flyway, Liquibase, dbmate and sqitch, and most application frameworks ship their own; the earlier lesson on Postgres transactions is why they can apply a multi-statement migration atomically there and not in MySQL. The file is also the documentation: six months later, the reason a column exists is the commit message on the migration that added it."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Before a schema change, answer three questions in writing: does this rewrite the table, what lock does it take, and what breaks if the old name disappears. Then set a lock timeout, and run it from a migration file that has already run on a copy."}]}],"check":{"question":[{"text":"A team runs "},{"text":"ALTER TABLE orders ADD COLUMN channel text","code":true},{"text":" on a busy production table during the day. The statement itself should take milliseconds, yet the site stops responding for four minutes. What is the most likely mechanism?"}],"options":[{"text":[{"text":"Adding a text column rewrites every row to make space for it, and forty million rows take four minutes."}],"correct":false,"tempting":[{"text":"A nullable column with no default is metadata only; existing rows are not touched."}]},{"text":[{"text":"The ALTER waited behind a long-running query for its exclusive lock, and every new query queued behind the ALTER until that query finished."}],"correct":true,"tempting":null},{"text":[{"text":"The application’s connection pool refreshed its cached column list and reconnected every client at once, overwhelming the server for the duration of the reconnect storm."}],"correct":false,"tempting":[{"text":"Clients adapt to a new column without reconnecting; the outage pattern here is a lock queue, not a reconnection storm."}]},{"text":[{"text":"Postgres automatically rebuilt every index on the table to include the new column, holding a lock throughout."}],"correct":false,"tempting":[{"text":"Indexes contain only the columns they were defined on; a new column is not added to them."}]}],"order":[0,2,1,3],"why":null},"prev":{"slug":"concurrent-writes","title":"Two people, one row: lost updates and how the database prevents them","minutes":9},"next":{"slug":"views-and-saved-questions","title":"Views: a saved question, not a saved answer","minutes":8}}}