Changing an integer primary key to bigint in place locks more than the table you name. PostgreSQL rewrites the key’s table, then checks every foreign key that points at it again, and holds an ACCESS EXCLUSIVE lock on the referencing tables while it does, so nothing can read them either. The alternative is a new bigint column filled in the background and swapped in as the key, which in the test below held its locks for about 45 ms instead of about 4 seconds.

The question usually comes from a counter. A serial or integer identity key stops at 2,147,483,647, and the next insert fails. These keys are still everywhere: Rails defaulted primary keys to bigint only from 5.1, whose release notes say “Change Default Primary Keys to BIGINT”, and Django’s 3.2 release notes say the default for DEFAULT_AUTO_FIELD is still AutoField, an integer, with only new projects generated as BigAutoField. Every statement and timing below was run on PostgreSQL 18.3 in a throwaway container on a laptop, with 1,000,000 rows in users (57 MB) and 1,000,000 in orders (42 MB), so read the timings as ratios between the two methods.

CREATE TABLE users (
    id serial PRIMARY KEY,
    email text NOT NULL
);
CREATE TABLE orders (
    id serial PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (id),
    total numeric(12,2) NOT NULL
);
CREATE INDEX orders_user_id_idx ON orders (user_id);

How close are your integer keys to 2,147,483,647?

Every serial and identity column reads from a sequence, and pg_sequences keeps the last value each one handed out. This lists the integer columns that draw from one, fullest first:

SELECT x.table_name, x.column_name, s.last_value,
       round(100.0 * s.last_value / 2147483647, 2) AS pct_used
FROM (
    SELECT c.table_name, c.column_name,
           pg_get_serial_sequence(format('%I.%I', c.table_schema, c.table_name), c.column_name) AS seq
    FROM information_schema.columns c
    WHERE c.data_type = 'integer'
      AND c.table_schema NOT IN ('pg_catalog', 'information_schema')
) x
JOIN pg_sequences s ON format('%I.%I', s.schemaname, s.sequencename) = x.seq
ORDER BY pct_used DESC NULLS LAST;

An empty last_value means the sequence has never been used or your role cannot read it, so run the query as the owner, and a negative one means someone already restarted it below zero, which is the stopgap at the end of this post. The query finds the keys. The foreign key columns that point at them are integer too and have no sequence, and they matter just as much: with users.id widened and orders.user_id left alone, user 2,147,483,648 was inserted without complaint, and the first order for that user failed with ERROR: integer out of range.

What does ALTER COLUMN TYPE bigint lock?

More than the table in the statement. With client_min_messages set to debug1, PostgreSQL says what the change does:

ALTER TABLE users ALTER COLUMN id TYPE bigint;

DEBUG:  rewriting table "users"
DEBUG:  building index "users_pkey" on table "users" with request for 1 parallel workers
DEBUG:  validating foreign key constraint "orders_user_id_fkey"

It took 2.2 seconds, and users got a new data file. The third line is the part people miss. Changing the type of a referenced key makes PostgreSQL check the foreign key again by reading orders, and pg_locks for the session showed AccessExclusiveLock on orders as well as on users. The explicit locking documentation says this mode “guarantees that the holder is the only transaction accessing the table in any way”, so a plain SELECT on orders waits for the whole statement.

The referencing column is the same in reverse. ALTER TABLE orders ALTER COLUMN user_id TYPE bigint rewrote orders, rebuilt both of its indexes and checked the key in 2.1 seconds, and it held AccessExclusiveLock on users as well. Every referencing column is its own statement with its own lock window, and during each one both ends of its key are closed to reads and writes.

Two more things to plan for. A serial key’s sequence stays integer after the column is widened, so the next insert after 2,147,483,647 still fails until you run ALTER SEQUENCE users_id_seq AS bigint. And a view that reads the column makes PostgreSQL refuse the type change outright, even before any of this.

How do you swap in a bigint column without locking the table?

You do not change the column. You add a second one, fill it while the application keeps writing, and swap it in at the end. The ALTER TABLE and CREATE TRIGGER steps still take a strong lock, ACCESS EXCLUSIVE for the column and SHARE ROW EXCLUSIVE for the trigger, but only for milliseconds. The danger is waiting for it: a statement queued behind a long-running query blocks every session that arrives after it, so set lock_timeout first and retry if it gives up.

SET lock_timeout = '2s';

ALTER TABLE users ADD COLUMN id_new bigint;
ALTER TABLE orders ADD COLUMN user_id_new bigint;

CREATE FUNCTION users_id_new_sync() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.id_new := NEW.id; RETURN NEW; END $$;
CREATE TRIGGER users_id_new_sync BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION users_id_new_sync();

CREATE FUNCTION orders_user_id_new_sync() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.user_id_new := NEW.user_id; RETURN NEW; END $$;
CREATE TRIGGER orders_user_id_new_sync BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION orders_user_id_new_sync();

Adding a nullable column with no default is a catalog change: 3.5 ms. The triggers copy the old value into the new column on every insert and update from now on. Then the backfill, in batches small enough that each one commits quickly and locks only its own rows:

UPDATE users SET id_new = id
WHERE id > 0 AND id <= 50000 AND id_new IS NULL;
-- repeat with the next range until the end of the table, then the same for orders

Twenty batches of 50,000 took 5 to 6 seconds for users and 7 to 8 for orders across two runs, spread over time however you like. Each UPDATE writes a new version of every row it touches, and users grew from 57 MB to 105 MB. A VACUUM afterwards makes that space reusable but did not shrink the file, so leave room on disk for it. Next, the index the new primary key will use, and proof that the new columns hold no NULL:

CREATE UNIQUE INDEX CONCURRENTLY users_id_new_key ON users (id_new);
CREATE INDEX CONCURRENTLY orders_user_id_new_idx ON orders (user_id_new);

ALTER TABLE users ADD CONSTRAINT users_id_new_nn_check CHECK (id_new IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_id_new_nn_check;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_new_nn_check CHECK (user_id_new IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_new_nn_check;

The CREATE INDEX documentation says a CONCURRENTLY build runs “without taking any locks that prevent concurrent inserts, updates, or deletes”, and it cannot run inside a transaction block. It also waits for transactions already running against the table to finish, so a long one delays it. These took between 0.5 and 2.3 seconds across two runs. If one fails, on a duplicate or a deadlock, it leaves an INVALID index behind that you drop and build again. Each VALIDATE CONSTRAINT read its table in 45 to 65 ms under SHARE UPDATE EXCLUSIVE, which lets reads and writes continue.

The CHECK constraint is there for the swap. ALTER TABLE says ADD PRIMARY KEY USING INDEX runs SET NOT NULL on any column not already marked NOT NULL, which “requires a full table scan”, and that the scan is skipped “if a valid CHECK constraint exists … which proves no NULL can exist”. So the slow part happens here, without the strong lock, instead of inside the swap.

The swap transaction

SET lock_timeout = '2s';
BEGIN;
LOCK TABLE users, orders IN ACCESS EXCLUSIVE MODE;
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ALTER COLUMN id_new SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_new_key;
ALTER TABLE users ALTER COLUMN id DROP DEFAULT;
ALTER SEQUENCE users_id_seq AS bigint OWNED BY users.id_new;
ALTER TABLE users ALTER COLUMN id_new SET DEFAULT nextval('users_id_seq');
DROP TRIGGER users_id_new_sync ON users;
DROP TRIGGER orders_user_id_new_sync ON orders;
ALTER TABLE users DROP COLUMN id;
ALTER TABLE users RENAME COLUMN id_new TO id;
ALTER TABLE users DROP CONSTRAINT users_id_new_nn_check;
ALTER TABLE users RENAME CONSTRAINT users_id_new_not_null TO users_id_not_null;
ALTER TABLE orders ALTER COLUMN user_id_new SET NOT NULL;
ALTER TABLE orders DROP COLUMN user_id;
ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;
ALTER TABLE orders DROP CONSTRAINT orders_user_id_new_nn_check;
ALTER TABLE orders RENAME CONSTRAINT orders_user_id_new_not_null TO orders_user_id_not_null;
ALTER INDEX orders_user_id_new_idx RENAME TO orders_user_id_idx;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
COMMIT;

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

The whole transaction, COMMIT included, took about 45 ms. The two RENAME CONSTRAINT lines are for PostgreSQL 18, which stores each NOT NULL as a named constraint and names it after the column it was created on, so without them the catalog keeps users_id_new_not_null on a column called id. They are also why the CHECK constraints have their own names: called users_id_new_not_null, the CHECK takes the name PostgreSQL 18 wants for the NOT NULL, which then becomes users_id_new_not_null1. Both SET NOT NULL statements printed existing constraints on column "users.id_new" are sufficient to prove that it does not contain nulls (and the same for orders.user_id_new) instead of scanning, and ADD PRIMARY KEY USING INDEX renamed users_id_new_key to users_pkey with a notice. The lock_timeout makes the LOCK TABLE give up after 2 seconds instead of queueing behind a long query while every other session queues behind it.

The foreign key comes back NOT VALID, which checks no existing row, and the VALIDATE CONSTRAINT after the commit read orders in 0.5 to 1.0 seconds under SHARE UPDATE EXCLUSIVE on orders and ROW SHARE on users, so both tables stayed open. Afterwards users.id was bigint with its nextval default, the sequence was bigint, and user 2,147,483,648 and an order pointing at it were both inserted.

Four details that went wrong or nearly did while testing:

• Move the sequence before dropping the old column. A serial sequence is OWNED BY its column, and DROP COLUMN takes it along. In a separate test, dropping the old column first left no sequence at all.
• The column moves to the end. \d users lists email before id afterwards. Nothing that names its columns cares, but SELECT * and INSERT without a column list do.
• An identity key swaps differently. Instead of the sequence lines, run ALTER COLUMN id DROP IDENTITY on the old column and, after the rename, ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH ...), keeping whichever of BY DEFAULT or ALWAYS the old column had. Django creates BY DEFAULT, and ALWAYS would refuse its fixtures, which supply ids. START WITH takes a number, not an expression, so read the next value from the old sequence inside the locked transaction and write it into the statement.
• The example’s own orders.id is still integer. It is a key too, with no table referencing it, so the same steps apply without the foreign key lines.

Which way should you change it?

The swap is more statements, two triggers to remove, and a window in which the application writes both columns. The in-place change is one statement per column, but each one rewrites a whole table, and the lock it holds covers every table at both ends of the key.

The stopgap is one statement once the sequence has run out: ALTER SEQUENCE users_id_seq MINVALUE -2147483648 RESTART WITH -2147483648. The next inserts got -2,147,483,648 and then -2,147,483,647, which buys another 2.1 billion rows in the negative half. Every client that assumed positive ids now sees negative ones, and the ids no longer sort by age.

How Schemity plans the change and shows what it locks

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. After you connect PostgreSQL, each key is drawn with its real type, so users.id and every integer column that references it sit on the canvas together.

Change users.id to BIGINT alone and lint reports each referencing column still on INTEGER as “Foreign key type does not match the column it references”, which is the integer out of range error found before the 2,147,483,648th user exists.

Change those columns too, and the planned migration is the in-place method: an ALTER COLUMN ... TYPE BIGINT per column, and right after the key’s, ALTER SEQUENCE users_id_seq AS BIGINT, so the sequence is not left at 2,147,483,647. Impact analysis then lets you see the impact of every change before anything runs: the rewrite of each table with its row estimate and size on disk, the reads and writes each type change holds back, and the tables on the other end of each key, such as “Checking orders_user_id_fkey on orders holds back reads and writes to users while it reads ~1M rows”. That last line is the lock this post found in pg_locks.

For the swap, write the steps above as a migration file and open it with Analyse a migration file (Shift+F7). Nothing in it runs. The concurrent unique build is reported as one that can fail on duplicate values, with a count you can ask for, and with no lock finding, because it takes none that blocks. The VALIDATE CONSTRAINT on the CHECK is reported as able to fail on NULL rows, and the SET NOT NULL and ADD PRIMARY KEY USING INDEX after it produce no finding, because the validated constraint already proves the column. The VALIDATE CONSTRAINT on the foreign key added back NOT VALID is reported the same way, able to fail on orders whose user_id has no user, with a count of those rows you can ask for. The DROP COLUMN id is reported as data loss, which is the moment to confirm the backfill reached every row. The trigger, function and sequence statements are listed as not analysed rather than skipped.

INT to BIGINT on PostgreSQL, in one list

• Find the integer keys near 2,147,483,647 with pg_sequences, and widen the foreign key columns that point at them in the same plan.
• ALTER COLUMN ... TYPE bigint rewrites the table and locks both ends of every foreign key on the column with ACCESS EXCLUSIVE, so reads wait too.
• Widen a serial key’s sequence with ALTER SEQUENCE ... AS bigint.
• To keep the tables open, add a bigint column, backfill it, build its unique index CONCURRENTLY, prove it NOT NULL with a validated CHECK, and swap it in with ADD PRIMARY KEY USING INDEX inside one short transaction.
• Bring the foreign key back NOT VALID and validate it after the commit.

The sequence that stays integer after the column is widened is covered in detail in serial vs identity in Postgres, and the CHECK ... NOT VALID proof comes from adding a NOT NULL column to a large Postgres table. Why USING INDEX turns an index into a constraint without reading a row is in unique constraint vs unique index, what to do when a view blocks the type change is in altering a column a view uses, and the same migration on SQL Server, where ALTER COLUMN is refused until every key is dropped, is in SQL Server INT to BIGINT.