Why Switch From Auto-Increment IDs to UUIDs (and What Might Break)

An AUTO_INCREMENT/IDENTITY column is the path of least resistance for a primary key — until the system stops being a single database. The moment you introduce multiple writers, offline clients, multi-region deployments, or a need to generate IDs before a row is ever inserted, sequential integers start actively working against you. This post covers the concrete technical reasons teams switch to UUIDs, and — just as importantly — the specific things that break when they do, because "just change the column type" is rarely the whole migration.

---

Why Auto-Increment Breaks Down

The single-writer bottleneck

IDENTITY/AUTO_INCREMENT guarantees uniqueness by serializing ID assignment through one counter, owned by one database instance. That's fine until you need more than one instance accepting writes — multi-region active-active deployments, offline-capable mobile clients, or horizontally sharded databases all need something that can generate a valid, collision-free ID without asking a central authority first.

-- This single sequence is a hard serialization point.
-- Every insert across every service, every region, waits on it.
CREATE TABLE Orders (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL
);

UUIDs (or any of the structured alternatives covered below) solve this by making uniqueness a property of the value itself — 122 bits of randomness, or a timestamp plus a pre-assigned identity range — rather than a property of a shared counter.

Merging data across environments

Auto-increment IDs collide by construction the moment you try to merge datasets that were generated independently — two staging databases, an acquired company's database, or offline-then-synced mobile data. Order #4519 means something different in every database that has its own counter. Merging requires remapping every ID and every foreign key that references it, a genuinely painful migration in a system of any size. Globally unique IDs make merges close to mechanical.

IDs needed before the insert happens

Auto-increment values don't exist until the database assigns them, which means any code that needs to reference the ID before the row is committed — building a batch of related child records, returning an ID to a client that will immediately use it in a follow-up request, event sourcing patterns — has to do an extra round trip to get it. A client-generated UUID sidesteps this entirely:

// No round trip needed — the ID exists before any database call
var orderId = Guid.CreateVersion7();

var order = new Order { Id = orderId, CustomerId = customerId };
var lineItems = items.Select(i => new OrderLine { OrderId = orderId, ... });

await _db.Orders.AddAsync(order);
await _db.OrderLines.AddRangeAsync(lineItems);
await _db.SaveChangesAsync(); // single round trip, IDs already known

Enumeration and information leakage

Sequential IDs expose your row count and creation order to anyone who can see one of them. /invoices/4518 tells a curious (or malicious) user that /invoices/4517 and /invoices/4519 probably exist too, and roughly how many invoices your system has generated in total. This is a real, if often secondary, motivation — UUIDs aren't an authorization mechanism (see the common mistakes post on that specific pitfall), but they do close off casual enumeration as an attack surface.

---

What Actually Breaks When You Switch

1. Clustered index write performance

This is the best-documented cost and deserves an honest framing rather than folklore. A random UUIDv4 as a clustered/primary key produces measurably worse insert throughput and page fill ratio than a sequential integer — our own benchmark measured roughly 3x slower insert throughput for UUIDv4 vs. BIGINT under one specific test setup, and the fragmentation deep dive found the effect real but workload-dependent, not catastrophic by default.

Mitigation: use UUIDv7 instead of UUIDv4 for anything that will be a clustered key — it recovers most of the throughput gap while keeping coordination-free generation.

2. Storage size, everywhere the key is referenced

An 8-byte BIGINT becomes a 16-byte UUID — twice the space on the primary table, and the same doubling on every foreign key column referencing it. On a normalized schema with several child tables per parent, this compounds quickly and isn't limited to the primary key table alone.

-- Every one of these FK columns doubles in size after migration
CREATE TABLE OrderLines (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID()),
    OrderId UNIQUEIDENTIFIER NOT NULL REFERENCES Orders(Id), -- was 8 bytes, now 16
    ProductId UNIQUEIDENTIFIER NOT NULL REFERENCES Products(Id) -- same
);

3. URL and log readability

/orders/4518 is short, typeable, and immediately recognizable in a log line. /orders/550e8400-e29b-41d4-a716-446655440000 is none of those things. This is a genuine ergonomics regression for support workflows, debugging sessions, and manually-shared links — not a performance issue, but a real cost worth naming rather than dismissing.

Mitigation: for user-facing, low-volume identifiers specifically (not high-write primary keys), a shorter scheme like NanoID (covered in its own post) or a short, separately-generated public slug alongside the internal UUID can recover some of this.

4. Cursor-based pagination assumptions

Pagination code that assumes IDs are monotonically increasing integers — WHERE Id > @lastSeenId ORDER BY Id — breaks silently with UUIDv4, because ID order and insertion order have no relationship at all. This is one of the most common "why did our API start returning duplicate/missing pages" bugs after a UUID migration.

-- Breaks with UUIDv4 — "greater than" no longer means "created after"
SELECT TOP 20 * FROM Orders WHERE Id > @LastSeenId ORDER BY Id;

-- Fix 1: paginate on a real timestamp column instead
SELECT TOP 20 * FROM Orders
WHERE CreatedAt > @LastSeenCreatedAt
ORDER BY CreatedAt;

-- Fix 2: switch to UUIDv7 — ID order and creation order are (mostly) the same again
SELECT TOP 20 * FROM Orders WHERE Id > @LastSeenId ORDER BY Id;

5. Downstream systems and integration contracts

Any external API consumer, webhook payload schema, analytics pipeline, or third-party integration that has orderId: integer baked into its contract will break on a UUID migration — this is an API versioning problem as much as a database one, and it's easy to underestimate how many systems quietly assume numeric IDs (spreadsheet exports, BI tools, CSV imports, even some ORMs' default type inference).

// A common silent breakage: JS treats large integers as IEEE-754 doubles,
// losing precision past 2^53. This was already a latent risk with BIGINT
// IDs over that threshold, but UUID migrations are exactly when teams
// finally audit for it, since the type change forces every consumer
// to be checked.
const unsafeId = 9007199254740993; // silently rounds in JS
const safeId = "9007199254740993"; // string form — but now the same
                                     // question resurfaces for UUID strings:
                                     // are all consumers treating IDs as
                                     // opaque strings, not numbers?

6. ORM and framework defaults

Many ORMs assume an auto-incrementing integer primary key unless told otherwise, and generate SQL, migrations, and change-tracking logic around that assumption.

// EF Core: must explicitly override the default key generation strategy
public class Order
{
    public Guid Id { get; set; } = Guid.CreateVersion7(); // client-generated, not DB-generated
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .Property(o => o.Id)
        .ValueGeneratedNever(); // tell EF Core the app owns ID generation, not the DB
}

Getting this wrong is a common source of a specific bug class: the ORM silently sends an all-zeros GUID or lets the database generate one via a column default while the application code also generates its own, resulting in two different values racing against each other depending on the code path taken.

7. Existing operational tooling

Database administration tools, sharding utilities, and some replication mechanisms that assume integer keys for range-based partitioning need to be re-evaluated. Range partitioning strategies built around "rows 1–1,000,000 in partition A" don't have an obvious UUID equivalent — hash-based partitioning becomes the more natural fit, which may itself be a schema and tooling change beyond just the column type.

---

Comparison: Migration Cost by Alternative

Not every "switch to UUIDs" migration has to mean UUIDv4. The right choice significantly changes how much of the above actually applies:

For most teams migrating specifically to solve the distributed-write-coordination problem, UUIDv7 as the primary key is the pragmatic middle ground: it keeps the operational simplicity of "just a UUID column, standard across every language and database," while avoiding the worst of UUIDv4's write and pagination costs.

---

A Pragmatic Migration Path

1. Add a UUID column alongside the existing integer key first — don't do a single flag-day cutover. Backfill it, dual-write it, and let consumers migrate incrementally.
2. Choose UUIDv7 unless you have a specific reason for v4 (e.g., a security requirement that creation time never be inferable from the ID — UUIDv7 does expose an approximate timestamp).
3. Audit every foreign key, every pagination query, and every external contract referencing the old key — this is almost always larger in scope than the schema change itself.
4. Update ORM configuration explicitly — don't rely on default key-generation behavior surviving the type change silently.
5. Re-benchmark, don't assume — run the same insert/query tests against your actual schema and data volume before and after, using a methodology like the one in our benchmark post, rather than trusting generic numbers from any blog post, including this one.

The switch is usually the right call once you have more than one writer, but it's a schema-and-contract migration, not a column-type edit — the auto-increment integer was quietly load-bearing in more places than the primary key definition alone.