A Rails 8.1 invoicing app for a carpentry business runs on SQLite on one server: what tells you one writer per file is no longer enough, and what do you change?
answer
- StatementTimeout in the error log
- a second app server cannot share the file
- separate files, separate write locks
- shorten writers before migrating
- move to PostgreSQL or MySQL
basics
~20 sThe signs are recurring ActiveRecord::StatementTimeout (database is locked) errors, writes that queue behind long transactions, and any need for a second app server or failover. Shorten transactions first; move to PostgreSQL or MySQL when you need more than one host.
solid answer
~40 sSQLite gives each database file one writer at a time, and Rails opens every transaction with `BEGIN IMMEDIATE`, so writes to `storage/production.sqlite3` run strictly one after another. That is plenty for a firm with a few staff. The warning signs are `ActiveRecord::StatementTimeout` errors after the 5-second busy wait, write latency that climbs with traffic, and requirements SQLite cannot meet: a second web server, a separate jobs host, or failover, since the file must sit on one machine's local disk. First fix the cheap things: shorter transactions, slow work moved to jobs, batched imports, and the Solid trio's tables kept in their own files. When you need more than one host or sustained concurrent writes, migrate to PostgreSQL or MySQL; that is a data migration and a deploy change, not a config flag.
go deeper
Recall that SQLite allows one writer per file and that the file must live on the app server's own disk.
Explain how BEGIN IMMEDIATE and the busy wait serialise writes, and why separate files for cache and queue help.
Diagnose lock contention from StatementTimeout and write latency, fix long transactions, and name the triggers for migrating.
Weigh SQLite's single-box simplicity against availability and growth needs, and time the move to a server database.
## What SQLite gives a Rails app Rails 8.1 makes SQLite a serious production option: the adapter enables WAL so reads continue during writes, waits on locks with `timeout: 5000`, and opens transactions with `BEGIN IMMEDIATE` so lock conflicts are waited out instead of failing. What it cannot change is SQLite's model: **one writer per database file at a time**, and every process that uses the file must be **on the same host**. For the carpentry firm's invoicing tool (a few staff creating quotes, invoices and payments, plus a handful of customer logins) that model fits well. One server, Puma with a few threads, Solid Queue running inside Puma, all opening the files in `storage/`. ## Signs the model is running out | Signal | What it means | |---|---| | `ActiveRecord::StatementTimeout` ("database is locked") in the error log | a writer waited the full `timeout:` and gave up | | p95 latency on writes rising with traffic while reads stay fast | writers are queuing on the single lock | | imports or month-end runs making the app sluggish for everyone | one long transaction holds the lock | | a request for a second web server or a separate jobs machine | the file cannot be shared across hosts | | a requirement for failover or replacing the server without downtime | there is no replica to promote | The last two are not about load at all. A SQLite database is a file on **one machine's local disk**. Running the same app on a second server means two separate databases, and putting the file on a network filesystem is unsafe because SQLite's WAL mode relies on shared memory between processes on the same machine. The generated deploy configuration even suggests moving job processing to a dedicated machine as you grow; with SQLite, the queue file would have to stay reachable from both, which it cannot. ## Cheap fixes before migrating Most early lock trouble is caused by the app, not the database: 1. **Shorten transactions.** Move PDF rendering, HTTP calls to a payment provider and email delivery out of `transaction` blocks. Under `BEGIN IMMEDIATE` every block holds the write lock from its first query to COMMIT. 2. **Push slow work to jobs.** A job that generates the month's statements takes many short transactions instead of one long request. 3. **Batch large writes.** Import a year of invoices in chunks of a few hundred rows per transaction. 4. **Keep separate data in separate files.** The generated production config gives cache, queue and cable their own SQLite files, so job polling and cache writes do not take the lock on the invoices file. Do not fold them back into the primary database. 5. **Scale up, not out.** More cores and more Puma threads on the same host still share the file correctly. ## Measuring before deciding - **Count the errors.** A daily count of `ActiveRecord::StatementTimeout` tells you whether contention is rare or routine. - **Time the writers.** Subscribing to the `transaction.active_record` notification gives each transaction's duration; a few slow ones usually explain the queue. - **Separate the causes.** Lock waits, pool waits (`ActiveRecord::ConnectionTimeoutError`) and slow queries have different fixes, so check which one you are seeing. ## When to move, and to what Move to **PostgreSQL** or **MySQL** when one of these is true: you need more than one app host, you need a standby database for failover, or sustained concurrent writes keep producing timeouts after the fixes above. Those engines run as servers that many hosts connect to, with row-level locking instead of one lock per file. The move is real work: - **Data migration**: schema via migrations is portable, but the rows must be exported and loaded, and column types SQLite stored loosely (it is permissive about types) may need cleaning first. - **SQL that relied on SQLite behaviour**: raw SQL in scopes, date functions and case-sensitivity of `LIKE` differ between engines. - **Operations**: a database server to run, back up and upgrade, and a `max_connections` budget that now matters across hosts. ## How to answer in an interview A strong answer neither dismisses SQLite nor oversells it: it names the single-writer and single-host limits, shows how to measure them (timeout errors, write latency), fixes transaction length first, and treats the move to a server database as a planned migration triggered by a concrete requirement such as a second host.
- Why does adding Puma workers on the same server not hit SQLite's single-host limit?Puma workers are processes on the same machine, opening the same files on the same local disk, which is exactly what SQLite supports. They still share one write lock per file, so more workers add read capacity and request concurrency, not write throughput. The limit bites only when a second machine needs the same database.
- Why do the generated Rails 8.1 production settings put Solid Queue's tables in their own SQLite file?The `queue` entry in database.yml points at `storage/production_queue.sqlite3`. Solid Queue polls and updates its tables constantly; in a separate file those writes take a different write lock, so they do not queue behind invoice saves, and invoice saves do not queue behind job bookkeeping.
- Says WAL mode allows several concurrent writers to one file
- Proposes sharing storage/ between servers over a network filesystem
- Raises timeout: to make database is locked errors go away
- Treats moving to PostgreSQL as only changing the adapter line
- Claims SQLite in Rails 8.1 is unfit for any production use