21–30 of 98 posts
ArchiveBox uses SQLite via django and I've run into exactly the issue the author describes in rails fairly often. It would be awesome to have a SQLite-layer solution that doesn't require serializing all my writes through some other channel in the app.
This is an excellent article! I wonder if there is any equivalent for Django? ArchiveBox uses SQLite via django and I've run into exactly the issue the author describes in rails fairly often. It would be awesome to have a SQLite-layer solution that doesn't require serializing all my writes through some other channel in the app.
https://simonwillison.net/2022/Oct/23/datasette-gunicorn/#be...
This is an excellent article! I wonder if there is any equivalent for Django? ArchiveBox uses SQLite via django and I've run into exactly the issue the author describes in rails fairly often. It would be awesome to have a SQLite-layer solution that doesn't require serializing all my writes through some other channel in the app.
General SQLite question for the group… I’m making a FOSS analytics system, and ease-of-installation is important. I want to send event data to a separate SQLite database, to keep analytics data separate from the main app’s data. I’m concerned about scaling, since even a modestly busy website could have 1000+ events per second. My thought is to store events in memory on the server and then make one batched write every…
What you need to worry about is slightly higher complexity: (1) what happens when a single batched write doesn't complete within one second; (2) what is the size of queue you store events in memory and whether it is unbounded or not; (3) if it is unbounded are you confident that overloading the server won't cause it to be killed by OOM (queueing theory says when the arrival rate is too high the queue size becomes infinite so there must be another mechanism to push back), and if it is bounded are you comfortable with dropping entries; (4) if you do decide to drop entries from a bounded queue, which entries you drop; (5) for a bounded queue what its limit is. These are very necessary questions that arise in almost every system that needs queueing. Thinking about these questions not only help you in this instance, but also in many other future scenarios you may encounter.
Here's the intro paragraph: "Litestack is a Ruby gem that provides both Ruby and Ruby on Rails applications an all-in-one solution for web application data infrastructure. It exploits the power and embeddedness of SQLite to deliver a full-fledged SQL database, a fast cache , a robust job queue, a reliable message broker, a full text search engine and a metrics platform all in a single package."
I'm currently using it on a project and can't say enough good things about it!
General SQLite question for the group… I’m making a FOSS analytics system, and ease-of-installation is important. I want to send event data to a separate SQLite database, to keep analytics data separate from the main app’s data. I’m concerned about scaling, since even a modestly busy website could have 1000+ events per second. My thought is to store events in memory on the server and then make one batched write every…
I'd suggest taking a good look at it.
Earlier quoted context omitted.
Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit? If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.
https://www.sqlite.org/np1queryprob.html
For others, the short-ish answer is that doing hundreds of SQL queries in response to a request (loading nested timeline elements in their case) in SQLite is fine because of the lack of networking/IPC overhead. The nature of N+1 queries is unchanged.
I run a Rails app; I switched to Postgres years ago and never looked back. Postgres is awesome. Still, it's great to have alternatives available, and I use sqlite for other tasks, so I know it has good capabilities too.
General SQLite question for the group… I’m making a FOSS analytics system, and ease-of-installation is important. I want to send event data to a separate SQLite database, to keep analytics data separate from the main app’s data. I’m concerned about scaling, since even a modestly busy website could have 1000+ events per second. My thought is to store events in memory on the server and then make one batched write every…
You can't beat SQLite for ease of use. I'd try it out and simulate some load to see if SQLite can keep up, if you keep your inserts simple I bet it can.
This is a really long blog post to just say that you should turn on the WAL if you want concurrency out of SQLite. All the other stuff is superfluous.
Can't agree. I learned about BEGIN IMMEDIATE TRANSACTION. And there's also busy_timeout. The article also explains why/how/when things occur in detail which is valuable.