oktools

UUID v4 vs UUID v7 vs ULID: Choosing an ID for Your Database

How random and time-ordered identifiers differ, why the difference shows up in your index, what a v7 UUID gives away about when a row was created, and when a plain integer is still the better key.

The short answer

Auto-incrementUUID v4UUID v7ULID
Size8 bytes (bigint)16 bytes16 bytes16 bytes
As textUp to 19 digits36 characters36 characters26 characters
Sorts by creation timeYesNoYes, to the millisecondYes, to the millisecond
Can be made without the databaseNoYesYesYes
Gives awayRow count and orderNothingCreation timeCreation time
StandardDatabase featureRFC 9562RFC 9562Community spec

For a new table that needs IDs generated outside the database, or IDs that are safe to expose, UUID v7 is the default to reach for in 2026: it is a standard UUID, so every database and library already stores and parses it, and it keeps the index-friendly ordering of an integer. Use v4 when creation time must not be readable from the ID, and a plain integer when the ID never leaves your system and one database hands them out.

What is inside each one

A UUID is 128 bits, written as 32 hex digits in groups of 8-4-4-4-12. In every version, the first digit of the third group is the version, and the first digit of the fourth group is 8, 9, a or b - the variant bits that mark it as an RFC UUID.

Version 4 fills the remaining 122 bits with random data. Two v4 UUIDs made a microsecond apart share nothing, which is the point: they reveal nothing and need no coordination.

Version 7, standardised in RFC 9562 in 2024, puts a 48-bit Unix timestamp in milliseconds first and fills the rest with random bits. Here are three made by this site's UUID generator code, two in the same millisecond and one a millisecond later:

UUID v7
01a1e8d7-81c0-7106-b7b3-fc7e88d4f06a
01a1e8d7-81c0-7107-b0ef-bfa9ef914f19
01a1e8d7-81c1-731f-a91c-7e21d85a89dc

The first 12 hex digits, 01a1e8d781c0, are 1794907800000 in hexadecimal: 17 November 2026, 09:30:00.000 UTC. The third ID ends its first group of 12 in c1, one millisecond later. Within the same millisecond the generator counts up in the 12 bits after the version digit (106, then 107), one of the methods RFC 9562 allows, so IDs from one generator still sort in the order they were made.

A ULID is the same idea from before v7 existed: a 48-bit millisecond timestamp followed by 80 random bits, written in Crockford's Base32 as 26 characters. A ULID made at the same moment as the first UUID above starts 01M7MDF0E0 - those 10 characters are the timestamp - followed by 16 random ones. It is a community specification rather than an IETF standard, so databases have no ULID type. Because it is also 128 bits, it can be stored in a UUID column, but then it is displayed as a UUID and the short form is lost.

Why random IDs slow down an index

Primary keys are almost always stored in a B-tree, which keeps keys in sorted order across fixed-size pages. Where a new key lands decides how much work an insert is.

  • An auto-increment ID, a v7 UUID or a ULID is larger than every key before it, so each insert goes to the last page. That page is already in memory, fills up, and a new one is started.
  • A v4 UUID lands on a random page. With a large table, that page is often not in memory and has to be read first. Pages split when they fill, leaving them partly empty, so the same rows take more space and more of the index has to stay in cache to keep inserts fast.
  • In MySQL's InnoDB, the table itself is stored in primary key order, so a random key scatters the rows, not just the index. In PostgreSQL the table is separate, but the primary key index and every write-ahead log entry for its pages pay the same cost.

The effect is small for a table of thousands of rows and grows with the table and the insert rate. It is the main practical reason v7 was standardised. A table keyed by v4 still works, but it is the first thing to change if inserts slow down as it grows.

Can two IDs collide?

With 122 random bits, a collision among v4 UUIDs becomes a 50% chance only after about 2.71 × 10^18 IDs. Making a trillion of them gives a chance of any collision at all of about 9.4 × 10^-14.

The practical risk is not the maths but the random source. UUIDs must come from a cryptographically secure generator - crypto.randomUUID() in browsers and Node, uuid.uuid4() in Python, java.util.UUID.randomUUID() in Java. Hand-rolled code built on Math.random() has produced real duplicates, and so have virtual machines cloned with the same random state.

A v7 UUID has fewer random bits, but they only need to differ between IDs made in the same millisecond, so the chance of a collision stays negligible at any realistic rate.

What a v7 UUID or ULID gives away

Anyone holding a v7 UUID can read when it was made: take the first 12 hex digits, convert them from hexadecimal, and paste the number into the Unix timestamp converter as milliseconds. The same works for a ULID's first 10 characters, decoded from Base32.

Usually that is harmless - a URL like /orders/01a1e8d7-... tells a customer when they placed their own order. It matters when creation time is private: an account ID that reveals when someone signed up, or a document ID that dates a leak. Use v4 for those, or keep the v7 internal and expose a separate random ID.

Auto-increment IDs give away more. Two IDs a week apart tell anyone how many records were created in that week, and the highest ID they see estimates the total. That is why integer keys are best kept out of public URLs even where they are the right primary key.

Storing them

  • PostgreSQL has a native 16-byte uuid type that accepts any version. PostgreSQL 18 added a built-in uuidv7() function; on older versions, generate the ID in the application.
  • MySQL and MariaDB: store BINARY(16) rather than CHAR(36), which takes more than twice the space in every index that contains the key. Convert with UUID_TO_BIN() but without its swap flag for v7: that flag reorders a version 1 UUID's time fields and would break a v7's ordering.
  • SQL Server's uniqueidentifier sorts by the last group of bytes first, so a v7 UUID does not insert in order there. Its NEWSEQUENTIALID() is the native ordered alternative.
  • As text, in JSON or logs, keep the standard lowercase hyphenated form. The generator can also produce uppercase, unhyphenated and braced forms for systems that expect them.

Which one to use

  • Internal table, one database, IDs never shown outside: bigint auto-increment. It is the smallest and fastest key.
  • IDs made by several services, offline clients or before the insert, or shown in URLs and APIs: UUID v7.
  • IDs whose creation time must stay private: UUID v4, ideally not as the clustered primary key of a large, write-heavy table.
  • You need short, URL-friendly IDs and control every system that reads them: ULID. Otherwise prefer v7, which is the same layout with standard tooling.
  • Test fixtures and seed data: whatever production uses. The mock data generator can fill UUID fields for you.