Adding, renaming, and dropping PostgreSQL columns.

How to change PostgreSQL table columns safely

1. 1

   Add a column

   Since PostgreSQL 11, adding a column with a constant default no longer rewrites the table — the default is stored as metadata and applied on read. This is instant regardless of table size. A volatile default such as now() or random() still forces a full rewrite, because each row needs its own value.

   -- Instant on any table size
   ALTER TABLE users ADD COLUMN country text;
   ALTER TABLE users ADD COLUMN plan text NOT NULL DEFAULT 'free';
   
   -- Rewrites every row: volatile default
   ALTER TABLE users ADD COLUMN token uuid NOT NULL DEFAULT gen_random_uuid();

2. 2

   Set a lock timeout first on anything busy

   ALTER TABLE needs an ACCESS EXCLUSIVE lock. If a long-running query holds the table, your ALTER waits — and every query arriving after it queues behind your pending lock, including reads. That is how a one-second migration takes a site down. A short lock_timeout makes the ALTER fail fast instead.

   SET lock_timeout = '3s';
   ALTER TABLE orders ADD COLUMN note text;
   -- If it cannot get the lock in 3s it errors out, and you retry later

3. 3

   Rename a column

   RENAME is a catalog change only, so it is instant on any table size. The risk is not the database, it is the application: every query referencing the old name breaks the moment it commits. For zero-downtime, add the new column, write to both, migrate reads, then drop the old one.

   ALTER TABLE users RENAME COLUMN signup_date TO created_at;

4. 4

   Change a column type carefully

   Most type changes rewrite the entire table and hold an exclusive lock for the duration — minutes or hours on a large table. Some widening conversions are exempt, such as varchar(50) to varchar(100) or to text. When a cast is not implicit, USING tells PostgreSQL how to convert.

   -- No rewrite: widening within the same type
   ALTER TABLE users ALTER COLUMN name TYPE text;
   
   -- Full rewrite under ACCESS EXCLUSIVE: test the duration first
   ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;

5. 5

   Add NOT NULL without a full scan

   SET NOT NULL normally scans the whole table to verify no nulls exist. On PostgreSQL 12 and later you can avoid that: add a validated CHECK constraint first — NOT VALID then VALIDATE, which takes only a share lock — and SET NOT NULL will then use it as proof and skip the scan.

   ALTER TABLE users ADD CONSTRAINT users_country_nn
     CHECK (country IS NOT NULL) NOT VALID;
   ALTER TABLE users VALIDATE CONSTRAINT users_country_nn;  -- no exclusive lock
   ALTER TABLE users ALTER COLUMN country SET NOT NULL;     -- now instant
   ALTER TABLE users DROP CONSTRAINT users_country_nn;

6. 6

   Drop a column

   DROP COLUMN is instant because PostgreSQL only marks the column dropped in the catalog — the data stays in each row until that row is rewritten. Disk space is reclaimed gradually by normal updates, or immediately by VACUUM FULL, which takes an exclusive lock and needs free space equal to the table.

   ALTER TABLE users DROP COLUMN legacy_flag;
   
   -- Space is only reclaimed on rewrite:
   VACUUM FULL users;   -- ACCESS EXCLUSIVE, needs room for a full copy