SQLite Primary Key Benchmarks: UUIDv7, UUIDv4, Snowflake, Integer

Choosing IDs in SQLite

Picking the ID for a database is likely the first important decision you’ll make. It’s hard to change later.

Furthermore, if you are exposing IDs to the client, then you can’t use Integer unless you’re ok with leaking information (e.g. how many users there are). So you’re left with UUIDv4, UUIDv7 via SipHash-masked UUIDv4, or dual column strategy that PlanetScale uses.

SQLite also has the option of tables without rowid, which is an optimization with various trade-offs.

Strategies

Here are the various strategies I tested:

• Integer: Your standard autoincrement integer (i64)
• Snowflake: A 64-bit integer with a timestamp and a sequence number
• UUID4: UUIDv4 stored as a BLOB
• UUID7: UUIDv7 stored as a BLOB
• UUID7WithoutRowid: UUIDv7 stored as a BLOB without rowid
• IntegerWithUuid4Col: Integer with a UUID4 column
• IntegerWithUuid4ColNoIndex: Integer with a UUID4 column without an index
• SnowflakeWithUuid4Col: Snowflake with a UUID4 column
• Uuid7WithUuid4Col: UUIDv7 with a UUID4 column
• Uuid7WithUuid4ColWithoutRowid: UUIDv7 with a UUID4 column without an index
• Uuid7WithUuid4ToUuid7Mapping: UUIDv7 with a UUID4 column without an index, using an additional mapping table to UUIDv7

IntegerWithUuid4ColNoIndex was added to act as a control for the dual column strategies, to learn how the additional unique index affects performance.

Methodology

I tested with 200000 user rows, and a few more tables to mimic a small real-world application, using rsqlite and criterion for the benchmarks. These were run on my MacBook M4 Pro.

Since SQLite isn’t supposed to be a high-performance database, I didn’t set the number of rows higher. 200k sounds about right for a small SaaS.

SQLite was configured with the following settings:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA trusted_schema = OFF;
PRAGMA busy_timeout = 5000;
PRAGMA page_size = 8192;
PRAGMA temp_store = MEMORY;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 67108864;
PRAGMA auto_vacuum = INCREMENTAL;

Results

Note: The “Mixed CRUD” benchmark results are a bit noisy. I wasn’t fully able to get consistent results, which may explain why IntegerWithUuid4ColNoIndex is faster than Integer.

Conclusion

• Integer is the fastest, no comparison.
• Dual column strategies with integer as the primary key takes up less space, and faster than pure UUID column strategies.
• UUID without Row IDs are slower to insert, but faster to query.
• Very little difference between UUIDv4 and UUIDv7.
• No benefit to using a mapping table.

So what should you use? It all boils down to your use case:

• If you don’t have to expose IDs to the client -> Integer.
• If you have to expose IDs, and absolutely have to squeeze out every last bit of performance -> Integer primary key with a UUID column.
• If each row is less than about 1/20th the size of a database page -> UUIDv7WithoutRowid/UUIDv4WithoutRowid.