Hacker News Reader

Comment by bravura | Hacker News Reader

Thank you for the thoughtful response.

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.

View on HN

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.

BTW, for schema migrations, I've found the approach described here [1] to be pretty good!

[1]: https://david.rothlis.net/declarative-schema-migration-for-s...

> Migrations are just writes

It is possible to change the schema of a SQLite db as "just a write"?

Yes. SQLite supports transactional DDL.

Thanks! The article someone posted in another comment in this thread seemed a lot more complicated than that.

Reply on news.ycombinator.com