6 October 2026 · postgresql

UUIDs stored as text: what it costs in PostgreSQL 18

Last week I compared UUIDv4 and UUIDv7 as primary keys. There is a third case I left out: the key is a UUID, but the column is text. Some ORMs and JSON-first codebases end up there because a string is the easy path.

So I kept the same test and changed only the column type. 5 million rows, UUIDv7 and UUIDv4, each stored as uuid and as text. Short answer: the text index was 88% bigger, one level deeper, and every random lookup read one more page.

Setup

Same as last time. One table per run:

CREATE TABLE t (id <key> PRIMARY KEY, payload text NOT NULL);

The key was one of:

• uuid DEFAULT uuidv7()
• text DEFAULT uuidv7()::text
• uuid DEFAULT gen_random_uuid()
• text DEFAULT gen_random_uuid()::text

5,000,000 rows in 50 committed batches of 100,000, then VACUUM ANALYZE, a checkpoint, and two read queries. Three runs each on a fresh table, medians below. Machine: 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM, SSD. PostgreSQL 18.6 with OpenSSL (I checked this time), default settings, database collation C.

Results

Buffers are shared hit plus read from the top plan node of EXPLAIN (ANALYZE, BUFFERS). Tree level is tree_level from pgstatindex.

Where the difference comes from

A uuid is 16 bytes. The same value as text is 36 characters plus a length byte: 37 bytes. Every index entry carries the key, so the text index holds more than twice the key data. It came out 88% bigger with v7 and 89% bigger with v4.

At 5 million rows, that was enough to add a level to the B-tree. The uuid index had a tree level of 2, the text index 3. A lookup walks from the root to a leaf, one page per level, so every lookup reads one more page. 10,000 lookups read about 10,400 more buffers, a little over one extra page each.

The table grew too, by 79 MB, because the heap stores the longer key as well.

What surprised me

Text made the bad choice worse, not just bigger. With v7 keys, text cost 6 seconds on insert. With v4 keys, it cost 33. My reading: random inserts into a bigger index split more pages and write more full-page images to WAL. The numbers point that way: 540 MB more WAL for v4, against 247 MB more for v7.

The cost I measured was size, not comparisons. I expected text comparisons to be the cost. Here the collation is C, which compares bytes, so comparisons are cheap. The cost I could measure was size: more pages, one more level.

What I did not test

• Other collations. This database uses C. Most servers use something like en_US.UTF-8, where text comparison goes through the locale and costs more. I could not reproduce that faithfully on macOS, so the text numbers here are a best case.
• Joins on text keys, and foreign keys referencing them. Every child table would carry the 37-byte value too.
• Tables larger than RAM. The whole data set fit in the OS page cache.
• The smaller sizes where both indexes have the same tree level. The extra page per lookup only appears once the text index is big enough to grow a level.
• Other hardware. Compare the ratios, not the seconds.

If your keys are already text, changing the column to uuid is an ALTER TABLE ... TYPE uuid USING id::uuid, which rewrites the table and its indexes and takes a strong lock while it runs. I did not measure that here.

Reproduce it

The script and every raw number: run.py, results.json. It expects a PostgreSQL 18 server and the pgstattuple extension.