UUIDs vs Sequential IDs: A Practical Comparison

The decision of how to identify records in your database is one of those choices that seems trivial at the start of a project and becomes painful to change later. Auto-incrementing integers are simple and fast. UUIDs are bulkier but distributed-friendly. And the newer options — UUID v7, ULIDs — try to give you both. Let's look at what actually matters.

Sequential (auto-increment) integer IDs

The default in most SQL databases: id SERIAL PRIMARY KEY (PostgreSQL) or id INT AUTO_INCREMENT (MySQL). Each new row gets the next integer in sequence.

Advantages:

• Compact. A 4-byte integer fits in every index efficiently.
• Monotonically increasing. New records always go to the end of an index. No page splits, no index fragmentation. Very fast for bulk inserts.
• Human-readable. When debugging, user_id=1042 is easier to work with than a 36-character string.
• Natural ordering. Sorting by ID gives you insertion order, which is often what you want for pagination.

Problems:

• Sequential IDs are enumerable. If your API exposes /users/1042, an attacker can try /users/1043, /users/1041, and so on. This is called an IDOR (Insecure Direct Object Reference) vulnerability. The ID itself leaks that records exist, and the pattern allows automated enumeration.
• Not distributed-friendly. Two database shards or services cannot independently generate IDs without coordination, because they'd generate conflicting sequences.
• Leaks record count. Anyone who creates a new account and checks their user ID now knows approximately how many users you have. Startups sometimes care about this.

UUID v4 — random and collision-resistant

A UUID (Universally Unique Identifier) is a 128-bit value, typically displayed in a canonical 36-character format with hyphens: 550e8400-e29b-41d4-a716-446655440000.

UUID v4 is randomly generated. 122 bits of randomness (6 bits are used for version/variant flags). The probability of a collision is so vanishingly small (approximately 1 in 5 undecillion for any given pair) that it's not a practical concern.

Advantages:

• Can be generated client-side, on any service, without coordination. Multiple shards, microservices, or offline clients can independently generate IDs that are guaranteed not to conflict.
• Not enumerable. Knowing one UUID gives no information about others.
• Doesn't leak record counts or insertion order.

Problems:

• Larger storage footprint. 16 bytes vs 4–8 bytes for an integer. In tables with hundreds of millions of rows, this adds up.
• Random UUIDs destroy index locality. Because UUIDs are random, new records insert at random positions in a B-tree index, causing page splits and index fragmentation. This significantly impacts write performance at scale.
• Not sortable by time. You can't tell from a v4 UUID when a record was created.

UUID v7 — sorted and distributed

UUID v7 (standardized in RFC 9562, 2024) solves the sortability problem by embedding a millisecond-precision Unix timestamp in the high bits. New records generate UUIDs that sort chronologically in B-tree indexes — you get the distributed generation of UUID v4 without the write performance hit.

If you're starting a new project and need distributed-friendly IDs, UUID v7 is worth considering over v4.

ULID — another time-sorted option

ULID (Universally Unique Lexicographically Sortable Identifier) is a popular third-party alternative: 48-bit timestamp + 80 bits of randomness, encoded as a 26-character base32 string. Like UUID v7, it sorts chronologically. Unlike UUID, it doesn't contain hyphens and is slightly more compact in string form. Not a formal standard but widely used in the Node.js ecosystem.

The IDOR problem deserves more attention

Sequential IDs don't automatically create security vulnerabilities — they only matter if your authorization controls are wrong. An API endpoint should check that the requesting user has permission to access the requested resource, regardless of whether the ID is sequential or random. If you properly implement authorization checks, sequential IDs are fine.

That said, random IDs provide defense in depth. If an authorization check is accidentally missing, a random UUID doesn't give an attacker an easy path to enumerate every record. Sequential integers do. For resources that should not be enumerable (private files, draft content, internal records), random IDs reduce the blast radius of authorization bugs.

Practical recommendation

• Small-scale internal tools, single-database apps: Auto-increment integers are perfectly fine. Simple, fast, and easy to work with.
• Public APIs where enumeration is a concern: UUID v4 or v7 for record IDs. Don't expose sequential integers in API responses.
• Distributed systems, microservices, offline-first apps: UUID v4 (if sort order doesn't matter) or UUID v7 (if you want time-sorted behavior).
• High-write-rate databases at scale: UUID v7 or ULID over UUID v4 to avoid B-tree fragmentation.