Random UUIDs as SQLite Primary Keys Can Slow Inserts 10x at Scale

Using a random UUIDv4 as a primary key in SQLite can degrade write performance by up to ten times as a table grows, according to benchmarks published by developer Anders Murphy. The problem stems from how SQLite stores rows in a B-tree sorted by the primary key, meaning random UUIDs force the database to locate, read, and rewrite scattered pages across the entire file. In contrast, sequential keys like auto-incrementing integers always append to the end of the tree, keeping disk I/O minimal and speed consistent. Murphy's test inserting 100 million rows showed integer and time-ordered UUIDv7 keys maintaining roughly one million inserts per second throughout, while UUIDv4 slowed from 2.6 seconds per million-row batch to over 12 seconds. Switching to UUIDv7, which is time-ordered rather than random, preserves the benefits of UUIDs while avoiding the performance penalty.