PostgreSQL type decision
PostgreSQL UUID vs BIGINT Primary Keys
Compare PostgreSQL uuid and bigint primary keys by storage, ordering, public exposure, distributed generation, and index behavior.
Use bigint when compact indexes and simple database-generated IDs matter most. Use UUID when IDs must be generated outside one database or safely exposed without revealing a sequence. On PostgreSQL 18, UUIDv7 is the practical UUID default for new write-heavy tables.
- uuid
- Distributed creation, public IDs, offline records, data merges
- bigint
- Compact internal keys, simple sequences, maximum index locality
A concrete schema
-- PostgreSQL 18
create table public_events (
id uuid primary key default uuidv7(),
created_at timestamptz not null default now()
);
create table internal_jobs (
id bigint generated always as identity primary key
);When uuid is the better fit
Distributed creation, public IDs, offline records, data merges.
When bigint is the better fit
Compact internal keys, simple sequences, maximum index locality.
The tradeoff that matters
A uuid is 16 bytes and a bigint is 8 bytes before index and foreign-key overhead. Random UUIDv4 values also scatter B-tree inserts. UUIDv7 keeps the UUID format while placing time information at the front, which improves insertion locality.
A migration note
Changing a column type in a live database can rewrite the table, rebuild indexes, or break application assumptions. Inspect dependent foreign keys and run the conversion against production-sized data before scheduling the migration.
Questions that come up
Is uuid always better than bigint?
No. The choice follows the meaning of the data and the queries you need to run. A blanket rule hides the one requirement that should decide the column.
Can I change the type later?
Usually, but the migration may require an explicit USING expression and can lock or rewrite a large table. Test the exact ALTER TABLE statement on a recent copy first.
Where can I try the schema?
Use the PostgresGUI SQL editor for syntax work or the schema designer to model the table and export PostgreSQL SQL.