Your SQLite Inserts Got 10 Slower — and a Random UUID Did It

You switched your primary key from an auto-incrementing integer to a random UUID. There are good reasons to: IDs you can generate on the client without a round trip, IDs that don't leak how many rows you have, IDs that won't collide when you merge two databases.

At first everything is fine. Inserts are fast. Then the table grows. And grows. And one day you notice writes are crawling — not a little slower, ten times slower — and nothing in your code changed.

The primary key did it. Here's exactly why, and the one-line fix.

First: in SQLite, the primary key is the table

This is the part most people skip, and it's the whole story.

Every ordinary SQLite table has a hidden 64-bit integer key called the rowid. The table's rows are stored in a B-tree, physically sorted by that rowid. That's a clustered index: the index and the table data are the same structure. The order of the key is the order of the bytes on disk.

When you write INTEGER PRIMARY KEY, your column simply becomes the rowid. When you write WITHOUT ROWID and give your own primary key, your key becomes the clustered index — the thing that decides where each row physically lands. (SQLite's own docs spell this out under "the WITHOUT ROWID optimization.")

So the value of your primary key isn't just an identifier. It's an address. And whether those addresses arrive in order or at random turns out to matter enormously.

The benchmark

Anders Murphy ran a clean version of this and published the numbers. He inserted 100 million rows, in batches of one million, and timed each batch — once with an integer primary key, once with a random UUIDv4 primary key (WITHOUT ROWID), and once with a time-ordered UUIDv7. (His full benchmark code is linked at the end.) Times below are milliseconds per one-million-row batch.

total rows	integer key	UUIDv4 (random)	UUIDv7 (time-ordered)
10 M	1,208	2,649	1,372
50 M	1,086	9,359	1,256
100 M	1,081	12,586	1,258

Two columns are flat; the middle one is not. The integer baseline does about a million inserts per second across the entire 100-million-row run — the table gets huge, the speed doesn't budge. UUIDv7 sits right next to it. UUIDv4 starts ~2× slower and keeps getting worse — from 2.6 seconds per batch to 12.6 seconds by the end.

That's the shape that bites you in production: it looks fine in testing with a small table, then degrades as real data piles up. The cause is the part of the table you already see — UUIDv4 is random, the other two are sequential — and the next section is why that one property matters this much.

Why random keys hurt: the B-tree has to keep rearranging itself

A B-tree stays sorted. New entries go in their sorted position, and when a page (a fixed-size block of the tree) fills up, it splits.

With an integer key that always counts up, every new row belongs at the end. You touch the same last page over and over, it fills, it splits once, you move on. The pages you're not writing to can stay out on disk; you don't need them in memory.

With a random UUID, the next row could belong anywhere — page 3, then page 90,000, then page 412. SQLite has to pull that page into memory, insert into the middle, split it if it's full, and write it back. Across millions of rows scattered across the whole tree, you're constantly reading and rewriting pages all over the file and rebalancing the tree. Murphy profiled the two runs and the difference is exactly that: far more time spent reading, writing, and balancing the tree.

Integer key (ordered)          Random UUID (unordered)
inserts land here:             inserts land everywhere:

[..][..][..][NEW]              [.N][..][N.][..][.N][N.]
            ▲                   ▲   split  ▲      split
       one hot page            random pages, constant splits

It's not that UUIDs are big (they are a bit bigger — more on that below). It's that they arrive in random order, and a clustered index pays for disorder.

Is this actually your problem?

Two signs together. First, inserts get slower as the table grows instead of staying flat — quick in a fresh test database, sluggish once real data piles up. Second, your schema clusters on a random key: a TEXT or BLOB PRIMARY KEY holding a UUIDv4, especially with WITHOUT ROWID. If both are true, you've almost certainly found it. If your inserts are slow but flat from the start, that's a different problem (transaction batching, fsync, indexes on other columns) — this one specifically gets worse over time.

The fix: a time-ordered UUID (version 7)

You don't have to give up UUIDs. You just need ones that arrive roughly in order.

That's exactly what UUID version 7 is. A UUIDv7 puts a 48-bit Unix-millisecond timestamp at the front, followed by random bits. So two IDs generated a moment apart sort next to each other — the values climb over time, just like an integer, while still being globally unique and effectively unguessable.

The third column of the benchmark table above is the result of swapping UUIDv4 for UUIDv7 in the same setup. Flat again, right back down next to the integer baseline. Same UUID benefits, none of the rebalancing tax — because the inserts are once again landing at the end of the tree instead of scattered through it.

Doing it in Go

You don't need anything exotic. github.com/google/uuid generates v7 with uuid.NewV7(), and modernc.org/sqlite is a pure-Go driver (no C compiler) that registers as "sqlite". Store the UUID as a 16-byte BLOB, and declare the table WITHOUT ROWID so your key is the clustered index:

import (
    "database/sql"
    "github.com/google/uuid"
    _ "modernc.org/sqlite"
)

db, _ := sql.Open("sqlite", "app.db")

db.Exec(`CREATE TABLE event (
    id   BLOB PRIMARY KEY,
    data BLOB
) WITHOUT ROWID`)

id, _ := uuid.NewV7()          // time-ordered — NOT uuid.New(), which is v4 (random)
db.Exec(`INSERT INTO event (id, data) VALUES (?, ?)`, id[:], data)

The only real change from a v4 setup is NewV7() instead of New(). That one call is the difference between the flat line and the 10× cliff.

A note on the driver: the fix lives in SQLite, not in Go, so it's the same whichever driver you pick. modernc.org/sqlite above is the well-worn pure-Go choice. If you want more than a bare driver, github.com/go-again/sqlite packs a whole ecosystem into one pure-Go, CGo-free package — vector search, full-text search (FTS5), encryption at rest, in-memory databases, and a catalog of loadable SQL extensions — as a drop-in replacement for the usual drivers. It's a newer project, so weigh that against how much you need those batteries, but the WITHOUT ROWID plus NewV7() pattern is identical either way.

Store it as 16 bytes, not 36 characters

There's a second, quieter cost: how you store the UUID. It's tempting to keep it as text — the familiar 550e8400-e29b-41d4-a716-446655440000. But that string is 36 characters, so 36 bytes. The raw value is only 128 bits — 16 bytes. Storing it as text more than doubles the size of your primary key, and since the key is the clustered index, a fatter key means fewer rows per page and more pages to read for the same work.

So store the UUID as a 16-byte BLOB, exactly like the Go example does with id[:]. SQLite compares blobs byte by byte, and a v7 UUID's bytes are laid out with the timestamp first — so byte order is time order, which is the property that keeps inserts landing at the end of the tree.

If you really want them human-readable in the database, at least use v7: a v7 UUID's string form also sorts in time order, because the leading hex digits are the timestamp. You'll pay the size but not the rebalancing. A v4 stored as text is the worst of both worlds — big and random.

Or: keep the integer key and put the UUID beside it

You don't always have to make the UUID your primary key. A boring, effective layout: keep the integer rowid as the primary key so the table stays append-only, and add the UUID as its own indexed column.

CREATE TABLE event (
    id   INTEGER PRIMARY KEY,   -- rowid: append-only, fast inserts
    uuid BLOB UNIQUE,           -- your external id, with its own index
    data BLOB
);

Now rows land at the end of the table by rowid, and the random UUID only disturbs its own secondary index. That index is far smaller than the whole table — it holds just the UUID and a pointer back to the row — so the rebalancing it pays is a fraction of clustering every full row by a random value. You still hand the UUID to the outside world and look rows up by it; SQLite just isn't sorting your entire table on it. Use v7 here too and even that index stays tidy.

The tradeoff: a lookup by UUID hops through the index to find the rowid, then to the row — one extra step versus a clustered key. For nearly every workload that's invisible, and the fast inserts come for free.

What about a table that's already full of v4?

You can't un-randomize IDs you've already handed out — other tables, caches, and clients reference them, so rewriting them isn't an option. The honest migration is two moves, neither dramatic:

1. Stop the bleeding. Switch your ID generation to NewV7() for new rows. The table stops getting worse immediately: every new insert now lands in time order, clustered together at a moving point in the tree instead of scattered across the whole thing, even while the old random keys sit where they are.
2. Optionally defragment once. Run VACUUM. It rewrites the database file, repacking pages in key order and reclaiming the slack that all those mid-tree splits left behind:

VACUUM;

It's a one-time, whole-file operation and needs free disk space roughly equal to the database, so schedule it — but afterward reads are tighter and the file is smaller.

What you don't do is try to be clever and regenerate keys. The win is almost entirely in step 1 — new rows behaving — and that's a one-line change in your code.

The honest caveats

• A v7 UUID leaks its creation time. The timestamp is right there in the first 48 bits. If you specifically chose v4 so IDs reveal nothing, v7 gives that up. For most apps it's a non-issue; for some it isn't.
• 16 bytes vs 8. A UUID blob key is twice the size of an integer rowid, so v7 lands slightly above the integer baseline (≈1.25s vs ≈1.08s in the numbers above). That's the price of a bigger key, and it's small and constant — not the runaway you get from v4.
• It's not just SQLite. Any database whose primary key is a clustered index has this problem. MySQL's InnoDB clusters on the primary key too, so random UUIDs fragment it the same way. PostgreSQL stores rows in a heap rather than clustering on the key, so it's less dramatic — but its indexes still fragment, and ordered keys still help.

What to take away

• In SQLite the primary key is the clustered index — it decides where rows physically live, so key order drives insert speed.
• Random UUIDv4 keys arrive out of order, forcing constant page splits and B-tree rebalancing. The cost grows with the table: ~2× slow at 10M rows, ~10× by 100M.
• UUIDv7 prepends a timestamp, so inserts land in order and performance returns to near the integer baseline — while keeping the reasons you wanted UUIDs.
• In Go: uuid.NewV7(), stored as a BLOB in a WITHOUT ROWID table. One function call.
• Same lesson for any clustered-index database (MySQL/InnoDB especially). When inserts mysteriously slow down as a table grows, look at whether your key arrives in order.

If your write throughput has been quietly sliding and you're on random UUIDs, you've probably found it. The fix is a smaller change than you'd think.

Comments

[Luis]

🧠 The real problem: UUIDs destroy insert locality in SQLite

The article explains a subtle but very real performance trap:

Switching from an auto-increment integer primary key to a random UUIDv4 can make SQLite inserts ~10× slower over time.

At small scale, everything looks fine. But as the table grows, performance collapses.

⚙️ Why this happens (the real mechanism)

SQLite stores tables as a B-tree clustered around the primary key.

That means:

Integer IDs → sequential inserts → append to the right side of the tree
UUIDv4 → random inserts → land everywhere in the tree

So instead of:

“write at the end of the structure”

SQLite must constantly:

split pages
rebalance B-tree nodes
pull random pages into memory
increase disk I/O churn

This is why performance degrades non-linearly with size.

📉 Why it suddenly becomes “10× slower”

It’s not gradual overhead — it’s structure collapse under scale:

cache locality breaks
pages stop being reused efficiently
write amplification increases
index fragmentation grows fast

Eventually, inserts spend more time maintaining structure than writing data.

🆚 The hidden comparison
Key type Insert pattern Performance behavior
INTEGER AUTOINCREMENT sequential stable, fast
UUIDv4 random degrades with size
UUIDv7 time-ordered mostly stable

Modern discussions often push UUIDv7 as a fix because it restores ordering while keeping global uniqueness.

🧩 The deeper takeaway

This isn’t really a “UUID problem.”

It’s a B-tree locality problem.

The mistake is assuming:

“primary key choice is just a schema detail”

When in reality, it defines:

disk layout
cache efficiency
write amplification
long-term scaling curve
🚀 Practical guidance

If you're using SQLite:

Prefer INTEGER PRIMARY KEY for hot-write tables
If you need UUIDs:
prefer UUIDv7 (time-ordered)
avoid pure random UUIDv4 for high-write tables
Batch inserts + transactions matter more than people expect (often 10–100× gains)