4 ms·
You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes. A single transaction that does a v
by cldellow 4y ago
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.
- irskep 4y ago> 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.
- cldellow 4y agoBTW, for schema migrations, I've found the approach described here [1] to be pretty good! [1]: https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s...
- sanderjd 4y ago> Migrations are just writes It is possible to change the schema of a SQLite db as "just a write"?