checklist.md

2026-07-11 Trying the migration to sqlite again; we previously tried on 2026-02-21 but had to revert. Today's PR is #1927. I'm not streaming this because lots of steps make it too easy to flash PII or secrets on the screen.

For all the gory details, we planned this in issue #539.

sqlite migration checklist:

• link to this gist from app/views/layouts/application.html.erb
• lobsters-deploy
• set the site in read-only mode (config/application.rb on prod)
• take mariadb backup to pushcx's desktop
• jj f; bump merge commit for notice + td's latest
• scp lib/tasks/migrate.rake l:lobsters/current/lib/tasks
• hatchbox panel: pause screensharing, add env vars (below)
• be rails dump_db
• scp db to my local dev
• local: edit config/database.yml from trilogy to sqlite (below)
• local: rm db/development/primary.sqlite3 and be rails db:create:primary and be rails db:schema:load:primary
• local: be rails load_db
• scp db/development/primary.sqlite3 l:lobsters/shared/storage/
• merge the PR
• pause streaming, delete DATABASE_URL (can't get relative with sqlite working immediately, tried sqlite://storage/primary.sqlite3 and config/database.yml has the absolute path)
• hatchbox: stop solid_queue process
• lobsters-deploy
• investigate exceptions - they're ok, the mariadb code was live for ~30s with the sqlite database config, so it threw syntax errors
• build the search indexes (below)
• inspect the slow query log for scary things
• take a sqlite backup to local dev
• run the backup script
• debug the backup script; it's not running the restic
• restore the restic backup to local
• if prod doesn't immediately melt down, rm read-only mode, notice, and trilogy gem
• hatchbox: start solid_queue process

database.yaml:

primary:
  #    <<: *trilogy
  #    database: lobsters
  <<: *sqlite3
    database: db/primary.sqlite3

ENV vars:

ENABLE_SLOW_QUERY_LOGS = true
SLOW_QUERY_THRESHOLD_MS = 100

build initial search indexes:

insert into comments_fts(rowid, comment) select id, comment from comments;
insert into story_texts_fts(rowid, title, description, body) select id, title, description, body from story_texts;

revert checklist

• revert the merge commit
• config/application.rb - set read_only = true
• hatchbox: stop puma
• hatchbox: stop background jobs
• be rails dump_db # sqlite -> dump.yml
• backup new dump.yaml down to local, move prod copy to ~ (so it's not rotated by deploy)
• restore DATABASE_URL
• be rails load_db # dump.yml -> mariadb
• lobsters-deploy
• hatchbox: restart puma
• hatchbox: restart bg jobs
• remove the layout note
• config/application.rb: set read_only = true
• lobsters-deploy
• prod: mv storage/primary.sqlite3* ~, back it up

followup chores

• rewrite my 'prod db -> dev' script to fire resticjob and restore from snapshot
• remove ~/.tmp/2026-07-11-dump.yml
• tomorrow 2027-07-11: confirm the automated backup script ran
• 2026-07-14: power off and image mariadb droplet
• 2026-07-21: delete mariadb droplet

History being re-written (to SQLite)

MariaDB -> SQLite.... seems like a downgrade unless I'm missing something?

Edit: yep i was

further reading here seemingly

And from the resulting PR:

Thanks for all this work, what a big project!

Any readers: we're on a call and going to migrate prod now.

🦀🦀🦀 lobste.rs is gone 🦀🦀🦀

wild to see in 2026, great stuff

inspiring move, let's gooo <3

Godspeed!

is this just fanta sea?

i am a heron. i haev a long neck and i pick fish out of the water w/ my beak. if you dont repost this comment on 10 other pages i will fly into your kitchen tonight and make a mess of your pots and pans

I was here for the SQLite migration!

2/22 never forget

btw can someone hire me, im looking for some k8s work, that'd be great, ty ty

to sqlite 😮

😂

🚀

What was the rationale for migrating to SQLite in the first place?

What was the rationale for migrating to SQLite in the first place?

lobsters/lobsters#1871

Here we go again!

🚀

One more for good measure

How big is the new sqlite database file?

Good luck! SQLite is awesome :)

Fingers crossed.

@simonw I just restored from backup to test that end-to-end, so I can conveniently say: 3.8G.

Fun experiment for offline analysis: export the database as column-oriented Parquet tables? If you have DuckDB installed, it "should" be a one-liner:

$ duckdb -c "ATTACH 'the_database.db' AS sqlite_db (TYPE SQLITE); EXPORT DATABASE sqlite_db TO 'the_output_directory' (FORMAT PARQUET);"

I wonder what that 3.8GB of sqlite storage would shrink to.

@tmoertel I don't know anything about duckdb, but I'd be happy to experiment on the next office hours stream. If you'd like to set up to call in and talk through fun with duckdb we could do that. Seems like a fun extension of the existing queries policy.

DigitalOcean has basic activity monitors. These charts cover the last 14 days to give a baseline; the spike on disk I/O near the right is the migration. CPU and load look normal, maybe spikier, and memory is down, which is wonderful.

@pushcx as SQLite is an embedded DB, I would expect CPU to get slightly more load; out of curiosity, how much MEM & CPU do you have on DO for the main Lobsters instance?

It's a s-4vcpu-8gb. They don't list the CPU, but /proc/cpuinfo lists 4 identical:

vendor_id	: GenuineIntel
cpu family	: 6
model		: 85
model name	: DO-Premium-Intel
stepping	: 7
microcode	: 0x1
cpu MHz		: 2494.136
cache size	: 4096 KB

After I see how Monday goes I may nudge up the box, but so far, so boring.