UUIDs vs. Sequential IDs as Primary Keys in Postgres 🛢

28 May, 2026

At work, we currently use a Postgres database to handle our regular OLTP transactions. For all of our tables, we use natural, sequential integer IDs as primary keys (PKs). This pattern is simple and familiar in terms of how "unique" IDs are generated for each row in a table.

However, the usage of UUIDs are everywhere in modern software. They show up in APIs, distributed systems, event pipelines, object storage, and more. If UUIDs are so common in software, it made me naturally wonder why we don't use them for our DB tables at work?

The TL;DR answer is not that UUIDs are “bad” or that sequential IDs are “old-fashioned.” It is that primary keys are not just identifiers. In Postgres, they affect indexing, storage layout, write patterns, query performance, debugging, and even how easy the system is to reason about.

So the better question is not “Should we use UUIDs or sequential IDs?” It is: what properties do we actually need from our primary keys?

NOTE: The following is written with the perspective of Postgres, but should generally apply to all SQL databases.

Primary Keys Are More Than Just Identifiers

At surface level, a primary key (PK) in Postgres is straightforward; it is just a column (or a group of columns) that uniquely identifies each row in a table. Technically, a PRIMARY KEY column automatically has both a NOT NULL and a UNIQUE constraint attached to it.

PK column has a NOT NULL and a UNIQUE constraint on it. A table can have zero or one PK, but it cannot have more than one PK.

However, in Postgres, a PK does more than just simply guarantee row uniqueness.

What a Primary Key Actually Does

Under the hood, Postgres implements a default B-tree index for the PK. An index is a specialized data structure that helps the database locate rows efficiently without scanning the entire table. Maintaining an index is not "free"; there comes an additional storage cost of actually maintaining the index. Postgres supports several index types, such as B-tree, Hash, GIN, GiST, and BRIN indexes, each optimized for different access patterns.

The PKs for tables become the most heavily used pieces of data that connect the whole database together. Most importantly, PKs influence physical access patterns across the database. Foreign keys (FK) relationships reference them and application queries frequently rely on them for joins and pagination.

So, the order and distribution of inserted PKs affect how index pages are laid out on the (hardware) disk, how often indexes fragment, how much data stays "hot" in memory, and how efficiently writes can be performed.

This is where the distinction between sequential IDs and UUIDs as PKs starts to matter.

Why Postgres Cares About Insert Order

As mentioned earlier, Postgres typically implements primary key indexes using a B-tree. While the technical details of indexes are beyond the scope of this post, it is useful to understand one key property: values are stored in "sorted" order.

However, "sorted" does NOT necessarily mean sequential. A B-tree index can efficiently maintain values in sorted order regardless of whether the keys are 1, 2, 3, 4, 5 or randomly generated UUIDs. The difference is not whether the index can store the values, but how much work Postgres must perform to maintain the index as new rows are inserted.

So, when a new row is inserted, Postgres must also insert the corresponding key into the correct location within the index. If keys arrive in a predictable order, such as 1, 2, 3, 4, 5, new entries are typically added near the end of the index. If keys arrive in a random order (i.e. UUID), Postgres may need to insert entries throughout many different parts of the index.

At small scales, this distinction is largely irrelevant. However, as tables grow to millions or billions of rows, insertion order begins to affect how index pages are organized, how efficiently memory is utilized, and how much work Postgres must perform to maintain the index. This is one of the primary reasons sequential IDs and UUIDs exhibit different performance characteristics.

Why Sequential IDs Work So Well

Sequential Inserts Are Friendly to B-Trees

When primary keys are generated sequentially, each new key is naturally larger than the one before it. As a result, new index entries are typically inserted near the end of the B-tree.

1 → 2 → 3 → 4 → 5 → 6 → 7

This insertion pattern is highly efficient because Postgres can continue writing to the same small set of index pages as the table grows. Because inserts are concentrated in the same area of the index, Postgres repeatedly accesses the same small set of pages. These pages are more likely to remain cached in memory, reducing the amount of disk I/O and bookkeeping required to maintain the index.

Sequential inserts also reduce the frequency and impact of page splits. A page split occurs when an index page becomes full and Postgres must allocate a new (index) page and redistribute existing entries to make room for additional inserts. This is a normal part of maintaining a B-tree, but it requires additional work because multiple pages and tree pointers must be updated.

Sequential Inserts

Before:

[ 1 | 2 | 3 ] -> [ 4 | 5 | 6 ] -> [ 7 | 8 | 9 ]  ← active page

Insert 10

[ 1 | 2 | 3 ] -> [ 4 | 5 | 6 ] -> [ 7 | 8 | 9 | 10 ]
                                              ^
                                       insert here

Eventually page becomes full...

[ 1 | 2 | 3 ] -> [ 4 | 5 | 6 ] -> [ 7 | 8 | 9 | 10 ]  ← full

Insert 11

[ 1 | 2 | 3 ] -> [ 4 | 5 | 6 ] -> [ 7 | 8 ] -> [ 9 | 10 | 11 ]

In the toy example above, notice how sequential inserts always occur at the growing edge of the index. Even when a page split occurs, Postgres only needs to modify a small, localized portion of the B-tree.

With sequential IDs, page splits tend to occur near the growing edge of the index where new entries are being appended. By contrast, randomly distributed keys such as UUID values can trigger page splits throughout many different parts of the index. As a result, Postgres must touch more pages, perform more bookkeeping, and maintain a less cache-friendly index structure.

The result is that sequential primary keys tend to produce indexes with better locality, fewer disruptive page splits, and more predictable write patterns. Individually, these savings may seem small, but across millions or billions of inserts they can have a meaningful impact on database performance.

Smaller Keys, Smaller Indexes

Beyond insertion patterns, the size of the primary key itself also matters.

Sequential primary keys are commonly represented as either INT (4 bytes) or BIGINT (8 bytes), depending on the expected scale of the application. By comparison, a UUID occupies 16 bytes.

A typical sequential primary key uses either an INT (4 bytes) or BIGINT (8 bytes), depending on the expected scale of the application. By comparison, a UUID occupies 16 bytes. While an additional 8-12 bytes may seem insignificant, primary keys are stored and referenced throughout the database, which causes this difference to compound.

Every primary key value must be stored in the primary key index itself. Foreign key columns that reference the primary key must also store the same value. As tables grow and relationships multiply, larger keys increase both storage requirements and memory consumption.

Larger keys can also make indexes less efficient. Because each index entry occupies more space, fewer entries fit within a given index page. This can cause indexes to grow larger, consume more memory, and require additional page reads during queries.

While this is a true difference between sequential IDs and UUIDs, the stronger point for sequential IDs is locality and insertion behavior, not the raw 8-byte savings.

Operational Simplicity

Not every tradeoff comes down to performance. Sequential IDs also provide a number of practical benefits when operating and debugging systems.

For humans, sequential IDs are easier to read, type, and reason about. Seeing records with IDs like 1001, 1002, and 1003 immediately conveys a sense of ordering and progression. UUIDs, while highly effective as unique identifiers, are significantly less approachable when scanning logs, debugging issues, or inspecting database records.

In many systems, sequential IDs also loosely correlate with insertion order. While they should not be treated as timestamps, they can provide a useful signal when investigating when records were created or understanding the rough sequence of events.

What UUIDs Optimize For

At this point, sequential IDs may seem like the obvious choice. They are compact, efficient for B-tree indexes, and generally easier for humans to work with. So why do UUIDs exist in the first place?

The answer is that UUIDs optimize for a different set of constraints.

UUIDs Solve a Different Problem

In many applications, ID generation is delegated to the database through sequences generators or identity columns. This works extremely well when there is a single source of truth responsible for writes, even if multiple application servers or read replicas exist. As long as all inserts flow through the same writer DB instance, generating unique sequential IDs is straightforward and requires little thought from application developers.

However, UUIDs become more compelling when identifiers must be generated outside the database, across multiple independent systems, or before data is written to a central datastore. Rather than relying on a database to assign an ID, applications can generate UUIDs independently while maintaining an extremely low probability of collisions.

The Performance Tradeoff of Randomness

The most common UUID variant, UUIDv4, generates identifiers using random values. While this randomness provides excellent uniqueness properties, it also changes how new entries are inserted into a B-tree index.

Recall that B-tree indexes maintain values in sorted order. With sequential IDs, new entries are typically appended near the growing edge of the index. UUIDs, however, can be inserted almost anywhere within the tree.

Sequential IDs

[ ... ] -> [ ... ] -> [ active page ]
                                ^
                         inserts land here


UUIDs

[ ... ]  [ ... ]  [ ... ]  [ ... ]
    ^        ^        ^        ^
  inserts can land anywhere

Because inserts are distributed throughout the index, Postgres must touch a larger number of index pages. Instead of repeatedly working with the same small set of pages, writes become spread across many different parts of the tree. This reduces locality and makes it less likely that the relevant pages remain cached in memory.

UUID randomness is not without purpose. It is the same property that allows UUIDs to be generated independently without relying on a central authority. In other words, the performance costs described above are the price paid for decentralized identifier generation, which is very helpful in distributed systems.

Fun fact:

"If every person on Earth generated 1 billion UUIDs per second for the next century, most engineers would still never witness a UUIDv4 collision in their lifetime!" ~ ChatGPT

Sequential IDs optimize for locality and efficient index maintenance by coordinating ID assignment through a single writer. UUIDs trade some of that efficiency for the flexibility to generate identifiers anywhere in the system while still maintaining an extremely low probability of collisions.

So Which One Should You Use?

When Sequential IDs Make Sense

Sequential IDs are often a great default choice when a single database acts as the source of truth for writes. They are compact, easy to reason about, and produce efficient B-tree indexes with predictable insertion patterns.

When UUIDs Make Sense

UUIDs are a strong choice when identifiers need to be generated outside the database or across multiple independent systems. They trade some database efficiency for increased flexibility in how and where IDs are created.

You should consider UUIDs when the answer to one or more of these questions is “yes”:

Do IDs need to be generated before the row is inserted into the database?
Do multiple independent systems need to create the same type of entity without coordinating through one writer?
Will records from different databases, regions, or tenants eventually need to be merged?
Are these IDs exposed publicly in URLs, APIs, logs, or client-side applications?
Would predictable IDs leak information, such as record counts or creation order?

If the answer to most of these questions is “no,” then sequential IDs are often a strong default. In a system where all writes flow through a single database writer, the database can safely and efficiently generate IDs using sequences.

A Common Hybrid Approach

In practice, many systems use both. A table may use a sequential INT or BIGINT as its primary key, while also storing a separate UUID column with a unique constraint. Internally, the database and foreign key relationships use the sequential ID, while APIs, URLs, and external systems reference the UUID.

Final Thoughts

References

#database #software