If you're using SQLite on Rails are you effectively constrained to one machine/server?
SQLite on Rails: The how and why of optimal performance
31–40 of 98 posts
Re: SQLite on Rails: The how and why of optimal performance
#32If you're using SQLite on Rails are you effectively constrained to one machine/server?
No, you can set up replication with, eg LiteFS where you have one writer and multiple read replicas.
That said, then you have operational overhead that defeats a part of the purpose with SQLite. In practice, you can get very far with a single machine and many CPUs (Postgres is ironically a good example of this). In eg Go you can easily parallelize most workloads. In rails, I don’t know if that’s possible. A quick search suggests there’s a GIL which can be limiting.
Re: SQLite on Rails: The how and why of optimal performance
#33Earlier quoted context omitted.
It is until you realize that using SQLite means you don't have to worry about N+1 queries, which actually does make a pretty big difference in Rails code.
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.
Right, but it effectively solves it (even if not theoretically).
Most applications won't come close to encountering the N+1 problem on Sqlite, whereas it comes early on in server-based databases.
Re: SQLite on Rails: The how and why of optimal performance
#34General 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…
Write events in parquet format and use DuckDB for your analytics?
Re: SQLite on Rails: The how and why of optimal performance
#35General 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 write large SQLite databases at disk speed with the right data model and schema.
What most people do as mentioned is either batch writes, or simply send them over some kind of channel to a single thread that is the designated writer, some kind of MPSC queue, and that queue effectively acts as a serialization barrier.
Either can work depending on your latency/durability requirements.
You also absolutely need to use the WAL journaling mode which allows concurrent reads/writes at the same time, N readers but only 1 writer, and you probably want to take a hard look at disabling synchronous mode, which forces SQLite to fsync everywhere all the time. In practice this sounds bad but consider your example: if you make one batched write every second, then there is always a 1-second window where data can be lost anyway. There's always a "window" where uncommitted data can be lost, it's mostly a matter of how small that window is, and if internal consistency of the system is preserved in face of that failed write.
In your case the lack of synchronous mode wouldn't really be that bad because your typical "loss window" would be much greater than what it implies. At the same time, turning off synchronous mode can give you an order of magnitude performance increase. So it's very well worth thinking about.
TL;DR use a single thread to serialize writes (or batch writes), enable WAL mode, and think about synchronous mode. If you do these things you can hit extremely fast write speeds quite easily.
Re: SQLite on Rails: The how and why of optimal performance
#36Earlier quoted context omitted.
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.
WAL mode makes those redundant. Just use WAL mode.
…what? No it doesn’t. Go read the article. Every optimization listed addresses a different performance aspect of SQLite.
Re: SQLite on Rails: The how and why of optimal performance
#37Earlier quoted context omitted.
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.
WAL mode makes those redundant. Just use WAL mode.
We all wish you were right. But alas life's not that simple.
I suggest reading the manual on the section: "Sometimes Queries Return SQLITE_BUSY In WAL Mode"
https://sqlite.org/wal.html#sometimes_queries_return_sqlite_...
Re: SQLite on Rails: The how and why of optimal performance
#38Earlier quoted context omitted.
https://www.sqlite.org/np1queryprob.html
Ah cool, thanks for the link! 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.
The other half of it that the sqlite page doesn't mention is that sqlite lacks query engine optimizations which would make one large query faster than many smaller queries. If you had a hypothetical in-process version of postgres which didn't have any IPC or networking overhead you'd still see benefits from performing fewer larger queries when using it because the query planner adds some overhead to small queries but makes complex queries faster.
Re: SQLite on Rails: The how and why of optimal performance
#39This 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.
I recall a post by Simon Willison https://simonwillison.net/2022/Oct/23/datasette-gunicorn/#be...
Thanks, he's even building a similar product as me on the same stack as me! shotscraper looks very similar to ArchiveBox.
Re: SQLite on Rails: The how and why of optimal performance
#40This 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.
Yes, there is: https://gcollazo.com/optimal-sqlite-settings-for-django/
Awesome, thanks.
I think @flexterra (aka gcollazo) should also add `"check_same_thread": False` to the recommended OPTIONS, right? Unless they left it out intentionally?