I’ve been working with SQLite lately. It has become my go-to database for all projects!

Blocking writes

One problem I encountered was that, by default, it uses rollback journal, where any write to the database will also block all reads. Because of this, my fedidevs.com site became unresponsive for about an hour every night while the nightly job inserted fresh data.

The solution was to enable Write-Ahead Logging. WAL allows multiple readers to access the database simultaneously, even if the table is being written to simultaneously. The link above has a few disadvantages listed, but for most web-server use cases, WAL is the better option.

Enabling WAL

Enable WAL by setting the journal_mode to WAL:

sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;'

The PRAGMA command only has to be run once per database. The setting is persistent.

.wal files

When WAL is enabled, SQLite will create .wal and .shm files. The .wal file records transactions committed but not yet applied to the main database. The .shm file is used for shared memory and caching.

Do remember to keep an eye on your .wal file sizes. Certain operations (like VACUUM) can make them grow as large or even larger than the database itself. If that happens to you as it did to me, you can regain the disk space by running the wal_checkpoint command:

sqlite3 db.sqlite3 'PRAGMA wal_checkpoint(TRUNCATE);'

The article SQLite: Vacuuming the WALs is worth a read if you need to run VACUUM often.

Conclusion

With WAL enabled and the VACUUM command removed, fedidevs.com (and other sites) are running smoothly - bound only by the fact that my Raspberry Pi runs on a very slow SD card 😅

I’ll write a post about my Raspberry Pi setup in the future.