Rails 8 SQLite Production: WAL Mode, Litestream Backups, and When to Choose SQLite Over Postgres

A founder called me last winter, exhausted. His Rails 7 app was burning twelve hundred dollars a month on a managed Postgres he barely used. Eighty-something paying customers, maybe four thousand requests an hour at peak, a database that fit comfortably in RAM. He had been told you need Postgres in production. He had been told SQLite is a toy. After nineteen years of Rails, both of those statements deserve a fight in 2026.

Rails 8 shipped with SQLite promoted to a first-class production database, not just a development convenience. Solid Queue, Solid Cache, and Solid Cable can all run on SQLite. DHH has been deploying production apps on it. And the Rails 8 SQLite production story is genuinely good — if you understand WAL mode, pragmas, backups, and the exact failure modes where SQLite stops being the right answer. This post is the playbook I give clients who are tired of paying the Postgres tax for workloads that do not need it.

Why Rails 8 SQLite Production Is Suddenly Plausible

For most of Rails history, SQLite in production was a meme. The database locked on every write. Connection handling was awkward. Backups were a tarball-and-prayer affair. The advice “use Postgres” was correct, even for tiny apps, because the operational story was easier.

Three things changed.

First, SQLite itself got serious. WAL mode (Write-Ahead Logging) means readers no longer block writers and writers no longer block readers. The journal_mode = WAL and synchronous = NORMAL combination is durable enough for almost any web app and dramatically faster than the old rollback journal. Modern SSDs and NVMe drives mean a single SQLite file can sustain thousands of writes per second.

Second, Rails 8 added explicit production support. The sqlite3 adapter now sets sane defaults for production (busy_timeout, WAL, foreign keys), the Rails 8 generators create separate SQLite databases for queue, cache, and cable, and the team has been honest about what SQLite is and is not good at.

Third, Litestream and litefs solved the backup problem. Litestream streams your WAL to S3 continuously. Point-in-time restore is a one-liner. You get a durable, replicated backup for two dollars a month in S3 storage. The “what about backups” objection went away.

The result: a Rails app on a single server with SQLite, Litestream, Solid Queue, and Solid Cache costs ten dollars a month and outperforms most managed Postgres setups for read-heavy workloads. That is what we are building here.

The Production database.yml

The Rails 8 default config/database.yml for SQLite is already close to right. Here is the version I deploy after a few tweaks for Rails 8 SQLite production safety.

# config/database.yml
default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  pragmas:
    journal_mode: WAL
    synchronous: NORMAL
    foreign_keys: ON
    busy_timeout: 5000
    cache_size: -64000     # 64 MB page cache
    temp_store: MEMORY
    mmap_size: 134217728   # 128 MB mmap
    wal_autocheckpoint: 1000

production:
  primary:
    <<: *default
    database: storage/production.sqlite3
  queue:
    <<: *default
    database: storage/production_queue.sqlite3
    migrations_paths: db/queue_migrate
  cache:
    <<: *default
    database: storage/production_cache.sqlite3
    migrations_paths: db/cache_migrate
  cable:
    <<: *default
    database: storage/production_cable.sqlite3
    migrations_paths: db/cable_migrate

A few of these pragmas matter enough to explain.

journal_mode: WAL is the headline change. Without it, you have a single global write lock and your app will deadlock under any concurrency. Always set this.

synchronous: NORMAL trades a tiny window of durability (the last transaction might be lost on power loss, but the database stays consistent) for a large performance win. FULL is the SQLite default and is overkill for almost all web workloads. Cloud VMs do not lose power.

busy_timeout: 5000 tells SQLite to wait up to five seconds for a write lock instead of immediately raising SQLite3::BusyException. Combined with the WAL mode allowing concurrent reads, this is the single most effective protection against database is locked errors in production.

cache_size: -64000 is the page cache in kilobytes (negative number means KB, positive means pages). 64 MB is generous and dramatically reduces disk reads on a hot dataset.

mmap_size: 134217728 enables memory-mapped I/O for the first 128 MB of the database. Reads from the mmap region skip the read syscall entirely. On a database that fits in RAM, this turns SQLite into something close to an in-process cache.

Separate database files for queue, cache, and cable matter because they have very different write patterns and you do not want Solid Queue’s heavy write traffic competing with your primary database for the write lock. Rails 8 makes this trivial.

Connection Handling and Threading

This is where I see the most production mistakes. SQLite has one writer at a time. Period. WAL mode allows many readers concurrent with a single writer, but two writers still serialize. Your application server’s threading model needs to respect this.

For Puma, set RAILS_MAX_THREADS=5 per worker and run multiple workers. Each thread gets its own connection from the pool. Writes serialize naturally because the database does it for you. With busy_timeout: 5000, a write that hits contention will wait up to five seconds rather than failing. That is almost always what you want.

Do not run forty threads per worker. SQLite’s serial-writer model means high thread counts on a single worker just create contention without buying anything. Five threads per worker, three to five workers, is the sweet spot on a 2-4 vCPU box. For deeper coverage of Puma sizing, see my post on Puma tuning for workers and threads.

One Rails 8 specific note: the sqlite3 adapter sets transaction_mode = IMMEDIATE by default in production. This avoids a subtle deadlock where two transactions both upgrade from read to write and one gets killed. If you are upgrading from Rails 7, verify this is on.

Litestream: Continuous Backups to S3

Litestream is the piece that makes Rails 8 SQLite production safe to bet a business on. It tails your SQLite WAL and streams it to S3 in real time. If your server dies, you restore to within a second of the failure. The whole thing is one binary and one config file.

Install Litestream alongside your Rails app (a Dockerfile snippet):

# Dockerfile
FROM ruby:3.4-slim AS base

RUN apt-get update && apt-get install -y \
    sqlite3 curl ca-certificates && \
    curl -L https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.deb -o /tmp/litestream.deb && \
    dpkg -i /tmp/litestream.deb && \
    rm /tmp/litestream.deb && \
    rm -rf /var/lib/apt/lists/*

Then the Litestream config:

# config/litestream.yml
dbs:
  - path: /rails/storage/production.sqlite3
    replicas:
      - type: s3
        bucket: $LITESTREAM_BUCKET
        path: production/primary
        region: $LITESTREAM_REGION
        access-key-id: $LITESTREAM_ACCESS_KEY_ID
        secret-access-key: $LITESTREAM_SECRET_ACCESS_KEY
        retention: 168h
        snapshot-interval: 24h

  - path: /rails/storage/production_queue.sqlite3
    replicas:
      - type: s3
        bucket: $LITESTREAM_BUCKET
        path: production/queue
        region: $LITESTREAM_REGION
        access-key-id: $LITESTREAM_ACCESS_KEY_ID
        secret-access-key: $LITESTREAM_SECRET_ACCESS_KEY
        retention: 72h

Run Litestream as a sidecar process. The pattern I use is litestream replicate -exec "bin/rails server", which makes Litestream supervise the Rails process. If Rails dies, Litestream dies. If Litestream dies, your container restarts. The Rails team also published a litestream-ruby gem that wraps this with sensible defaults and a Rails-friendly CLI.

# Gemfile
gem "litestream"

# Run Rails under Litestream supervision
bundle exec litestream replicate

Restoring is one command:

litestream restore -o storage/production.sqlite3 \
  s3://$LITESTREAM_BUCKET/production/primary

I do a quarterly fire drill on every production SQLite app I run. Spin up a fresh VM, restore from S3, point a staging Rails at it, run the test suite against it. The whole thing takes ten minutes. The first time you do it, you will sleep better. Untested backups are not backups.

Deploying Rails 8 SQLite Production with Kamal

Kamal 2 deploys SQLite Rails apps beautifully once you understand persistent volumes. The database file needs to survive container restarts and image swaps, so it cannot live inside the container.

# config/deploy.yml
service: myapp
image: myapp

servers:
  web:
    hosts:
      - 192.0.2.10
    options:
      add-host: host.docker.internal:host-gateway

registry:
  server: 127.0.0.1:5555
  username: rogerheykoop
  password:
    - KAMAL_REGISTRY_PASSWORD

env:
  clear:
    RAILS_MAX_THREADS: 5
    WEB_CONCURRENCY: 3
  secret:
    - RAILS_MASTER_KEY
    - LITESTREAM_ACCESS_KEY_ID
    - LITESTREAM_SECRET_ACCESS_KEY
    - LITESTREAM_BUCKET
    - LITESTREAM_REGION

volumes:
  - /var/lib/myapp/storage:/rails/storage

accessories:
  litestream:
    image: litestream/litestream:0.3.13
    host: 192.0.2.10
    volumes:
      - /var/lib/myapp/storage:/rails/storage
    files:
      - config/litestream.yml:/etc/litestream.yml
    cmd: replicate -config /etc/litestream.yml
    env:
      secret:
        - LITESTREAM_ACCESS_KEY_ID
        - LITESTREAM_SECRET_ACCESS_KEY
        - LITESTREAM_BUCKET
        - LITESTREAM_REGION

The volumes block is the key. The host path /var/lib/myapp/storage is where your SQLite files actually live. The container path /rails/storage is what the Rails app sees. Container rebuilds, image swaps, and Kamal redeploys all keep the data. For a deeper walkthrough of Kamal 2 production deploys, see my post on deploying Rails 8 with Kamal 2.

One thing Kamal does not handle automatically: ensuring only one Rails container writes to a given SQLite file at a time. If you run two web containers on the same host pointing at the same volume, you will get database is locked errors and worse, potential corruption. For single-host SQLite, run one set of Puma workers per database file. Scale by adding workers and threads within that process, not by adding containers.

When SQLite Beats Postgres for Rails Apps

This is the part people get wrong. I am not telling you SQLite is always the right answer. I am telling you it is the right answer more often than the Rails community currently admits.

SQLite is a great fit when:

• You have a single application server. SQLite is a file. Files live on one machine. The moment you need a second app server reading the same database, you need either a network filesystem (don’t) or to switch to Postgres. Most apps never get to a second app server.
• Your dataset fits in RAM, or close to it. With a 64 MB page cache and mmap, SQLite is brutally fast on datasets up to a few gigabytes. Past 50 GB you are doing more disk I/O and the picture changes.
• You have many more reads than writes. WAL mode lets readers fly. A SQLite-backed Rails app can comfortably do ten thousand reads per second per core. Writes are serial and cap out around a few thousand per second per database file.
• You can tolerate a few hundred milliseconds of replication lag for backups. Litestream is asynchronous. In the worst case you might lose one transaction. For most B2B SaaS, blogs, internal tools, and dashboards, this is fine. For payment processing or anything with strict regulatory durability requirements, it is not.
• You want to optimize for operational simplicity over horizontal scale. One server. One backup target. No connection pooler, no replica lag, no pg_hba.conf. The whole stack fits in your head.

SQLite is a bad fit when:

• You need multiple app servers writing concurrently. Use Postgres.
• You have heavy write contention. A queue with thousands of writes per second on the same database file will hit the write lock ceiling.
• You need true point-in-time recovery with zero data loss. Use Postgres with synchronous replication.
• You need rich indexing types like GIN, BRIN, partial indexes with complex predicates, or trigram search. SQLite has improved but Postgres is still ahead. For full-text search at scale, see my post on Postgres full-text search with pg_search.
• Your team already operates Postgres well. Switching tools to save fifty bucks is rarely worth the cognitive cost.

For the founder I mentioned at the start, we moved his app to a single Hetzner box with SQLite, Solid Queue, and Litestream. His monthly hosting bill went from twelve hundred to forty-eight dollars. P95 response time improved because round trips to the database are now in-process. He has more runway and a stack he can debug himself.

Monitoring SQLite in Production

The monitoring story is simpler than Postgres because there are fewer moving parts, but it is not zero. Three things to watch.

First, database file size growth. A surprise growth spike usually means a missing index or a forgotten delete_all somewhere. Run du -h storage/*.sqlite3 daily.

Second, WAL file size. The WAL grows during writes and shrinks on checkpoint. If the WAL is consistently larger than the main database, you have a stuck checkpoint, often because a long-running read transaction is preventing it.

# config/initializers/sqlite_stats.rb
class SqliteStats
  def self.report
    db = ActiveRecord::Base.connection
    {
      page_count: db.execute("PRAGMA page_count").first["page_count"],
      page_size: db.execute("PRAGMA page_size").first["page_size"],
      wal_pages: db.execute("PRAGMA wal_checkpoint(PASSIVE)").first
    }
  end
end

Third, Litestream lag. The litestream snapshots command tells you when the last snapshot completed. Page on snapshots older than fifteen minutes.

For deeper observability patterns across Rails apps, see my post on OpenTelemetry Rails 8 production observability.

FAQ

Is SQLite really safe for Rails 8 production apps?

Yes, for the workload profile described above. Single-server apps with read-heavy traffic and a dataset that fits comfortably on disk run beautifully on SQLite with WAL mode, sensible pragmas, and Litestream backups. Rails 8 ships explicit production support, and the Rails core team is using it in production themselves. The “SQLite is a toy” reputation is from a decade ago.

How does SQLite compare to Postgres for Rails 8 performance?

For reads on a hot dataset, SQLite is often faster because the data is in-process and there is no network hop. For writes, Postgres scales better past a few thousand writes per second because SQLite serializes writers. For analytical queries with complex joins, Postgres has a more mature query planner. The honest answer is that for most CRUD Rails apps under a few hundred requests per second, both perform far past what the app needs.

Can I use Litestream and Solid Queue together on the same Rails app?

Yes, and you should put them on separate SQLite database files. Solid Queue does heavy writes and you do not want that contention on your primary database. The Rails 8 generators set up separate queue, cache, and cable databases out of the box. Point Litestream at the primary database and optionally at queue if you need durable job history.

What happens if my SQLite file gets corrupted?

You restore from Litestream. The whole point of streaming WAL to S3 is that corruption on the source is recoverable. Run litestream restore to a new file, verify with PRAGMA integrity_check, and swap. If you have not done a recovery fire drill, you do not have backups. Run one quarterly.

Thinking about whether SQLite or Postgres fits your Rails app, and what the operational cost actually looks like? TTB Software helps Rails teams make these calls without the dogma. Nineteen years of Rails, and the right database is whichever one your app actually needs.