SQLite ID Benchmarks

Published Oct 15, 2025 - 3 min read

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:

sql

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

Strategy	Insert Throughput (Kelem/s)	Database Size (MB)	Avg Age Query Time (ms)	Random Select Time (µs)	Join Paginated Time (µs)	Select Orders Time (µs)	Mixed CRUD Throughput (Kelem/s)	Mixed CRUD Time (ms)
Integer	4640.0	55.47	3.6818	1.6541	147.71	4.4939	17.123	5.8401
IntegerWithUuid4ColNoIndex	3696.0	58.75	4.4946	1.7171	157.17	4.3855	18.163	5.5056
Snowflake	3252.7	63.04	4.1183	1.6940	158.82	4.4169	16.951	5.8993
IntegerWithUuid4Col	510.91	63.79	4.6581	1.7223	167.13	4.3678	15.953	6.2686
SnowflakeWithUuid4Col	418.10	72.43	5.2922	1.7853	179.81	4.5050	16.285	6.1407
Uuid4	500.03	80.10	4.2762	2.6851	217.89	4.5930	13.272	7.5346
Uuid4WithoutRowid	428.77	75.10	4.3679	1.8997	181.56	4.7315	15.124	6.6119
Uuid7	510.70	80.15	4.2995	2.6551	212.82	4.6973	12.954	7.7194
Uuid7WithoutRowid	429.42	75.05	4.5979	1.9021	178.80	4.5704	15.072	6.6348
Uuid7WithUuid4Col	206.57	88.63	5.3424	2.7702	214.42	4.6885	13.769	7.2626
Uuid7WithUuid4ColWithoutRowid	150.87	86.56	5.5200	1.9777	207.81	4.8204	14.791	6.7607
Uuid7WithUuid4ToUuid7Mapping	98.808	91.19	4.3770	1.9445	180.77	4.6546	16.085	6.2169

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.