Add a column without locking the table

Sql

On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care.

ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';

-- Backfill in batches, then:
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

Download

Databases postgres migrations locking chris

More in Databases

Group rows into a JSON array Databases Returns one row per author with their posts nested, instead of a join that repeats the author on every row. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value. Which indexes is nobody using? Databases Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any. Find slow queries in Postgres Databases Ranks statements by total time spent. Needs the pg_stat_statements extension enabled. Delete duplicate rows, keeping the oldest Databases Uses a window function to number the duplicates and removes everything but the first of each group. Reset a Postgres sequence after a bulk import Databases Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row. Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST.

Random picks

Type a fetch response safely TypeScript unknown forces a parse step rather than letting an any spread silently through the codebase. Result and the question mark operator in Rust Systems ? propagates the error to the caller, which is why Rust functions rarely need explicit error branches. Cache busting with a content hash Build & CI Names the built file after its contents so a deploy invalidates the cache without a version bump. Stop tracking a file without deleting it Git For the config file that should never have been committed. Add it to .gitignore afterwards. Responsive images that do not blow the bandwidth budget Web & CSS srcset lets the browser pick; sizes tells it how wide the image will actually be rendered. Narrow a union with a type guard TypeScript A predicate returning `x is T` teaches the compiler what a runtime check proved. Undo the last commit but keep the changes Git Moves HEAD back one commit and leaves everything staged, which is what you usually want after committing too early. Pretty-print and query JSON Text Processing jq for the common cases: reformat, pick a path, filter an array, and reshape.