UUID vs Auto-Increment: Which to Choose for Database?

You're designing your database schema and face the fundamental question: what type of primary key should you use? On one side is the classic auto-increment (1, 2, 3...), on the other are UUIDs, those 36-character strings that seem random. The choice has profound implications for performance, scalability, and security. Let's see when to use what.

Auto-increment: the classic that works

With auto-increment, the database automatically generates an increasing integer for each new record:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  name VARCHAR(100)
);

-- Insert without specifying id
INSERT INTO users (email, name) VALUES ('john@example.com', 'John');
-- id assigned automatically: 1, 2, 3...

Advantages

• Minimal storage: 4 bytes (INT) or 8 bytes (BIGINT) vs 16-36 bytes for UUID
• Read performance: indexes on integers are faster to navigate
• Ordered inserts: new records always go at the end of the index, no reordering
• Readability: "user 12345" is easier to remember than "a8c99d2e-..."

Disadvantages

• Predictability: /users/1, /users/2... an attacker can enumerate all resources
• Conflicts in distributed systems: two separate databases could generate the same ID
• Exposes metrics: ID 50000 reveals how many records exist
• Database dependency: you can't generate the ID client-side before insert

UUID: the universal alternative

UUID (Universally Unique Identifier) is a 128-bit string that can be generated anywhere with (practical) guarantee of global uniqueness:

CREATE TABLE orders (
  id CHAR(36) PRIMARY KEY,  -- or better: BINARY(16)
  user_id INT,
  total DECIMAL(10,2)
);

-- UUID is generated client-side
-- f47ac10b-58cc-4372-a567-0e02b2c3d479

UUID versions that matter

UUIDv4 (random): completely random, most used but problematic for performance.

UUIDv7 (time-ordered): approved in May 2024, includes timestamp at the beginning. Maintains global uniqueness but is chronologically ordered. This is the recommended choice in 2025.

// UUIDv7 example - note the temporal prefix
0193bafc-2e8d-7000-8000-...  // sortable timestamp

Advantages

• Global uniqueness: can be generated anywhere without coordination
• Security: impossible to enumerate or predict resources
• Distributed systems: perfect for microservices and replicated databases
• Client-side generation: you can create the ID before saving to database

Disadvantages

• Storage: 16 bytes (binary) or 36 bytes (string) vs 4-8 bytes for INT
• Insert performance (UUIDv4): randomness causes index fragmentation
• Readability: debugging is harder
• Long URLs: /orders/f47ac10b-58cc-4372-a567-0e02b2c3d479

Performance: the numbers that matter

Storage

With millions of records and many indexes, this difference multiplies significantly.

Insert performance

MySQL and other databases with clustered indexes suffer with random UUIDv4:

• Auto-increment: insert always at end of index, optimal
• UUIDv4: insert at random positions, causes page splitting and fragmentation
• UUIDv7: chronologically ordered inserts, performance similar to auto-increment

PostgreSQL uses heap instead of clustered index, so it suffers less with random UUIDs.

Query performance

For single lookups (WHERE id = X), the difference is minimal. For range queries and joins on large tables, integers are faster due to smaller index size.

The hybrid solution: the best of both

Many applications use a combined approach:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,    -- internal PK for joins
  uuid BINARY(16) UNIQUE NOT NULL,      -- public ID for API
  name VARCHAR(255),
  INDEX idx_uuid (uuid)
);

-- The application exposes only uuid in APIs
-- /products/f47ac10b-58cc-4372-a567-0e02b2c3d479

-- Internal joins use id for performance
SELECT p.name, o.quantity
FROM products p
JOIN order_items o ON p.id = o.product_id;

Best practices for UUID

• Use BINARY(16) instead of CHAR(36) to save 56% space
• Prefer UUIDv7 over UUIDv4 to avoid fragmentation
• Generate client-side to have the ID before insert
• Consider hybrid approach for tables with many joins

When to use what: practical guide

Use Auto-increment when:

• Single database, not distributed
• Tables with millions of records and many joins
• IDs are not publicly exposed
• Performance is the absolute priority

Use UUID when:

• Microservices or distributed databases
• IDs exposed in public APIs
• Security against enumeration is important
• You need to generate IDs client-side
• Merging data from different sources

Generating UUID in code

JavaScript/Node.js

import { randomUUID } from 'crypto';

// UUIDv4 (built-in since Node 14.17+)
const id = randomUUID();

// UUIDv7 (with library)
// npm install uuid
import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();

Python

import uuid

# UUIDv4
id = str(uuid.uuid4())

# UUIDv7 (with library)
# pip install uuid7
from uuid_extensions import uuid7
id = str(uuid7())

Conclusion

There's no universal answer. The choice depends on your context:

• Monolithic app, single database: auto-increment works great
• Microservices, public APIs: UUIDv7 is the modern choice
• Tables with millions of joins: consider the hybrid approach

The industry trend is converging on UUIDv7 for most cases, combining global uniqueness with performance comparable to auto-increment. With the official approval of UUIDv7 in 2024, we expect it to become the de facto standard for new applications in the coming years.

Remember: the primary key choice is an important architectural decision that impacts the entire application lifecycle. Take the time to evaluate the trade-offs for your specific case.

Try our free UUID generator to create UUIDv4 and UUIDv7 instantly.