lobste.rs is now running on SQLite

<p>This past Saturday, <a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> and I deployed the <a href="https://github.com/lobsters/lobsters/pull/1927" rel="ugc">SQLite pull request</a> to production. We were waiting till this morning to see how it would react to the Monday traffic spike before making this post. Needless to say, SQLite seems to have passed with flying colors: cpu usage is down, memory usage is down, site seems to be snappier at least for me, 1/2 the vps cost once mariadb vps is taken down, and finally <a href="https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc?permalink_comment_id=6251610#gistcomment-6251610" rel="ugc">"We're having a quiet Monday."</a>. Finally <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-4959857588" rel="ugc">#539 Migrate to SQLite</a> was closed this morning.</p> <p>Let us know if you have any questions about the migration.</p> <p>Background Story:</p> <p>I got involved with this migration because back in 2019 I stumbled upon <a href="https://github.com/lobsters/lobsters/issues/539" rel="ugc">#539</a> and because I had lots of experience working with, managing and migrating largish databases, I left a <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-509041532" rel="ugc">comment</a> suggesting MySQL as an alternative, because of the compatibility between MariaDB and MySQL. At that time I wasn't planning on getting involved since there were already conversations in place to migrate to PostgreSQL.</p> <p>Fast forward to 2025, <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2604683262" rel="ugc">Rahul left a comment</a> mentioning K1's acquisition of MariaDB. A discussion around the details of migrating to postgresql proceeded. Then in February, <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2640076884" rel="ugc">Rahul asked "Can lobsters run on sqlite?</a> which included a very detailed post around SQLite.</p> <p>I officially showed interest in taking on this project in <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-2956952922" rel="ugc">June 2025</a>. I think this somehow got mentioned in <a href="https://lobste.rs/s/zzprkr/weekly_lobsters_office_hours" rel="ugc">lobsters office hours</a> but it has been so long since then that I don't rememeber for certain.</p> <p>In <a href="https://github.com/lobsters/lobsters/issues/539#issuecomment-3198784051" rel="ugc">August 2025</a> I opened my <a href="https://github.com/lobsters/lobsters/pull/1705" rel="ugc">first pull request attempt</a> when I got busy and couldn't attend to the PR. Github closed it as stale and I couldn't reopen it so I opened another PR. The <a href="https://github.com/lobsters/lobsters/pull/1871" rel="ugc">second PR attempt</a> included some performance testing, a <a href="https://github.com/lobsters/lobsters/blob/main/lib/tasks/migrate.rake" rel="ugc">database x to database y script</a> (since none of the existing mariadb/mysql to sqlite scripts satisfied me), debugging and thinking around data integrity.</p> <p>Then came the first deploy on Feb 21st. <a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> and I got on a call, came up with a <a href="https://gist.github.com/pushcx/eb7cdf2dc9707dc3ab9e7173d197ddfc/a11e6d5865776f3bb4e1adbc56582a3b1f99878c" rel="ugc">checklist</a> for the deployment. Everything went right up until the deployment of the PR. Once deployed the site was in readonly mode, but just the readonly traffic was spiking all the cpus to 100%. We couldn't figure out what the problem was so we decided to <a href="https://github.com/lobsters/lobsters/pull/1924" rel="ugc">revert</a>. I didn't feel great after that first failed deploy since I knew that performance could be a problem due to not having access to the production database.</p> <p>Two days after the failed deploy I opened the <a href="https://github.com/lobsters/lobsters/pull/1927" rel="ugc">3rd and final pr attempt</a>. I fixed some <a href="https://github.com/lobsters/lobsters/pull/1927/changes/e7630ffe5c0df9fc0a388db5b6b6e11eb4a4c407" rel="ugc">minor issues with search</a> that were discovered during the failed deploy, created a <a href="https://github.com/lobsters/lobsters/pull/1927/changes/c3c12e6f134c9d67c37c57a0390f9d3a2c9f26d8" rel="ugc">bulk data creation script</a> which took a week to get half of lobsters' data set size created locally, and committed the three changes that fixed the performance issues during the first deploy: <a href="https://github.com/lobsters/lobsters/pull/1927/changes/14a8c5f412cc23c887a5b809453596c02d8df6d1" rel="ugc">1</a>, <a href="https://github.com/lobsters/lobsters/pull/1927/changes/4dc58fc1d98ed393c4b447c71568bc0cb700896b" rel="ugc">2</a>, <a href="https://github.com/lobsters/lobsters/pull/1927/changes/4476ae3e50602b5e0d7805fef40fc94a81daaa39" rel="ugc">3</a>. The performance issues boiled down to SQLite doing full table scans on the largest tables in the database for 2 of queries and the 3rd one solved an n+1 issue. During the morning of the second deployment, I also added a <a href="https://github.com/lobsters/lobsters/pull/1927/changes/a62fc572c068f7df45ade8a5fad117dafc68cc00" rel="ugc">slow query log</a> just in case there were more performance issues during the deployment.</p> <p>Then came the second deploy on July 11th. <a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> and I got on a morning call and came up with a deployment and revert checklist. Everything was going smoothly and then <a href="https://lobste.rs/~pushcx" rel="ugc">@pushcx</a> merged and deployed the PR. Once deployed the site was still live and the cpu/memory usage was still good. This was a big relief for me. We monitored site metrics and irc for people mentioning issues, which a couple did and those were promptly fixed: <a href="https://github.com/lobsters/lobsters/commit/1dd9d5e4d7ecc7f924a88c8792ec44f7c90d4691" rel="ugc">1</a>, <a href="https://github.com/lobsters/lobsters/commit/e88a07e1f121cbae772340c29e482399b1e92b75" rel="ugc">2</a>. Overall the site seemed to work so we wrapped up the call and waited till Monday when the traffic spikes.</p> <p>Monday came and the site is stil happy so we're calling this a win and moving on.</p> <p>SQLite lessons:</p> <ol> <li>The SQLite gem supports user defined functions (udfs) and we used it to implement some missing functions in SQLite like <a href="https://github.com/lobsters/lobsters/blob/main/config/initializers/sqlite_functions.rb" rel="ugc">regexp, if and stddev</a> so that we wouldn't have to deal with too many sql migration workarounds.</li> <li>SQLite doesn't support unsigned bigints. Previously, the mariadb was using unsigned bigints for certain ids, so we had to switch those to bigints for the migration.</li> <li>Collation in SQLite is rather weak compared to MariaDB. Lobste.rs used utf8mb4_general_ci in MariaDB, but used <a href="https://www.sqlite.org/datatype3.html#collating_sequences" rel="ugc">NOCASE</a> in SQLite. The downside of NOCASE is that it only supports ASCII characters, not the full UTF case folding.</li> <li>Use the preferred <a href="https://www.sqlite.org/fts5.html#contentless_delete_tables" rel="ugc">Contentless-Delete Tables in SQLite</a> for your full text search tables. These are not the default. I'm constantly surprised by the default choices of SQLite.</li> </ol> <p>Rails lessons:</p> <ol> <li>The <a href="https://github.com/rails/rails/blob/v8.0.5/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L84-L91" rel="ugc">default PRAGMAs in Rails</a> seem to be working for lobsters.</li> <li>You typically don't think about it but database migrations are database specific. I had to move the old migrations out to an <a href="https://github.com/lobsters/lobsters/tree/main/db/old_migrations" rel="ugc">old migrations</a> directory so that db:migrate would continue to work.</li> </ol> <p>Lobsters codebase lessons:</p> <ol> <li>There is a <a href="https://github.com/lobsters/lobsters/blob/main/app/models/search_parser.rb" rel="ugc">search parser</a> in the lobsters codebase.</li> <li>I learned about <a href="https://github.com/lobsters/lobsters/blob/main/config/initializers/heinous_inline_partial.rb" rel="ugc">heinous_inline_partials</a> which is a hack to speed up rendering.</li> <li>The lobsters testsuite was essential in making sure I could migrate to SQLite without a ton of manual testing.</li> </ol> <p>Overall lessons:</p> <ol> <li>I think a key ingredient in making this work was good communication from everyone that participated. I don't think this would have been possible otherwise.</li> <li>Migrating the underlying database without having access to the production database is really hard to get right. This was my first underlying database migration without having access to production. One lesson I'll take away from this is that I'll make sure to have realistic dataset sizes before doing another underlying database migration in the future.</li> </ol> <p>Wishes:</p> <ol> <li>I wish we could say in a test, "Fail if you encounter any full table scans". Which would have caught the perf issues we experienced during the first deploy.</li> <li>I wish creating a production like dataset would be much easier than having to manually write something and waiting a week.</li> </ol>