This article documents several practises when deploying SQLite as a production database for a Rails app.

1) Separate databases

In SQLite, each database file has its own locking and WAL state. Separating main, queue, cache, and cable into different files helps isolate spikes in each file.

development:
  primary:
    <<: *default
    database: storage/main.sqlite3
  queue:
    <<: *default
    database: storage/queue.sqlite3
    migrations_paths: db/queue_migrate
  cache:
    <<: *default
    database: storage/cache.sqlite3
    migrations_paths: db/cache_migrate
  cable:
    <<: *default
    database: storage/cable.sqlite3
    migrations_paths: db/cable_migrate

2) Tune configuration via pragmas

  • cache_size - The SQLite page cache size, which has a default of 2MB. More in-memory cache reduces disk operations.
  • busy_timeout - Default is 0, which results in a BUSY error when the database is locked. Setting a timeout allows it to retry.
  • temp_store - Use memory instead of disk for temporary files.

Some of these tuning trade off memory usage for better performance. Please consider carefully if you are constrained by memory size.

default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000
  pragmas:
    journal_mode: wal
    synchronous: normal
    cache_size: -8192
    mmap_size: 0
    temp_store: memory
    busy_timeout: 5000
    foreign_keys: on

3) Backups

Please also set up a backup plan and test it by restoring it.