PostgreSQL BIGINT vs INTEGER: Ranges, Storage, and IDs

Check integer ranges and sequence headroom

select
  pg_typeof(id) as id_type,
  max(id) as largest_id,
  2147483647::bigint - max(id)::bigint as integer_ids_remaining
from events
group by pg_typeof(id);

This arithmetic is useful only for an INTEGER ID. Also inspect the sequence value because rolled-back or deleted inserts can consume IDs without increasing max(id).

Start with range and growth

INTEGER allows roughly 2.1 billion positive values when an identity sequence starts at 1. At one million generated IDs per day, that theoretical space lasts a little under six years. At ten thousand per day it lasts centuries. Failed transactions, deleted rows, sequence caching, and manual jumps still consume or skip values.

BIGINT removes that practical ceiling for most applications. Its cost is four additional bytes in every table row that stores the value and in each index entry or foreign-key column that includes it. The total difference matters most on large tables with several indexes and referencing tables.

Use identity columns for generated IDs

INTEGER and BIGINT describe storage and range. Identity describes how PostgreSQL generates a value. Keep those decisions separate: an identity column can use either integer type, and a plain BIGINT does not generate values by itself.

Prefer SQL-standard identity columns for new schemas. The existing SERIAL vs identity guide explains ownership, defaults, and migration from older serial columns.

Create integer and bigint identity columns

create table projects (
  id integer generated always as identity primary key,
  name text not null
);

create table audit_events (
  id bigint generated always as identity primary key,
  project_id integer not null references projects (id),
  payload jsonb not null
);

Monitor the sequence, not just the table

A sequence advances independently of committed rows. INSERT attempts that fail or roll back can leave gaps, and deleting old rows does not return their values. Compare the sequence's current position with the underlying type limit when forecasting exhaustion.

Find the sequence and read its current state

select pg_get_serial_sequence('public.events', 'id') as sequence_name;

select last_value, is_called
from public.events_id_seq;

pg_get_serial_sequence also works for an identity column despite its historical name. Sequence access requires the appropriate privilege.

Plan an INTEGER to BIGINT migration early

PostgreSQL does not widen an overflowing INTEGER automatically. ALTER COLUMN TYPE must account for the main table, its indexes, foreign keys, referencing columns, locks, disk space, replication, and the PostgreSQL version in use. Test the exact operation against a production-sized copy.

For a large busy table, teams often use a staged migration: add BIGINT columns, keep them synchronized, backfill in batches, build replacement indexes, update foreign keys, and switch columns during a short controlled lock. The right process depends on table size and write rate; do not wait until the sequence has only days of headroom.

Map unsigned MySQL integers carefully

PostgreSQL integer types are signed. A MySQL INT UNSIGNED can hold values up to 4,294,967,295, which does not fit in a PostgreSQL INTEGER. BIGINT preserves that range. MySQL BIGINT UNSIGNED can exceed PostgreSQL BIGINT and may require NUMERIC(20), a constraint, or a redesigned identifier strategy.

Audit the actual maximum values before converting a schema. The MySQL to PostgreSQL migration guide covers type mapping, pgloader, validation, and cutover checks.