Bigint in PostgreSQL: Range, Storage and int Migration

TL;DR: bigint (alias int8) is PostgreSQL's 8-byte signed integer, with a range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Use it for primary keys and counters that could ever pass 2,147,483,647, the ceiling of integer. Moving a live int key to bigint with a plain ALTER COLUMN TYPE rewrites the whole table under an exclusive lock, so large tables need a shadow-column migration.

Bigint in PostgreSQL is a whole-number type that stores values in 8 bytes, twice the 4 bytes of integer, and raises about 4.3 billion times the ceiling: integer stops at 2,147,483,647 (about 2.1 billion) while bigint stops at 9,223,372,036,854,775,807 (about 9.2 quintillion). The PostgreSQL 18 manual calls integer "the common choice" and says bigint "is designed to be used when the range of the integer type is insufficient". In practice that means identity columns on any table that grows without bound (events, logs, orders, messages), counters of bytes or milliseconds, and foreign keys that point at a bigint key. Inserting a value outside a type's range does not wrap or truncate; it fails with ERROR: integer out of range (SQLSTATE 22003).

What is the max value of bigint in PostgreSQL?

The maximum value of bigint is 9,223,372,036,854,775,807 (2^63 - 1) and the minimum is -9,223,372,036,854,775,808 (-2^63). PostgreSQL has no unsigned integer types, so you cannot trade the negative half for a larger positive range. The full family, from the PostgreSQL numeric types documentation:

int2, int4 and int8 are PostgreSQL extensions; the SQL standard only names smallint, integer and bigint. The serial "types" are not real types: bigserial creates a bigint column with a sequence default, which is why it shows as bigint in \d.

You can check the limits yourself:

SELECT 9223372036854775807::bigint;        -- ok
SELECT 9223372036854775807::bigint + 1;    -- ERROR: bigint out of range
SELECT 2147483647::int + 1;                -- ERROR: integer out of range
SELECT 2147483647::bigint + 1;             -- 2147483648

Why does the integer limit matter?

The integer limit matters because auto-increment IDs only move up, and a sequence never reuses a value. The manual notes that serial columns can have gaps: a value taken by an insert that rolls back is gone for good. Bulk loads that fail, ON CONFLICT DO NOTHING upserts and retries all burn IDs without adding rows, so max(id) can be far ahead of count(*).

How long 2,147,483,647 IDs last depends only on the rate at which IDs are consumed:

Some published guides say 1,000 inserts per second lasts "around 68 days". The arithmetic is 2,147,483,647 / 1,000 / 86,400 = 24.9 days. At 1,000 per second, bigint lasts about 292 million years.

When the sequence behind an integer identity column hits its ceiling, inserts fail with nextval: reached maximum value of sequence "orders_id_seq" (2147483647). If the sequence is already bigint but the column is still integer, the error moves to the insert itself: integer out of range. Either way, writes to that table stop until the type changes.

Integer vs bigint: which should you use for IDs?

Use bigint for primary keys by default and integer only when the row count is bounded by something real (countries, plans, internal admin users). The extra 4 bytes per row is about 4 MB per million rows before alignment, which is small next to the cost of an emergency migration on a large table. Newer PostgreSQL releases point the same way: CREATE SEQUENCE defaults to bigint, and the SQL-standard identity syntax makes the choice explicit:

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts (id),
  kind       text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Foreign keys must match: if accounts.id is bigint, events.account_id should be bigint too, or you rebuild the same problem one table over. For a time column next to it, see our guide to timestamptz in PostgreSQL.

Does bigint hurt performance?

Bigint does not measurably slow down arithmetic or comparisons on 64-bit servers, where 8-byte integers are native word size. The real costs are storage and memory: wider rows, wider indexes, and more data per cache page. Two effects make the cost smaller than "4 bytes per row" suggests:

1. Alignment padding. PostgreSQL aligns 8-byte types such as bigint and timestamptz to 8-byte boundaries. A table of (id int, created_at timestamptz) pads 4 bytes after id anyway, so switching id to bigint costs nothing on the heap.
2. Index tuples. A B-tree entry carries an 8-byte header plus the key, padded to 8 bytes, so an int4 key and an int8 key usually take the same 16 bytes per entry.

Measure instead of guessing:

SELECT pg_column_size(row(1::int,    now())) AS int_row,
       pg_column_size(row(1::bigint, now())) AS bigint_row;
-- both return 40: the int row is padded to the timestamptz boundary

The one place bigint causes bugs is the application layer. JavaScript numbers are exact only up to 9,007,199,254,740,991 (Number.MAX_SAFE_INTEGER), so node-postgres returns int8 columns as strings by default. Parse them with BigInt() or keep them as strings; do not convert to Number once IDs can pass 2^53.

How do I change a column from int to bigint?

On a small table, one statement is enough:

ALTER TABLE orders ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE orders_id_seq AS bigint;          -- for serial/identity columns
ALTER TABLE order_items ALTER COLUMN order_id TYPE bigint;

According to the ALTER TABLE documentation, changing int4 to int8 changes the on-disk representation, so PostgreSQL rewrites the table and rebuilds every index while holding an ACCESS EXCLUSIVE lock. Nothing can read or write the table until it finishes. On a table of tens of millions of rows that is minutes to hours of downtime, so large tables use a shadow column instead:

-- 1. Add the new column (no rewrite: no default, nullable)
ALTER TABLE orders ADD COLUMN id_new bigint;

-- 2. Keep it in sync for new and updated rows
CREATE FUNCTION orders_sync_id() RETURNS trigger AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_id BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_id();

-- 3. Backfill existing rows in batches (repeat with the next range)
UPDATE orders SET id_new = id WHERE id BETWEEN 1 AND 100000 AND id_new IS NULL;

-- 4. Build the future primary key without blocking writes
CREATE UNIQUE INDEX CONCURRENTLY orders_id_new_idx ON orders (id_new);
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;

-- 5. Swap, briefly locked
BEGIN;
ALTER TABLE orders DROP CONSTRAINT orders_pkey;   -- drop referencing FKs first
ALTER TABLE orders RENAME COLUMN id TO id_old;
ALTER TABLE orders RENAME COLUMN id_new TO id;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_new_idx;
ALTER SEQUENCE orders_id_seq AS bigint OWNED BY orders.id;
ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('orders_id_seq');
DROP TRIGGER orders_sync_id ON orders;
COMMIT;

VALIDATE CONSTRAINT scans the table without blocking writes, and with a valid CHECK (id_new IS NOT NULL) in place, ADD PRIMARY KEY USING INDEX can set NOT NULL without a second full scan. Foreign keys on child tables need the same treatment, then re-adding with NOT VALID followed by VALIDATE CONSTRAINT. Drop id_old once nothing reads it. Tools such as pg-osc and pgroll automate this pattern, and the same steps apply on managed PostgreSQL (Amazon RDS, Supabase, Neon).

How do I find columns about to overflow?

Run this before it becomes an incident. It lists every sequence and how much of its range is used:

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC;

A bigint sequence feeding an integer column shows a tiny percentage while the column is close to full, so also compare max(id) against 2,147,483,647 for every integer key. Finding duplicate or orphaned IDs after a migration is covered in our find duplicates SQL query guide.

What are common bigint pitfalls?

• Mismatched foreign keys. Changing the parent to bigint and leaving children as integer moves the overflow to the child insert.
• Integer division and overflow in expressions. count(*) already returns bigint, but sum() of an integer column returns bigint and sum() of bigint returns numeric. Multiplying two integer columns can overflow even when the result fits in bigint: cast first, a::bigint * b. Our Postgres CAST reference lists the cast rules.
• ORMs and JSON. Clients that map int8 to a 53-bit float silently round large IDs. Serialize them as strings in JSON APIs.
• Assuming serial means bigint. serial is integer. Only bigserial or bigint GENERATED ... AS IDENTITY gives an 8-byte key.

If you want to see which of your tables have growing integer keys, or watch pct_used without writing the monitoring yourself, AI for Database connects to PostgreSQL read-only, answers questions like "which sequences are more than 50% used?" with the SQL it ran, and can send a Slack or email alert when a threshold is crossed. Try it free at aifordatabase.com. For more syntax, keep our SQL cheat sheet open.