UUIDv7 in PostgreSQL 18: uuidv7() vs UUIDv4 vs bigint Primary Keys, Benchmarked

UUIDv7 is a UUID that starts with a 48-bit Unix timestamp in milliseconds, so new IDs sort in creation order. PostgreSQL 18 generates it natively with uuidv7(). As a primary key it keeps the B-tree index compact like a bigint sequence does, while staying globally unique like a random UUIDv4.

I loaded 10 million rows into three otherwise identical PostgreSQL 18.6 tables, keyed by bigint, uuidv4() and uuidv7(). UUIDv7 matched bigint on load time (37.3 s each), while UUIDv4 took 84.2 s and wrote 2.2x more WAL. I also found one trap: filtering with uuidv7(interval ...) directly in a WHERE clause skips the index.

TL;DR: should I use UUIDv7 primary keys in Postgres?#

Yes, if you want UUID primary keys and the IDs do not need to hide when a row was created. On PostgreSQL 18, use id uuid primary key default uuidv7(). On PostgreSQL 17 or older, generate UUIDv7 in your app. Keep UUIDv4 for tokens and IDs whose creation time must stay private, and keep bigint if you never need IDs outside one database.

What is UUIDv7?#

UUIDv7 is the time-ordered UUID version defined in RFC 9562, published in May 2024. The first 48 bits hold the Unix timestamp in milliseconds, then 4 version bits, then random or counter bits. Because the timestamp comes first, sorting UUIDv7 values as bytes sorts them by creation time, which B-tree indexes handle well.

The RFC describes it as featuring "a time-ordered value field derived from the widely implemented and well-known Unix Epoch timestamp source," and says "implementations SHOULD utilize UUIDv7 instead of UUIDv1 and UUIDv6 if possible."

A UUIDv7 from my test database, read left to right:

01a14a80-6c02-7160-b932-6c5ecd65f3c7
└──────┬─────┘ │
  timestamp    version 7   (rest: sub-millisecond precision + random)

Does PostgreSQL support UUIDv7?#

Yes, since PostgreSQL 18, released on September 25, 2025. It added uuidv7(), an alias uuidv4() for gen_random_uuid(), and uuid_extract_timestamp(), which reads the timestamp back out. The PostgreSQL 18 docs say the timestamp uses "millisecond precision + sub-millisecond timestamp + random." PostgreSQL 17 and older have no uuidv7().

SELECT uuidv7(), uuidv4();
-- 01a14a74-450a-7a7e-a2f5-713a6b1da251 | 8c13f904-b813-4570-92de-5622dbb59443

SELECT uuid_extract_version(uuidv7()), uuid_extract_timestamp(uuidv7());
-- 7 | 2026-10-17 20:21:36.266+05

SELECT uuidv7(interval '-1 hour');   -- a UUIDv7 with a timestamp shifted one hour back

On PostgreSQL 17 the same call fails with ERROR: function uuidv7() does not exist. There, generate UUIDv7 in your application (Prisma's uuid(7) or the npm uuid package's v7()), or upgrade.

The PostgreSQL 18 release announcement calls it "the database-friendly uuidv7() function that provides better indexing and read performance for UUIDs." Here is what that looked like in my test.

Is UUIDv7 faster than UUIDv4?#

For inserts, yes. Loading 10 million rows took 37.3 s with UUIDv7 and 84.2 s with UUIDv4, and UUIDv4 wrote 4.6 GB of WAL against 2.1 GB. With 8 clients each inserting one row per transaction, UUIDv4 managed about 15% fewer inserts per second. UUIDv7 was within 2% of a bigint sequence on every insert test.

Benchmark setup#

• PostgreSQL 18.6 with default settings (shared_buffers 128 MB), on an 8 vCPU Xeon with 15 GB RAM.
• Three tables: id (the key under test), created_at timestamptz default now() and a 64-character payload.
• Bulk load: one INSERT ... SELECT of 10,000,000 rows per table after a CHECKPOINT. WAL is measured with pg_current_wal_lsn() and index density with pgstattuple's pgstatindex().
• Concurrent inserts: pgbench, 8 clients, 60 seconds of single-row INSERTs into the preloaded tables, 2 rounds. The table shows the average of the two rounds.

CREATE TABLE t_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now(), payload text NOT NULL);
CREATE TABLE t_uuidv4 (id uuid PRIMARY KEY DEFAULT uuidv4(),                created_at timestamptz NOT NULL DEFAULT now(), payload text NOT NULL);
CREATE TABLE t_uuidv7 (id uuid PRIMARY KEY DEFAULT uuidv7(),                created_at timestamptz NOT NULL DEFAULT now(), payload text NOT NULL);

Results#

UUIDv7's index is 41% larger than bigint's, because each key is 16 bytes instead of 8, but it is packed just as tightly (90% leaf density). UUIDv4's index is 28% larger again than UUIDv7's, because random inserts split pages all over the tree and leave them about 70% full.

Why are random UUIDv4 primary keys slow?#

Each new UUIDv4 lands at a random position in the B-tree, so inserts touch pages spread across the whole index. Once the index is larger than memory, every insert can mean reading a cold page, dirtying it, and logging a full-page image to WAL after each checkpoint. UUIDv7 keys always go to the rightmost leaf, the same hot page a sequence uses.

That explains each number in the table. The 2.2x WAL comes from full-page writes for thousands of different leaf pages. The 70% leaf density comes from 50/50 page splits in the middle of the tree. In my test the UUIDv4 index (385 MB) was three times shared_buffers (128 MB), so the gap in single-row inserts (15%) was smaller than in bulk loading (2.3x). It will grow as the table and index outgrow memory.

How do I use UUIDv7 as a primary key in PostgreSQL 18?#

Declare the column as uuid with DEFAULT uuidv7(). Keep the native uuid type, not text: it stores 16 bytes instead of 36 characters plus overhead. Existing UUIDv4 rows can stay. Both versions share one uuid column, and new UUIDv7 keys simply sort after the old random ones.

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

ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

Changing the default does not rewrite old rows or rebuild the index. It only changes how future keys are generated, so the right edge of the index starts behaving like a sequence from then on.

How do I use UUIDv7 with Prisma and Drizzle?#

In Prisma, use @default(uuid(7)) to generate the ID in Prisma Client, or @default(dbgenerated("uuidv7()")) to let PostgreSQL 18 generate it. In Drizzle, use uuid().primaryKey().default(sql\uuidv7()`)`. I tested all three on PostgreSQL 18.6. Every ID came out as version 7, and 1,000 sequential creates sorted exactly in creation order.

// UUIDv7 generated by Prisma Client in Node.js
model Order {
  id        String   @id @default(uuid(7)) @db.Uuid
  total     Int
  createdAt DateTime @default(now())
}

// UUIDv7 generated by PostgreSQL 18
model Invoice {
  id        String   @id @default(dbgenerated("uuidv7()")) @db.Uuid
  total     Int
  createdAt DateTime @default(now())
}

The two Prisma options are not equivalent. prisma migrate dev created "id" UUID NOT NULL DEFAULT uuidv7() for Invoice but no default at all for Order. So a raw SQL insert into Order failed with null value in column "id" ... violates not-null constraint (code 23502), while the same insert into Invoice worked. If anything other than Prisma Client writes to the table (SQL scripts, another service, a queue worker), use dbgenerated("uuidv7()"). The Prisma schema reference documents both.

import { sql } from "drizzle-orm";
import { integer, pgTable, timestamp, uuid } from "drizzle-orm/pg-core";

export const invoices = pgTable("invoices", {
  id: uuid().primaryKey().default(sql`uuidv7()`),
  total: integer().notNull(),
  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
});

drizzle-kit generate turned that into "id" uuid PRIMARY KEY DEFAULT uuidv7() NOT NULL. Drizzle's .defaultRandom() still means gen_random_uuid(), which is UUIDv4. For which ORM to pick in the first place, see my Drizzle vs Prisma benchmark.

Can I query by time using a UUIDv7 key?#

Yes, because UUIDv7 order is time order: ORDER BY id DESC LIMIT 20 returns the newest rows from the primary key alone. For a time range, compare against uuidv7(interval '-1 hour'), but wrap it in a subquery. Used directly, the volatile function forced a full parallel scan of 15.8 million rows in my test.

-- Slow: uuidv7() is VOLATILE, so it cannot be an index condition
SELECT count(*) FROM t_uuidv7 WHERE id >= uuidv7(interval '-30 seconds');
-- Parallel Seq Scan, Rows Removed by Filter: 5,280,621 per worker, 3,430 ms

-- Fast: a scalar subquery is evaluated once (InitPlan) and used as the index bound
SELECT count(*) FROM t_uuidv7 WHERE id >= (SELECT uuidv7(interval '-30 seconds'));
-- Parallel Index Only Scan using t_uuidv7_pkey, Index Cond: (id >= (InitPlan 1).col1), ~42 ms

Both queries returned the correct count. The fix only changes the plan. The boundary is approximate within a millisecond, because the bound has random bits after the timestamp, so keep a real created_at column for exact cut-offs and reports. ORDER BY id DESC on a UUIDv4 table also runs fast, but its "latest" rows are random ones.

Is UUIDv7 safe to expose in URLs?#

Only if creation time is not sensitive. Anyone can decode the timestamp from a UUIDv7: uuid_extract_timestamp() returned 2026-10-17 20:32:57.748+05 for a row whose created_at was 10 ms earlier. RFC 9562 says "if UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 SHOULD be utilized."

Practical rules:

• Fine as UUIDv7: orders, posts, messages, events, internal IDs, anything you would happily sort by date.
• Use UUIDv4 or a random token: password reset tokens, invite links, API keys, session IDs, and records where signup time is private (for example, user IDs in a service where account age matters).
• Never rely on any UUID for authorization. Unguessable is not the same as access control.

UUIDv7 vs bigint vs ULID: which should I use?#

Use bigint identity when IDs are created only by one database and never leave it: smallest index, fastest joins. Use UUIDv7 when clients, services or offline devices create IDs, or when you merge data across databases. ULID gives similar time-ordered properties, but it needs a text column or a conversion, while UUIDv7 fits the native 16-byte uuid type.

Before relying on uuidv7(), run SELECT version(); on every environment, including CI and local Docker containers, since a server still on 17 will reject it. If you self-host Postgres next to your app as in my Coolify self-hosting guide, the major version is set by the database image you choose.

Common UUIDv7 mistakes#

• Storing UUIDs as text or varchar(36). Use the uuid type: 16 bytes, faster comparisons.
• Calling uuidv7(interval ...) directly in WHERE. Wrap it in (SELECT ...) or compute the bound in the app, or the index is skipped.
• Using Prisma uuid(7) when other writers exist. It sets no database default. Use dbgenerated("uuidv7()").
• Expecting uuidv7() on PostgreSQL 17. It does not exist there. Generate in the app or upgrade to 18.
• Exposing UUIDv7 where timing is private. Its first 48 bits are the creation time.
• Dropping created_at. The UUID timestamp is approximate and set by whoever generated the ID. Keep a real column for reporting.

Key takeaways#

• UUIDv7 starts with a 48-bit millisecond Unix timestamp, so IDs sort by creation time; PostgreSQL 18 generates it with uuidv7().
• In my 10M-row test, UUIDv7 loaded as fast as bigint (37.3 s) while UUIDv4 took 84.2 s and wrote 2.2x more WAL.
• UUIDv4's primary key index ended 28% larger than UUIDv7's, with 70% leaf density and 50% fragmentation.
• With 8 concurrent clients, UUIDv4 inserted about 15% fewer rows per second than UUIDv7.
• Wrap uuidv7(interval ...) in a scalar subquery for time-range filters; called directly, it forced a full table scan.
• Prisma's uuid(7) generates IDs in the client with no database default; dbgenerated("uuidv7()") puts the default in Postgres.
• UUIDv7 reveals creation time, so keep UUIDv4 or random tokens for secrets and private timestamps.