Why UUIDs Can Hurt PostgreSQL Performance and When to Use Them

Introduction

UUID (Universally Unique Identifier) is widely known. This article revisits its advantages and disadvantages in PostgreSQL from a DBA perspective.

Analysis

PostgreSQL supports UUID generation via the uuid-ossp or pgcrypto extensions in older versions, and natively via gen_random_uuid() (v4) from version 13 onward. The author lists three main drawbacks of using UUID as a primary key: WAL bloat, index bloat, and performance loss.

WAL Bloat

Because UUID values are unordered, indexes on UUID columns cause frequent page splits and updates, which increase the amount of Write‑Ahead Log (WAL) generated. In contrast, sequential integers produce sequential page changes, resulting in smaller WAL.

Index Bloat

Unordered UUIDs cause leaf nodes of B‑tree indexes to split and merge frequently, creating empty space and fragmentation. The fragmentation can be measured with pgstattuple.leaf_fragmentation. The article references a previous post on index fragmentation.

Performance Loss

UUIDs occupy 16 bytes versus 8 bytes for a BIGINT sequence, and generating them is more CPU‑intensive. Benchmarks show that a simple SELECT nextval('test_seq') loop runs in about 9.5 s, while generating the same number of UUIDs with gen_random_uuid() takes about 27.8 s. Additional tests on a 10‑million‑row table reveal that index‑only scans on a UUID column consume far more shared buffers and execution time than on a sequential column.

# create sequence test_seq;
CREATE SEQUENCE test_seq;
# explain analyze select nextval('test_seq') from generate_series(1,10e6);
... (output omitted) ...
# explain analyze select gen_random_uuid() from generate_series(1,10e6);
... (output omitted) ...

Conclusion

The author emphasizes a balanced view: while UUIDs provide security benefits and avoid sequence gaps, they can cause WAL and index bloat and degrade performance, especially on high‑throughput workloads. In sharded environments, sequences also have limitations, making ordered UUIDs (e.g., UUID v6/v7 or sequential‑UUID libraries) a viable alternative.

References for ordered UUID implementations include https://github.com/tvondra/sequential-uuids and https://github.com/fboulnois/pg_uuidv7.