Slide 1
Slide 1 text
Stephen Margheim · 16 December 2023
SQLite on Rails
How (and why) to run SQLite in production
Slide 2
Slide 2 text
Who am I?
• Head of Engineering at Test IO
• Open source developer
• rails/rails
• sparklemotion/sqlite3-ruby
• oldmoe/litestack
• joeldrapper/phlex
• joeldrapper/literal
• fractaledmind/activerecord-enhancedsqlite3-adapter
• fractaledmind/litestream-ruby
• fractaledmind/acidic_job
• Writer: https://fractaledmind.github.io
Stephen Margheim · @fractaledmind
🇺🇸 🛫 🇩🇪 💒 🇷🇺
Slide 3
Slide 3 text
$ rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 4
Slide 4 text
$ rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 5
Slide 5 text
Why?
Slide 6
Slide 6 text
Why not?
Slide 7
Slide 7 text
No content
Slide 8
Slide 8 text
Simplicity
Slide 9
Slide 9 text
Simplicity
Control
Slide 10
Slide 10 text
Simplicity
Control
Speed
Slide 11
Slide 11 text
How?
Slide 12
Slide 12 text
MVP
Slide 13
Slide 13 text
$ rails new .
--database=sqlite3
--asset-pipeline=propshaft
--javascript=esbuild
--css=tailwind
--skip-jbuilder
--skip-spring
Slide 14
Slide 14 text
No content
Slide 15
Slide 15 text
Demo →
Slide 16
Slide 16 text
Deployment
Slide 17
Slide 17 text
No content
Slide 18
Slide 18 text
No content
Slide 19
Slide 19 text
No content
Slide 20
Slide 20 text
No content
Slide 21
Slide 21 text
No content
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
No content
Slide 24
Slide 24 text
No content
Slide 25
Slide 25 text
No content
Slide 26
Slide 26 text
No content
Slide 27
Slide 27 text
No content
Slide 28
Slide 28 text
No content
Slide 29
Slide 29 text
No content
Slide 30
Slide 30 text
No content
Slide 31
Slide 31 text
No content
Slide 32
Slide 32 text
No content
Slide 33
Slide 33 text
No content
Slide 34
Slide 34 text
No content
Slide 35
Slide 35 text
No content
Slide 36
Slide 36 text
No content
Slide 37
Slide 37 text
No content
Slide 38
Slide 38 text
No content
Slide 39
Slide 39 text
No content
Slide 40
Slide 40 text
No content
Slide 41
Slide 41 text
No content
Slide 42
Slide 42 text
No content
Slide 43
Slide 43 text
No content
Slide 44
Slide 44 text
No content
Slide 45
Slide 45 text
No content
Slide 46
Slide 46 text
Running
demo.sqliteonrails.com
Slide 47
Slide 47 text
$ hey -c N -z 10s -m POST /
https://demo.sqliteonrails.com/benchmarking/read_heavy
$ hey -c N -z 10s -m POST /
https://demo.sqliteonrails.com/benchmarking/write_heavy
$ hey -c N -z 10s -m POST /
https://demo.sqliteonrails.com/benchmarking/balanced
⇒ 16, 24, 22 ⇒ 21 RPS
N ⇒ [1, 2, 4, 8, 16, 32, 64, 128, 256] ×2
Slide 48
Slide 48 text
$ hey -c N -z 10s -m POST /
https://demo.sqliteonrails.com/benchmarking/balanced
⇒ 14 RPS
N ⇒ [1, 2, 4, 8, 16, 32, 64, 128, 256] ×2
Slide 49
Slide 49 text
Average Peak
2 34
3.5 ??
19.3 29.6
33 116
36.67 66.67
66 110
Slide 50
Slide 50 text
Running SQLite in
production with a Rails app
wasn’t viable 1 year ago.
But today, it is.
Slide 51
Slide 51 text
Rails default PRAGMAs
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA mmap_size = 134217728; -- 128 megabytes
PRAGMA journal_size_limit = 67108864; -- 64 megabytes
PRAGMA cache_size = 2000;
>= 7.1.0
Slide 52
Slide 52 text
SQLite default compilation
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
SQLITE_ENABLE_FTS5
Slide 53
Slide 53 text
Enhancing
Slide 54
Slide 54 text
Enhancing ActiveRecord
$ bundle add activerecord-enhancedsqlite3-adapter
Slide 55
Slide 55 text
Enhancing ActiveRecord
• deferred foreign keys,
• generated columns,
• PRAGMA tuning,
• extension loading, and
• improved concurrency support
How?
Slide 56
Slide 56 text
Enhancing ActiveRecord
$ bundle add sqlite-vss
# /config/database.yml
default: &default
adapter: sqlite3
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
timeout: 5000
extensions:
- sqlite_vss
Extension loading
Slide 57
Slide 57 text
Enhancing ActiveRecord
Improved concurrency support
Demo →
Slide 58
Slide 58 text
Enhancing ActiveRecord
busy_handler do |count|
timed_out = false
if (count % 100).zero?
timed_out = (count * retry_interval) > timeout_seconds
end
if timed_out
false
else
sleep(retry_interval)
true
end
end
Improved concurrency support
Slide 59
Slide 59 text
Fine-tuning compilation
$ bundle config set build.sqlite3 \
"--with-sqlite-cflags='
-DSQLITE_DQS=0
-DSQLITE_THREADSAFE=0
-DSQLITE_DEFAULT_MEMSTATUS=0
-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS
-DSQLITE_MAX_EXPR_DEPTH=0
-DSQLITE_OMIT_PROGRESS_CALLBACK
-DSQLITE_OMIT_SHARED_CACHE
-DSQLITE_USE_ALLOCA
-DSQLITE_ENABLE_FTS5'"
Slide 60
Slide 60 text
Extending
Slide 61
Slide 61 text
Branch-specific databases
# /config/database.yml
development:
<<: *default
database: storage/<%= `git branch --show-current`.chomp
|| 'development' %>.sqlite3
# /config/environments/development.rb
config.after_initialize do
ActiveRecord::Tasks::DatabaseTasks.prepare_all
end
Slide 62
Slide 62 text
Integrating Litestream
$ bundle add litestream
$ bin/rails generate litestream:install
Backups/replication
Slide 63
Slide 63 text
Integrating Litestack
$ bundle add litestack
$ bin/rails generate litestack:install
SQLite all the things!
Slide 64
Slide 64 text
• Database Server (e.g. PostgreSQL, MySQL)
• Cache Server (e.g. Redis, Memcached)
• Job Processor (e.g. Sidekiq, Goodjob)
• Pubsub Server (e.g. Redis, PostgreSQL)
• Fulltext Search Server (e.g. Elasticsearch, Mielisearch)
What?
Integrating Litestack
Slide 65
Slide 65 text
No content
Slide 66
Slide 66 text
Thank you