Should You Generate IDs in the App or the Database?

Jim Knicely

UUIDs are a common choice for modern distributed applications because they provide globally unique identifiers without coordination.

But once you decide to use UUIDs, the next question is just as important:

  • Where should you generate them?

In the application?
Or in the database?

In a distributed SQL system like YugabyteDB, this decision has real implications for performance, scalability, and even correctness.

TL;DR
If you care about maximum performance, batching, and retries, generate UUIDs in the application.
If you care about simplicity and convenience, generate them in the database using a DEFAULT.
In distributed SQL, this choice directly impacts write efficiency, idempotency, and system behavior.

Why this decision matters more in YugabyteDB

In a traditional single-node database, this decision is mostly about convenience.

In a distributed database, it affects:

  • ● Write batching
  • ● Network round trips
  • ● Retry behavior
  • ● Data distribution patterns
  • ● Storage efficiency
🧠 Key Insight
In YugabyteDB, the earlier an ID is known, the easier it is to batch writes efficiently.
Generating IDs in the application improves batching. Generating them in the database improves convenience.
Choosing between them is really a tradeoff between performance and simplicity.

Option 1: Generate UUIDs in the application

This means your application creates the UUID before sending the insert.

				
					INSERT INTO orders (id, payload)
VALUES ('018f...uuidv7...', 'data');
🚀 Why this works so well in distributed systems
When the application generates the ID, the database receives a fully-formed row. This allows YugabyteDB to batch writes more efficiently and avoid unnecessary coordination.
Pros
  • ● Better batching – database receives complete rows
  • ● Idempotent retries – retrying the same request won’t create duplicates
  • ● Offline support – generate IDs without database connectivity
  • ● No extra round trips – no need to fetch or reserve IDs
  • ● Decoupling – no dependency on database-specific logic
Cons
  • ● Clock skew – incorrect client clocks can affect ordering
  • ● Timestamp exposure (UUIDv7) – creation time is embedded in the ID
  • ● Trusting the client – buggy or malicious clients could generate problematic IDs
  • ● No automatic DEFAULT behavior

Option 2: Generate UUIDs in the database

This uses a schema-level default:

				
					id uuid DEFAULT uuid_v7()
⚡ Simplicity
The database automatically generates IDs… no application changes required.
Pros
  • ● Simple schema-driven design
  • ● No application changes needed
  • ● Centralized logic
  • ● Great for demos and rapid development
Cons
  • ● Can reduce batching efficiency (depending on implementation)
  • ● More per-row work inside the database
  • ● Harder to evolve logic later

Side-by-side comparison

Approach Strengths Tradeoffs
App-side Batching, retries, performance, flexibility More responsibility in application
DB-side Simplicity, centralized control Potential batching inefficiency

Best practices for YugabyteDB

  • ● Use the native UUID type (not VARCHAR)
  • ● Prefer application-side generation for high-throughput workloads
  • ● Use well-tested UUID libraries (don’t roll your own)
  • ● Treat DB-side defaults as a convenience layer, not a performance strategy
🧠 Practical Guidance
High-scale production workload? → App-side UUIDs
Rapid development / POC? → DB-side UUIDs

Final takeaway

There is no single “correct” answer… but there is a clear pattern:

  • ● Application-side UUID generation maximizes performance, batching, and reliability.
  • ● Database-side UUID generation maximizes simplicity and ease of use.

In YugabyteDB, where distributed writes and batching matter, that distinction becomes critical.

Related YugabyteDB tips

Have Fun!