database migration

Bilingual

lobste.rs is now running on SQLite
2 months ago
- Lobste.rs migrated from MariaDB to SQLite after planning since 2018, initially targeting PostgreSQL but switching to SQLite last year.
- The migration was completed recently and is now considered stable, with improvements in CPU usage, memory usage, performance, and reduced VPS costs.
- The Lobsters Rails app runs on a single VPS with multiple SQLite database files, including a 3.8GB primary database and smaller cache, queue, and security databases.
- The migration involved significant code changes detailed in a PR by Thomas Dziedzic, building on previous work, and serves as a case study for using SQLite in 2026.
Lobste.rs is now running on SQLite
2 months ago
- SQLite deployment completed successfully with improved performance and reduced costs.
- Migration process involved multiple PR attempts and performance fixes.
- Lessons learned include SQLite's concurrency model and backup strategies.
- Community collaboration and healthy expectations contributed to the project's success.
- SQLite chosen over PostgreSQL for simplicity and meeting project needs.
Lobste.rs is now running on SQLite
2 months 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.

database migration

lobste.rs is now running on SQLite

Lobste.rs is now running on SQLite

Lobste.rs is now running on SQLite