Postgres UUID vs BIGSERIAL Primary Keys: How to Choose

Primary key choice affects storage, index behavior, foreign keys, and where IDs can be generated. Compare sequential integers, UUID v4, and UUID v7 against those requirements.

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

PostgreSQL 18 added the built-in uuidv7() generator. UUID v7 includes a timestamp, so values generated close together tend to sort close together. Earlier PostgreSQL versions can store UUID v7 values generated externally. PostgreSQL 18 release notes.

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.

For 100 million rows, the raw difference is about 0.8 GB for each stored copy of an 8-byte-larger key, before index and row overhead. Count only indexes that actually contain the key; PostgreSQL does not automatically add the primary key to every secondary index.

Index Performance and Fragmentation

B-tree indexes are fastest when insertions arrive in roughly sorted order. Sequential integers generally place new keys near the end of a B-tree. Vacuum work also depends on updates and deletes, not just insertion order.

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.

UUID v4 uses random bits, while UUID v7 exposes a time component. Neither replaces authorization checks, and UUID v7 should not be described as revealing no timing information. PostgreSQL UUID functions.

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 18+ for built-in generation, or generate UUID v7 values outside the database

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.

Discuss Postgres schema design with LinkIT.