PostgreSQL UUID vs Serial vs ULID in 2026: Ordering, Indexing, and Sharding Trade-offs

The choice of primary key type affects insert performance, index health, cursor-based pagination, distributed system compatibility, and information exposure in URLs. It's not a religious debate — the trade-offs are concrete and measurable.

This post compares the four main options in 2026: SERIAL/BIGSERIAL, UUID v4 (gen_random_uuid()), UUID v7 (sortable, new in PostgreSQL 17), and ULID — across the dimensions that matter for production systems.

---

The Four Contenders

-- Option 1: SERIAL (auto-increment integer)
CREATE TABLE orders_serial (
  id    SERIAL PRIMARY KEY,
  total NUMERIC
);
-- Inserts: 1, 2, 3, 4... predictable, sequential

-- Option 2: BIGSERIAL (64-bit auto-increment)
CREATE TABLE orders_bigserial (
  id    BIGSERIAL PRIMARY KEY,
  total NUMERIC
);
-- Same as SERIAL but supports 9.2 × 10^18 values (no overflow risk)

-- Option 3: UUID v4 (random)
CREATE TABLE orders_uuid4 (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  total NUMERIC
);
-- Inserts: 7a3f1b2c-..., random, unordered

-- Option 4: UUID v7 (time-ordered) — PostgreSQL 17+
CREATE TABLE orders_uuid7 (
  id    UUID PRIMARY KEY DEFAULT uuidv7(),
  total NUMERIC
);
-- Inserts: 018e4d2a-..., ordered by time prefix

-- Option 5: ULID (via pgulid extension)
-- Similar to UUID v7: time-prefix + random suffix, 26-char base32
CREATE EXTENSION IF NOT EXISTS pgulid;
CREATE TABLE orders_ulid (
  id    TEXT PRIMARY KEY DEFAULT gen_ulid(),
  total NUMERIC
);

---

B-Tree Index Fragmentation: The UUID v4 Problem

-- Measure index bloat after 1M inserts

-- With SERIAL (sequential inserts):
-- B-tree pages are filled in order → ~100% page fill, minimal fragmentation
-- Index size for 1M rows: ~22MB

-- With UUID v4 (random inserts):
-- Random inserts cause page splits throughout the tree
-- ~50% page fill on average after heavy insert load
-- Index size for 1M rows: ~35-55MB (50-150% larger than serial)

-- Check actual index fragmentation:
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'orders_uuid4'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Measure bloat:
SELECT
  pg_size_pretty(pg_table_size('orders_uuid4')) AS table_size,
  pg_size_pretty(pg_indexes_size('orders_uuid4')) AS index_size,
  pg_size_pretty(pg_total_relation_size('orders_uuid4')) AS total_size;

Real numbers (1M row table, measured):

UUID v4's random insert pattern causes frequent page splits and poor cache utilization — 63% lower throughput and 2× index size vs serial. UUID v7 and ULID solve this with time-ordered prefixes.

---

UUID v7 in PostgreSQL 17

PostgreSQL 17 (released Sept 2024) adds native uuidv7():

-- PostgreSQL 17+ (use uuidv7() built-in)
SELECT uuidv7();
-- 018e4d2a-6b5c-7000-8000-abcdef123456
-- └─ 48-bit ms timestamp ─┘ ver   rand

-- The first 48 bits are millisecond timestamp
-- → Inserts are time-ordered at ms precision
-- → B-tree pages fill sequentially like SERIAL

-- Extract timestamp from UUID v7:
SELECT
  id,
  to_timestamp(
    ((('x' || replace(id::text, '-', ''))::bit(128)::bigint >> 80) & ((1::bigint << 48) - 1))
    / 1000.0
  ) AS created_at
FROM orders_uuid7
LIMIT 5;

For PostgreSQL 16 and earlier, use the pg_uuidv7 extension:

-- PostgreSQL 16 and earlier
CREATE EXTENSION pg_uuidv7;
SELECT uuid_generate_v7();

---

ULID: Sortable + URL-Safe

ULID (Universally Unique Lexicographically Sortable Identifier) uses base32 encoding, making it shorter and URL-friendly compared to UUID:

UUID v4:  7a3f1b2c-4d5e-6f7a-8b9c-0d1e2f3a4b5c  (36 chars, not sortable)
UUID v7:  018e4d2a-6b5c-7000-8000-abcdef123456   (36 chars, sortable by time)
ULID:     01HRTZ6XYPRQRQ5P4K7QRXM5YS             (26 chars, sortable, URL-safe)

// Generate ULIDs in Node.js
import { ulid } from "ulid";

const id = ulid(); // "01HRTZ6XYPRQRQ5P4K7QRXM5YS"

// Extract timestamp from ULID
import { decodeTime } from "ulid";
const timestamp = decodeTime(id); // Unix ms timestamp

-- Store ULID as TEXT in PostgreSQL
CREATE TABLE events (
  id          TEXT PRIMARY KEY DEFAULT gen_ulid(), -- Requires pgulid extension
  workspace_id UUID NOT NULL,
  payload     JSONB
);

-- Or generate in application layer and insert as TEXT
INSERT INTO events (id, workspace_id, payload)
VALUES ('01HRTZ6XYPRQRQ5P4K7QRXM5YS', $1, $2);

---

URL Exposure: Serial vs UUID

❌ Serial IDs in URLs expose business information:
  GET /api/orders/1          → "You're customer #1, we have few customers"
  GET /api/orders/1000       → "~1000 orders in database"
  GET /api/orders/1001       → Enumeration attack: try 1, 2, 3...
  GET /api/invoices/524      → "Invoice #524 exists for this org"

✅ UUID/ULID in URLs are opaque:
  GET /api/orders/018e4d2a-6b5c-7000-8000-abcdef123456
  → No business information, not enumerable

This matters even with auth — it prevents reconnaissance and avoids IDOR vulnerabilities in misconfigured endpoints.

---

Cursor-Based Pagination

All four ID types support cursor pagination, but the experience differs:

-- SERIAL: perfect ordering guarantee
SELECT * FROM orders
WHERE id > $cursor
ORDER BY id ASC
LIMIT 20;

-- UUID v4: NO natural ordering — must use created_at as cursor
-- (UUIDs sort lexicographically, not by insertion time)
SELECT * FROM orders
WHERE (created_at, id) > ($cursor_time, $cursor_id)
ORDER BY created_at ASC, id ASC
LIMIT 20;
-- Requires composite index: (created_at, id)

-- UUID v7 / ULID: natural ordering by time prefix — use ID as cursor
SELECT * FROM orders
WHERE id > $cursor
ORDER BY id ASC
LIMIT 20;
-- Single-column cursor works correctly

---

Sharding Compatibility

SERIAL/BIGSERIAL:
  ❌ Requires global coordination for sequence (or per-shard sequences with gaps)
  ❌ Shard merges cause ID conflicts without remapping
  ✅ Works fine for single-server PostgreSQL

UUID v4/v7/ULID:
  ✅ Generated independently on any node — no coordination needed
  ✅ Global uniqueness guaranteed by design
  ✅ Merge data from multiple shards without ID conflicts

For multi-region or sharded PostgreSQL (Citus, PlanetScale-style):

-- UUID v7: generate on application server (no DB round trip)
-- Each app server generates IDs independently
-- IDs are globally unique and sortable

---

Migrating SERIAL → UUID

-- 1. Add UUID column (nullable first)
ALTER TABLE orders ADD COLUMN uuid_id UUID DEFAULT gen_random_uuid();

-- 2. Backfill (all existing rows get a random UUID)
UPDATE orders SET uuid_id = gen_random_uuid() WHERE uuid_id IS NULL;

-- 3. Add NOT NULL
ALTER TABLE orders ALTER COLUMN uuid_id SET NOT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_uuid_id_unique UNIQUE (uuid_id);

-- 4. Update foreign key references (other tables that reference orders.id)
-- Add uuid_order_id columns in those tables, backfill via JOIN
ALTER TABLE order_items ADD COLUMN uuid_order_id UUID;
UPDATE order_items oi
SET uuid_order_id = o.uuid_id
FROM orders o
WHERE oi.order_id = o.id;

-- 5. (After app is updated to use uuid_id as primary identifier)
-- Promote uuid_id to primary key in a later migration

---

Recommendation Decision Tree

Starting a new project?
  → UUID v7 (PostgreSQL 17+) or ULID
  → Sortable + globally unique + no enumeration

Existing project with SERIAL?
  → Keep SERIAL for internal PKs
  → Add UUID/ULID as public_id column for URLs
  → Never expose serial ID in URLs

High-throughput insert workload (>50K inserts/sec)?
  → UUID v7 or ULID (avoid UUID v4 fragmentation)

Multi-tenant SaaS with eventual sharding?
  → UUID v7 or ULID (no coordination needed)

Simple CRUD app, small team, PostgreSQL only?
  → BIGSERIAL is fine — simplest option

---

TypeScript: Type-Safe IDs

// Branded types prevent mixing up ID types across entities
declare const __brand: unique symbol;
type Brand<T, B> = T & { [__brand]: B };

type UserId    = Brand<string, "UserId">;
type OrderId   = Brand<string, "OrderId">;
type WorkspaceId = Brand<string, "WorkspaceId">;

// Compiler catches this at compile time:
function getOrder(orderId: OrderId) { /* ... */ }
const userId = "018e4d2a-..." as UserId;
getOrder(userId); // ❌ TypeScript error: UserId is not assignable to OrderId

// Create typed IDs
import { ulid } from "ulid";

export function newUserId(): UserId       { return ulid() as UserId; }
export function newOrderId(): OrderId     { return ulid() as OrderId; }
export function newWorkspaceId(): WorkspaceId { return ulid() as WorkspaceId; }

---

Cost and Timeline

---

See Also

• PostgreSQL Schema Migrations — Zero-downtime migration of ID columns
• PostgreSQL Row-Level Security — Using UUID as tenant identifier
• SaaS Multi-Workspace — UUID-based workspace routing
• TypeScript Utility Types — Branded types and other type patterns

---

Working With Viprasol

We design PostgreSQL schemas with the right ID strategy for your workload — from single-server BIGSERIAL through globally-distributed ULID. Our team has migrated production systems from SERIAL to UUID without downtime and advises on sharding-ready ID design.

What we deliver:

• ID type audit and recommendation for your specific workload
• Zero-downtime migration from SERIAL to UUID/ULID
• Cursor-based pagination implementation for any ID type
• Branded TypeScript types for compile-time ID safety
• Index health analysis after UUID migration

Explore our web development services or contact us to design your database ID strategy.