📝

SQLite WAL Mode — Pros and Cons

How Write-Ahead Logging solves SQLite concurrency problems

In SQLite's default mode (DELETE journal), reads are blocked during writes. WAL (Write-Ahead Logging) mode overcomes this limitation.

How It Works

In the default DELETE mode, changes are written directly to the DB file while the original is backed up to a journal file. In WAL mode, it's reversed — changes are first recorded in a separate -wal file, then later applied to the DB file (checkpoint).

Thanks to this structure, readers access the original DB file while writers go to the WAL file, so they don't block each other.

Perfect Match with Solid Queue22

Solid Queue stores background jobs in SQLite with frequent INSERT/UPDATE operations. This is exactly the case WAL mode solves. In DELETE mode, web request DB reads would be blocked during job processing, causing response delays.

Enabling WAL Mode

Method 1: Rails 8 (database.yml) — Recommended

Set journal_mode: wal in config/database.yml pragmas. This is the Rails 8 default.

Method 2: Direct SQL

sqlite3 storage/production.sqlite3 "PRAGMA journal_mode=WAL;"

Rails console: ActiveRecord::Base.connection.execute("PRAGMA journal_mode=WAL")

Once set, it's permanently stored in the DB file. WAL mode persists across app restarts.

Disabling WAL Mode (Revert to DELETE)

sqlite3 storage/production.sqlite3 "PRAGMA journal_mode=DELETE;"

Rails: ActiveRecord::Base.connection.execute("PRAGMA journal_mode=DELETE")

Caution: When switching WAL → DELETE, no open connections should exist. Stop the app first. After switching, -wal and -shm files are automatically deleted.

Checking Current Mode

sqlite3 storage/production.sqlite3 "PRAGMA journal_mode;"

Rails: ActiveRecord::Base.connection.execute("PRAGMA journal_mode").first

DELETE Journal vs WAL — How They Work

Aspect	DELETE (Default)	WAL Mode
Write Method	Direct overwrite to DB file	Append to -wal file
Read + Write Concurrent	No (reads blocked too)	Yes
Write + Write Concurrent	No	No (Single Writer)
BusyException	Frequent	Dramatically reduced
Files Managed	DB + journal (2)	DB + -wal + -shm (3)
NFS Compatible	Yes	No (corruption risk)

Assessment for This Project

Concern	Applicability
Multi-machine access	No issue with single Fly.io machine
WAL file bloat	SQLite auto-checkpoints (default 1000 pages)
Backup caution	`sqlite3 .backup` command is safe
Solid Queue write contention	Exactly the case WAL solves

Conclusion: Pros overwhelmingly outweigh cons. Rails 8 SQLite guide also recommends WAL mode in production.

Rails 8 database.yml Default Config

production:
  primary:
    <<: *default
    database: storage/production.sqlite3
    pragmas:
      journal_mode: wal
      synchronous: normal
      mmap_size: 134217728       # 128MB
      journal_size_limit: 67108864  # 64MB
      busy_timeout: 5000         # 5 seconds

Checkpoint Types

Type	Behavior	Blocking
PASSIVE	Applies what it can without interfering	None
FULL	Applies all frames (default auto)	Minimal
RESTART	FULL + restarts WAL from beginning	Medium
TRUNCATE	RESTART + truncates WAL to zero	High

Key Points

Enable WAL mode: PRAGMA journal_mode=WAL (Rails 8 default)

Concurrent reader/writer access enabled — impossible in DELETE mode

Auto checkpoint applies changes to DB when WAL reaches 1000 pages (~4MB)

Adjust Writer lock wait with busy_timeout (Rails 8: 5000ms default)

Include .sqlite3 + -wal + -shm in backups (sqlite3 .backup recommended)

Pros

✓ Concurrent reader/writer access — DELETE mode blocks reads during writes
✓ Improved write performance — frequent small transactions (exactly Solid Queue's case)
✓ Dramatically fewer BusyExceptions — readers and writers don't block each other
✓ Fewer fsync calls — only needed at checkpoints, reducing I/O overhead
✓ Rails 8 default — applied immediately with no extra configuration

Cons

✗ WAL file bloat — delayed checkpoints cause -wal file to keep growing (increased disk usage)
✗ NFS/network filesystem incompatible — corruption risk with multi-machine access (single Fly.io machine is fine)
✗ Reads may be very slightly slower — must check WAL file too (barely noticeable in practice)
✗ Backup caution — cannot copy just .sqlite3 file, must include -wal and -shm files
✗ Three files to manage — DB + -wal + -shm, must always handle as a set

Use Cases

Rails 8 + Solid Queue — concurrent job writes and web request reads Solid Cache/Cable — frequent small transactions for cache and WebSocket Read-heavy apps where occasional writes must not block reads Fly.io single machine deploy — WAL's NFS limitation is not an issue

💎

Rails 8 + SQLite — Why It Works in Production

The era of no PostgreSQL has arrived — SQLite in production proven by 37signals

→

← 💎 Rails 8 + SQLite — Why It Works in Production 🔒 Tenant Isolation Query Patterns — Start from Organization Scope, Not Model.where →