The short answer
| Auto-increment | UUID v4 | UUID v7 | ULID | |
|---|---|---|---|---|
| Size | 8 bytes (bigint) | 16 bytes | 16 bytes | 16 bytes |
| As text | Up to 19 digits | 36 characters | 36 characters | 26 characters |
| Sorts by creation time | Yes | No | Yes, to the millisecond | Yes, to the millisecond |
| Can be made without the database | No | Yes | Yes | Yes |
| Gives away | Row count and order | Nothing | Creation time | Creation time |
| Standard | Database feature | RFC 9562 | RFC 9562 | Community 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:
01a1e8d7-81c0-7106-b7b3-fc7e88d4f06a
01a1e8d7-81c0-7107-b0ef-bfa9ef914f19
01a1e8d7-81c1-731f-a91c-7e21d85a89dcThe 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
uuidtype that accepts any version. PostgreSQL 18 added a built-inuuidv7()function; on older versions, generate the ID in the application. - MySQL and MariaDB: store
BINARY(16)rather thanCHAR(36), which takes more than twice the space in every index that contains the key. Convert withUUID_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
uniqueidentifiersorts by the last group of bytes first, so a v7 UUID does not insert in order there. ItsNEWSEQUENTIALID()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:
bigintauto-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.
