SQLite lessons:

• The SQLite gem supports user defined functions (udfs) and we used it to implement some missing functions in SQLite like regexp, if and stddev so that we wouldn't have to deal with too many sql migration workarounds.
• 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.
• Collation in SQLite is rather weak compared to MariaDB. Lobste.rs used utf8mb4_general_ci in MariaDB, but used NOCASE in SQLite. The downside of NOCASE is that it only supports ASCII characters, not the full UTF case folding.
• Use the preferred Contentless-Delete Tables in SQLite for your full text search tables. These are not the default. I'm constantly surprised by the default choices of SQLite.
  [...]
  Overall lessons:
• 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.
• 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.

Wishes:

• 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.
• I wish creating a production like dataset would be much easier than having to manually write something and waiting a week.