# PostgreSQL INT to BIGINT Primary Key: In-Place ALTER vs Column Swap, and What Each Locks

> Widening a referenced `integer` key with `ALTER COLUMN ... TYPE bigint` rewrites the table and also takes an `ACCESS EXCLUSIVE` lock on every table whose foreign key points at it, because PostgreSQL checks the key again. On 1,000,000 rows per table that was about 2 seconds per column with both tables unreadable. Adding a `bigint` column, backfilling it and swapping it in with `ADD PRIMARY KEY USING INDEX` held its locks for about 45 ms.

Source: https://schemity.com/blog/postgres-change-int-primary-key-to-bigint/

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](https://guides.rubyonrails.org/5_1_release_notes.html) say "Change Default Primary Keys to BIGINT", and Django's [3.2 release notes](https://docs.djangoproject.com/en/5.2/releases/3.2/) 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.

```sql
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:

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/explicit-locking.html) 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.

```sql
SET lock_timeout = '2s';
```

```sql
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:

```sql
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`:

```sql
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](https://www.postgresql.org/docs/current/sql-createindex.html) 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](https://www.postgresql.org/docs/current/sql-altertable.html) 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

```sql
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?

| Method | Locks held | Measured here (2 x 1,000,000 rows) | Use it when |
| --- | --- | --- | --- |
| `ALTER COLUMN ... TYPE bigint` on the key and each referencing column | `ACCESS EXCLUSIVE` on both ends of every key, one column at a time | 2.2 s for `users.id`, 2.1 s for `orders.user_id` | The tables are small enough, or a maintenance window is long enough |
| Add a `bigint` column, backfill, swap | `ACCESS EXCLUSIVE` for milliseconds per `ALTER TABLE`, and for the swap transaction | About 45 ms for the swap; the backfill, index builds and validation ran with both tables open | The table must stay readable and writable |
| Restart the sequence at -2,147,483,648 | None | Instant | You need time now, and every consumer accepts negative ids |

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](https://schemity.com/doc/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.

![Schemity's canvas reading the users and orders tables from PostgreSQL 18.3: users.id is an INTEGER primary key marked auto for its serial default, beside email TEXT; orders.id is an INTEGER serial key, orders.user_id is an INTEGER foreign key whose relation line runs to users.id, and orders.total is NUMERIC(12,2), with idx: 1 for the index on user_id](https://schemity.com/images/blog/postgres-change-int-primary-key-to-bigint/canvas-int-keys.webp)

Change `users.id` to `BIGINT` alone and [lint](https://schemity.com/doc/schema-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](https://schemity.com/doc/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`.

![Schemity's Findings drawer after users.id and orders.user_id were changed to BIGINT on PostgreSQL 18.3: the planned migration alters orders.user_id and users.id to BIGINT, then runs ALTER SEQUENCE users_id_seq AS BIGINT; lint has no findings; the seven impact findings rewrite orders (~1M rows, 85 MB) and users (~1M rows, 79 MB), hold back reads and writes to each table while its type changes, report that checking orders_user_id_fkey on orders holds back reads and writes to users and that checking orders_user_id_fkey holds back reads and writes to orders, and show that changing users.id reaches orders through orders_user_id_fkey](https://schemity.com/images/blog/postgres-change-int-primary-key-to-bigint/impact-widen-key.webp)

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.

![Schemity's SQL migration drawer after the users.id swap file was pasted on PostgreSQL 18.3: 19 statements, 15 analysed and 4 not analysed; under Loses data, Drops users.id: ~1M rows; under Can fail on existing data, Adds a UNIQUE on users, fails if duplicates exist among ~1M rows, and Validates users.id_new NOT NULL, fails if any of ~1M rows is NULL, each with Count exactly; under Spreads through the diagram, changing users.id reaches orders via orders_user_id_fkey; under Changes data, the batched UPDATE of users; and Not analysed (4) collapsed at the bottom](https://schemity.com/images/blog/postgres-change-int-primary-key-to-bigint/migration-file-swap.webp)

## 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](https://schemity.com/blog/postgres-serial-vs-identity/), and the `CHECK ... NOT VALID` proof comes from [adding a NOT NULL column to a large Postgres table](https://schemity.com/blog/postgres-add-not-null-column-large-table/). Why `USING INDEX` turns an index into a constraint without reading a row is in [unique constraint vs unique index](https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/), what to do when a view blocks the type change is in [altering a column a view uses](https://schemity.com/blog/postgres-alter-column-type-used-by-view/), and the same migration on SQL Server, where `ALTER COLUMN` is refused until every key is dropped, is in [SQL Server INT to BIGINT](https://schemity.com/blog/sqlserver-change-int-to-bigint-primary-key/).

## Frequently asked questions

### Does ALTER COLUMN TYPE bigint lock the table in Postgres?

Yes. Changing `integer` to `bigint` rewrites every row and its indexes under an `ACCESS EXCLUSIVE` lock, so reads and writes wait until it commits. When the column is either end of a foreign key, the table at the other end gets the same lock while the key is checked again, which took about 2 seconds per 1,000,000-row table on PostgreSQL 18.3.

### Can a foreign key column stay integer when the primary key is bigint?

PostgreSQL accepts the key, since `integer` and `bigint` compare directly, so nothing fails at first. The first parent id above 2,147,483,647 can be inserted, but a child row pointing at it fails with `integer out of range`. Widen every referencing column before the parent's ids reach the limit.

### How do I change an integer primary key to bigint in Postgres?

On a table you can lock for a few seconds, about 2 seconds per 1,000,000 rows in the test here, run `ALTER TABLE ... ALTER COLUMN id TYPE bigint` on the key and on every foreign key column, and widen a `serial` key's sequence with `ALTER SEQUENCE ... AS bigint`. On a table you cannot lock, add a `bigint` column, keep it filled with a trigger, backfill it in batches, build a unique index `CONCURRENTLY`, and swap it in as the primary key in one short transaction.