2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`.
3) Don't have long-running transactions.
4) Have some process to do backups.
(3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite system with writers, they're the difference between an amazing experience and a garbage one.
I'm hopeful that Fly can eventually make (4) painless by having super-easy out-of-the-box litestream and S3 backups. Until then, roll your own cron scripts or what-have-you.
I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4)
However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database".
I guess this is also the piece that I'm missing. How do I run migrations? Do I deploy a new version with the migration and temporarily take down the server? I'm glad to do that.
I guess I'm also walking through this because---as I said---I'd love just to switch to SQLite but I'm still not sure how many simple non-esoteric gotchas will pop up.
You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.
A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes.
I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, simple SELECT queries experience high latency (what would be a 1ms query takes 100-200ms), because they're blocked while the WAL gets checkpointed into the main database.
If you have much lower volume of writes, the checkpoint is smaller and so completes much faster, and so the worst case latency is much better. Since crawling is a non-interactive process, I don't care about the worst case latency. If I was writing a website, I'd feel differently--but most websites won't do 5,000 writes/second.
> You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.
Thank you for explicitly saying this! I'll see if I can update my repo to allow for online migrations. I was trying to be as conservative as possible based on what I knew at the time. It'll be nice to update the migration steps to be simpler.
I think it's worth noting that this combination loses committed transactions on kernel crash / power failure / etc. That is, your app replied "OK" to the client, but the data still gets lost.
> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.
1) When you open the database:
2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`.3) Don't have long-running transactions.
4) Have some process to do backups.
(3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite system with writers, they're the difference between an amazing experience and a garbage one.
I'm hopeful that Fly can eventually make (4) painless by having super-easy out-of-the-box litestream and S3 backups. Until then, roll your own cron scripts or what-have-you.