4 ms·
Queuing and retry works most of the time. Only time I truly get "database is locked" is when I open the shell while other processes are accessing the DB
by cheez 8y ago
Queuing and retry works most of the time. Only time I truly get "database is locked" is when I open the shell while other processes are accessing the DB
- ngrilly 8y agoWhere do you queue: in memory? on disk? in a dedicated queue server (Redis, Kafka, ActiveMQ, etc.)? in another SQLite database?
- cheez 8y agoSo far, haven't needed queuing outside of a single thread in the application (I know, what happens if it crashes!) Hasn't happened in ~8 years. Won't cry if it does.
- ngrilly 8y agoI have an app that uses an in-memory queue too in one thread :-) I'm curious: why do you queue instead of directly writing to the database?
- cheez 8y agoAt the moment, I queue to batch insert real time data at ~5 second intervals. It seems less wasteful of resources, but quite frankly is probably not needed.
- ngrilly 8y agoAre all writes blocked during database migrations? ALTER TABLE is quite limited in SQLite (for example there is no DROP COLUMN). Have you missed this?
- cheez 8y agoAll writes are blocked during database migrations, so there is potential offline time. For example, when I added the index, I needed to be offline. Fortunately, my application is only needed during business hours. Yes I miss proper ALTER TABLE a lot when it would have been useful. But my experience shows me that I can design software pretty well following YAGNI and the occasional instances of when I need missing functionality is more than made up for the ease of use otherwise.
- ngrilly 8y agoThanks for sharing your experience with SQLite :-) What are the advantages, in your own experience, of using SQLite instead of something like PostgreSQL? With PostgreSQL, you could run database migrations during business hours and use a proper ALTER TABLE.
- cheez 8y agoMain reason is that SQLite is embedded with my application. There isn't a second, third or fourth "microservice" to spin up. I know Terraform/Docker, love them, but the gymnastics I need to orchestrate for my particular situation is not worth it. So for the tradeoff of complexity for functionality, SQLite hits a very good sweet spot for me.