How (and why) to run SQLite in production

Stephen Margheim's wroclove.rb 2024 single-speaker talk arguing that SQLite is a viable choice for production Rails applications and often a better default than cloud-managed Postgres for solo developers and small teams. Frames SQLite as the 'one-person database engine' paired with Rails as the 'one-person framework' (simplicity, control, speed). Addresses the linear-writes objection by benchmarking: you can run ~10 SQLite writes in the time one self-hosted Postgres write takes on the same machine, ~20-30 across availability zones, and ~1,000 when Postgres is in a different AWS region (using Ben Johnson's 2021 GopherCon data). Uses a Hacker News clone ('laurem news') with a load-testing CLI to reveal four performance problems and their fixes: (1) SQLite3::BusyException under concurrent writes → solve with immediate-mode transactions via the sqlite3 gem's default_transaction_mode (1.6.9+) instead of deferred mode, because Rails always writes inside transactions and deferred mode errors immediately on retry; (2) 5-second latency spikes caused by SQLite's C busy_timeout holding Ruby's GVL → replace with a Ruby-level busy_handler callback that uses Sleep (which releases the GVL), letting Puma workers do real work during DB waits; (3) long-tail p99.9 latency caused by SQLite's native timeout algorithm penalizing older queries with exponential-ish backoff (a 12-entry delays array ending at 100ms) while new queries retry at 1-2ms → replace with a fair constant-1ms retry (now merged into sqlite3 main, aimed at Rails 8); (4) contended connection pool when reads wait on writes → experimental isolated reader/writer connection pools via Active Record's multiple-database support pointing two role configs at the same file, a custom database selector that defaults every request to reading and a monkey-patched transaction method that switches to the writer role for writes ('deferred requests'). Also notes WAL (write-ahead-log) journal mode became the Rails 7.1 default. Resilience section recommends Litestream for point-in-time backups to any S3-compatible bucket from day one. Discusses additional features of the enhanced-sqlite3-adapter gem: backported deferred foreign keys, virtual columns, custom returning values, pragmas via database.yml, SQLite extension loading via database.yml, and branch-specific databases via ERB shell expansion in database.yml and active_record tasks database prepare. Promotes sqlite-compile-flags tuning through Bundler (work done with Mike Dalessio) for compilation customization. Q&A covers security trade-offs of an embedded database (single attack surface vs. hardening trade-off) and migrating from SQLite to Postgres (easier than the reverse — SQLite's types are a subset, Active Record abstracts the rest).