Why UUIDs Beat Integers as Primary Keys (And Why Performance Isn’t the Issue)

One of the first schema decisions you face when designing a database table is: Should I use an INT or a UUID as the primary key?

Most developers default to an auto-incrementing integer. It’s simple, compact, and familiar. But UUIDs (a.k.a. GUIDs) are increasingly popular — and many of the old performance objections don’t hold up with modern hardware.

Let’s break this down like developers, not DB theorists.

What’s a UUID, Really?

At the end of the day, a UUID is just a number. Specifically: a 128-bit number.

Compare that to the types you already know:

That’s it. No magic. Just more space.

Why Developers Like UUIDs

1. Generate Anywhere

With integers, the database usually assigns the ID:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

You don’t know the ID until after the row is inserted. With UUIDs, you can generate the ID in your application:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  name VARCHAR(50)
);

Then in code:

import uuid
user_id = uuid.uuid4()

No database round trip just to get a key.

2. Merge Without Collisions

If you’ve ever tried merging data from two databases that both used AUTO_INCREMENT, you know the pain of ID collisions.

-- From DB A
id | name
---+-------
 1 | Alice
 2 | Bob

-- From DB B
id | name
---+-------
 1 | Charlie
 2 | Dana

Good luck merging those.

With UUIDs:

id                                   | name
-------------------------------------+-------
550e8400-e29b-41d4-a716-446655440000 | Alice
ddeb27fb-d9a0-4624-be4d-4615062daed4 | Bob
d8f1f8d4-4b0f-11ee-be56-0242ac120002 | Charlie
42c9aeea-4b0f-11ee-be56-0242ac120002 | Dana

No collisions. Merge away.

3. Safer URLs

Ever seen a URL like /user/12345? Anyone can guess /user/12346.

UUIDs make this harder:

/user/550e8400-e29b-41d4-a716-446655440000

Much less guessable.

The Performance Myth

Here’s the common objection:

“But UUIDs are so much bigger than integers. Won’t that kill performance?”

Not really.

• INT = 4 bytes
• BIGINT = 8 bytes
• UUID = 16 bytes

That’s 12 extra bytes per row compared to an INT. For perspective, a single VARCHAR(255) column can take 255 bytes — orders of magnitude more than the difference between keys.

Modern CPUs handle 128-bit comparisons natively. Databases already juggle kilobytes of row data per query. The cost of comparing two UUIDs instead of two integers is microscopic compared to disk I/O, joins, or network latency.

Real-world bottlenecks are almost never the 16-byte key.

Things to Watch Out For

That said, UUIDs aren’t free candy:

• Bigger indexes → Indexes on UUID columns take more space. If you’re storing billions of rows, that matters.
• Fragmentation → Random UUIDs don’t insert sequentially. This can cause page splits in clustered indexes. Solutions: use UUIDv1 or database-specific functions like UUID_TO_BIN() (MySQL) that produce more sequential values.
• Readability → Debugging 12345 is easier than 550e8400-e29b-41d4-a716-446655440000. Most teams solve this with tooling/log formatting.

Rule of Thumb for Developers

• Use INT or BIGINT if:

  • Your app is small and won’t need cross-database merges.
  • You want compact storage and don’t care about guessable IDs.

• Use UUID if:

  • You’re building distributed or microservice systems.
  • You need to merge data from multiple sources.
  • You want to generate keys outside the database.
  • You want harder-to-guess identifiers.

Perfect 👌 — let’s add a side-by-side benchmark example so developers can see how little difference there actually is between INT and UUID in practice. I’ll base this on PostgreSQL since it has native UUID support, but the same principle applies to MySQL, SQL Server, etc.

Benchmark: INT vs UUID in PostgreSQL

Let’s test with two identical tables:

-- INT-based table
CREATE TABLE users_int (
  id SERIAL PRIMARY KEY,
  name TEXT
);

-- UUID-based table
CREATE TABLE users_uuid (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT
);

We’ll insert 1 million rows into each:

-- Insert into INT table
INSERT INTO users_int (name)
SELECT 'user_' || g
FROM generate_series(1, 1000000) g;

-- Insert into UUID table
INSERT INTO users_uuid (name)
SELECT 'user_' || g
FROM generate_series(1, 1000000) g;

Query Performance

Point lookup by primary key:

EXPLAIN ANALYZE
SELECT * FROM users_int WHERE id = 500000;

Output (abridged):

Index Scan using users_int_pkey on users_int
  (cost=0.42..8.44 rows=1 width=36)
  (actual time=0.018 ms)

Now with UUID:

EXPLAIN ANALYZE
SELECT * FROM users_uuid
WHERE id = '550e8400-e29b-41d4-a716-446655440000';

Output (abridged):

Index Scan using users_uuid_pkey on users_uuid
  (cost=0.42..8.44 rows=1 width=52)
  (actual time=0.019 ms)

The difference? Basically zero. Both queries run in ~0.02ms.

Index Size

Let’s compare index sizes:

SELECT
  relname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_all_indexes
WHERE relname LIKE 'users_%_pkey';

Sample output:

Yes, the UUID index is bigger (about double, which makes sense: 16 bytes vs 8). But even at 1 million rows, we’re only talking tens of MB — trivial on modern systems.

Takeaway for Developers

• Lookup speed: Identical.
• Insert speed: Nearly identical (UUIDs may fragment clustered indexes if totally random, but UUIDv1/sequential UUIDs solve this).
• Storage: UUIDs take more index space, but unless you’re working with billions of rows, it’s not the bottleneck.

The actual performance bottlenecks will almost always be query design, joins, and I/O — not whether your primary key is 4 bytes or 16.

So when someone says “UUIDs are slow”, you can point at query plans and actual numbers: they’re not.

TL;DR

• A UUID is just a 128-bit number, the same way an INT is a 32-bit number.
• The performance hit is negligible on modern hardware.
• UUIDs unlock distributed key generation, collision-free merges, and safer URLs.
• Integers are fine for simple, single-database apps — but UUIDs future-proof your schema.