Does ALTER COLUMN TYPE rewrite the table in PostgreSQL?

Short answer

Every ALTER TABLE … ALTER COLUMN … TYPE takes an ACCESS EXCLUSIVE lock, so reads and writes wait while it runs. How long it runs depends on whether PostgreSQL can reuse the stored values as they are:

• Instant when the old values are already valid in the new type: varchar(50) → varchar(100), varchar(n) → text, text → varchar (no length), numeric(10,2) → numeric(12,2).
• Full rewrite of the table and every index on it when each value has to be converted or checked: int → bigint, int → text, varchar(50) → varchar(20), text → varchar(100), numeric(10,2) → numeric(10,4), and timestamp → timestamptz unless the session time zone is UTC.

A rewrite holds the lock for as long as it takes to copy the table and rebuild its indexes. For those changes, use one of the patterns below instead.

What it locks

• ACCESS EXCLUSIVE on the table, for every type change we tried, held until the transaction ends. SELECT and INSERT from other sessions waited.
• When the table is rewritten, an ACCESS EXCLUSIVE lock on every index of the table too, because each one is rebuilt.
• When it isn’t rewritten, only the indexes that include the column are locked, and they’re usually kept as they are.

Does it rewrite the table?

On a 200,000-row table with six indexes, identical on PostgreSQL 14, 15, 16, 17 and 18:

On a 3-million-row table with no indexes, int → bigint took 1.5 to 2.1 seconds (median of three). The time grows with the size of the table and the number of indexes, and reads and writes are blocked for all of it.

Why text → varchar(100) rewrites: every value has to be checked against the new limit, and PostgreSQL does that check by rewriting. Widening a limit needs no check, so it’s instant.

Run it safely

For an instant change: lock_timeout and retry.

SET lock_timeout = '2s';
ALTER TABLE customers ALTER COLUMN email TYPE varchar(320);

If it fails with ERROR: canceling statement due to lock timeout, nothing changed; retry after a pause. See lock timeout.

Adding a length limit: use a CHECK constraint instead of a type. It enforces the same rule with no rewrite, and can be validated without blocking writes:

SET lock_timeout = '2s';
ALTER TABLE customers ADD CONSTRAINT customers_email_length
  CHECK (char_length(email) <= 320) NOT VALID;          -- instant
ALTER TABLE customers VALIDATE CONSTRAINT customers_email_length;  -- scans, but reads and writes continue

See adding a CHECK constraint.

timestamp → timestamptz: run it with the session in UTC, if the stored values are UTC times. PostgreSQL then reads them as UTC and doesn’t rewrite the table, though it still rebuilds indexes on that column while holding the lock. If the values were stored in some other local time, this would shift them; in that case convert with a rewrite (USING col AT TIME ZONE '…') or the swap pattern below.

SET TimeZone = 'UTC';
SET lock_timeout = '2s';
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;

int → bigint on a big table: add a new column and swap. Each step either is instant or lets reads and writes continue. For an orders table whose id serial PRIMARY KEY is running out:

-- 1. New column, kept in step for new and changed rows
ALTER TABLE orders ADD COLUMN id_new bigint;
CREATE FUNCTION orders_sync_id() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$;
CREATE TRIGGER orders_sync_id BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_sync_id();

-- 2. Backfill in batches, one transaction each
UPDATE orders SET id_new = id WHERE id BETWEEN 1 AND 10000 AND id_new IS NULL;

-- 3. Prove NOT NULL and build the unique index without long locks
ALTER TABLE orders ADD CONSTRAINT orders_id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_new_not_null;
CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_key ON orders (id_new);

-- 4. Swap in one short transaction
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ALTER COLUMN id_new SET NOT NULL;   -- no scan: the CHECK proves it
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_key;
ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN id DROP NOT NULL;
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('orders_id_seq');
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
ALTER SEQUENCE orders_id_seq AS bigint;
DROP TRIGGER orders_sync_id ON orders;
ALTER TABLE orders DROP CONSTRAINT orders_id_new_not_null;
COMMIT;

-- 5. Later
ALTER TABLE orders DROP COLUMN id_old;

The swap transaction took 4 to 7 ms with no table scan on every version we tested. If other tables have foreign keys to orders.id, they need the same treatment first, and dropping orders_pkey needs those foreign keys dropped and re-added (adding a foreign key).

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), each change inside a transaction that was rolled back:

CREATE TABLE seo_mig.ty (id int PRIMARY KEY, i int, v varchar(50), tx text,
                         nm numeric(10,2), ts timestamp, vu varchar(50));
INSERT INTO seo_mig.ty
SELECT g, g, 'v' || g, 't' || g, g / 100.0, now(), 'u' || g FROM generate_series(1, 200000) g;
CREATE INDEX ty_i_idx ON seo_mig.ty (i);
CREATE INDEX ty_v_idx ON seo_mig.ty (v);
-- … and on tx, nm and ts

BEGIN;
SELECT pg_relation_filenode('seo_mig.ty'), pg_relation_filenode('seo_mig.ty_i_idx');
ALTER TABLE seo_mig.ty ALTER COLUMN i TYPE bigint;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT pg_relation_filenode('seo_mig.ty'), pg_relation_filenode('seo_mig.ty_i_idx');
ROLLBACK;

A new data file number for the table means it was rewritten; a new number for an index means it was rebuilt. Locks we saw:

pg_locks also listed a SHARE lock on the table; ACCESS EXCLUSIVE already blocks everything, so it changes nothing. The blocking test ran ALTER COLUMN a TYPE bigint in one session while another ran a SELECT and an INSERT with lock_timeout = '1s'. The swap recipe ran end to end on each version against a 200,000-row table with a serial key; afterwards id was bigint NOT NULL, the primary key was on it, and a new insert got the next sequence value.

In Inlet

The structure editor shows the ALTER TABLE … TYPE statement before it runs and warns when a change rewrites or scans the table. The Activity monitor shows what a long-running change is blocking, and lets you cancel it.