Every database row needs an identifier. Two strategies dominate: auto-incrementing integers and UUIDs. Each has clear strengths, and choosing between them affects your API security, database performance, and distributed system design.
Auto-Increment Integers
The classic approach: each new row gets the next integer in sequence. Simple, compact, and fast.
``sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255)
);
`
Advantages:
- Tiny storage footprint (4 bytes for INT, 8 for BIGINT)
- Fast B-tree index inserts (always appends to the end — no fragmentation)
- Human-readable for debugging
Problems:
- Leaks information: /api/users/4382
tells an attacker how many users you have and that adjacent IDs exist - Breaks in distributed systems: two servers can independently assign ID 1000 to different rows
- Can't generate IDs on the client without a server round-trip
- Enumerable: anyone can walk through /users/1
, /users/2, ... to scrape your data
UUIDs
A UUID (Universally Unique Identifier) is a 128-bit number represented as 32 hex characters:
<code>
<p>f47ac10b-58cc-4372-a567-0e02b2c3d479</p>
</code>
Generated anywhere — client, server, database — with negligible collision probability. You'll find them in RFC 4122 and supported natively in PostgreSQL, MySQL 8, and most major languages.
Advantages:
- Globally unique without coordination between servers
- Can be generated client-side (mobile apps, offline-first systems)
- Opaque: reveals nothing about table size or creation order
- Safe to expose in public APIs
Problems:
- Storage: 16 bytes as BINARY(16), 36 bytes as CHAR(36) — 4–9× larger than INT
- Random inserts cause B-tree index fragmentation (writes scatter across the index)
- Harder to read and debug
UUID Versions That Matter
UUID v4 (random) — the default. 122 bits of randomness, effectively zero collision risk for any real-world application. Use this when you just need unique IDs.
UUID v7 (timestamp + random) — a newer draft standard. Starts with a Unix timestamp, so rows sort naturally by creation time. Combines UUID's global uniqueness with sequential insert performance. Use this in new applications when database performance matters.
UUID v5 (deterministic) — hashes a namespace + name to produce a stable UUID. Same input → same UUID, every time. Useful for generating deterministic IDs for URLs, email addresses, or external entity IDs.
Performance Reality
The index fragmentation problem with UUID v4 is real but often overstated:
- Under ~10M rows: negligible performance difference vs auto-increment
- Over ~50M rows: random inserts start causing measurable page splits and cache misses
- Solution 1: Use UUID v7 — timestamp-prefixed, inserts sequentially
- Solution 2: Store UUID as BINARY(16)
instead of CHAR(36) — halves storage, improves index efficiency - Solution 3: Surrogate key pattern — keep an internal INT AUTO_INCREMENT
primary key, add the UUID as a UNIQUE secondary index for external use
`sql
-- Surrogate key pattern
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- internal
uuid BINARY(16) UNIQUE NOT NULL, -- external
customer_id BIGINT,
created_at TIMESTAMP
);
-- Insert with generated UUID
INSERT INTO orders (uuid, customer_id)
VALUES (UNHEX(REPLACE(UUID(), '-', '')), 42);
`
Generating UUIDs in Code
JavaScript (modern, no library needed):
<code>javascript
<p>const id = crypto.randomUUID();</p>
<p>// → 'f47ac10b-58cc-4372-a567-0e02b2c3d479'</p>
<p>// Works in Node.js 14.17+ and all modern browsers</p>
</code>
Python:
<code>python
<p>import uuid</p>
<p>str(uuid.uuid4()) # → 'f47ac10b-58cc-4372-a567-0e02b2c3d479'</p>
</code>
PostgreSQL:
`sql
-- Built-in since PG 13
SELECT gen_random_uuid();
-- Or with uuid-ossp extension
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
`
MySQL:
<code>sql
<p>SELECT UUID(); -- Returns UUID v1 (timestamp-based)</p>
</code>`
For quick generation without writing code, use the SnappyTools UUID Generator — generates UUID v1 and v4 in bulk, client-side, no account needed.
Decision Matrix
| Situation | Recommended ID |
|-----------|---------------|
| Internal tables, no public API exposure | Auto-increment INT |
| Public API with resources users access by URL | UUID v4 |
| High-write distributed system | UUID v7 (or ULID) |
| Need IDs on the client before server sync | UUID v4 |
| Need stable deterministic IDs | UUID v5 |
| Need readable IDs | NanoID or CUID |
| Need short, URL-safe IDs | NanoID |
Summary
Auto-increment IDs are fast and simple — the right choice for internal tables that never appear in public URLs. UUIDs are the right choice the moment your IDs become part of your public interface or your system needs to generate IDs without central coordination.
UUID v4 for general use. UUID v7 when you need sortability. UUID v5 when you need determinism. And if you're on a new application with no legacy constraints, UUID v7 is the modern default to reach for.
Originally published at https://snappytools.app/uuid-generator/