---
title: "Bigint in PostgreSQL: Range, Storage and int Migration"
description: "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."
canonical: "https://www.aifordatabase.com/blog/bigint-in-postgresql/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-10-28
updated: 2026-10-28
---

# 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](https://www.postgresql.org/docs/current/datatype-numeric.html):

| Type | Aliases | Storage | Minimum | Maximum |
| --- | --- | --- | --- | --- |
| `smallint` | `int2` | 2 bytes | \-32,768 | 32,767 |
| `integer` | `int`, `int4` | 4 bytes | \-2,147,483,648 | 2,147,483,647 |
| `bigint` | `int8` | 8 bytes | \-9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| `smallserial` | `serial2` | 2 bytes | 1 | 32,767 |
| `serial` | `serial4` | 4 bytes | 1 | 2,147,483,647 |
| `bigserial` | `serial8` | 8 bytes | 1 | 9,223,372,036,854,775,807 |

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

| IDs consumed per second | Time until `integer` runs out |
| --- | --- |
| 10 | about 6.8 years |
| 100 | about 249 days |
| 1,000 | about 24.9 days |
| 10,000 | about 2.5 days |

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.

![At 1,000 IDs per second an integer key runs out in 25 days, a bigint key in 292 million years](https://cdn.sanity.io/images/upsr56wu/production/1c39fa603e1e1fa3570eaf63a22dfcdb06973e4e-1344x364.png?w=1280)

FIG 01 — Rolled-back inserts and failed upserts consume IDs too.

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](https://www.aifordatabase.com/blog/postgres-timestamptz/).

| Choose | When |
| --- | --- |
| `bigint` | Primary keys on growing tables, foreign keys to them, byte or millisecond counters, external IDs (Twitter/X, Discord snowflakes) |
| `integer` | Bounded lookup tables, quantities, ages, small counts |
| `smallint` | Enum-like codes and flags where you control every value |
| `numeric` | Money and anything that needs more than 19 digits or exact decimals |
| `uuid` | IDs generated outside the database or exposed in URLs |

## 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](https://node-postgres.com/features/types) 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](https://www.postgresql.org/docs/current/sql-altertable.html), 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:

![Online int to bigint migration: shadow column, sync trigger, batched backfill, concurrent index, then swap](https://cdn.sanity.io/images/upsr56wu/production/11185cb67071b1bdff7b9c60d55ec46cbbab802e-1344x326.png?w=1280)

FIG 02 — Only the last step takes a short exclusive lock.

```
-- 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](https://www.aifordatabase.com/blog/find-duplicates-sql-query/).

## 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](https://www.aifordatabase.com/blog/postgresql-cast/) 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](https://www.aifordatabase.com/) 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](https://www.aifordatabase.com/blog/sql-cheat-sheet/) open.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is the difference between bigint and int8 in PostgreSQL?

There is none. int8 is a PostgreSQL alias for bigint; both are the same 8-byte type, and \\d displays the column as bigint. The 8 counts bytes, not bits, unlike MySQL's display-width syntax.

### What is the difference between bigint and bigserial?

bigint is the data type. bigserial is shorthand that creates a bigint NOT NULL column, a sequence, and a default of nextval() on that sequence. In new schemas, bigint GENERATED ALWAYS AS IDENTITY does the same job with SQL-standard syntax and stricter permissions.

### Can a PostgreSQL bigint be unsigned?

No. PostgreSQL has no unsigned integer types. Add CHECK (col >= 0) to forbid negative values, and use numeric(20,0) if you must store unsigned 64-bit values above 9,223,372,036,854,775,807, for example from a MySQL BIGINT UNSIGNED column.

### Does changing int to bigint lock the table?

Yes. ALTER COLUMN ... TYPE bigint on an integer column rewrites the table and its indexes under an ACCESS EXCLUSIVE lock, which blocks all reads and writes. Use the shadow-column migration above for tables you cannot take offline.

### How many digits can a bigint hold?

Up to 19 digits. The maximum, 9,223,372,036,854,775,807, has 19 digits; for longer whole numbers use numeric.

### Is bigint slower than integer?

Not in a way most workloads notice. Arithmetic is native on 64-bit CPUs; the cost is extra bytes on disk and in memory, often hidden by alignment padding. Measure with pg\_column\_size() and pg\_relation\_size() on your own tables.

Read next

*   [CUMULATIVE SUM IN SQL: RUNNING TOTALS WITH SUM() OVER →](https://www.aifordatabase.com/blog/cumulative-sum-in-sql/)
*   [FILTER IN SQL: WHERE, HAVING AND FILTER REFERENCE →](https://www.aifordatabase.com/blog/filter-in-sql/)
*   [SQL CHEAT SHEET: COMMANDS, CLAUSE ORDER AND DIALECTS →](https://www.aifordatabase.com/blog/sql-cheat-sheet/)

Source: https://www.aifordatabase.com/blog/bigint-in-postgresql/