How to Safely Add a Column with a Default Value in PostgreSQL

Adding a column with a DEFAULT value is one of the most common migration operations. It is also one of the most misunderstood. Whether it takes milliseconds or causes a full table rewrite depends on your PostgreSQL version and what your DEFAULT expression looks like. The Problem: Table Rewrites Before PostgreSQL 11, adding a column with a DEFAULT value always rewrote the entire table. PostgreSQL had to physically write the default value into every existing row. During this rewrite, the table was locked with an ACCESS EXCLUSIVE lock, blocking all reads and writes. -- PostgreSQL 10 and earlier: FULL TABLE REWRITE -- On a 100M row table, this could take 10+ minutes -- The table is completely locked during the entire operation ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'; On a table with 100 million rows, this could take 10 minutes or more. During that time, every query against the table — reads included — queues up. Connection pools exhaust. Application timeouts cascade. User