Learning a few things about running SQLite
3 hours ago
- #Database Management
- #SQLite
- #Django ORM
- Using SQLite in production for small websites is viable but requires learning about database operations.
- Running ANALYZE on SQLite databases significantly improves query performance, as seen when a query dropped from 5 seconds to 0.05 seconds.
- Cleanup operations like deleting rows can cause timeouts and crashes if they take too long, so handling them in small batches or during maintenance is recommended.
- Django ORM queries generally perform well without optimization for small databases (e.g., 10,000 rows).
- SQLite backups can be done using tools like Restic for full backups or Litestream for incremental backups, with considerations for memory usage and efficiency.
- Splitting tables into multiple SQLite database files can be beneficial for some projects, as demonstrated with Mess with DNS.
- Learning basic SQLite features, such as ANALYZE, can take time, even after years of use.