Lobste.rs is now running on SQLite

3 hours ago

SQLite migration was successfully deployed on Saturday, with positive results including reduced CPU and memory usage, faster site performance, and lower VPS costs.
The migration project was initiated due to concerns about MariaDB's future after K1's acquisition, with initial plans to move to PostgreSQL but ultimately choosing SQLite for pragmatism and simplicity.
Multiple pull request attempts were made, with a failed first deploy due to performance issues from full table scans and n+1 queries, which were fixed in subsequent attempts.
A successful second deploy occurred on July 11th, after thorough testing and fixes, with the site handling Monday traffic spikes well and minor issues promptly addressed.
Key lessons included SQLite's concurrency model (multiple readers, single writer via WAL mode), the importance of performance tuning, and the benefits of using simpler solutions like SQLite over more complex databases like PostgreSQL.
Backup strategy involves a nightly job using restic, and deployment uses Hatchbox with Capistrano for zero-downtime updates, leveraging SQLite's local file system and WAL mode for concurrent access.
Community contributions were acknowledged, and the migration is seen as a step toward ensuring the site's long-term sustainability, with SQLite proving suitable for the site's needs despite initial skepticism about concurrency.

Hasty Briefsbeta