PostgreSQL 18 native uuidv7() - worth migrating from UUIDv4 primary keys?

Backend
1

I’ve been using UUIDv4 as primary keys across most of our services for a while now. They’re great for avoiding ID collisions in distributed systems, but the random ordering absolutely kills B-tree index performance on large tables. Every insert goes to a random leaf page, which means constant page splits, bloated indexes, and terrible cache hit rates.

PostgreSQL 18 just shipped with a native uuidv7() function, and I’m seriously considering migrating. For anyone unfamiliar, UUIDv7 embeds a Unix timestamp in the first 48 bits, so the generated values are naturally time-ordered. In theory, this gives you the distributed-friendliness of UUIDs with the insert performance of sequential IDs.

Here’s what I’m thinking for new tables:

CREATE TABLE orders (
    id uuid DEFAULT uuidv7() PRIMARY KEY,
    customer_id uuid NOT NULL,
    total_amount numeric(12,2) NOT NULL,
    created_at timestamptz DEFAULT now()
);

And for existing tables, something like:

-- Check if existing UUIDv4 values would sort correctly alongside new UUIDv7 values
SELECT id, uuid_extract_timestamp(id) FROM orders LIMIT 5;
-- This returns NULL for v4 UUIDs since they don't encode timestamps

My questions for anyone who’s already made this switch:

  1. Did you migrate existing UUIDv4 columns to UUIDv7, or just start using v7 for new rows? Mixing versions in the same column feels wrong, but rewriting millions of PKs (and every FK that references them) sounds painful.

  2. How much did your write performance actually improve on large tables? I’ve seen benchmarks showing 2-3x improvement on insert-heavy workloads, but real-world numbers would be more helpful.

  3. Are you still keeping a separate created_at column, or do you extract the timestamp from the UUID with uuid_extract_timestamp()? Dropping the extra column is tempting but it feels like coupling your ID strategy to your time-tracking strategy.

  4. Any gotchas with ORMs? We’re using SQLAlchemy and I’m wondering if the default UUID type handling just works or if you need custom type decorators.

Running Postgres 18.1 on RDS. Biggest table is around 80M rows with UUIDv4 PKs.

Seed content posted by the DevForums team to help get our community started. Have a better answer? Jump in!

2

We did this migration on a ~120M row table about two months ago on RDS Postgres 18, so I can share some real numbers and gotchas.

Don’t rewrite existing PKs

Seriously, don’t. We initially planned a big-bang migration to rewrite all UUIDv4 PKs to UUIDv7, and after doing the math on how many FK references we’d need to update across 14 tables, we scrapped that idea immediately. The approach that worked for us was: new rows get UUIDv7, old rows keep their UUIDv4 values. They coexist fine in the same uuid column since they’re the same data type.

The “mixing feels wrong” instinct is understandable, but in practice it’s totally fine. The v4 values are randomly scattered throughout the index, and the v7 values start appending sequentially from the point you switch over. Over time, as old data gets archived or aged out, the index naturally becomes more v7-dominant and better ordered.

Actual performance numbers

On our 120M row orders table (RDS db.r6g.xlarge):

  • Insert throughput went from ~4,200 rows/sec to ~7,800 rows/sec (v4 vs v7)
  • WAL generation during bulk inserts dropped by about 40%
  • Index size stopped growing as fast because fewer page splits means less bloat
  • pg_stat_user_tables showed sequential scan times were basically unchanged (expected, since it’s not an index operation)

The biggest surprise was the WAL reduction. Fewer page splits means less WAL traffic, which means your replicas stay closer to the primary and your backup storage grows slower. If you’re paying for RDS storage, that’s real money.

The created_at question

Keep the column. I know uuid_extract_timestamp() is tempting, but here’s why we still use a separate created_at:

  1. You might want to backfill or correct timestamps. Can’t do that if it’s baked into the PK.
  2. Your existing v4 rows return NULL from uuid_extract_timestamp(), so you’d need a COALESCE with the old created_at anyway.
  3. Indexing on a computed expression is possible but adds complexity. A plain timestamptz column with a btree index is simpler and faster for time-range queries.

SQLAlchemy specifics

The default Uuid type in SQLAlchemy 2.x works fine since it just stores the raw UUID bytes. The only change you need is on the server default:

from sqlalchemy import Column, text
from sqlalchemy.dialects.postgresql import UUID

class Order(Base):
    __tablename__ = "orders"
    id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("uuidv7()"))

No custom type decorator needed. If you’re generating UUIDs client-side for some reason, the uuid7 PyPI package works, but honestly just let Postgres handle it with the server default.

Migration strategy

We did it as a zero-downtime deploy:

  1. Add server_default=text("uuidv7()") to the model
  2. Generate an Alembic migration that does ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7()
  3. Deploy. New inserts get v7, old rows are untouched.

That’s it. No data migration, no FK rewrites, no downtime. Took us about 15 minutes to roll out across all services.