SQLite in production is a big win, with a few tweaks
Rails is fantastic if you want to truly build the ultimate monolith and with the advent of Rails 8, it's gotten even better. This is largely because of great folks in the Rails community like Stephen Margheim and the fine folks at 37signals who really fine-tuned Rails to not only take advantage of power and speed of SQLite, but solved the biggest challenge of concurrency. Now, we can have a screaming fast setup, all powered by onboard SQLite - meaning no dedicated database server, no latency in waiting for data to make the round trip, and money savings realized as an added bonus. I am currently running SQLite in production on several apps including this one, which utilizes SQLite for the main database and then Rails' Solid Trifecta - Solid Cache, Solid Queue and Solid Cable. By using Solid Queue, for example, you can get rid of the need for external services like Redis and Sidekiq. While SQLite will work out of the box in production, you do want to make some tweaks for optimization and edge cases that could cause slowdowns or write locks. Here's a look at an optimized SQLite setup in Rails 8 for production and the explanation behind it. In the database.yml, just a few lines makes a big difference. default: &default adapter: sqlite3 pool: 64 # Changed from max_connections to standard 'pool' and bumped up timeout: 5000 # This sets the busy_timeout at the connection level pragmas: journal_mode: wal synchronous: normal mmap_size: 134217728 # 128 MB memory-mapped I/O for faster reading cache_size: -64000 # 64MB cache size journal_size_limit: 67108864 # 64 MB WAL journal file limit foreign_keys: true # Enforce foreign key constraints temp_store: memory # Store temp tables/indexes in memory development: <<: *default database: storage/development.sqlite3 test: <<: *default database: storage/test.sqlite3 production: primary: <<: *default database: storage/production.sqlite3 cache: <<: *default database: storage/production_cache.sqlite3 migrations_paths: db/cache_migrate queue: <<: *default database: storage/production_queue.sqlite3 migrations_paths: db/queue_migrate cable: <<: *default database: storage/production_cable.sqlite3 migrations_paths: db/cable_migrateSQLite's defaults are optimized for single-user embedded devices (like mobile phones). These changes specifically configure it to act like a enterprise-grade server database. The difference here from the default is the added pragmas which give the server explicit instructions and allow both Puma's web threads and Solid Queue's background workers to handle the workload gracefully. Here's a breakdown of what the pragmas are doing. journal_mode: WAL (Write-Ahead Logging) • What it means: By default, SQLite locks the entire database when writing. WAL mode completely changes this by writing new data to a separate "roll-forward" journal file instead of the main database file first. • Why it matters: This is the secret sauce for concurrency. It allows multiple readers to read the database at the exact same time a writer is writing to it. They don't block each other anymore. synchronous: NORMAL • What it means: This controls how aggressively SQLite forces data to be synced to the physical disk. In FULL mode, SQLite pauses and waits for the disk to confirm data is written at every critical step. In NORMAL mode, it syncs less frequently, mostly at critical checkpoints in WAL mode. • Why it matters: It is a massive speed boost for writes. If your server suddenly loses power, there is a tiny chance the very last transaction could be lost, but the database will not become corrupted. For 99% of web apps, this trade-off is absolutely worth it. mmap_size: 134217728 (128 MB) • What it means: This tells the operating system to map up to 128 MB of the database file directly into the application's memory space (Memory-Mapped I/O). • Why it matters: Instead of the OS constantly making slow system calls to read chunks of the database file from the disk, it reads directly from RAM. It makes read operations incredibly snappy. cache_size: -64000 (64 MB) • What it means: This sets the maximum number of database pages SQLite will hold in memory. The negative number is a clever SQLite trick: it means "measure in kilobytes" rather than page count. So, -64000 allocates exactly 64 MB of RAM for the cache. • Why it matters: It ensures frequently accessed data (like your application's hot rows and indexes) stays in RAM, reducing the need to look at the disk at all. journal_size_limit: 67108864 (64 MB) • What it means: This puts a cap on how large that WAL journal file can grow before SQLite automatically shrinks it back down. • Why it matters: Without this, if you have a massive burst of writes, your WAL file could grow to gigabytes, eating up disk space and making the "checkpointing" process (merging the WAL back into the main database) sluggish. foreign_keys: true • What it means: This turns on standard relational database guardrails. If you try to delete a user who still has active orders, SQLite will stop you and throw an error. • Why it matters: Historically, SQLite left this off by default for backwards compatibility. Turning it on ensures your data stays clean and relationships don't break. timeout: 5000 • What it means: "If the database is currently busy writing something else, don't panic and crash. Wait up to 5 seconds to see if it clears up before throwing an error." • Why it matters: SQLite allows unlimited simultaneous readers, but only one writer at a time. If User A is saving a long blog post, the database is briefly locked for writing. If User B clicks "Buy Now" at that exact microsecond, SQLite would normally instantly fail and show User B a nasty database is locked error. With timeout: 5000 enabled, User B’s request simply pauses for a few milliseconds, waits for User A to finish, and then executes seamlessly. Since SQLite writes take microseconds, User B won't even notice the pause. temp_store: memory • What it means: "When you need to build temporary tables or sort massive lists behind the scenes, do it directly in RAM instead of creating temporary files on the hard drive." • Why it matters: Whenever Rails executes a complex query that involves heavy sorting (.order), grouping (.group), or complex JOIN statements, SQLite often needs to create a temporary "scratchpad" index to figure out the answer. By default, it writes that scratchpad to the server's hard drive. Forcing it into memory means SQLite uses lightning-fast RAM for its math homework, resulting in massive speed boosts for your heaviest ActiveRecord queries and eliminating unnecessary disk wear and tear. You might have noticed in the database.yml above that we set pool: 64 instead of matching it dynamically to Puma's RAILS_MAX_THREADS. Because Rails 8 splits our primary application data, Solid Queue, and Solid Cache into their own independent SQLite files, each process gets its own connection pool. Since SQLite connections are handled purely in-memory with virtually zero overhead, setting a high static pool ensures that when Solid Queue spins up its concurrent background workers, they will never be starved for database connections. Running the "Solid Trifecta" on a single bare-metal server or VPS gives you an app that feels incredibly snappy, costs pennies to host, and completely cuts out the complexity of external dependencies like Redis. But you might be wondering: “If my whole database is just a flat file on a single server, what happens if that server goes down?” In a traditional setup, a compromised server means lost data. But with modern SQLite tools, it doesn't have to. In my next article, I’ll show you how to pair this exact configuration with Litestream to stream real-time, per-second cryptographic backups directly to an S3 bucket or Object Storage for ultimate peace of mind. Until then, drop your database optimizations in the comments below!
