UUID vs BIGINT: I Have Picked Both, Regretted Both, and Still Have Opinions

The UUID-vs-BIGINT debate is the database equivalent of tabs-vs-spaces. Both work. Both have committed advocates. Both have specific failure modes that show up around year two of a system's life, when the team has either grown enough to outsource onboarding to a wiki page or shrunk enough to lose institutional memory.

I have shipped systems with each. I will tell you what I would do today, and the conditions that change my answer.

The default I usually pick: BIGINT with a sequence

For a new internal-facing system without specific requirements pulling me elsewhere, I reach for BIGINT generated by a sequence. The reasons are boring:

• 8 bytes vs 16. Indexes are smaller, cache fit is better, B-tree page splits are more predictable.
• Inserts append to the end of the index. No random insertion fragmenting your hot pages.
• Support and debugging are easier when the IDs are ordered. "What were the last 50 orders" is a cheap query against the sequence.
• Logical replication, change data capture, materialized view refresh — every tool I have used is happier with sortable integer keys.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The IDENTITY syntax is preferred over SERIAL in modern Postgres. Same outcome, cleaner semantics around grants and dump/restore.

When I switch to UUID

Three cases reliably push me toward UUIDs:

1. The ID is going to be exposed externally. API responses, URLs, mobile clients. A monotonic integer leaks information — anyone can count your orders by signing up twice and subtracting. UUIDs hide the cardinality.

2. There are multiple writers and offline operation matters. Mobile apps that create records offline and sync later. Multi-region writes that cannot afford to coordinate a sequence. UUIDs are generated client-side without coordination, which removes a class of failure modes.

3. There is a real chance of merging this database with another one later. Different acquired systems, multi-tenant migrations. UUIDs do not collide; sequence-based integers do.

None of those are speculative-future reasons. If you are 80% sure none of them apply, stay on BIGINT.

The UUIDv7 update

This is the change worth knowing about: UUIDv7 sorts roughly chronologically. The first 48 bits are a millisecond timestamp. This single property fixes the worst operational pain point of UUIDv4 — random insertion order.

UUIDv4 inserts are ugly because the index pages get hit randomly. Cache hit rate drops. Page splits scatter writes across the table. Two years in, the index has grown disproportionately to the data. With UUIDv7, inserts are largely append-only at the leaf level, the same as BIGINT.

If your Postgres version supports gen_random_uuid_v7() or you are willing to use an extension or generate v7 in the application, UUIDv7 dissolves most of my historical objections to UUIDs. The size cost (16 bytes vs 8) is still there. The opacity benefit is still there. The operational pain is mostly gone.

Mistakes I see often

Storing UUIDs as TEXT instead of UUID. This doubles the storage and prevents the planner from using the cheap UUID comparison. If the column is a UUID, declare it as UUID.

Using UUIDv4 in a write-heavy hot table. The page splits will hurt. Either move to v7 or take a serious look at whether you actually need the UUID's properties on this table.

Mixing both within the same database. I have seen teams use BIGINT for internal tables and UUID for externally exposed ones, which is reasonable. I have also seen teams use both for related tables joined hot, which means joins constantly cross-cast and the planner does inconvenient things. Pick one type per relationship cluster.

Generating UUIDs server-side when client-side would do. If the whole point of UUIDs is uncoordinated generation, defaulting them with gen_random_uuid() in the database column re-coordinates them. Generate in the application unless there is a specific reason not to.

What about partitioning, sharding, or massive scale?

At very large scale (billions of rows, multi-region writes), the conversation moves past the type and into the partition key, range strategy, and routing layer. UUIDs stop being a clear win because you have to think about which UUID prefix maps to which shard. BIGINTs need a coordinated allocator (Snowflake-style). Both work; both require infrastructure.

Most systems do not reach this scale. If you are architecting for it preemptively, you are probably overbuilding.

My checklist before I commit to either

1. Is the ID exposed externally? UUID weighs heavier.
2. Are there multiple uncoordinated writers? UUID weighs heavier.
3. Is this an internal table joined by integer FKs everywhere else? BIGINT weighs heavier.
4. Does our Postgres support UUIDv7? If not, that is one more reason to pick BIGINT for write-heavy tables.
5. Have I budgeted for the index size difference? At 100 million rows, a UUID index is roughly twice the disk and cache pressure of a BIGINT one.

The answer is rarely dramatic either way. Pick deliberately, document why, and do not change it under pressure during an incident.