PGConf.dev 2025 — UUID v7 in PostgreSQL

PGConf.dev is the annual international PostgreSQL developer conference, drawing core contributors, extension authors, and enterprise PostgreSQL users. The 2025 edition included dedicated sessions on the UUID v7 integration landing in PostgreSQL 18, covering the implementation of uuidv7(), the decision to add uuid_extract_timestamp(), and measured B-tree index performance data comparing UUID v4, UUID v7, and BIGSERIAL primary keys at scale. This page summarises the key technical points for developers planning migrations.

The PostgreSQL 18 UUID v7 addition

The core patch adding uuidv7() to PostgreSQL 18 was discussed in depth. Key design decisions covered at the conference:

• Why uuidv7() rather than a single gen_uuid(version := 7) function — The explicit naming follows PostgreSQL’s convention of separate, clearly named functions rather than version-parameterised variants that are harder to optimise.
• uuid_extract_timestamp() design — Returns a timestamptz from UUID v7 values, NULL for UUID v4/other versions. Allows direct filtering: WHERE uuid_extract_timestamp(id) > '2026-01-01' — though a dedicated timestamp column is usually faster for range queries on large tables.
• uuidv4() as an alias for gen_random_uuid() — Added for naming symmetry. The underlying implementation is identical.

The full function reference is in the PostgreSQL 18 UUID functions documentation.

Index performance data

Conference presentations included B-tree benchmark data comparing three primary key strategies on a standard PostgreSQL 18 instance with 8 GB RAM and SSD storage:

UUID v7 insert performance is within 15% of BIGSERIAL. UUID v4 degrades significantly at 10M rows due to index fragmentation — a well-understood consequence of random inserts into a B-tree documented in the PostgreSQL B-tree implementation guide.

Extension path for PostgreSQL 14–17

For installations that cannot upgrade to PostgreSQL 18 immediately, the pg_uuidv7 extension provides the same uuidv7() function. Install via:

CREATE EXTENSION pg_uuidv7;

-- Use as a column default
CREATE TABLE orders (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz NOT NULL DEFAULT now()
);

The extension was presented as a migration bridge — the function signature is identical to PostgreSQL 18’s native implementation, so removing the extension and relying on the built-in function after upgrading requires no application changes.

Migration from SERIAL to UUID v7

A full session covered the migration pattern used by a PostgreSQL-backed SaaS company migrating from BIGSERIAL primary keys to UUID v7:

-- Step 1: Add UUID v7 column alongside existing integer PK
ALTER TABLE orders ADD COLUMN uuid_id uuid DEFAULT uuidv7() NOT NULL;

-- Step 2: Backfill existing rows (generate UUIDs with synthetic timestamps)
-- (Done in batches to avoid lock contention)
UPDATE orders SET uuid_id = uuidv7() WHERE uuid_id IS NULL;

-- Step 3: Add unique constraint, verify FK references
ALTER TABLE orders ADD CONSTRAINT orders_uuid_id_key UNIQUE (uuid_id);

-- Step 4: Swap application to use uuid_id as PK, drop integer PK
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_uuid_id_key;

The PostgreSQL ALTER TABLE documentation covers constraint management during live migrations.

UUID v7 with BRIN indexes

An alternative to B-tree for time-range queries on append-heavy tables: BRIN (Block Range Index) indexes work naturally with UUID v7 because the values are time-ordered and therefore spatially correlated with physical storage location. A BRIN index on a UUID v7 primary key column can support efficient WHERE id > $threshold queries with a tiny index footprint — appropriate for large event log tables. The PostgreSQL BRIN index documentation covers the spatial correlation requirement.

Further reading

• UUID v7 Performance Benchmarks — full insert throughput data
• UUID in Databases — storage types for PostgreSQL, MySQL, SQLite
• UUID v4 vs UUID v7 — when each version is the right choice

External references

• PostgreSQL 18 UUID functions
• pg_uuidv7 extension on GitHub
• PostgreSQL B-tree implementation
• RFC 9562 — UUID standard
• PGConf.dev official site