Upgrading

CREATE INDEX holds a SHARE lock for the entire build, blocking INSERT/UPDATE/DELETE on the table. Build time scales with table size, so a migration that is instant in dev blocks writes for minutes in production.

CREATE INDEX idx_orders_region ON orders (region);

ALTER COLUMN TYPE takes ACCESS EXCLUSIVE and, unless the change is binary-coercible, rewrites every row. The table is completely unavailable, reads included, for the duration.

ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Between the migration running and the last old app instance stopping, old code still queries the old name. A rename or drop makes those queries error immediately, a partial outage that lasts exactly as long as your rollout.

ALTER TABLE users RENAME COLUMN email TO email_address;

Adding a column with a constant default is instant on modern Postgres, so teams assume all defaults are. A volatile default must be evaluated per existing row, so Postgres rewrites the whole table under ACCESS EXCLUSIVE, reads and writes blocked for the duration.

ALTER TABLE users ADD COLUMN uid uuid DEFAULT gen_random_uuid();

Adding a validated foreign key scans every existing row while holding a SHARE ROW EXCLUSIVE lock on both tables. With NOT VALID the constraint applies to new writes instantly, and VALIDATE CONSTRAINT can scan later with a much weaker lock.

ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (user_id) REFERENCES users (id);

Adding a validated CHECK constraint scans the whole table while holding ACCESS EXCLUSIVE, nothing can read or write until the scan finishes. NOT VALID makes the ALTER instant; VALIDATE CONSTRAINT afterwards only takes SHARE UPDATE EXCLUSIVE, which does not block reads or writes.

ALTER TABLE orders ADD CONSTRAINT positive CHECK (total >= 0);

CASCADE resolves the dependency graph at execution time and drops everything in it, without listing what it took. What it removes in production can differ from dev, a view or foreign key added since, gone silently.

DROP TABLE legacy_events CASCADE;

SET NOT NULL must verify every existing row, and it does so while holding ACCESS EXCLUSIVE, the table is fully unavailable for the scan. The safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which does not block), then SET NOT NULL, which sees the validated constraint and skips the scan.

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

ADD PRIMARY KEY / ADD UNIQUE builds a whole index while holding ACCESS EXCLUSIVE, build time scales with table size and the table is completely unavailable meanwhile. Building the index CONCURRENTLY first and attaching it with ADD CONSTRAINT ... USING INDEX reduces the exclusive lock to a metadata swap.

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (id);

A WHERE-less UPDATE/DELETE rewrites or removes every row in a single transaction: it locks all rows for the duration, doubles the table in dead tuples, floods WAL, and stalls replicas. Backfills belong in batched jobs, not migrations.

UPDATE orders SET region = 'eu';

Adding an enum value has transaction restrictions: before Postgres 12 it errors inside a transaction block, and on 12+ the new value is unusable until the transaction commits. A migration runner that wraps the file in one transaction either fails outright or fails on the first statement that uses the new value.

ALTER TYPE order_status ADD VALUE 'archived';
UPDATE orders SET status = 'archived';

DETACH PARTITION without CONCURRENTLY holds ACCESS EXCLUSIVE on the parent and the partition, the whole partition tree stalls behind it. ATTACH PARTITION validates the partition bound by scanning the incoming table while the parent is locked, unless a CHECK constraint matching the bound already exists to skip the scan.

ALTER TABLE measurements DETACH PARTITION measurements_2024;

A plain REFRESH MATERIALIZED VIEW takes an exclusive lock on the view while it recomputes, every SELECT against it blocks for the whole rebuild. REFRESH ... CONCURRENTLY lets readers keep the old contents during the rebuild; it requires a unique index on the view.

REFRESH MATERIALIZED VIEW daily_revenue;

LOCK TABLE holds its lock until the transaction commits. Migration runners wrap the file in one transaction, so an explicit lock taken early is held across every remaining statement, a manual outage whose duration is the rest of the migration.

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

A STORED generated column must be computed for every existing row, so adding one rewrites the entire table under ACCESS EXCLUSIVE, the constant-default fast path does not apply.

ALTER TABLE orders ADD COLUMN total_c numeric GENERATED ALWAYS AS (subtotal + tax) STORED;

Moving a table to another tablespace physically copies every block while holding ACCESS EXCLUSIVE. Copy time scales with table size; the table is unavailable throughout.

ALTER TABLE orders SET TABLESPACE fast_ssd;

Switching a table between LOGGED and UNLOGGED rewrites the whole table (SET LOGGED additionally writes every row to WAL) under ACCESS EXCLUSIVE.

ALTER TABLE staging_events SET LOGGED;

DISABLE TRIGGER ALL includes the system triggers that enforce foreign keys, writes made while it is off can violate referential integrity permanently, and re-enabling does not re-check them. Disabling a single trigger silently skips whatever logic it carried.

ALTER TABLE orders DISABLE TRIGGER ALL;

Dropping a column destroys its data irreversibly, and any app instance deployed before the migration still selects or writes the column until the rollout completes, those queries fail immediately. The safe order is: stop reading it in code, deploy, then drop one release later.

ALTER TABLE users DROP COLUMN legacy_flags;

ADD COLUMN ... UNIQUE (or PRIMARY KEY) creates the constraint's index inside the ALTER, under ACCESS EXCLUSIVE, the same trap as ADD CONSTRAINT, hidden in a column definition.

ALTER TABLE users ADD COLUMN email text UNIQUE;

SET lock_timeout = 0 (or statement_timeout = 0) switches the safety off: zero means 'wait forever'. A migration that disables its timeouts can queue behind one slow query indefinitely, with all new traffic queueing behind it, precisely the stall the guard exists to prevent.

SET statement_timeout = 0;
ALTER TABLE orders ADD COLUMN region text;

GRANT ... TO PUBLIC applies to every role the cluster has now, and every role created later, forever. Granting write privileges (or ALL) on a table to PUBLIC turns one migration line into a standing policy that no future account can be excluded from.

GRANT ALL ON orders TO PUBLIC;

pg_stat_user_indexes counts how many times the planner chose each index. Dropping one with thousands of scans since the last stats reset sends every one of those queries to a sequential scan or a worse index, a regression that lands the moment the migration applies, invisible in a dev database where nothing has run. Silent when the counters are too young to trust, or when another index with the same leading columns remains.

DROP INDEX CONCURRENTLY idx_orders_region;

NOT VALID is half of a two-step pattern: add the constraint instantly (protecting new writes), then VALIDATE CONSTRAINT in a later migration to check existing rows without an exclusive lock. Skip the second step and the rows that were already there are never checked, the constraint looks enforced, the planner may even trust it, and the data underneath is not. Fires only when the rest of the migration set is visible and no later file validates it.

ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- ...and no later migration runs VALIDATE CONSTRAINT orders_user_fk

A column called password, api_key, secret or token that receives a literal in a DEFAULT, an INSERT or an UPDATE is a credential written into the migration by hand, the seeded admin account with password 'admin', the api_key DEFAULT 'changeme' that ships to every row. The name is the evidence, so this is a warning rather than a certainty: the rule stays silent on hashed values (bcrypt, argon2, SCRAM, pbkdf2), on empty strings and placeholders, and on names that merely contain the word (password_hash, token_type, api_key_id).

INSERT INTO users (email, password) VALUES ('admin@example.com', 'admin123');

DB_PASSWORD=hunter2 in a .env, "client_secret": "…" in a JSON config, api_key = '…' in a settings module, password: … in a YAML manifest, a key whose name says secret, given a literal instead of a reference. The name is the evidence, so this is a warning rather than a certainty: the scanner stays silent when the value is a placeholder, an environment or template reference, a hash, a type annotation, or a word like example or test, and on names that merely contain the word (password_hash, token_type, api_key_id).

DB_PASSWORD=hunter2
STRIPE_SECRET="sk_live_…"
"client_secret": "0f3a…"

When foreign keys are enforced (PRAGMA foreign_keys=ON, which most applications set on every connection), a column added with a REFERENCES clause must default to NULL, anything else fails with "Cannot add a REFERENCES column with non-NULL default value". Whether it fails depends on a per-connection pragma, so the same file passes in one runner and stops in another.

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER REFERENCES warehouses(id) DEFAULT 1;

DROP COLUMN edits the schema and then rewrites every row of the table to purge the column's values, one write transaction that holds the database's single write lock for the length of the copy. It also fails outright if anything else in the schema names the column (an index, a foreign key, a view, a trigger, a generated column) and is unsupported before SQLite 3.35.

ALTER TABLE orders DROP COLUMN legacy_flag;

A rename is instant in SQLite, and that is the problem: the moment it commits, every process still running the previous release fails on the old name. Views and triggers are rewritten to the new name, but application SQL is not.

ALTER TABLE orders RENAME COLUMN qty TO quantity;

This migration rebuilds a table the documented way, create the new table, copy, drop the old, rename, but never turns foreign keys off first. With PRAGMA foreign_keys=ON the DROP TABLE performs an implicit DELETE FROM: rows referencing the table fail the migration with a constraint error, or ON DELETE CASCADE quietly removes them.

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

PRAGMA foreign_keys and PRAGMA journal_mode do nothing while a transaction is open, SQLite does not error, it silently leaves the setting as it was. A rebuild that switches foreign keys off after BEGIN runs with them on, and everything SL009 warns about applies.

BEGIN;
PRAGMA foreign_keys = OFF;
DROP TABLE orders;
COMMIT;

PRAGMA foreign_keys=OFF is per connection and does not reset at COMMIT. A migration that turns enforcement off for a rebuild and never turns it on leaves the runner's connection accepting orphans for as long as it lives, every later statement in the run, and any backfill sharing the connection, goes unchecked.

PRAGMA foreign_keys = OFF;
DROP TABLE orders;

An UPDATE or DELETE with no WHERE touches every row in one write transaction. SQLite has one writer at a time: the whole application queues behind it (or hits SQLITE_BUSY) for as long as the statement runs, and the rollback journal or WAL grows by the size of the change.

UPDATE orders SET status = 'archived';

VACUUM rewrites the entire database file into a temporary copy and swaps it in: it needs up to twice the database's size in free disk space, holds the write lock for the whole rewrite, and fails outright if a transaction is open on the connection, so inside a migration runner's transaction it is an error, and outside one it is an outage-sized pause.

VACUUM;

REINDEX with no argument rebuilds every index in every attached database in one write transaction, the write lock is held for the total, not per table. In a migration it is almost always broader than intended.

REINDEX;

With PRAGMA legacy_alter_table=ON, RENAME TABLE and RENAME COLUMN go back to the pre-3.26 behaviour: references inside views and triggers are not rewritten. The rename succeeds, and the first query through a view that named the old table fails with "no such table".

PRAGMA legacy_alter_table = ON;
ALTER TABLE orders RENAME TO purchases;

A DELETE with no WHERE on a table that other tables reference with ON DELETE CASCADE empties those tables too, in the same statement, with foreign keys on, and does nothing to them with foreign keys off. The blast radius depends on a per-connection pragma, and the statement itself names only one table.

CREATE TABLE lines (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE);
DELETE FROM orders;

ATTACH opens a second database file on the runner's connection, by a path that only makes sense on one machine, for the life of that connection. In a migration it is almost always an import script that got committed: it fails on any other host, and while it works it can read from or write to a file the migration never declared.

ATTACH DATABASE '/var/backups/legacy.db' AS legacy;
INSERT INTO orders SELECT * FROM legacy.orders;

DEFINE FIELD OVERWRITE and ALTER FIELD with a new TYPE do not convert the values already stored. A record written under the old type keeps its old value, and its next write fails with "Couldn't coerce value for field" unless that value happens to coerce. OVERWRITE also drops every clause the new statement does not repeat: a DEFAULT, ASSERT or PERMISSIONS clause that is not restated is gone.

DEFINE FIELD OVERWRITE age ON TABLE person TYPE int;

An ASSERT added with DEFINE FIELD or ALTER FIELD is not checked against the records already stored. A record whose value breaks it is found on its next write, which fails with "field must conform to" even when the write does not touch that field.

ALTER FIELD email ON TABLE user ASSERT string::is_email($value);

DEFINE INDEX ... UNIQUE builds the index over every record already in the table and fails the statement at the first duplicate, "Database index already contains ...". Production data is where the duplicate that test data never had is most likely to be.

DEFINE INDEX user_email ON TABLE user FIELDS email UNIQUE;

A SCHEMAFULL table rejects fields it has no DEFINE FIELD for. Switching an existing table with DEFINE TABLE OVERWRITE ... SCHEMAFULL or ALTER TABLE ... SCHEMAFULL does not clean the records already stored: a record that carries an undeclared field fails its next write with "Found field 'x', but no such field exists", and so does every write from code still sending that field.

ALTER TABLE user SCHEMAFULL;

REMOVE FIELD deletes the definition, not the data: every record keeps its stored value. On a SCHEMAFULL table those records then carry a field the schema no longer knows, and each one fails its next write with "Found field 'x', but no such field exists", as does any code still writing the field. On a SCHEMALESS table the old values stay, unvalidated.

REMOVE FIELD legacy_flag ON TABLE user;

READONLY rejects any change to the field once the record exists. Added to a field the application already updates, every such write starts failing with "Found changed value for field ... but field is readonly".

ALTER FIELD status ON TABLE invoice READONLY;

A RELATION table requires every record to carry in and out record links. Redefining a table that already holds normal records does not convert them: each fails its next write with "Couldn't coerce value for field `in`".

ALTER TABLE follows TYPE RELATION IN user OUT user;

OVERWRITE replaces the whole table definition, and a clause it does not repeat falls back to its default. For PERMISSIONS the default is NONE: a table that granted record users access with a WHERE clause stops returning or accepting anything for them the moment the statement runs. System users are unaffected, so tests run as root do not notice.

DEFINE TABLE OVERWRITE post SCHEMALESS;

DELETE with a table name and no WHERE removes every record of the table in one statement: the data is gone, the table's events run once per record, and on a large table the whole delete is one transaction that holds every change until it commits.

DELETE session;

UPDATE with a table name and no WHERE rewrites every record of the table in one statement and one transaction. Each record re-runs the table's field clauses, index updates and events, and a single record that fails a TYPE or ASSERT rolls the whole statement back, after it has run for the length of the table.

UPDATE user SET plan = 'free';

FOR $row IN (SELECT ... FROM table) with no WHERE or LIMIT loads the whole table and runs the loop body once per record, all inside one statement's transaction. The migration holds every change until the loop ends, and one failing iteration rolls back all of them.

FOR $u IN (SELECT VALUE id FROM user) { UPDATE $u SET plan = 'free'; };

DEFINE INDEX without CONCURRENTLY builds the index over every record already in the table inside the defining statement: the migration waits for the whole build, and the table's writes compete with it until it finishes. CONCURRENTLY builds it in the background, and the index serves queries once it has caught up.

DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer;

REBUILD INDEX drops the index's entries and builds them again from every record in the table. Without CONCURRENTLY the migration waits for the whole rebuild, and queries that relied on the index work without it until the rebuild ends.

REBUILD INDEX purchase_customer ON TABLE purchase;

SurrealDB 3 renamed SEARCH ANALYZER indexes to FULLTEXT ANALYZER and removed MTREE vector indexes in favour of HNSW. The old syntax is a parse error on 3.x, so a migration written for 2.x fails the whole file after an upgrade.

DEFINE INDEX post_body ON TABLE post FIELDS body SEARCH ANALYZER simple BM25 CONCURRENTLY;

Two indexes over the same fields, in the same order, on one table hold the same entries twice. Queries use one of them, and every write to the table pays to maintain both.

DEFINE INDEX purchase_customer ON TABLE purchase FIELDS customer CONCURRENTLY;
DEFINE INDEX purchase_by_customer ON TABLE purchase FIELDS customer CONCURRENTLY;

A root user with the OWNER role controls every namespace and database on the server, including removing them and defining other users. Created from an application migration, it exists wherever that migration runs, with credentials that live wherever the migration does.

DEFINE USER ops ON ROOT PASSHASH '$argon2id$v=19$m=19456,t=2,p=1$c2FsdA$aGFzaA' ROLES OWNER;

PERMISSIONS FULL on a table, or FOR create, update or delete FULL, lets every record user signed in through an access method write every record in the table, not only their own. Root, namespace and database users bypass table permissions, which is why tests run as root do not notice.

DEFINE TABLE note SCHEMAFULL PERMISSIONS FULL;

http::get, http::post and the other http functions make a network request from the database server when the statement runs. In a migration that request fires in every environment the migration reaches, and again on every re-run, and the migration now fails when the remote service is down.

http::post('https://hooks.example.com/deployed', { version: '42' });