Rails 8 and SQLite in production

The question behind "rails 8 sqlite" is whether the default is a default you can ship, and the honest answer is yes with one condition that almost nothing written about it names. It is not throughput. Throughput was never the problem. It is that a single write transaction held open for a few seconds by anything other than your web process turns into 500s, and everything Rails 8 added is there to make that the only way to break it.

Everything below ran today on an Apple M2 Max, 12 cores, macOS 26.5.1, arm64. Ruby 4.0.5. The app was generated by the rails 8.1.3.1 executable on PATH, which resolved rails (8.1.4) into Gemfile.lock; that is the version every runtime number comes from. sqlite3 gem 2.9.6, which links SQLite 3.53.2, reported by select sqlite_version() on a live Active Record connection. Puma 8.0.2. ApacheBench 2.3 at /usr/sbin/ab. The machine was not quiet: other Rails servers of mine were up during the benchmark runs, which is why every throughput figure below is given as a range over repeated runs rather than a single number.

The six pragmas you did not set

configure_connection runs on every connection Rails opens, and it applies a constant. This is activerecord-8.1.4/lib/active_record/connection_adapters/sqlite3_adapter.rb:111:

DEFAULT_PRAGMAS = {
  "foreign_keys"        => true,
  "journal_mode"        => :wal,
  "synchronous"         => :normal,
  "mmap_size"           => 134217728, # 128 megabytes
  "journal_size_limit"  => 67108864, # 64 megabytes
  "cache_size"          => 2000
}

Asked back from a production connection in a freshly generated app, with no pragmas: key anywhere in config/database.yml:

$ RAILS_ENV=production bin/rails runner '
c = ActiveRecord::Base.lease_connection
%w[journal_mode synchronous mmap_size journal_size_limit cache_size foreign_keys busy_timeout wal_autocheckpoint].each do |p|
  puts "%-20s %s" % [p, c.query_value("PRAGMA #{p}")]
end
puts "sqlite lib: " + c.query_value("select sqlite_version()")'
journal_mode         wal
synchronous          1
mmap_size            134217728
journal_size_limit   67108864
cache_size           2000
foreign_keys         1
busy_timeout         0
wal_autocheckpoint   1000
sqlite lib: 3.53.2

synchronous = 1 is NORMAL. In WAL mode that means a commit returns once the WAL write has reached the operating system, without an fsync, so a power cut can lose recently committed transactions while leaving the database structurally intact. That is a durability trade Rails made for you and did not ask about. It is the right one for a web application and the wrong one for a ledger, and the fix is one line of pragmas: in database.yml, at a cost you will feel immediately on the write benchmark further down.

busy_timeout reading 0 next to a timeout: 5000 in database.yml is not a missing key. That one is unpicked in What database Rails uses, and it matters here only as a warning not to use that pragma to confirm your configuration.

The line that actually changed

Rails 8 did not make SQLite fast. It made it not lose. The difference is one keyword, at sqlite3_adapter.rb:162:

@connection_parameters = @config.merge(
  database: @config[:database].to_s,
  results_as_hash: true,
  default_transaction_mode: :immediate,
  extensions: extensions
)

Note the direction of the merge: :immediate goes on top of @config, so a default_transaction_mode: in your database.yml is discarded. I added default_transaction_mode: deferred to the default: &default anchor and reran the contention test. Nothing changed, and connection_db_config.configuration_hash[:default_transaction_mode] read "deferred" the whole time. The key is accepted and ignored.

Why it matters is the difference between two runs of the same race. Thread A opens a transaction, reads, holds for 0.6 s, then writes. Thread B commits a write in the middle. With default_transaction_mode: :deferred, which is what the sqlite3 gem does on its own:

== default_transaction_mode: deferred ==
  B committed at 0.505s
  A SQLite3::BusyException: database is locked at 1.511s

With :immediate:

== default_transaction_mode: immediate ==
  A committed at 1.512s
  B committed at 1.512s

The deferred failure is the nasty one and it is worth understanding rather than avoiding. A deferred transaction takes no lock at BEGIN. A's SELECT pins a read snapshot. B commits, so A's snapshot is now stale, and when A tries to write, SQLite has no way to give it a lock without silently serving it a view of the database that no longer exists. It refuses, with error code 5, immediately. Not after a timeout. A busy timeout is useless against it, because waiting does not make a stale snapshot fresh again. BEGIN IMMEDIATE takes the write lock up front, so the loser is B, and B losing means B waits, which is a thing a timeout can fix.

Every write transaction Active Record opens is one of these. Subscribing to sql.active_record during a Hit.transaction { Hit.create! } shows the literal statement:

TRANSACTION (0.0ms)  BEGIN immediate TRANSACTION
Hit Count (0.9ms)  SELECT COUNT(*) FROM "hits"
Hit Create (0.1ms)  INSERT INTO "hits" ...
TRANSACTION (0.2ms)  COMMIT TRANSACTION

This is also the cost of the choice, and it is a real one: Rails takes a write lock at the top of every transaction, including transactions that turn out to only read. A read-only block wrapped in transaction now serialises against every writer in the deployment. That is the price of never seeing error code 5, and it is worth paying, but stop wrapping reads in transactions.

How many writes per second, through the whole stack

A benchmark of SQLite is not interesting. A benchmark of a request is. The app has two routes:

class BenchController < ApplicationController
  def read
    render plain: Hit.count.to_s
  end

  def write
    Hit.create!(label: "ab")
    render plain: "ok"
  end
end

RAILS_ENV=production, RAILS_MAX_THREADS=5, the generated config/puma.rb unchanged, which means single mode with no workers line at all. Solid Cache, Solid Queue and Solid Cable installed and their schemas loaded, so all four SQLite files in storage/ are real. Table emptied and the WAL truncated before the run, 1000 warm-up requests, then five runs of 3000:

=== /write, -c 10, five runs of 3000 ===
Requests per second:    1472.66 [#/sec] (mean)
Requests per second:    1239.77 [#/sec] (mean)
Requests per second:    1271.80 [#/sec] (mean)
Requests per second:    1484.77 [#/sec] (mean)
Requests per second:    1391.11 [#/sec] (mean)
=== /read, -c 10, three runs of 3000 ===
Requests per second:    1717.00 [#/sec] (mean)
Requests per second:    1735.29 [#/sec] (mean)
Requests per second:    1783.80 [#/sec] (mean)

One representative write run in full:

Concurrency Level:      10
Time taken for tests:   2.176 seconds
Complete requests:      3000
Failed requests:        0
Requests per second:    1378.54 [#/sec] (mean)
Time per request:       7.254 [ms] (mean)
...
  50%      6
  90%      9
  99%     20
 100%     71 (longest request)

A request that writes runs at about 80 percent of the rate of a request that only counts a table. That gap is the whole of what SQLite costs on the write path here, and it is smaller than the variance between my benchmark runs. Raising concurrency does not change throughput, only queueing: on the same server, -c 1 gave 1583.49 req/s with a 1 ms median and a 1 ms p99, -c 50 gave 1566.79 req/s with a 31 ms median and a 49 ms p99, zero failures either way. Writes serialise, and five threads in front of a serialised resource is a queue that behaves like a queue.

The -wal file settles at 4,169,472 bytes and stays there across 24,000 more writes, because wal_autocheckpoint is 1000 pages. It does not grow, and it does not shrink either.

What a second writer costs, and what it does not

A background job process writing to the same file continuously, 130,580 Hit rows in 30 seconds from a separate bin/rails runner, while ApacheBench held 10 concurrent writers against the web process: 1244 to 1310 req/s with the background writer against 1089 to 1289 without it, over three alternating rounds. No measurable cost. Two processes hammering one SQLite file with short transactions is fine, and this is the part of the folklore that is wrong. "One writer process" is not the rule.

The rule is transaction duration. Here is the same setup, except the second process holds one write transaction open for 10 seconds:

Hit.transaction do
  Hit.create!(label: "holder")
  sleep 10
end

== 10s lock held by a second process, ab -n 2000 -c 5 ==
Complete requests:      2000
Non-2xx responses:      1
Requests per second:    194.08 [#/sec] (mean)
  50%      3
  99%      6
 100%   5008 (longest request)

Two runs, identical outcome: one request out of 2000, having waited 5.008 s, answered ActiveRecord::StatementTimeout (SQLite3::BusyException: database is locked). Throughput for the duration went from ~1400 to 194. That is the whole failure mode. One long transaction, one 500, and a latency cliff at exactly your timeout:. Everything you do to run SQLite in production safely reduces to keeping transactions short: no HTTP call inside a transaction block, no file upload, no sleep, no batch job that wraps ten thousand rows in one ActiveRecord::Base.transaction.

The stall I could not explain, and then could

Setting the contention test up, I hit something that looked like a much worse problem than the one I was measuring. Thread A takes the write lock and holds it 3.0 s. Thread B tries to write at 0.5 s. Expected: B waits 2.5 s, then commits. Observed, three runs in a row:

  A took the write lock at 0.002s
  B (same process, second thread) ActiveRecord::StatementTimeout at 5.512s
  A insert returned at 5.516s
  A committed at 5.517s

A held the lock for 3 s and its own INSERT did not land until 5.5 s, four milliseconds after B gave up. The loser was blocking the winner, which would mean any write contention inside one Puma process costs a full timeout: no matter how short the transactions are. Dropping timeout: to 1000 moved A's insert to 3.028 s and B's failure to 1.515 s, so the causal link was not in doubt.

It was not the GVL. A 20-million-iteration integer loop inside A's transaction, in the same window, ran in 0.56 s against 0.749 s uncontended, and a third thread doing 300,000 string allocations and no database work at all ran in 0.067 s against a 0.069 s baseline. Ruby was fine. Instrumenting A statement by statement put the entire 2.5 s in one place, and it was not SQL:

  A awake at 3.012s
  B gave up at 5.503s
  A built the record at 5.505s
  A leased the connection at 5.505s
  A raw execute returned at 5.505s

Hit.new was the stall. It was the first model instantiation in the process, so it triggered a lazy schema load, and a schema load needs the connection pool that B was sitting in. Calling Hit.new(label: "warm") once before the race made the whole thing disappear:

  A awake at 3.002s
  A insert returned at 3.004s
  A committed at 3.005s
  B committed at 3.005s

So my dramatic finding was a cold start, which is exactly the objection to it: in production the schema loads on the first request and never again. Except the first request is not special enough to be safe, and the obvious fix makes it worse. bin/rails db:schema:cache:dump writes db/schema_cache.yml, and with that file present the same cold race took 10.5 seconds and printed a line I had never seen:

Failed to validate the schema cache because of ActiveRecord::StatementTimeout: SQLite3::BusyException: database is locked

That is schema_cache.rb:134. load_cache compares the dumped schema version against the live one inside pool.with_connection, that query is stuck behind the writer, the rescue warns and returns, and Rails then falls back to loading the schema from the database, which costs a second full timeout. Two timeouts, 10.5 s, reproduced twice. Turning the check off fixed it properly:

# config/environments/production.rb
config.active_record.check_schema_cache_dump_version = false

  A awake at 3.007s
  A insert returned at 3.007s
  A committed at 3.008s
  B committed at 3.009s

The cost of that line is that a stale db/schema_cache.yml will now be trusted, so it has to be regenerated in the build, after migrations, every time. On a SQLite deployment where the database file is on the server and migrations run in bin/docker-entrypoint, that is not free to arrange. The alternative is to leave the check on and touch every model once during boot, which is what config.eager_load = true nearly does and does not guarantee.

Backups, and the copy that lies to you

The database is one file, so the backup is cp, and that is wrong. Five thousand rows committed from a live process that is still running:

committed rows:                 5000
production.sqlite3:             352256 bytes
production.sqlite3-wal:         4124152 bytes
rows in cp production.sqlite3:  4922
rows in sqlite3 .backup:        5000
integrity_check on the cp:      ok

Seventy-eight committed rows gone, in a file that PRAGMA integrity_check calls ok, because they were in the WAL and the WAL was not copied. There is no corruption to detect. The copy is a valid SQLite database as of the last checkpoint, and nothing in it tells you which writes it is missing. This is the one that will hurt, because it fails silently and it fails on exactly the rows you cared about most, the newest ones.

Copying all three files (-wal and -shm as well) returned 8000 of 8000 in my run. I am not recommending it. Three cp calls are not one atomic operation, a checkpoint can land between them, and the result looks exactly as healthy when it is wrong. Use sqlite3 "$DB" ".backup out.sqlite3", which took a proper read lock and got every row, or VACUUM INTO.

Litestream is the answer people will tell you to use, and it is not installed on this machine, so I have no numbers for it and will not quote anyone else's.

What the deployment shape actually is

config/database.yml says it, in a comment above the production block:

# Store production database in the storage/ directory, which by default
# is mounted as a persistent Docker volume in config/deploy.yml.

config/deploy.yml:71 is where that happens:

volumes:
  - "sqliteprod_storage:/rails/storage"

A Docker named volume, not a host path, which means it lives under the Docker root on whichever server you deployed to and nothing backs it up. The generated comment above it says so: "Recommended to change this to a mounted volume path that is backed up off server." The Dockerfile has no VOLUME line of its own, so without Kamal or an explicit -v, storage/ is a container layer and your database dies with the container.

One volume means one server. config/deploy.yml ships with a single servers.web entry, and adding a second one to a SQLite app gives you two databases that do not know about each other. That is the constraint, and it is a bigger decision than any pragma on this page.

SOLID_QUEUE_IN_PUMA=1 reads like it keeps everything in one process. It does not:

78101     1 puma 8.0.2 (tcp://0.0.0.0:3210) [sqliteprod]
78130 78101 solid-queue-fork-supervisor(1.7.0): supervising 78132, 78133, 78134
78132 78130 solid-queue-dispatcher(1.7.0): dispatching every 1 seconds
78133 78130 solid-queue-worker(1.7.0): waiting for jobs in *
78134 78130 solid-queue-scheduler(1.7.0): scheduling clear_solid_queue_finished_jobs

Four extra processes, forked by Puma, polling production_queue.sqlite3 once a second. On the write benchmark that cost nothing measurable: 1504 to 1558 req/s with it enabled against 1240 to 1485 without, which is to say the difference is smaller than my run-to-run noise in the other direction. Solid Queue's own tables live in a separate file, which is why. Your jobs' writes do not.

The three installers that did not run

This one is not about SQLite and I ran into it because of SQLite. A rails new on 8.1.4 here produced an app with solid_cache, solid_queue and solid_cable in the Gemfile, four SQLite databases in config/database.yml, and none of them wired up:

$ RAILS_ENV=production bin/rails runner 'puts Rails.cache.class; puts ActiveJob::Base.queue_adapter.class'
ActiveSupport::Cache::FileStore
ActiveJob::QueueAdapters::AsyncAdapter

config/cable.yml production said adapter: redis against redis://localhost:6379/1. db/cache_schema.rb, db/queue_schema.rb and db/cable_schema.rb did not exist, and production_cache.sqlite3, production_queue.sqlite3 and production_cable.sqlite3 were 4096-byte empty files. The generator log says why:

       rails  solid_cache:install solid_queue:install solid_cable:install
/Users/mehdifarsi/.rvm/rubies/ruby-4.0.5/lib/ruby/4.0.0/bundled_gems.rb:60:in 'Kernel.require': cannot load such file -- bootsnap/setup (LoadError)
    from .../thirdapp/config/boot.rb:4:in '<top (required)>'
    from bin/rails:3:in 'Kernel#require_relative'

app_base.rb:750 runs those three installers through execute_command, which at actions.rb:470 shells out to #{Gem.ruby} bin/rails ... with abort_on_failure unset, so the failure is logged and the generation reports success. Running the same command by hand in the finished app creates all nine files in under a second.

Three clean generations, two of them without --quiet, all three missing the files. I am not going to call this a Rails bug, because I could not work out why that subprocess cannot load the app's bundle on this rvm setup and a broken environment is the likelier explanation. What I will say is that the check is cheap and worth running on any generated app before you believe anything about its four databases: grep solid config/environments/production.rb should print three lines.

The test

Seven tests in the scratch app, run against RAILS_ENV=production:

$ bundle exec ruby test/sqlite_production_test.rb
Run options: --seed 46117

# Running:

.......

Finished in 1.740482s, 4.0219 runs/s, 11.4911 assertions/s.

7 runs, 20 assertions, 0 failures, 0 errors, 0 skips

The one worth copying is the backup assertion, because it is the claim on this page that costs the most to get wrong and the cheapest to check:

def test_copying_the_sqlite3_file_alone_loses_committed_rows
  before = Hit.count
  3000.times { Hit.create!(label: "backup-test") }
  assert_equal before + 3000, Hit.count

  assert_operator File.size(DB + "-wal"), :>, 0, "expected an uncheckpointed WAL"
  FileUtils.cp(DB, naive)
  system("sqlite3", DB, ".backup #{safe}", exception: true)

  count = ->(f) { SQLite3::Database.new(f).execute("select count(*) from hits").first.first }
  assert_operator count.(naive), :<, Hit.count, "the plain copy should be missing rows"
  assert_equal Hit.count, count.(safe)
  assert_equal "ok", SQLite3::Database.new(naive).execute("pragma integrity_check").first.first
end

The call, and what would change it

Ship it. A generated Rails 8 app on SQLite serves roughly 1400 write requests a second through the full stack on one laptop core budget, with a p99 of 20 ms and no failures, and every application I have shipped would have been fine on that for years. The decision is not about the database engine. It is about accepting one server and one volume, which is the same decision Kamal is asking you to make anyway, and which you should make on the strength of your backup story rather than your traffic.

Two conditions come with that, and they are not negotiable. Nothing may hold a write transaction open across an I/O call, because the cost of one that does is 500s at exactly your timeout:. And the backup is sqlite3 .backup or Litestream, never cp, because cp produces a file that passes integrity_check and is missing your newest rows.

What would change the verdict: a write path that genuinely needs two machines. Not a read path, that is what a read replica of the file is for, and not a scaling story you have not measured. Two machines both taking writes is the one requirement SQLite cannot be argued into, and the point at which -d postgresql stops being a preference and becomes the answer.

What this post does not cover

Litestream and its replication guarantees, since it is not installed here and I have no measurements. The sqlite3 gem's extensions: key and anything about full text search, which needs its own page. Read replicas of a SQLite file, ATTACH, and the multi-database routing that would make one useful. Whether any of these numbers hold on the Linux filesystems a real deployment uses: every measurement above is APFS on a laptop, and WAL behaviour on a network filesystem in particular is a different subject with a different answer. And the choice between SQLite and the other three adapters, which is What database Rails uses and includes the migration rollback and column type behaviour deliberately left out here.

The default_transaction_mode race, the schema cache stall and the backup test each reproduce from a rails new in under twenty lines. The throughput numbers will not reproduce on your hardware and are not meant to; the shape of them, reads and writes within 25 percent of each other, is the part worth checking.