dumbledorf on The perils of UUID primary keys in SQLite

Comment on The perils of UUID primary keys in SQLite

dumbledorf 3mo

Wait how is sqlite doing a million inserts a second?

kg 3mo

sqlite is really fast. I'm surprised it's only a million.

andersmurphy 3mo

It's running on an M1 mac with synchronous full. Wouldn't surprise me if it's possible to get higher numbers.

JSR_FDED 3mo

In batches

smitty1e 3mo

':memory:'

https://sqlite.org/inmemorydb.html

KPGv2 3mo

Except this source code is not using :memory: The linked source code has

    (defonce db
      (d/init-db! "db/db.db"
        {:pool-size 4 :pragma {:synchronous "FULL"}}))

That's writing to disk.

andersmurphy 3mo

Yes it's writing to disk (on a M1 mac which has terribly slow fsync). But, because of the transaction the fsync dance is done once per batch. Each row is the id + a 50 byte data blob.

There's only one index so there's no real write amplification. The numbers will go down as you add more data and indexes.

Comments