I loaded 10 million rows into PostgreSQL 18 tables keyed by bigint, UUIDv4 and UUIDv7 and compared load time, WAL, index size and insert throughput. The original, with any updates, lives on fabwaseem.com.
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.
bigint identity |
UUIDv4 | UUIDv7 | |
|---|---|---|---|
| Size | 8 bytes | 16 bytes | 16 bytes |
| Generated by | One sequence in the database | Anyone, anywhere | Anyone, anywhere |
| Sort order | Insert order | Random | Creation time (ms) |
| Reveals | Row count, insert order | Nothing | Creation timestamp |
| 10M-row load (my test) | 37.3 s | 84.2 s | 37.3 s |
| PK index after load | 214 MB | 385 MB | 301 MB |
| Postgres built-in | Always |
gen_random_uuid() / uuidv4()
|
uuidv7() since 18 |
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().
```sql title="PostgreSQL 18.6"
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](https://www.postgresql.org/about/news/postgresql-18-released-3142/) 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](https://www.postgresql.org/docs/18/pgstattuple.html)'s `pgstatindex()`.
- Concurrent inserts: `pgbench`, 8 clients, 60 seconds of single-row `INSERT`s into the preloaded tables, 2 rounds. The table shows the average of the two rounds.
```sql title="The three tables"
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
| Metric | bigint |
UUIDv4 | UUIDv7 |
|---|---|---|---|
| 10M-row bulk load | 37.3 s | 84.2 s | 37.3 s |
| WAL written by the load | 1,964 MB | 4,598 MB | 2,098 MB |
| Table (heap) size | 1,116 MB | 1,202 MB | 1,202 MB |
| Primary key index size | 214 MB | 385 MB | 301 MB |
| Index leaf density | 90.0% | 70.3% | 90.0% |
| Index leaf fragmentation | 0.0% | 49.8% | 0.0% |
| Single-row inserts, 8 clients | 49,528/s | 41,331/s | 48,690/s |
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.
```sql title="New table"
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
total_cents integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
```sql title="Existing table: new rows get UUIDv7"
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.
```prisma title="prisma/schema.prisma (Prisma 7.10.1)"
// 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](https://www.prisma.io/docs/orm/reference/prisma-schema-reference) documents both.
```ts title="schema.ts (Drizzle 0.45.4)"
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.
```sql title="Rows from the last 30 seconds"
-- 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.
| Need | Pick |
| --- | --- |
| Internal IDs, one database, maximum speed | `bigint GENERATED ALWAYS AS IDENTITY` |
| IDs generated in the app, mobile or other services | UUIDv7 |
| Merging rows from several databases or regions | UUIDv7 |
| IDs that must not reveal creation time | UUIDv4 |
| Secrets: tokens, reset links, API keys | Random token (UUIDv4 or longer) |
| Existing ULID system | Keep ULID, or store it as `uuid` (both are 128 bits) |
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](https://www.fabwaseem.com/blog/self-host-nextjs-with-coolify), 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.
## FAQ
### What is UUIDv7?
UUIDv7 is the time-ordered UUID version from RFC 9562, published in May 2024. Its first 48 bits are a Unix timestamp in milliseconds, followed by version bits and random or counter bits. Because the timestamp comes first, UUIDv7 values sort by creation time, which keeps B-tree indexes compact when they are used as primary keys.
### Does PostgreSQL support UUIDv7?
Yes, since PostgreSQL 18, released on September 25, 2025. It added the uuidv7() function, an optional interval argument that shifts the timestamp, uuidv4() as an alias for gen_random_uuid(), and uuid_extract_timestamp() to read the time back. PostgreSQL 17 and older return an error that uuidv7() does not exist.
### Is UUIDv7 faster than UUIDv4 in Postgres?
For inserts, yes. In my PostgreSQL 18.6 test, a 10-million-row load took 37.3 seconds with UUIDv7 and 84.2 seconds with UUIDv4, and UUIDv4 wrote 2.2 times more WAL. With 8 clients inserting single rows, UUIDv4 managed about 15 percent fewer inserts per second. UUIDv7 stayed within 2 percent of bigint.
### Should I use UUIDv7 or bigint as a primary key?
Use bigint identity when one database creates every ID and the IDs never leave your system, because its index is smallest and joins are fastest. Use UUIDv7 when the app, mobile clients or other services create IDs, or when you merge data across databases. In my test UUIDv7's index was 41 percent larger than bigint's.
### How do I use UUIDv7 with Prisma?
Use @default(uuid(7)) to generate the ID in Prisma Client, or @default(dbgenerated("uuidv7()")) on PostgreSQL 18 to generate it in the database. Add @db.Uuid for the native type. The uuid(7) option sets no database default, so raw SQL inserts fail with a not-null error; dbgenerated works for every writer.
### How do I use UUIDv7 on PostgreSQL 17?
PostgreSQL 17 has no uuidv7() function, so generate UUIDv7 in your application and store it in a uuid column. Prisma's uuid(7) default and the npm uuid package's v7() both produce RFC 9562 version 7 values. When you upgrade to PostgreSQL 18, switch the column default to uuidv7() without rewriting old rows.
### Is it safe to expose UUIDv7 in URLs?
Only when creation time is not sensitive. Anyone can decode the first 48 bits, and uuid_extract_timestamp() returns the time in PostgreSQL. RFC 9562 says UUIDv4 should be used if UUIDs are involved in any security operation. Keep UUIDv4 or random tokens for reset links, invites, API keys and private user records.
### Can I query a time range using UUIDv7 ids?
Yes. Compare the key against uuidv7(interval '-1 hour'), but wrap the call in a scalar subquery such as id >= (SELECT uuidv7(interval '-1 hour')). Because uuidv7() is volatile, calling it directly in WHERE forced a full parallel scan of 15.8 million rows in my test, about 3.4 seconds instead of about 42 milliseconds.
---
*Originally published on [fabwaseem.com](https://www.fabwaseem.com/blog/uuidv7-postgres).*
Top comments (0)