Should a new relational table use a bigint identity or a UUID primary key?

Use a bigint identity primary key when the database is the only place that creates rows and you care about compact indexes and append-friendly inserts. Use a UUID primary key when clients, edge workers, or multiple systems must mint IDs before talking to the database, or when you will merge data from separate databases. If you need public IDs, consider keeping a fast internal key and a separate external identifier instead of forcing one column to do both jobs.

What decision actually comes first?

Choose the generation boundary before the data type. In PostgreSQL, an identity column is “generated automatically from an implicit sequence” and is suited to database-assigned keys, but the docs also note that identity does not itself guarantee uniqueness without a PRIMARY KEY or UNIQUE constraint (PostgreSQL identity columns).

UUIDs solve a different problem. PostgreSQL defines uuid as a 128-bit identifier and explicitly says UUIDs can be generated outside the database; for distributed systems they provide a better uniqueness guarantee than sequence generators that are only unique within one database (PostgreSQL UUID type). RFC 9562: Universally Unique IDentifiers (UUIDs) makes the same architectural point: UUIDs require no central registration process.

So the first question is not performance. It is: where must an ID be created?

When is bigint identity the right default?

Pick bigint GENERATED ... AS IDENTITY when a single database is the source of truth and every insert can wait for the database to assign an ID. This is the clean default for order lines, ledger entries inside one service, or join tables created only by server-side code.

The practical upside is smaller keys and denser indexes. A bigint is 8 bytes; a UUID is 16 bytes. That difference repeats in primary indexes, foreign keys, and joins. You also get better locality from sequence-backed inserts because new rows tend to land near the end of a B-tree instead of scattering.

This advice does not apply if records must exist before the database round trip, such as offline mobile creation or event production in disconnected workers.

When should UUID win?

Use UUID when multiple writers need to create identifiers independently: offline-first clients, imports from partner systems, multi-region ingestion, or future merges between databases that should not collide.

There is one important nuance. RFC 9562: Universally Unique IDentifiers (UUIDs) warns that non-time-ordered UUIDs such as UUIDv4 have poor database-index locality and can cause random insert placement. The PostgreSQL docs say PostgreSQL can generate UUIDv4 and UUIDv7 natively (PostgreSQL UUID type). If you want UUIDs in a write-heavy relational table, UUIDv7 is usually the better fit because its time ordering reduces the locality penalty compared with UUIDv4.

Do you need one identifier or two?

Often the best design is two identifiers:

• internal primary key: bigint
• external/public ID: uuid

Worked example: an accounts table used heavily in joins inside one PostgreSQL cluster, but account IDs also appear in URLs and import files. Keep account_pk bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, add public_id uuid NOT NULL UNIQUE, and generate public_id in the app or database. Internal joins stay compact; public APIs avoid exposing a simple sequence. That is cleaner than treating the primary key as both storage key and public reference.

Do not confuse either choice with authorization. An unguessable ID can reduce accidental enumeration, but access control still belongs in application policy.

Quick checklist?

Choose bigint identity if:

• the database assigns IDs on every write
• one database is the coordination point
• the table is join-heavy or write-heavy

Choose UUID if:

• IDs must be created before insertion
• you will merge records from independent systems
• public references should not depend on one sequence

Follow-up Q&A

What about sharding later?
If sharding is only a distant possibility, do not pay today’s UUID cost automatically. Start with bigint unless independent ID generation is already a requirement.

Can I switch later?
Yes, but it is a migration across primary keys, foreign keys, and APIs. If offline creation or cross-system merges are already on the roadmap, decide now instead of retrofitting later.

Next step: write down who creates the row first—database, application server, or external client—and let that boundary choose the key strategy.

Reviewed: 2026-09-05.