Primary Key Generation: Sequence Generator vs UUIDv7 vs MongoDB ObjectId

Primary keys look simple.

We create a table, add an id column, or create a MongoDB collection with _id, and move on.

But once a system grows, that small id field starts affecting more than just uniqueness. It impacts insert performance, index size, database round trips, debugging, data migration, event publishing, and distributed system design.

While exploring SQL and NoSQL databases, I realized that primary key generation is not just an implementation detail.

It is a design decision.

In this blog, I want to compare three common ID generation strategies:

1. Sequence generator
2. UUIDv7
3. MongoDB ObjectId

All three generate unique identifiers, but they behave very differently.

1. Sequence Generator: Simple, Fast, and Predictable

In SQL databases like PostgreSQL, one common primary key strategy is using a sequence generator.

For example:

id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

This gives IDs like:

1
2
3
4
5

Simple. Clean. Easy to debug.

For many systems, this is still one of the best choices.

Sequence-based IDs are compact, readable, and friendly for B-tree indexes because new inserts usually happen in increasing order. If you see order_id = 1024 in logs, it is much easier to reason about than a long random identifier.

But sequences also have trade-offs.

The database owns ID generation. That is fine when one database is the main source of truth, but it can become limiting in distributed systems where multiple services or regions need to create records independently.

There is also a predictability concern. If public URLs look like /orders/1001, /orders/1002, and /orders/1003, nearby IDs are easy to guess. That does not automatically make the system insecure, but authorization must be handled properly.

Database round trips with sequences

In PostgreSQL, the application usually does not need a separate round trip to get the ID. It can insert the row and get the generated ID back using RETURNING.

INSERT INTO orders (customer_id, amount)
VALUES (101, 500)
RETURNING id;

Flow:

Application → Database: Insert row
Database → Application: Row inserted + generated ID returned

But if the application needs the ID before inserting the row, it may need to call the sequence first:

SELECT nextval('orders_id_seq');

That creates an extra database round trip before the actual insert.

So sequences are efficient when the ID is needed after insert. But if the ID is needed before insert for events, references, or external systems, they add a database dependency.

Use sequence generators when the database owns the data and the application does not need independent ID generation.

2. UUIDv7: Distributed and More Database-Friendly

UUIDs became popular because they solve a common distributed systems problem:

How do we generate unique IDs without asking the database first?

Traditional UUIDs, especially UUIDv4, are mostly random.

550e8400-e29b-41d4-a716-446655440000

UUIDv4 is useful because the application can generate the ID locally. No database call is required.

But randomness has a downside in databases. Random IDs can cause random inserts into indexes, which can reduce locality and increase index fragmentation.

That is where UUIDv7 becomes interesting.

UUIDv7 includes a timestamp component. In simple words:

UUIDv7 is like a UUID, but with time-ordering built in.

This makes UUIDv7 more database-friendly than fully random UUIDv4 while still allowing application-side ID generation.

UUIDv7 is useful because it:

• can be generated by the application
• avoids asking the database for an ID
• works well in distributed systems
• is roughly sortable by time
• does not expose simple incremental counts
• behaves better for indexing than UUIDv4

Database round trips with UUIDv7

With UUIDv7, the application does not need a database round trip for ID generation.

Flow:

Application: Generate UUIDv7 locally
Application → Database: Insert row with generated ID

This is useful when the same ID needs to be used before persistence, such as in logs, events, child records, or API responses.

But UUIDv7 is not always better than sequences.

A UUID is typically 16 bytes, while a BIGINT is 8 bytes. That means UUID indexes are larger than numeric indexes. UUIDs are also less human-friendly while debugging.

Use UUIDv7 when the application needs to generate IDs independently, but you still want better database behavior than random UUIDv4.

3. MongoDB ObjectId: Practical Default for Documents

MongoDB has its own default ID strategy: ObjectId.

If you insert a document without providing an _id, MongoDB usually creates one automatically.

{
  "_id": ObjectId("65f87920068c7d17cb1288d6"),
  "name": "Yash"
}

ObjectId is a 12-byte value made of:

• timestamp
• random value
• counter

So ObjectId is not completely random. It has structure.

A simplified view:

[timestamp] [random value] [counter]

This makes ObjectId practical for MongoDB because it can be generated by the MongoDB driver or application before the document is inserted.

For example, in Java:

ObjectId id = new ObjectId();

Now the application can use the same ID for references, logs, events, and API responses.

Database round trips with ObjectId

ObjectId also avoids an extra database round trip.

Flow:

Application: Generate ObjectId locally
Application → Database: Insert document with generated _id

This is useful when the application needs the document ID before insert, for example while creating references, publishing events, or returning the ID immediately from an API.

What about ObjectId collisions?

A common question is:

Can two MongoDB ObjectIds collide?

The practical answer is: extremely unlikely, but not impossible.

ObjectId is designed to be highly unique using timestamp, random value, and counter components. In normal application usage, collision chances are extremely low.

But MongoDB still protects the collection. The _id field has a unique index by default. If a duplicate _id is inserted, MongoDB rejects it with an error like:

E11000 duplicate key error collection

When the application generates ObjectIds itself, the responsibility becomes shared. MongoDB validates uniqueness, but the application owns ID generation.

A practical fallback strategy can be:

Generate ObjectId in application
Try insert
If duplicate key error occurs, generate a new ObjectId
Retry once or twice
If it still fails, log and fail safely

In Java, conceptually:

try {
    collection.insertOne(document);
} catch (MongoWriteException e) {
    if (e.getError().getCode() == 11000) {
        document.put("_id", new ObjectId());
        collection.insertOne(document);
    } else {
        throw e;
    }
}

This fallback may almost never be used, but it makes the system more resilient.

Use ObjectId when MongoDB is the natural home of the data and you want client-side ID generation with fewer database round trips.

My Takeaway

There is no universal winner.

A sequence generator is great when the database owns ID generation and the application can receive the ID after insert.

UUIDv7 is great when multiple services need to generate IDs independently before writing to the database.

ObjectId is great when working naturally within MongoDB and when the application or driver needs to generate document IDs before insert.

The real question is not:

“Which primary key strategy is best?”

The better question is:

“Where should this ID be generated, and who should own the responsibility for it?”

If the database owns the ID, sequences are simple and efficient.

If the application owns the ID, UUIDv7 or ObjectId can reduce database round trips and work better in distributed flows.

But application-side ID generation also means application-side responsibility for rare failures.

Primary keys are easy to ignore in the beginning, but later they appear everywhere: logs, APIs, indexes, events, queues, dashboards, debugging sessions, and migration scripts.

So it is worth thinking about them early.

Because just like databases, ID strategies are not good or bad in isolation.

They are good or bad for a workload.