How SQLite Scales Read Concurrency

Fly.io enables running apps close to users worldwide by upgrading containers to virtual machines on their hardware. SQLite, often criticized for not scaling well or being a single-user database, has evolved since its rollback journal days with the introduction of the write-ahead log (WAL) in 2010. The WAL allows concurrent reading and writing by writing new versions of pages to another file while leaving the original page intact in the main database file. This enables SQLite to scale better, as demonstrated through a sandwich shop analogy. Enabling the WAL involves setting journal_mode to wal using a PRAGMA command. The WAL file starts with a 32-byte header containing information such as magic number, format version, page size, checkpoint sequence number, and salt value for integrity checks. SQLite's checkpointing procedure copies the latest versions of pages from the WAL back into the main database file to prevent it from growing too large. The SHM index helps in quickly looking up the latest version of any given page for a transaction by storing page numbers and hash map positions. Most applications will benefit from using WAL mode, with rare exceptions where rollback journal might be more suitable.