PostgreSQL 18 Native uuidv7() Time-Ordered Primary Keys
-- 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');