PostgreSQL 18 Native uuidv7() Time-Ordered Primary Keys

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 46 lines · 4 views

Artifact

Verified Blueprint PostgreSQL 18

Eliminate B-tree page splits and WAL bloat in PostgreSQL 18 using native DEFAULT uuidv7() primary keys and timestamp extraction.

✓ Uses built-in PostgreSQL 18 uuidv7() without pgcrypto or third-party C extensions
✓ Keeps B-tree right-leaning append locality like BIGSERIAL while preserving globally unique 128-bit IDs
✓ Extracts embedded millisecond creation timestamps via uuid_extract_timestamp() without extra index columns

Quick Fetch

-- PostgreSQL 18+ Native Time-Ordered UUIDv7 Primary Key Blueprint
-- Solves random UUIDv4 B-tree index fragmentation, buffer churn, and WAL write amplification

-- 1. Core Table Using Built-In uuidv7() Default (RFC 9562)
CREATE TABLE IF NOT EXISTS tenant_events (
    id           uuid PRIMARY KEY DEFAULT uuidv7(),
    tenant_id    uuid NOT NULL,
    event_type   text NOT NULL,
    payload      jsonb NOT NULL DEFAULT '{}'::jsonb,
    -- Optional: PG 18 virtual generated column (zero disk overhead) exposing embedded timestamp
    recorded_at  timestamptz GENERATED ALWAYS AS (uuid_extract_timestamp(id)) VIRTUAL
);

-- 2. Composite Index Leveraging Monotonic ID Ordering
-- Prefix by tenant_id; trailing id is naturally chronological (no separate created_at needed)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tenant_events_tenant_id_desc
    ON tenant_events (tenant_id, id DESC);

-- 3. Backfill / Historical Seed Helper Using uuidv7(interval_offset)
-- PostgreSQL 18 uuidv7() accepts an optional interval shift for backdated event imports:
INSERT INTO tenant_events (id, tenant_id, event_type, payload)
VALUES (
    uuidv7('-1 hour'::interval),
    '0195f3a0-7c00-7000-8000-000000000001'::uuid,
    'invoice.paid',
    '{"amount_cents": 4900, "currency": "USD"}'::jsonb
);

-- 4. Keyset (Cursor) Pagination Without OFFSET Slowdowns
-- Uses monotonic UUIDv7 ordering to fetch the next 50 events in < 1ms
SELECT
    id,
    uuid_extract_timestamp(id) AS occurred_at,
    uuid_extract_version(id)   AS uuid_ver,
    event_type,
    payload
FROM tenant_events
WHERE tenant_id = '0195f3a0-7c00-7000-8000-000000000001'::uuid
  AND id < '0195f3e8-0000-7000-8000-ffffffffffff'::uuid
ORDER BY id DESC
LIMIT 50;

-- 5. Inspect Index Leaf Density (Compare ~90% fill on UUIDv7 vs ~50-65% on UUIDv4)
-- CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('tenant_events_pkey');

1-- PostgreSQL 18+ Native Time-Ordered UUIDv7 Primary Key Blueprint

2-- Solves random UUIDv4 B-tree index fragmentation, buffer churn, and WAL write amplification

4-- 1. Core Table Using Built-In uuidv7() Default (RFC 9562)

5CREATE TABLE IF NOT EXISTS tenant_events (

6 id uuid PRIMARY KEY DEFAULT uuidv7(),

7 tenant_id uuid NOT NULL,

8 event_type text NOT NULL,

9 payload jsonb NOT NULL DEFAULT '{}'::jsonb,

10 -- Optional: PG 18 virtual generated column (zero disk overhead) exposing embedded timestamp

11 recorded_at timestamptz GENERATED ALWAYS AS (uuid_extract_timestamp(id)) VIRTUAL

12);

14-- 2. Composite Index Leveraging Monotonic ID Ordering

15-- Prefix by tenant_id; trailing id is naturally chronological (no separate created_at needed)

16CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tenant_events_tenant_id_desc

17 ON tenant_events (tenant_id, id DESC);

19-- 3. Backfill / Historical Seed Helper Using uuidv7(interval_offset)

20-- PostgreSQL 18 uuidv7() accepts an optional interval shift for backdated event imports:

21INSERT INTO tenant_events (id, tenant_id, event_type, payload)

22VALUES (

23 uuidv7('-1 hour'::interval),

24 '0195f3a0-7c00-7000-8000-000000000001'::uuid,

25 'invoice.paid',

26 '{"amount_cents": 4900, "currency": "USD"}'::jsonb

27);

29-- 4. Keyset (Cursor) Pagination Without OFFSET Slowdowns

30-- Uses monotonic UUIDv7 ordering to fetch the next 50 events in < 1ms

31SELECT

32 id,

33 uuid_extract_timestamp(id) AS occurred_at,

34 uuid_extract_version(id) AS uuid_ver,

35 event_type,

36 payload

37FROM tenant_events

38WHERE tenant_id = '0195f3a0-7c00-7000-8000-000000000001'::uuid

39 AND id < '0195f3e8-0000-7000-8000-ffffffffffff'::uuid

40ORDER BY id DESC

41LIMIT 50;

43-- 5. Inspect Index Leaf Density (Compare ~90% fill on UUIDv7 vs ~50-65% on UUIDv4)

44-- CREATE EXTENSION IF NOT EXISTS pgstattuple;

45-- SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('tenant_events_pkey');

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

PostgreSQL 18 Native uuidv7() Time-Ordered Primary Keys

Replies 0

Related PostgreSQL 18 & Database, Cache & SQL Blueprints

Live PostgreSQL Lock Tree (pg_blocking_pids) & Safe Killer

Production PgBouncer Transaction Connection Pooling Config

PostgreSQL Slow Query Logging & auto_explain Configuration

PostgreSQL Logical Replication & CDC WAL Settings