UUID vs BIGINT: The Real Impact on Your PostgreSQL Queries at 100 Million Rows

When BIGINT crushes UUID, and when UUID v7 saves the day.

The debate comes up with every new project: should you use a simple BIGSERIAL or a UUID for primary keys? Answers oscillate between "BIGINT is faster" and "UUID is essential for distributed systems." But at what volume does the difference actually become noticeable?

I wanted to settle this with concrete measurements on 100 million rows, running on an untuned PostgreSQL 16 instance, with an NVMe SSD and 32 GB of RAM. UUID v7 values were generated using the uuid npm package v9.0 (or uuid-utils v0.9 in Python). Here are the detailed results and the conclusions you should draw for your future schemas.

Decision Table — Which Strategy Fits Your Workload?

If you’re in a hurry, here’s the quick summary.

Reproducible Test Protocol (DDL Included)

Three main tables, each with 100 million rows, representing three primary key strategies.

-- Tables for PostgreSQL 16
CREATE TABLE events_bigint (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE events_uuid4 (
  id      UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE events_uuid7 (
  id      UUID PRIMARY KEY,  -- generated app‑side
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- FK table for join benchmarks
CREATE TABLE event_details_bigint (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_id BIGINT REFERENCES events_bigint(id),
  detail   TEXT
);
CREATE INDEX ON event_details_bigint(event_id);

-- After 100M inserts, measure index size
SELECT
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename IN ('events_bigint', 'events_uuid4', 'events_uuid7')
ORDER BY pg_relation_size(indexrelid) DESC;

Benchmark 1: Pure Inserts

100 million INSERTs in batches of 1,000 rows, executed with \copy from pre‑generated CSV files.

BIGINT is 3.3× faster than UUID v4 and about 1.4× faster than UUID v7. The absence of index fragmentation greatly benefits BIGINT, but UUID v7 performs very honorably because writes remain sequential.

Concurrency matters: With 16 parallel insertion workers, UUID v4 performance degrades sharply due to page split contention on the B‑tree index. In our test, UUID v4 took 215 minutes vs 58 minutes for UUID v7 — almost 4× slower. BIGINT remained stable at 42 minutes.

Benchmark 2: Foreign Key Joins

The event_details table references the events table via an FK. We run a simple join on 10 million rows (with an index on the FK).

Here, BIGINT wins by a wide margin: the FK index is half the size, and traversal is significantly less costly in I/O. UUID v4 and v7 are equivalent for reads: fragmentation does not impact joins; only the index size matters.

Benchmark 3: Range Queries on created_at

Typical query: SELECT * FROM events WHERE created_at BETWEEN '2026-01-01' AND '2026-01-02'. We measure the impact of PK ordering on clustering.

UUID v4 is dramatically bad: rows created on the same day are scattered across the entire disk, multiplying reads by 12. UUID v7 almost recovers BIGINT’s performance, because the PK order follows the created_at order. For temporal queries, a sorted PK acts as a quasi‑clustering index.

Benchmark 4: Point Lookups by PK

SELECT * FROM events WHERE id = '...' executed 100,000 times with random values.

The difference is negligible. A point lookup traverses a B‑tree of nearly identical height. Once the index is warm in cache, the gap shrinks to less than 0.1 ms. Don’t choose your PK based on this criterion.

Measuring Index Fragmentation

Use the pgstattuple extension to see the real internal state:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  indexrelid::regclass AS index_name,
  avg_leaf_density,
  avg_fragmentation_in_percent
FROM pgstatindex('events_uuid4_pkey');

-- Expected after 100M random inserts:
-- UUID v4: avg_fragmentation_in_percent ~85–99%
-- UUID v7: ~2–5%
-- BIGINT : ~0–1%

After a REINDEX TABLE CONCURRENTLY events_uuid4; the fragmentation drops to zero, but it quickly returns with new random inserts. This hidden maintenance cost is one more reason to prefer UUID v7 or BIGINT for write‑heavy tables.

The Real Cost: Foreign Key Storage Size

It’s not the PK that’s expensive: it’s every FK that references it. If you have 20 tables pointing to users.id, you multiply the gap by 20.

ScenarioVolume PK + 5 FKs (GB)All BIGINT (8 bytes)12.8All UUID (16 bytes)25.6Hybrid (internal BIGINT + exposed UUID)15.4

For 100M users and 5 related tables, the disk bill doubles with full UUID. The RAM used for caching follows the same proportion. If you’re on a tight cloud budget, BIGINT still has bright days ahead.

The Best Approach in 2026

UUID v7 massively narrows the gap with BIGINT for writes and temporal queries. But BIGINT remains king for joins and memory footprint.

The strategy I recommend today:

• Auto-increment BIGINT internally (fast joins, compact FKs).
• UUID v7 exposed publicly in your APIs (security, non‑enumerability).

This hybrid approach adds one column, but it gives you the best of both worlds. The overhead is minimal compared to the gains in security and flexibility.

Find our free tools for generating and comparing identifiers at DevToolbox.