Postgres UUID vs BIGSERIAL Primary Keys: How to Choose

The choice between a UUID and a sequential integer primary key feels minor at schema design time. By the time you have millions of rows and a distributed system, it is a decision that shapes your index performance, your row size, storage costs, and how cleanly your application can generate IDs without a database round-trip. This guide gives you the full picture.

The Core Options in Postgres

BIGSERIAL / BIGINT GENERATED ALWAYS AS IDENTITY

A 64-bit integer, auto-incremented by a database sequence. Simple, small (8 bytes), and sequential — each new row gets the next integer in order.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- ...
);

BIGSERIAL is the older shorthand. BIGINT GENERATED ALWAYS AS IDENTITY is the SQL-standard form and the one you should use in new schemas.

UUID v4

A 128-bit random identifier. Globally unique without coordination. Commonly generated by the application or by gen_random_uuid() in Postgres.

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- ...
);

UUID v7

UUID v7 is a time-ordered UUID format introduced in Postgres 17 as uuidv7(). It embeds a millisecond-precision timestamp in the high bits, so UUIDs generated close together sort close together in an index.

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  -- ...
);

UUID v7 combines global uniqueness with near-sequential insert order.

The Key Tradeoffs

Storage Size

• BIGINT: 8 bytes
• UUID: 16 bytes

For the primary key column alone, UUIDs use twice the storage. More importantly, every index that references the primary key stores a copy of it. A table with five indexes on columns that include the primary key stores the key value five additional times. At scale, this adds up.

A UUID primary key on a table with 100 million rows and five indexes uses roughly 8 GB more storage than a BIGINT key — before you account for row data.

Index Performance and Fragmentation

B-tree indexes are fastest when insertions arrive in roughly sorted order. Sequential integers satisfy this perfectly — each new row goes to the end of the index, pages fill up cleanly, and AUTOVACUUM has little to do.

UUID v4 insertions are random. Each new ID lands somewhere arbitrary in the index tree, causing page splits and fragmentation. On high-insert tables, UUID v4 primary keys produce bloated indexes that perform worse and require more frequent maintenance.

UUID v7 largely eliminates this problem because the time-ordered prefix keeps recent insertions in the same region of the index.

Application-Level ID Generation

BIGINT IDs require a round-trip to the database sequence before the application knows the ID. For bulk inserts or distributed systems, this is a bottleneck.

UUIDs can be generated entirely in the application without a database call. This matters for:

• Batch import jobs that generate thousands of records
• Event-driven systems where an ID must be assigned before the record is persisted
• Multi-region or multi-primary setups where no central sequence is available

Security and Predictability

Sequential integers expose your insert rate: a user who sees order ID 1200 today and ID 1500 tomorrow knows roughly how many orders you processed. For many applications, this is not a concern. For some, it is.

UUIDs are not guessable and reveal nothing about volume. If you expose record IDs in URLs or APIs and want to avoid information leakage, UUIDs are the safer default.

Foreign Keys and Join Size

Every foreign key that references a UUID primary key stores 16 bytes instead of 8. On a join table that stores millions of relationship rows — like a user_roles or order_items table — the difference in row size has a real effect on memory consumption and query performance.

Decision Framework

Use BIGINT GENERATED ALWAYS AS IDENTITY when:

• Your system is a single Postgres instance with no external ID generation requirement
• You want the smallest possible index size and fastest B-tree performance
• The table has a very high insert rate
• Exposing a sequential ID is not a security concern

Use UUID v7 when:

• You need globally unique IDs across distributed systems or microservices
• The application must generate IDs without a database round-trip
• You want the global uniqueness of UUID without the index fragmentation of UUID v4
• You are on Postgres 17+ (or can provide the function via an extension on earlier versions)

Avoid UUID v4 for primary keys on large tables with high write rates. The index fragmentation is real and measurable. UUID v7 gives you global uniqueness without the cost.

Consider a hybrid: Use a BIGINT surrogate primary key internally and expose a UUID (or nanoid) externally in your API. The internal key keeps indexes fast; the external key prevents enumeration. This is more complexity to manage but is a legitimate pattern for products where both properties matter.

A Practical Recommendation

For most SaaS products on a single Postgres instance: start with BIGINT GENERATED ALWAYS AS IDENTITY. It is simple, fast, and easy to work with. If your architecture later requires distributed ID generation or external ID exposure, move to UUID v7 at that point — a migration, but a manageable one.

If you are building a distributed system from the start where records must be identifiable before they hit the database, use UUID v7.

Do not use UUID v4 as a primary key on tables you expect to grow to hundreds of millions of rows.

If you are designing a data model for a new product and want to get foundational decisions like this right before you build, start a build with Clixo and we can work through the schema design with you.