Learn how to optimize SQLite for Rails 8 production with WAL mode, busy timeouts, Solid Queue, and database.yml best practices.
• 14 min read
• 14 min read
Rails 8 made an important change: SQLite is no longer just for development. For years, developers reached for PostgreSQL or MySQL the moment an app went live. That was the safe, conventional choice. But conventions are shifting.
Rails 8 ships with SQLite support baked into its defaults, making it genuinely suitable for many production workloads. With enhancements like Solid Queue, Solid Cache, and Solid Cable, you can run a full-featured Rails application on a single server using only SQLite as your data engine. No Redis. No external database server. No extra infrastructure to maintain.
This guide is for Rails developers who want to understand how to configure SQLite correctly for production. You will learn about WAL mode, busy timeouts, PRAGMA settings, Solid Queue setup, and the practical limits of this approach so you can make a confident, informed decision.
SQLite has a reputation as a toy database. That reputation is no longer fair for a large class of applications.
There is no database server to install, configure, or secure. Your database is a file on disk. Deployments become simpler, and there are fewer moving parts that can break.
Running PostgreSQL on a managed cloud service costs money. With SQLite, you eliminate that dependency. For solo developers, small teams, or early-stage products, this savings adds up quickly.
When you deploy with Kamal (Rails 8's default deployment tool), your entire application, including the database, lives on one server. Migrations are straightforward, rollbacks are easier to reason about, and you avoid network latency between your app and database.
Backing up a SQLite database is as simple as copying a file. Tools like Litestream can stream SQLite changes to an S3-compatible bucket in real time, giving you continuous backups without complex configuration.
SQLite in production works well for:
SQLite is not a good fit for every application. A few honest limitations:
Keep these in mind as you read the rest of this guide.
Before tuning SQLite, it helps to understand how it handles concurrent database access.
SQLite uses file-level locking. When a write happens, it locks the database file so other writes must wait. Reads, however, can usually proceed concurrently with writes when WAL mode is enabled (more on that in a moment).
In the default journal mode (called DELETE or rollback journal mode), even reads can be blocked while a write transaction is open. This causes problems under any real-world load.
Because SQLite stores everything in a single file, it does not have a separate server process managing connections. Every connection goes directly to the file. This is what makes it simple, but it also means Rails connection pooling and SQLite locking behavior must be understood together.
Without proper configuration, you will see errors like:
SQLite3::BusyException: database is locked: database is locked
This happens when one thread or process holds a write lock and another times out while waiting. Out of the box, SQLite has a very short timeout before it gives up and raises this error.
With the default SQLite settings and multiple Rails threads, even light traffic can produce locking errors. The solution involves two key changes: enabling WAL mode and setting a reasonable busy timeout.
Write-Ahead Logging (WAL) is the most important SQLite optimization you can make for any production application.
In WAL mode, SQLite writes changes to a separate log file instead of directly modifying the database file. Readers continue accessing the original database file while writers work on the log. This means reads and writes can happen concurrently without blocking each other.
You can enable WAL mode in a Rails initializer or via a migration. The recommended approach is to set it as a connection option that runs automatically.
Create a file at config/initializers/sqlite.rb:
# config/initializers/sqlite.rb
module SqliteConfig
def configure_connection
super
db = @connection
db.execute("PRAGMA journal_mode=WAL;")
db.execute("PRAGMA synchronous=NORMAL;")
db.execute("PRAGMA cache_size=5000;")
db.execute("PRAGMA foreign_keys=ON;")
end
end
ActiveRecord::ConnectionAdapters::SQLite3Adapter.prepend(SqliteConfig)
Alternatively, Rails 8 supports configuring PRAGMAs through database.yml via the pragmas key (more on that in the configuration section).
Connect to your SQLite database and run:
sqlite3 db/production.sqlite3 "PRAGMA journal_mode;"
The output should be wal. If it returns delete, WAL mode is not active.
Even with WAL mode enabled, write operations are still serialized. When two writes compete, one must wait. The busy timeout controls how long SQLite waits before giving up and raising an error.
A locked database error means a write attempted to acquire a lock, but another write was already holding it. SQLite's default timeout is zero milliseconds, which means it gives up immediately. For any real application, this will cause errors under even light concurrent load.
When you set a busy timeout, SQLite will retry acquiring the lock repeatedly for the specified duration before giving up. For most applications, setting a timeout between 5,000 and 30,000 milliseconds gives writes enough time to queue and succeed without making users wait too long.
You can set the busy timeout directly in database.yml:
production:
adapter: sqlite3
database: db/production.sqlite3
timeout: 5000
The timeout key maps directly to SQLite's busy timeout in milliseconds.
If you need more control, you can also set it via PRAGMA in your initializer:
db.execute("PRAGMA busy_timeout=5000;")A longer busy timeout means requests will queue longer before failing. This can mask deeper problems. A good rule of thumb:
Here is a complete, production-ready database.yml configuration for a Rails 8 app using SQLite.
# config/database.yml
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
development:
<<: *default
database: db/development.sqlite3
test:
<<: *default
database: db/test.sqlite3
production:
<<: *default
database: db/production.sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
# Rails 8 pragma support
pragmas:
journal_mode: wal
synchronous: normal
mmap_size: 134217728 # 128MB
journal_size_limit: 67108864 # 64MB
cache_size: 2000
foreign_keys: true
The pool setting controls how many database connections Rails can hold open at once. Set this equal to your Puma thread count. If RAILS_MAX_THREADS is 5, your pool should be 5.
Using more connections than your pool allows leads to ActiveRecord::ConnectionTimeoutError errors.
pool aligned with your Puma threadstimeout to set a busy wait, not the ActiveRecord connection checkout timeout (those are different settings)Solid Queue is a database-backed background job queue that ships as a Rails 8 default. It uses Active Record to store jobs in the database, which means no Redis, no Sidekiq, and no external queue server.
Historically, background jobs required Redis and a gem like Sidekiq or Resque. Rails 8 changes this default. For most applications, a database-backed queue is fast enough and dramatically simpler to operate.
Solid Queue is configured through config/queue.yml. Here is a production-ready setup:
# config/queue.yml
default: &default
dispatchers:
- polling_interval: 1
batch_size: 500
workers:
- queues: "*"
threads: 3
polling_interval: 0.1
processes: 1
production:
<<: *default
workers:
- queues: "default,mailers"
threads: 3
polling_interval: 0.5
processes: 1
- queues: "low"
threads: 1
polling_interval: 2
processes: 1
Solid Queue uses a separate database by default in Rails 8. Here is how to configure that in database.yml:
production:
primary:
adapter: sqlite3
database: db/production.sqlite3
pragmas:
journal_mode: wal
synchronous: normal
foreign_keys: true
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
queue:
adapter: sqlite3
database: db/production_queue.sqlite3
pragmas:
journal_mode: wal
synchronous: normal
pool: 5
timeout: 5000
migrations_paths: db/queue_migrate
Separating the queue database from the primary database is a best practice. It reduces write contention on the primary database and lets you tune each database independently.
solid_queue_jobs table size. Stale failed jobs accumulate over time.Beyond WAL mode and busy timeouts, these PRAGMAs improve performance:
PRAGMA synchronous = NORMAL;
By default, SQLite syncs to disk after every transaction (FULL mode). NORMAL mode syncs less aggressively and is safe with WAL enabled. You trade a tiny risk of data loss on a hard crash for noticeably better write performance.
PRAGMA cache_size = 2000;
This sets the page cache size. Each page is typically 4KB, so 2000 pages means about 8MB of cache in memory. Increase this if your server has RAM to spare.
PRAGMA mmap_size = 134217728;
Memory-mapped I/O lets SQLite read database pages directly from memory-mapped files. This can significantly speed up reads on modern operating systems. The value above is 128MB.
PRAGMA foreign_keys = ON;
SQLite does not enforce foreign key constraints by default. Turn this on to catch data integrity issues.
Run VACUUM periodically to reclaim space from deleted rows:
sqlite3 db/production.sqlite3 "VACUUM;"
Run ANALYZE to update query planner statistics:
sqlite3 db/production.sqlite3 "ANALYZE;"
You can automate these with a Solid Queue recurring job or a cron task.
Litestream is the recommended tool for SQLite backups in production. It streams WAL changes to S3, R2, or another object storage service with sub-second latency. Your recovery point objective can be under a second.
A basic Litestream configuration looks like this:
# litestream.yml
dbs:
- path: /app/db/production.sqlite3
replicas:
- type: s3
bucket: your-backup-bucket
path: production/db
region: us-east-1
Not enabling WAL mode. This is the most common mistake. Without WAL mode, reads block writes and vice versa. Always enable WAL in production.
Using the default busy timeout of 0. With zero timeout, any write contention immediately raises an error. Set it to at least 5,000 milliseconds.
Setting the connection pool higher than your thread count. Extra connections do not help and waste resources. Match the pool to RAILS_MAX_THREADS.
Storing the database on a network file system. NFS, EFS, and similar network volumes can corrupt SQLite databases. Use the local disk only.
Running too many background job workers. With Solid Queue and SQLite, each worker thread is a potential concurrent writer. Start conservative and scale up carefully.
Forgetting to set up backups. SQLite, being a file, does not mean it cannot be lost. Use Litestream or another backup strategy before you go live.
Ignoring slow query logs. Rails 8 includes the Query Logs feature. Enable it and periodically review slow queries to catch missing indexes early.
SQLite production deployments work best when:
Many successful SaaS applications run at revenue levels that never require more than a single well-tuned server. SQLite can take you further than you might expect.
There are clear signals that it is time to switch:
Migrating from SQLite to PostgreSQL in a Rails application is manageable, and tools like pgloader can help. Plan for it as a future milestone rather than a crisis.
Can SQLite handle real production traffic in a Rails 8 app?
Yes, for the right workloads. If your application has a healthy mix of reads and writes and does not receive thousands of write requests per second, SQLite with WAL mode and proper configuration can handle production traffic reliably. Many apps that feel "busy" are not generating the write volume that would push SQLite past its limits.
Is SQLite safe to use in production, given that it is a file?
Safety comes from your backup strategy, not your database engine. With Litestream providing continuous replication to object storage, SQLite can be as safe as any managed database. The key is having a tested restore process before you need it.
Does Solid Queue work well with SQLite?
Yes, and Rails 8 is specifically designed for this combination. The recommended approach is to use a separate SQLite database for Solid Queue so it does not compete with your primary application database for write locks.
How do I know if I have write contention problems?
Watch for SQLite3::BusyException in your logs and error tracking tools. You can also monitor the Solid Queue dispatcher metrics and look for high queue latency. A large number of retried jobs is another signal.
What is the difference between timeout in database.yml and PRAGMA busy_timeout?
In Rails, the timeout key in database.yml for the SQLite3 adapter maps directly to PRAGMA busy_timeout. They control the same thing. The timeout key in database.yml is the more idiomatic Rails way to set it, while using the PRAGMA directly in an initializer gives you more control and clarity.
Can I use multiple SQLite databases in one Rails 8 app?
Yes. Rails 8 supports multiple databases through Active Record's multi-database features. This is actually a recommended pattern: one database for your primary data, one for the queue, and one for the cache. Each database has its own connection pool and can be tuned independently.
Do I need Redis at all if I use Rails 8 with SQLite?
No. Rails 8 with Solid Queue (jobs), Solid Cache (caching), and Solid Cable (WebSockets) eliminates the need for Redis in most applications. This is one of the core design goals of the Rails 8 release.
SQLite is no longer the database you graduate away from. For a large class of Rails 8 applications, it is a deliberate and mature production choice.
The key to making it work is configuration. Enable WAL mode so reads and writes can happen concurrently. Set a busy timeout so write contention does not immediately crash your app. Use separate SQLite databases for your queue and cache. Tune your PRAGMA settings. Set up continuous backups with Litestream.
Done right, a single-server Rails 8 application with SQLite can handle significant traffic, cost very little to operate, and be simple enough to maintain solo. That combination is genuinely rare and worth taking seriously.
Start with the database.yml and initializer configurations from this guide, get Litestream running before you launch, and monitor write contention as your traffic grows. You will likely be surprised by how far SQLite takes you.
If you are starting a new Rails 8 project or simplifying an existing one, give SQLite in production a serious look. The defaults are better than ever, and the community knowledge around this pattern is growing quickly.