GoKawiilSQLite improving performance with pre-sort 07 Jun 2026
In the last post we showed how the randomness of UUID4 can have a large impact on insert speed and how UUID7 can help. But, what about other data that might have random qualities? Where we can't reach for UUID7 to solve our problems?
Random data
We will use a 160-bit (20 byte) random value generated by SecureRandom similar to the one described in this article. Why? Because for things like session tokens you probably do not want to use UUID7 (leaks information, might not have enough entropy for your use case etc). This could also represent any data that has a primary key that is random (or more specifically unordered).
Here's the code to generate them:
( defn random-unguessable-uid [] ( let [buffer ( byte-array 20 ) ] ( .nextBytes secure-random buffer )))
Let's see how this performs.
( d/q writer ["CREATE TABLE IF NOT EXISTS event ( id BLOB PRIMARY KEY, data BLOB ) WITHOUT ROWID"] ) ( dotimes [_ 10] ( time ( d/with-write-tx [db writer] ( dotimes [_ 1000000] ( d/q db ["INSERT INTO event ( id, data ) values ( ?, ? ) " ( random-unguessable-id ) data] )))))
Results:
total rows time in ms 1000000 2478 2000000 4927 3000000 6262 4000000 7195 5000000 8257 6000000 8704 7000000 9244 8000000 9771 9000000 10387 10000000 11103
... continue reading