SQLite Production

Lean CMS is built for SQLite in production. This is not a compromise — 37signals runs HEY and Basecamp on SQLite. For a marketing site CMS, it is the right choice.

Why SQLite for marketing sites:

  • No separate database server to provision or pay for
  • No network latency between app and database
  • A single-file backup with VACUUM INTO
  • Rails 8 is designed for it
  • Read-heavy traffic patterns are SQLite’s sweet spot

Configuration 

# config/database.yml
production:
  adapter: sqlite3
  database: storage/production.sqlite3
  pool: 5
  timeout: 5000
  pragmas:
    journal_mode: wal
    synchronous: normal
    mmap_size: 134217728
    journal_size_limit: 67108864
    cache_size: 2000
    foreign_keys: true

WAL mode initializer

Add this to ensure WAL mode is active on every boot:

# config/initializers/sqlite3.rb
if Rails.env.production?
  Rails.application.config.after_initialize do
    ActiveRecord::Base.connection.execute("PRAGMA journal_mode = WAL")
    ActiveRecord::Base.connection.execute("PRAGMA synchronous = NORMAL")
    ActiveRecord::Base.connection.execute("PRAGMA mmap_size = 134217728")
    ActiveRecord::Base.connection.execute("PRAGMA journal_size_limit = 67108864")
    ActiveRecord::Base.connection.execute("PRAGMA cache_size = 2000")
  end
end

File storage to S3

Store uploads (images) in S3, not alongside the database:

# config/storage.yml
amazon:
  service: S3
  access_key_id: <%= ENV['AWS_ACCESS_KEY_ID'] %>
  secret_access_key: <%= ENV['AWS_SECRET_ACCESS_KEY'] %>
  region: <%= ENV['AWS_REGION'] %>
  bucket: <%= ENV['AWS_BUCKET'] %>

# config/environments/production.rb
config.active_storage.service = :amazon

This keeps your deployment clean: the SQLite file is the database, S3 is the media library, and you can replace either independently.

Backup strategy

Automated daily backups 

# lib/tasks/backup.rake
namespace :db do
  desc "Backup production database"
  task backup: :environment do
    timestamp = Time.now.strftime("%Y%m%d%H%M%S")
    backup_dir = Rails.root.join("backups")
    FileUtils.mkdir_p(backup_dir)

    db_file = Rails.root.join("storage", "production.sqlite3")
    backup_file = backup_dir.join("production_#{timestamp}.sqlite3")

    # VACUUM INTO is a hot backup — doesn't lock the database
    ActiveRecord::Base.connection.execute("VACUUM INTO '#{backup_file}'")

    if ENV["AWS_BACKUP_BUCKET"]
      s3_key = "database-backups/production_#{timestamp}.sqlite3"
      `aws s3 cp #{backup_file} s3://#{ENV["AWS_BACKUP_BUCKET"]}/#{s3_key}`
      File.delete(backup_file)
    end

    puts "Database backed up"
  end
end

Cron schedule 

# Daily at 2 AM
0 2 * * * cd /app && /usr/local/bin/rails db:backup

Restore 

# Stop the app
systemctl stop your-app

# Restore from backup
cp backups/production_20250111020000.sqlite3 storage/production.sqlite3

# Or from S3
aws s3 cp s3://your-backup-bucket/database-backups/production_20250111020000.sqlite3 storage/production.sqlite3

# Start the app
systemctl start your-app

Maintenance tasks 

# lib/tasks/maintenance.rake
namespace :db do
  desc "Optimize SQLite database"
  task optimize: :environment do
    ActiveRecord::Base.connection.execute("VACUUM")
    ActiveRecord::Base.connection.execute("ANALYZE")
    puts "Database optimized"
  end

  desc "Checkpoint WAL file"
  task checkpoint: :environment do
    ActiveRecord::Base.connection.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    puts "WAL checkpointed"
  end
end

# Weekly optimization (Sunday 3 AM)
0 3 * * 0 cd /app && /usr/local/bin/rails db:optimize

# Daily WAL checkpoint (4 AM)
0 4 * * * cd /app && /usr/local/bin/rails db:checkpoint

Useful commands 

# Check database size
du -h storage/production.sqlite3

# Check WAL size
du -h storage/production.sqlite3-wal

# Run backup manually
rails db:backup

# Optimize database
rails db:optimize

# Check database stats
rails db:stats

When to consider Postgres

You’ll know it’s time when you hit one of these:

  • Multiple app servers — SQLite is single-writer; it doesn’t work behind a load balancer
  • High write concurrency — multiple editors constantly publishing at the same time
  • Database > 100GB — unlikely for a CMS, but possible
  • Postgres-specific features — PostGIS, full-text search, JSONB queries

Switching is straightforward: change database.yml, run rails db:setup, and redeploy. Rails abstracts most of the differences.

Production checklist

  • WAL mode enabled
  • Daily backups to S3 configured
  • Health check endpoint working
  • Database optimization cron job scheduled
  • File uploads going to S3 (not local disk)
  • Fragment caching enabled
  • Test database restore process

Troubleshooting

“Database is locked”

  1. Confirm WAL mode is active: rails db:stats
  2. Increase timeout in database.yml: timeout: 10000
  3. Check for long-running transactions

WAL file growing too large 

rails db:checkpoint

Or check the journal_size_limit pragma in database.yml.