Guides › Text & developer

UUID v4 or v7 as a database primary key? We inserted a million rows of each

UUIDs are popular primary keys because any server, phone or browser can create one without asking the database for the next number. The classic choice is version 4, which is almost entirely random; it is what crypto.randomUUID() and our UUID generator produce. Since RFC 9562 was published in 2024 there is an official alternative, version 7, whose first 48 bits are a timestamp. The usual claim is that v7 is "better for databases". We wanted to see how much better, so we measured it.

Short answer: inserting one million rows keyed by random v4 UUIDs took 4.2 seconds in our test, against 0.9 seconds for v7 stored as text and 0.8 seconds for a plain integer. The file sizes were nearly identical. The gap comes from where new keys land in the index, and it grows as the table outgrows memory.

What is different about v7

Both versions are 128 bits written as 32 hexadecimal digits. In v4, 122 of the bits are random. In v7, the first 48 bits hold the number of milliseconds since 1970, followed by the version and variant markers and 74 random bits. Two v7 UUIDs created a second apart therefore sort in creation order, while two v4 UUIDs sort in no meaningful order at all.

v4  3f0c8a1e-9b2d-4c55-a7e1-52d0e6b4f913   random throughout
v7  019a95c2-6f41-7b3e-9d20-8c51f0a7e244   019a95c2-6f41 = timestamp in ms

The timestamp has a side effect worth knowing: anyone who sees a v7 UUID can read roughly when it was created. A v4 UUID reveals nothing.

The test

We used SQLite 3.45 from Python on a Linux machine. For each key type we created a fresh table with the UUID as the primary key, using WITHOUT ROWID so that the table itself is stored in key order, the same "clustered" layout used by MySQL's InnoDB and SQL Server by default. We generated one million keys in advance, then inserted them in batches of 10,000 rows with a 20-character payload, and timed only the inserts. To imitate a table that is larger than the memory set aside for it, we limited SQLite's page cache to 2 MB. Journaling and syncing were switched off so that the measurement shows index work rather than disk flushes.

Results

Integer, INTEGER29 MBUUID v7, TEXT70 MBUUID v7, BLOB47 MBUUID v4, TEXT71 MBUUID v4, BLOB47 MB
Database size after 1,000,000 inserts — chart drawn from the table below.
Primary keyStored asInsert 1,000,000 rowsDatabase size
Integer (auto-increment)INTEGER0.8 s29 MB
UUID v7TEXT (36 chars)0.9 s70 MB
UUID v7BLOB (16 bytes)1.4 s47 MB
UUID v4TEXT (36 chars)4.2 s71 MB
UUID v4BLOB (16 bytes)4.0 s47 MB

v4 keys were about 4.7 times slower than v7 as text and about 3 times slower as binary. v7 text keys took 0.1 seconds longer than plain integers. Storing the UUID as 16 raw bytes instead of a 36-character string cut the database by a third for both versions.

A second run you can repeat

We ran the test again on a different machine and are publishing the script. This run used SQLite 3.53 and Python 3.13 on a four-core 2.1 GHz Linux machine, with the same settings as above. We ran each of the five combinations three times, each time into a fresh database; the table shows the median.

Primary keyStored asInsert 1,000,000 rows (median of 3)Range of the 3 runsDatabase size
IntegerINTEGER0.57 s0.57–0.69 s33 MB
UUID v7TEXT (36 chars)1.04 s0.90–1.25 s70 MB
UUID v7BLOB (16 bytes)1.01 s0.95–1.21 s47 MB
UUID v4TEXT (36 chars)4.31 s4.10–4.40 s70 MB
UUID v4BLOB (16 bytes)3.86 s3.83–3.87 s47 MB

To repeat it, download the script and run it with Python 3; it needs nothing beyond the standard library and takes about a minute. It uses whichever SQLite version your Python includes, so your times and sizes will differ somewhat.

Why random keys are slow

A primary key index is a sorted tree. When keys arrive in increasing order, as integers and v7 UUIDs do, every new row goes onto the right-most page of the tree. That page is always in memory, it fills up neatly, and the database moves on to a new one.

Random keys land anywhere. Each insert must find the right page somewhere in the middle of the tree, which may no longer be cached and must be read back, and when that page is full it has to be split in two, leaving two half-empty pages. With a 2 MB cache and a 50–70 MB table, most v4 inserts touched a page that was not in memory. On a server database the same effect shows up as more disk reads, more write-ahead log traffic and a larger, fragmented index. With a small table that fits entirely in memory, the difference shrinks a lot, which is why many teams never notice it.

When to choose which

SituationSuggested key
High insert rate, large tables, clustered storage (InnoDB, SQL Server, SQLite WITHOUT ROWID)UUID v7, or an integer if IDs never leave the database
IDs appear in public URLs and must not reveal creation timeUUID v4, or keep v7 internally and expose a separate random ID
Small tables, configuration, test fixturesEither; v4 is fine
Values generated offline on many devices and merged laterEither; both are practically collision-free
You need IDs that also work as unguessable tokensNeither. Use a dedicated random token of at least 128 bits

Practical notes

Common questions

Are v7 UUIDs less unique than v4?

They have fewer random bits (74 instead of 122), but the random part only needs to be unique within the same millisecond. Collisions remain vanishingly unlikely for any realistic system.

Can I tell v4 and v7 apart by looking?

Yes. The first digit of the third group is the version: 4 for random UUIDs, 7 for time-ordered ones.

Would results differ on PostgreSQL or MySQL?

The absolute numbers would, the direction would not. PostgreSQL stores table rows separately from the index, so the penalty is confined to the index, but random inserts still spread writes across it. Measure on your own data before deciding.

References

  1. RFC 9562: Universally Unique IDentifiers (UUIDs), IETF, 2024
  2. SQLite: Clustered indexes and the WITHOUT ROWID optimization
  3. PostgreSQL documentation: UUID functions
  4. MDN: Crypto.randomUUID()

Written and checked by Ria Kim for GlowHalo; figures without a source link come from the steps shown on this page. Companion tool: UUID Generator. Spotted an error? Write to help@glowhalo.org.

More guides

Text & developerIs your random picker really fair? Modulo bias, measured on 6.2 million drawsA common shortcut made eight characters 25% more likely than the rest in our test. How fair random picks are made, and how to check a tool.Read guide →Text & developerHow big should a printed QR code be? What data length and error correction do to itWe generated QR codes for five real payloads at all four error-correction levels and counted the modules. Shorter data matters more than anything else.Read guide →Text & developerUp 20%, down 20%, and you've lost money: five percentage traps, worked throughWe ran common percentage mistakes through our calculator: why +20% then -20% leaves 96, why 3% to 4% is a 33% rise, and how to remove tax from a price correctly.Read guide →