Let’s talk about IDs in databases. At first glance, this may sound very unimportant, but there are many different factors to consider. Especially if you have never thought about primary keys before, you should read this article.

When choosing primary keys or object IDs in databases, you will have to decide between incremental IDs, UUIDs, or database-specific IDs such as the MongoDB ObjectID.

This decision has an impact on scalability, security, performance, and architecture.

Of course, there are many different types of IDs. Just UUIDs have now 8 different versions. Therefore, I will focus on the most common forms that you are most likely to encounter.

Incremental IDs (1, 2, 3, …)

A classic SQL database often uses auto-increment columns as primary keys.

1. Central system

Auto-increment only works reliably if a central instance assigns the next ID.

This becomes difficult in distributed systems:

Multiple database writers
Microservices
Offline synchronisation
Multi-region deployments

In that case, you need:

ID server
Hi/Lo algorithm
Snowflake IDs
or other workarounds

This makes the architecture more complex.

2. Deterministic / Predictable

If a user sees the ID “1000”, they already have too much information. It can immediately be assumed that there are already 999 other users. At this point, you are already revealing valuable information. This also introduces a security risk.

This enables:

Enumeration attacks
Data scraping
Possible unauthorised access to other user's records

3. Object only exists after insert

With auto-increment, the ID is created only when the record is inserted into the database.

This means:

You cannot create references beforehand
No offline object
No event with ID before persistence
More difficult with event sourcing
More difficult with CQRS

With client-generated IDs, the object already exists before the database insert.

UUID Version 4

UUIDv4 are random IDs and are 128 bits long. 4 bits are used for the UUID version, and another 2 bits are used to identify the variant. You can find the exact specification in RFC 4122.

1. Not predictable

UUIDs are random → practically impossible to guess.

Therefore suitable for:

Public APIs
URLs
Multi-tenant systems
Security

2. Can be generated decentrally

UUIDs can be generated by:

Client
Server
Worker
Offline
Multiple services simultaneously

No central ID system required.

Perfect for:

Microservices
Event-driven architecture
Offline applications
Synchronisation
Multi-region systems

3. Object exists before insert

You can:

Create the object
Generate events
Create references
Upload files
Write logs

Before the object is stored in the database.

4. Disadvantages of UUIDv4

UUIDv4 are random → bad for database indexes:

Inserts occur throughout the index
Index fragmentation
Larger indexes
Slower inserts
More storage
Poor cache locality

Therefore UUIDv4 are not ideal for very large tables with a high insert rate.

UUID Version 7

UUIDv7 is relatively new and combines:

Timestamp
Random bits

This makes them:

Time-sortable
Still globally unique
Better for databases

1. Sortability

UUIDv7 increase over time → similar to auto-increment, but can be generated in a distributed manner.

This improves:

Index performance
Insert speed
Range queries
Pagination
Log events
Event stores

2. Better insert performance

Since new IDs usually end up at the end of the index:

Less index fragmentation
Fewer page splits
Faster inserts
Better performance for large tables

UUIDv7 is currently one of the best general-purpose ID solutions.

3. Combines advantages

UUIDv7 combines:

Decentral generation
Not predictable
Sortable
Good database performance
No central system
Suitable for distributed systems

Many consider UUIDv7 to be a modern replacement for auto-increment.

MongoDB ObjectID

MongoDB uses an ObjectID by default.

Example: 69c13a91590a780b5fa6a740

The ObjectID consists of:

4 bytes timestamp
5 bytes random / machine ID
3 bytes counter

1. Advantages

Time-sortable
Can be generated decentrally
Good insert performance
No central system
Contains creation timestamp
Works well in distributed systems

2. Reveals the database system

If someone sees an ObjectID, they immediately know → MongoDB is being used

This can:

Reveal architecture
Increase attack surface
Expose the technology stack

3. Timestamp is visible

You can extract from the ID:

When the object was created
Order of inserts
System activity

Depending on the application, this may be undesirable.

4. Not a standard

ObjectID is MongoDB-specific → problematic if:

The system is migrated later
Multiple databases are used
Data is exchanged between systems

Small note: If IDs are generated on the application side, the ObjectID format can also be used with other database systems, similar to using UUIDs.

Summary - TL;DR

Each ID type has its advantages and disadvantages
From today’s perspective, UUIDv7 offers the best overall package in terms of
- Scalability
- Sortability
- Indexing
- (Security)
Even though incremental IDs have many disadvantages, there are still valid use cases for them

Blog

UUID vs. MongoDB ObjectIDs vs. Incremental IDs

My "Hello world" to the world