Switch to SQLite

[northeastprince]

It's not common that the "step up" happens in reverse, but with recent advances in SQLite and Rails' optimized configuration for it (meaning concurrent reads and writes are now possible), I believe it will yield massive benefits and reduce operational complexity.

SQLite is just a file. With modern SSD technology, this enables lightning-fast queries even without caching. The performance improvement would be especially noticable since we're currently using Heroku for Postgres (even though the app server is hosted by Hetzner) requiring traversal between data centers - potentially in different regions - just to connect to the database. With Mission Control for Solid Queue coming out soon, this will pave the way to eventually remove Redis too, eliminating the need for separate processes to store data and the "convenience" markup for doing so. We can also expand the existing public database dumps feature to periodically backup the whole database for disaster recovery (but only publicly show backups of public tables).

Proposed Procedure

• Make the schemas compatible (JSONB -> string, etc.)
• Prepare a PR that switches the Postgres adapter to SQLite and persists it between deploys
• Put the app into some sort of maintenance mode to inform people it'll be up soon
• Make a backup of the Postgres database in case something goes wrong
• Migrate the Postgres data to an SQLite file
• Merge the PR and deploy
• Bring the app back up and monitor carefully