Sequential numbers as primary keys are simple and fast. But as soon as data is created in several places, IDs show up in URLs or datasets need to be merged, they reach their limits. A UUID is the obvious alternative – yet the wrong version can noticeably slow down a large table.
This guide compares auto-increment, UUID v4, UUID v7 and ULID, explains what happens in the index and shows how to store the IDs compactly in PostgreSQL, MySQL and MariaDB.
Auto-increment or UUID?
An auto-increment BIGINT takes 8 bytes, is easy to read and is assigned by the database in ascending order – ideal for the index. The downsides:
- Only the database can assign IDs. The application doesn’t know the ID of a new record until it has been saved.
- When you merge several databases, for example from different tenants or locations, the numbers collide.
- IDs in URLs are guessable (
/invoice/1042,/invoice/1043) and reveal roughly how many records exist.
A UUID (Universally Unique Identifier) is 128 bits long and can be generated anywhere – in the browser, in a microservice, offline in an app – without coordinating with a central authority. When generated correctly, collisions are practically impossible. In return, it is twice the size of a BIGINT, harder to read and puts a strain on the index if its values are randomly distributed.
How a UUID is structured
The usual notation consists of 32 hexadecimal digits in five groups. Two positions have a fixed meaning:
fbd0a224-e753-47f1-8ab9-f8f688240eb6
^ ^
| variant (8, 9, a or b)
version (4 here)
The first digit of the third group indicates the version, the first digit of the fourth group the variant. The current specification is RFC 9562 from May 2024; it replaces RFC 4122 and introduces versions 6, 7 and 8, among other things. With the UUID & ULID Generator you can decode an existing UUID and see its version, variant and – for time-based versions – the timestamp it contains.
UUID v4 and the index problem
Apart from the version and variant bits, version 4 consists of 122 random bits. That is ideal for uniqueness, but not for a B-tree index – the default index type in PostgreSQL, MySQL and MariaDB:
- Every new ID lands at a random position in the index. Full index pages have to be split (page splits), and afterwards the pages are only partly filled.
- As a result, the index grows larger and becomes fragmented.
- Inserts constantly touch different pages. As long as the index fits in memory, this is barely noticeable; once it outgrows the cache, the database has to read from disk frequently and writes slow down considerably.
MySQL with InnoDB is particularly affected: there, the primary key is a clustered index, meaning the table rows themselves are stored sorted by ID. In addition, every secondary index contains the primary key – so a large ID makes every index on the table larger.
Sequential IDs, by contrast, are always appended at the right-hand edge of the index. UUID v7 and ULID bring back exactly this property.
UUID v7 and ULID: sortable by time
UUID v7 starts with a 48-bit timestamp in milliseconds since 1970 (Unix time), followed by the version, the variant and 74 bits that are random or partly used as a counter. Three v7 UUIDs, generated one millisecond apart:
01a1f873-c8a0-75c3-9f44-cdacf58c2bf3
01a1f873-c8a1-7074-8292-f8d38b0b8d9c
01a1f873-c8a2-7c30-8879-e52c96bfbcc4
The first twelve hex digits (01a1f873c8a0) are the timestamp: 1795169700000 milliseconds in decimal, which is 20 November 2026, 10:15:00 UTC. You can turn values like this into a readable date with the timestamp converter. New IDs are therefore greater than older ones and – just like auto-increment – are inserted at the end of the index.
The ULID (Universally Unique Lexicographically Sortable Identifier) follows the same principle: a 48-bit millisecond timestamp and 80 random bits. Instead of hexadecimal, however, it is written in Crockford’s Base32 – 26 characters, only uppercase letters and digits, without the easily confused letters I, L, O and U:
01M7W77J50RS5VA0XF7WT5AGYY
The first ten characters contain the timestamp. ULIDs are shorter than UUIDs in text form and can be sorted as strings. They are not an IETF standard, though, but an open specification. In the database, you store them as 16 bytes, just like a UUID.
All options compared
| BIGINT auto-increment | UUID v4 | UUID v7 | ULID | |
|---|---|---|---|---|
| Size (binary) | 8 bytes | 16 bytes | 16 bytes | 16 bytes |
| Text form | up to 19 digits | 36 characters | 36 characters | 26 characters |
| Generated without the database | no | yes | yes | yes |
| Sorted by time | yes | no | yes | yes |
| Behavior in a B-tree index | very good | poor for large tables | good | good |
| Reveals information | count and order | nothing | creation time | creation time |
| Standard | – | RFC 9562 | RFC 9562 | own specification |
The UUID & ULID Generator generates all three as well as name-based v5 UUIDs – up to 1,000 at once if needed, as a list, JSON or SQL.
Storing UUIDs properly
PostgreSQL
PostgreSQL has a dedicated uuid data type that takes 16 bytes internally. gen_random_uuid() generates v4 UUIDs, and since PostgreSQL 18 there is also uuidv7() (see the documentation):
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
created_at timestamptz NOT NULL DEFAULT now()
);
MySQL
MySQL has no UUID data type. Store the ID as BINARY(16), not as CHAR(36) – that saves more than half the space in every index. For conversion there are UUID_TO_BIN() and BIN_TO_UUID():
CREATE TABLE orders (
id BINARY(16) PRIMARY KEY
);
INSERT INTO orders (id) VALUES (UUID_TO_BIN('01a1f873-c8a0-75c3-9f44-cdacf58c2bf3'));
SELECT BIN_TO_UUID(id) FROM orders;
MySQL’s UUID() function generates version 1, in which the time components are arranged unfavorably. UUID_TO_BIN(UUID(), 1) reorders them so the values are stored in ascending order. Use this second parameter only for v1; a v7 UUID is already in the right order and is stored without it. In MySQL, it is best to generate v7 UUIDs in the application.
MariaDB
Since version 10.7, MariaDB has its own UUID data type, which takes 16 bytes and accepts and returns the usual text form. No conversion functions are needed.
In the application
In Symfony, the Uid component generates v7 UUIDs (Uuid::v7()) and ULIDs (new Ulid()), and matching column types are available for Doctrine. In Laravel, the HasUuids trait has assigned v7 UUIDs by default since version 12, while HasUlids generates ULIDs.
Caution: what an ID reveals
A v7 UUID or a ULID contains its creation time to the millisecond – anyone who sees the ID can read it. For an order number, that is usually harmless; for a user account ID, it may reveal more than intended, such as when someone signed up. If that matters, use a v4 UUID or a separate public identifier externally.
Also, never treat IDs as secrets. Even if a UUID is hard to guess, someone who knows the link to /invoice/01a1f873-… may only see the invoice if your application checks their permissions. Password reset links or API keys need their own random values of sufficient length, without a time component.
Recommendation
- Small application with a single database, IDs not public: an auto-increment
BIGINTis still a good choice. - IDs are generated in different places, data is merged or IDs appear in URLs: UUID v7 as the primary key. It is standardized, generated natively by PostgreSQL and supported by common libraries.
- Short, readable IDs wanted: ULID – especially if the ID often appears in text form.
- The creation time must not be visible: UUID v4, ideally only as an additional public identifier alongside a sortable primary key for large tables.
Always store UUIDs in binary form (uuid, BINARY(16) or MariaDB’s UUID), not as text. And pick one version per table – mixing v4 and v7 in the same column loses the benefit of ordering.
Tools for this topic
- UUID & ULID Generator Generate UUIDs (v4, v7, v5) and ULIDs for free – up to 1,000 at once as a list, JSON or SQL – and decode existing IDs. Nothing is transmitted.
- Unix Timestamp Converter Convert Unix timestamps to dates and back – seconds to nanoseconds, any time zone, with code for PHP, JavaScript, Python, SQL and Bash.
More guides
- Special characters in database passwords Why @, :, / or # in a password break your DATABASE_URL and how to encode the password correctly – for Symfony, Laravel, Doctrine and Docker.
- Redirects for a website relaunch How to plan 301 redirects for a relaunch: collect old URLs, map targets, avoid chains and check the result.
- Which favicons does a website need? Which favicon files and sizes a website really needs today – favicon.ico, SVG, Apple touch icon and web manifest – including the HTML.
- Password-protect a directory (.htpasswd) Protect a directory or staging site with .htaccess and .htpasswd – for Apache and nginx, using secure hashing.
- Optimize images for the web Resize and compress images the right way: suitable sizes, WebP, AVIF or JPEG, quality, responsive images and lazy loading.
- Accessible colors: contrast according to WCAG What the WCAG contrast ratio means, which values apply to text and UI components and how to fix colors that are too weak.
- Using JWT securely Common security mistakes with JSON Web Tokens and how to avoid them: alg none, weak keys, missing checks and where to store tokens.