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
| Primary key | Stored as | Insert 1,000,000 rows | Database size |
|---|---|---|---|
| Integer (auto-increment) | INTEGER | 0.8 s | 29 MB |
| UUID v7 | TEXT (36 chars) | 0.9 s | 70 MB |
| UUID v7 | BLOB (16 bytes) | 1.4 s | 47 MB |
| UUID v4 | TEXT (36 chars) | 4.2 s | 71 MB |
| UUID v4 | BLOB (16 bytes) | 4.0 s | 47 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 key | Stored as | Insert 1,000,000 rows (median of 3) | Range of the 3 runs | Database size |
|---|---|---|---|---|
| Integer | INTEGER | 0.57 s | 0.57–0.69 s | 33 MB |
| UUID v7 | TEXT (36 chars) | 1.04 s | 0.90–1.25 s | 70 MB |
| UUID v7 | BLOB (16 bytes) | 1.01 s | 0.95–1.21 s | 47 MB |
| UUID v4 | TEXT (36 chars) | 4.31 s | 4.10–4.40 s | 70 MB |
| UUID v4 | BLOB (16 bytes) | 3.86 s | 3.83–3.87 s | 47 MB |
- v4 was again several times slower than v7. About 4 times here, both as text (4.31 s against 1.04 s) and as binary (3.86 s against 1.01 s); the first run showed about 4.7 times as text and 2.9 times as binary.
- Small differences between v7 variants are noise. Each v7 variant's three runs spread by about 0.3 seconds, more than the difference between text and binary, so this run cannot say which of the two inserts faster, and the half-second difference in the first run should be read with the same caution. The space saving from binary storage, a third, was the same in both runs.
- v7 stored as text was further from integers this time. It took nearly twice as long as the integer key, where the first run showed 0.9 s against 0.8 s. Stored as binary, v7 took about 1.75 times as long as the integer key in both runs. Against the v4 result these differences are still small.
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.
- uuid_insert_test.py: creates the tables, generates the keys (including version 7, built as RFC 9562 describes), times the inserts and writes a results file.
- results.csv: the 15 timings from the run above, with columns key (integer, v7-text, v7-blob, v4-text or v4-blob), run, rows, insert_seconds and database_bytes.
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
| Situation | Suggested 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 time | UUID v4, or keep v7 internally and expose a separate random ID |
| Small tables, configuration, test fixtures | Either; v4 is fine |
| Values generated offline on many devices and merged later | Either; both are practically collision-free |
| You need IDs that also work as unguessable tokens | Neither. Use a dedicated random token of at least 128 bits |
Practical notes
- Store UUIDs in a native or binary type. PostgreSQL has a
uuidtype; MySQL users commonly useBINARY(16). In our test this alone saved a third of the space. - Getting v7 values. Recent PostgreSQL releases include a
uuidv7()function, and most languages have maintained libraries. Our browser tool currently makes v4 only, which is the right choice for test data, fixtures and one-off identifiers. - Clock order is approximate. v7 values from different machines, or several created within the same millisecond, are not guaranteed to be in strict order. Do not use them as the only way to sort events that must be exact.
- Do not convert existing keys just for this. Changing the primary key of a live table is risky. Use v7 for new tables and measure before migrating old ones.
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
- RFC 9562: Universally Unique IDentifiers (UUIDs), IETF, 2024
- SQLite: Clustered indexes and the WITHOUT ROWID optimization
- PostgreSQL documentation: UUID functions
- 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.