Topic: PostgreSQL

UUIDv7 for Primary Keys: Better, Not Universal

UUIDv7 improves the index locality of UUIDv4, and PostgreSQL 18 and Python 3.14 support it natively—but bigint, timestamp privacy, and migration costs still matter.

Few database arguments last as long as auto-incrementing integers versus UUID primary keys.

A recent Reddit discussion about the cost of UUID primary keys in SQLite brought the familiar camps back together. One side argued that UUIDv7 solves the ordering problem. The other pointed out that a 128-bit key is still wider than an integer, and joins do not become free when the version number changes to 7.

Both sides are still right.

UUIDv7 fixes one of UUIDv4’s worst database properties, but it does not repeal the basic costs of data structures. The practical conclusion is not “use UUIDv7 everywhere.” It is: if the requirement already calls for a UUID, v7 should be the first option for most new designs; if the system does not need UUIDs, bigint remains excellent.

UUIDv4’s real problem: every insert lands somewhere else

UUIDv4 is almost entirely random. Independent nodes can generate IDs without asking a central database for a number first. The trade-off is that consecutively generated values have no useful ordering relationship.

For a B-tree index, new rows therefore land at random positions. Cache behavior, page locality, page splits, and write amplification can all be worse than with an increasing key. RFC 9562 explicitly lists UUIDv4’s poor database-index locality as one motivation for the newer formats.

The issue is not simply that a UUID is long. It is that the next value can appear anywhere. That is the part UUIDv7 addresses.

What UUIDv7 changes: time goes first

Under RFC 9562, the first 48 bits of UUIDv7 contain a Unix Epoch timestamp in milliseconds. The remaining space includes the version, variant, and randomness, a counter, or sub-millisecond precision chosen by the implementation.

The result is that a later UUID will usually sort after an earlier one. A database can compare it as an opaque 128-bit value without parsing text to recover the time order.

That gives UUIDv7 three practical advantages:

• Applications, devices, and separate services can still generate IDs in advance.
• New values usually stay near recent inserts in a B-tree.
• Logs and cursor pagination get an order that roughly follows generation time.

“Roughly time ordered” is not the same as “exact event time.” Monotonicity within one millisecond depends on whether the generator uses a counter, extra timestamp precision, or another permitted method. Across machines, clock skew still exists.

PostgreSQL 18 finally makes it boring

The largest historical friction around UUIDv7 was not understanding the format. It was choosing an extension or third-party library in every project. PostgreSQL 18 now includes native uuidv7(), so a new table can be this ordinary:

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz NOT NULL DEFAULT now()
);

The explicit created_at is intentional. PostgreSQL also provides uuid_extract_timestamp(id), but its documentation warns that the extracted timestamp is not necessarily the exact generation time; that depends on the generator.

Application code may not need a dependency either. Python 3.14’s standard library now supports uuid.uuid7():

from uuid import uuid7

order_id = uuid7()

Client-side generation is useful when objects must exist offline, relationships are assembled before a write, or multiple writers need IDs without coordination. With one PostgreSQL writer, database generation is usually simpler and can provide better monotonicity within that node.

Why bigint is not retired

UUIDv7 is still 128 bits. PostgreSQL’s bigint uses 8 bytes. When a primary key is repeated across secondary indexes, foreign keys, and join buffers, that difference accumulates.

If a system has these properties, bigint GENERATED ... AS IDENTITY is often still the least expensive answer:

• One central database creates the data.
• The application does not need an ID before insertion.
• IDs do not need to remain unique across databases, devices, or services.
• Internal join and storage efficiency matter more than a public identifier.

That is not nostalgia for an old technology. It is refusing to pay the permanent cost of a 16-byte key for a distributed requirement that does not exist.

A hybrid design can also be reasonable: keep a bigint primary key and add a uuid column as the public or cross-system identifier. It costs another unique index but keeps heavily repeated internal foreign keys compact. Whether that trade is worthwhile depends on which path—public lookup or internal join—is actually hot.

UUIDv7 is not created_at, and it is not a secret

Once an ID is sortable, dropping the timestamp column looks tempting. Do not.

RFC 9562 allows generators to alter timestamps for clock precision, correction, or other implementation concerns. It does not guarantee perfect agreement with real time. Business creation time, receipt time, and scheduled time still deserve explicit columns and database constraints.

A UUID is not an authorization capability either. The RFC warns implementations not to assume UUIDs are hard to guess. UUIDv7 also exposes approximate generation time and order. If possession of a value grants access, the system needs a high-entropy, revocable, rotatable secret token plus normal authorization checks.

IDs identify. Timestamps record time. Secrets grant authority. Keeping those jobs separate prevents strange exceptions six months later.

Do not inflate 128 bits back into text

A standard UUID looks like 36 characters, but the database does not need varchar(36) to store it. RFC 9562’s DBMS guidance notes that the textual representation needs 288 bits for an original 128-bit value and recommends native or binary storage where practical.

PostgreSQL already has a native uuid type for input, output, and comparison. A string column usually buys only a larger index, weaker type checks, and more tedious formatting bugs.

Do not rewrite a healthy primary key just to change versions

If an existing UUIDv4 table has no measured index-locality or write-performance problem, replacing its primary key because v7 is newer is rarely worthwhile.

A primary-key migration touches every foreign key, replication path, cache key, event payload, and external integration. UUIDv7 fits best at the boundary of a new table, a new bounded context, or a migration that already needs a new identity contract. For an old system, measure the problem before paying the migration cost.

My selection rule

When choosing UUIDv7, add five checks:

1. Use an RFC 9562-compatible generator; do not assemble bits yourself.
2. Store it in the database’s native uuid type, not text.
3. Keep the real created_at column.
4. Never treat the identifier as a secret.
5. Benchmark with the application’s real data volume, indexes, and query patterns.

Conclusion: a better UUID, not a better everything

UUIDv7’s main contribution is removing the forced choice between distributed generation and index-friendly ordering. The RFC is final, and PostgreSQL 18 and Python 3.14 have brought it into native toolchains, so adoption is much less awkward than it was a few years ago.

Primary-key decisions still involve key width, foreign-key count, writer topology, public identifiers, timestamp privacy, and migration cost.

My default is therefore simple: once the design requires UUIDs, start with v7; until the need for UUIDs is proven, keep using bigint.

External references

• RFC 9562: Universally Unique IDentifiers
• PostgreSQL 18: UUID Functions
• PostgreSQL 18: UUID Type
• PostgreSQL 18: Numeric Types
• Python 3.14: uuid — UUID objects according to RFC 9562
• daily.dev: What’s a UUID, and what do you use that for?
• Reddit: The perils of UUID primary keys in SQLite