Database Primary Keys: AUTO_INCREMENT, UUID, and UUIDv7

Should you use auto-increment or UUID for primary keys? While reading recent discussions, I noticed that the consensus seems to be converging on UUIDv7. But convergence aside, it’s worth clarifying the underlying trade-offs and context—otherwise, it’s just another form of jumping on the bandwagon.

TL;DR

• Using UUIDv4 offers almost no advantages.
• If you are certain it’s a single-database architecture, IDs are never exposed externally, and you won’t need cross-database merges in the future, Auto Increment is the simplest choice.
• For all other cases, use UUIDv7.

What is a UUID?

A UUID (Universally Unique Identifier) is a 128-bit identifier, with the standard format looking like this:

550e8400-e29b-41d4-a716-446655440000

36 characters, split into 5 segments with hyphens (-) (8-4-4-4-12). This segmented format originated from the earliest UUIDv1, where each segment corresponded to different internal fields. In v4 and v7, most fields are filled with random bits, but the format is preserved for backward compatibility.

There are several versions of UUID. This article primarily discusses:

• UUIDv4¹: 122 bits of pure randomness, completely structureless. Currently the most widely used version, but rather unfriendly to databases.
• UUIDv7²: The first 48 bits store a millisecond-level timestamp, and the remaining ~74 bits store random data. It balances chronological sorting with randomness and is currently the recommended choice.

Both look identical on the outside; the difference lies in their internal structure. You can distinguish them by looking at the start of the third segment: 4xxx is v4, and 7xxx is v7.

UUIDv4: 550e8400-e29b-41d4-a716-446655440000
                      ^
UUIDv7: 018f3a3b-7a5d-7a3b-8b3a-3b7a5d7a3b8b
                      ^

Why Are Primary Keys So Important?

Depending on the use case, primary keys affect data write and query efficiency, future system scalability, and whether you’ll run into landmines during migrations. As shown in the benchmarks below, performance differences can exceed 7x.

Auto Increment vs. UUID: What’s the Real Difference?

Let’s start with the most fundamental decision points. When deciding on a Primary Key, you can evaluate it from several angles:

• Insertion performance
• INDEX performance
• Search performance

The advantages of Auto Increment are straightforward: the database automatically increments it for you, it’s an 8-byte integer, compact, fast, and friendly to B-Tree indexes. Because it always appends to the end, INSERT performance is essentially the best.

However, Auto Increment has two fundamental limitations:

1. Unfriendly to Distributed Architectures

In distributed systems, generating globally unique IDs is difficult. This can be solved by implementing a centralized ID generator to ensure every ID retrieved is globally unique, such as Twitter’s Snowflake algorithm.

However, centralized generators introduce performance bottlenecks and single points of failure (SPOF). To avoid SPOF, multiple workers are typically deployed for coordination in practice, adding complexity and components to maintain.

2. Predictability

Changing a number to 1024 or 1022 might allow someone to view another user’s order (if the implementation has security flaws). Competitors can deduce your user growth or transaction volume from the rate of ID increases. (Though, on the flip side, this can also be used as a deception tactic.)

UUID (represented primarily by UUIDv4) solves both problems: a 128-bit random space that any node can generate independently without central coordination, making it virtually impossible to guess.

UUIDv4 is completely random, but when INSERTing into a B-Tree, it inserts randomly across already full pages, triggering massive page splits. Write performance degrades significantly, which is why many people who switched to UUID felt “Why has my database slowed down?”

So when making a choice, what you really need to consider is:

• Does the system require distributed generation?
• Will the ID be exposed externally (URLs, API responses)?
• How demanding are your database write performance requirements?

If your system is a monolith and IDs are not exposed externally, auto-increment works perfectly fine. But whenever distributed systems are involved, or IDs appear in URLs, UUID-based approaches become almost indispensable.

What About Collisions?

This is probably the question asked every single time UUIDs are discussed.

UUIDv4 has 122 bits of random space, yielding roughly 5.3 × 10³⁶ combinations.

To reach a 50% collision probability, you would need to generate approximately 2.7 × 10¹⁸ UUIDs—an order of magnitude equivalent to “generating 1 billion UUIDs per second worldwide continuously for 85 years.”

With UUIDv7, the random space “appears” to shrink from 122 bits to about 74 bits, but collisions can only occur within the same millisecond. In other words, only UUIDs generated within the exact same millisecond need to rely on the 74-bit random portion to avoid collisions, and 74 bits provide about 1.8 × 10²² combinations—more than enough for a single millisecond.

UUIDv7 vs. created_at + index: Benchmark Performance

The most appealing feature of UUIDv7 is that the first 48 bits contain a millisecond Unix timestamp, making it naturally time-ordered.

UUIDv7 (128 bits)
┌──────────────────────┬────┬──────────────────────────────┐
│  Unix Timestamp (ms) │ ver│         Random               │
│      48 bits         │ 4b │        ~74 bits              │
└──────────────────────┴────┴──────────────────────────────┘
   ↑ 可按時間排序        ↑v7   ↑ 亂數防碰撞

If you use UUIDv7 as the primary key, ORDER BY id is inherently equivalent to ORDER BY created_at. You no longer need an extra created_at column and an associated index for time-based sorting.

Sounds great, but how big is the actual difference? I ran a PostgreSQL 17 instance in Docker and populated it with 20 million rows to find out.

Test Setup

Three tables with the same data structure, differing only in their primary key strategy:

-- 方案 A：UUIDv7 主鍵（不需要額外的 created_at）
CREATE TABLE orders_uuidv7 (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
  user_id int NOT NULL,
  amount numeric(10,2) NOT NULL,
  status text NOT NULL
);

-- 方案 B：Serial 主鍵 + created_at 索引
CREATE TABLE orders_serial (
  id bigserial PRIMARY KEY,
  user_id int NOT NULL,
  amount numeric(10,2) NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX idx_orders_serial_created_at ON orders_serial (created_at);

-- 方案 C：UUIDv4 主鍵（對照組）
CREATE TABLE orders_uuidv4 (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id int NOT NULL,
  amount numeric(10,2) NOT NULL,
  status text NOT NULL
);

Test environment:

• Mac Mini M4
• Docker PostgreSQL 17
• shared_buffers = 256MB

1 million rows inserted per round across 20 rounds, accumulating to 20 million rows. UUIDs were generated on-the-fly in each round (not pre-generated), including generation function overhead.

Test 1: INSERT Performance Trend (per 1M rows, in ms)

Cumulative Rows	Serial	UUIDv7	UUIDv4
1M	1,132	3,185	2,998
5M	1,474	2,661	2,972
10M	1,165	2,560	6,497
13M	1,123	2,745	9,668
15M	1,411	2,630	9,188
16M	1,133	2,552	15,857
20M	1,017	2,539	20,845

The key takeaway isn’t the absolute values, but the trend.

UUIDv4 skyrocketed from 3 seconds all the way to 21 seconds.

Because as the B-Tree index grows larger, the probability that a random insert hits a page already flushed to disk increases. Every INSERT might trigger: reading the old page from disk back into the buffer pool → page split → writing back to disk.

For every 1M rows inserted, WAL volume directly reflects the database’s actual disk write burden:

Cumulative Rows	Serial WAL	UUIDv7 WAL	UUIDv4 WAL
1M	146 MB	171 MB	744 MB
5M	147 MB	171 MB	1,227 MB
8M	148 MB	171 MB	1,715 MB
10M	147 MB	171 MB	2,418 MB

Serial and UUIDv7 WAL volumes remained constant from start to finish (~147 vs ~171 MB). The gap between them (24 MB) is simply the raw data size difference between a 16-byte UUID and an 8-byte bigint.

But UUIDv4’s WAL bloated from 744 MB to 2,418 MB—for the exact same 1M row inserts, by round 10, the WAL write volume was 14 times that of UUIDv7.

This is high write amplification³.

UUIDv7 remained stable at 2.5–2.8 seconds from start to finish, with zero degradation.

Because its time-ordered nature ensures that new keys are almost always appended to the rightmost leaf page of the B-Tree, mimicking auto-increment behavior—the DB can perform sequential writes, and page splits are minimal.

Serial remained stable at 1.0–1.5 seconds.

Serial is undeniably the fastest, but the performance gap between UUIDv7 and Serial primarily stems from:

1. The UUID itself is 16 bytes—double the size of bigint’s 8 bytes—meaning index pages can hold fewer entries.
2. In real-world applications, UUIDs are typically generated in the application layer using native functions, which further closes the gap (as UUID generation time shifts to the application side).

Regarding whether to generate UUIDs using Postgres native functions, I used to lean towards “delegate everything the database can do to the database” to save effort.

However, embedding business logic inside the database makes it easy to overlook in the future. Therefore, I now lean towards generating UUIDs in the application layer.

Test 2: Storage Space (20M rows)

Strategy	Heap	Index	Total Size
Serial	1,149 MB	428 MB	1,578 MB
UUIDv7	1,302 MB	749 MB	2,051 MB
UUIDv4	1,302 MB	770 MB	2,072 MB

UUID-based heap storage is 153 MB larger than Serial (since the PK of each row increases from 8 bytes to 16 bytes), and the index is about 1.75 times larger, but in terms of overall storage footprint, there is no drastic difference.

Test 3: Cursor-Based Pagination

The principle of cursor-based pagination is using the value of the last item from the previous page as a “cursor,” querying the next page with WHERE > cursor ORDER BY ... LIMIT N. Compared to OFFSET, cursor-based pagination maintains stable performance regardless of page depth.

At a scale of 1.1 million rows, I tested three scenarios, running each 100 times, with the cursor set at row 900,000 (simulating deep pagination):

Query Method	Total Time for 100 Runs
Serial + OFFSET 900000 (baseline)	7,012 ms
Serial + cursor on created_at index	1.9 ms
UUIDv7 + cursor on PK	1.6 ms

Several observations:

1. UUIDv7 cursor pagination works directly on the primary key. No extra index is required. For the Serial approach to do time-sorted cursor pagination, you must use the created_at index, which is a secondary index requiring an index scan followed by a table lookup to retrieve data (index scan → heap fetch).
2. UUIDv7 is slightly faster than the created_at index. Because UUIDv7’s cursor traverses the primary key, whereas created_at is a secondary index.
3. OFFSET is disastrous for deep pagination. 7 seconds vs. less than 2 milliseconds—a 3,600x difference. OFFSET must scan and discard all preceding rows. While this conclusion is less specific to this article, avoid using OFFSET for pagination as it can easily drag down performance.

Summary

The advantages of using UUIDv7 as a primary key include:

1. No write performance degradation: Like auto-increment, it uses sequential appends and won’t slow down as data grows. UUIDv4 was already 7x slower at 20 million rows, and it only gets worse.
2. One index serves two purposes: It acts as both the primary key constraint and the basis for chronological sorting. It saves the created_at column and its index; cursor-based pagination can simply be done via WHERE id > :last_id ORDER BY id (though you may still need it in practice).

If your query patterns heavily rely on chronological ordering (e.g., feeds, timelines, order lists), UUIDv7 can keep schemas cleaner with fewer indexes.

However, if you need microsecond precision for creation time, or if created_at holds business logic significance (e.g., querying across time ranges), you should still keep the created_at column. UUIDv7 timestamps have millisecond precision, and ordering within the same millisecond is determined randomly, so strict insertion order is not guaranteed.

What About External URLs?

UUIDv7 works great as an internal primary key, but exposing it directly in URLs has several drawbacks: it’s too long (36 characters), and the timestamp portion still leaks the creation time.

Common approaches include:

1. Encode the 16-byte UUID using Base62 or Base58 to compress it to 21–22 characters. The underlying data remains identical, just represented in a shorter format. Base58 also excludes easily confused characters like 0/O and I/l, which is friendlier for manual input.
2. Use an independent external ID: Use NanoID to generate an external identifier completely detached from the internal ID. NanoID is purely random without time structure, offering about 126 bits of entropy across its default 21 characters, with collision resistance on par with UUIDv4.

Personally, I prefer approach 2. While Base62/58 encoding shortens the string, it is essentially still the same UUID; anyone determined enough can decode it and obtain the timestamp. NanoID is completely decoupled from the internal ID, meaning even if an external ID is leaked, nothing about the database can be inferred. The trade-off is requiring an additional column and index to store the external ID, but this trade-off is usually well worth it.

What If You Truly Need “Guaranteed Absolute Uniqueness”?

The uniqueness of UUIDs (regardless of version) is probabilistic—the chance of collision is astronomically low, but theoretically non-zero. For the vast majority of systems, this is more than sufficient. However, certain scenarios demand a 100% guarantee: financial transaction IDs, invoice numbers, medical record IDs, etc.

In these cases, you must return to a “centralized” paradigm—where a single authoritative source is responsible for issuing IDs. Common approaches include:

• Database sequences + distributed locks. The most direct approach: use PostgreSQL’s SEQUENCE or Redis’s INCR paired with distributed locks to ensure global uniqueness. The downside is the risk of a single point of failure, and lock contention becomes a bottleneck under high concurrency.
• Twitter Snowflake and its variants. As mentioned earlier, Snowflake is fundamentally centralized—allocating Worker IDs requires central coordination (ZooKeeper / etcd). Baidu’s UidGenerator and Sony’s Sonyflake follow similar concepts, with slight adjustments to bit allocation or Worker ID management.
• Database-native distributed IDs. NewSQL databases like CockroachDB and TiDB have built-in distributed unique ID generation mechanisms, resolving this problem at the database layer without requiring extra handling in the application layer.

Regardless of which option you choose, you are fundamentally trading availability or operational complexity for absolute uniqueness. If your business domain does not have regulatory or compliance requirements enforcing strict uniqueness, the probabilistic uniqueness of UUIDv7 is sufficient.

Other Trivia

• 00000000-0000-0000-0000-000000000000 is a valid UUID known as the Nil UUID⁴.
• FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF is a valid UUID known as the Max UUID.
• Don’t write your own UUID generator functions.

Looking at post-mortems from real-world incidents, failures rarely occur due to actual collisions; nine times out of ten, it’s simply buggy application code. Sigh, taking that into account, perhaps Serial really is the safest choice after all.

Footnotes

1. https://www.rfc-editor.org/rfc/rfc9562#name-uuid-version-4 ↩

2. https://www.rfc-editor.org/rfc/rfc9562#name-uuid-version-7 ↩

3. A single insertion may trigger rewriting multiple pages, significantly exceeding the volume of data actually written. ↩

4. https://www.rfc-editor.org/rfc/rfc9562.html#name-nil-uuid ↩