[HN Gopher] SQLite on Rails: The how and why of optimal performance
___________________________________________________________________
SQLite on Rails: The how and why of optimal performance
Author : tosh
Score : 152 points
Date : 2024-09-11 17:49 UTC (5 hours ago)
(HTM) web link (fractaledmind.github.io)
(TXT) w3m dump (fractaledmind.github.io)
| rbranson wrote:
| 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.
| hu3 wrote:
| 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.
| rbranson wrote:
| WAL mode makes those redundant. Just use WAL mode.
| NortySpock wrote:
| I thought it was a pretty good list of common Rails-
| application-specific and sqlite3-specific knobs to turn, for
| newcomers to performance tuning. (Really just a guided tour
| though -- turn this knob to enable this particular tool for
| dealing with concurrency problems...)
| hu3 wrote:
| I can't imagine how many days the author took to write such
| detailed article.
|
| This is useful to anyone trying to scale SQLite web applications,
| even beyond Rails.
|
| So thank you!
| dorianmariefr wrote:
| much easier just to use pg
| jilles wrote:
| Not really. Setting a few sqlite options and enabling
| litestream for backups is much easier than setting up Postgres
| with reliable backups.
|
| Should you do this for all apps? No. Do you have read heavy
| applications? Consider SQLite
| vidarh wrote:
| I'm not using Rails, but I now have several sites using my own
| little thing that is a single docker container where all state
| + content is in a single sqlite file, and it's very nice to be
| able to just move that single file around. I love postgres, but
| doing the equivalent of that with Postgres is a lot more
| hassle.
| tptacek wrote:
| 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.
| abound wrote:
| 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.
| tptacek wrote:
| https://www.sqlite.org/np1queryprob.html
| abound wrote:
| 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.
| anothername12 wrote:
| Agreed. I always regret starting a new rails project with
| sqlite. Invariably I end up wanting some bunch of features only
| pg has.
|
| Even for running tens of thousands of integration tests in a
| few seconds, pg is fine.
| dewey wrote:
| I kind of have to agree, I recently thought I'll use sqlite in
| Rails for my new project to keep things simple but then
| realized it's actually more annoying for my use case. I'd need
| a persistent volume with the right permissions, and I can't
| just connect to my PG instance running on the server from my
| local machine to run some queries.
|
| I'm sure it makes things easier for some use cases but it's not
| a given.
| Alifatisk wrote:
| So this is Rails + Litstream, cool!
| iambateman wrote:
| 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 second.
|
| Does this seem like a reasonable way to get around the SQLite
| limitation where it struggles with lots of DB writes? Any better
| ideas?
| tptacek wrote:
| SQLite doesn't struggle with writes. But it only supports a
| single write transaction at a time; if you don't trust SQLite's
| transaction concurrency performance, you might serialize all
| your writes on a specific thread/process.
| iambateman wrote:
| Thanks!
| kccqzy wrote:
| I'd say that's a very reasonable and very good idea. I've
| implemented similar ideas in many other systems. Batching in
| general always reduces the per-entry overhead. It's easy to
| prove this via a benchmark. You can even put all the batched
| items in a single transaction. And since you are batching, you
| essentially will always have one thread that's getting the
| batch and actually doing the write, perfectly matched with
| SQLite's limitation of one concurrent write at a time.
|
| 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.
| hu3 wrote:
| For analytics it's hard to beat clickhouse in many aspects.
| It's quite portable too.
|
| I'd suggest taking a good look at it.
| duffyjp wrote:
| I do something similar for an audit trail at work. I work with
| the type of data where we may need to know who looked at what
| and when. All those records are stored in a separate SQLite DB
| (main DB is postgres), and I cycle it out once per calendar
| year. That makes archival trivial, and should a compliance
| person need to look at it a simple desktop app can open the
| file easily.
|
| 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.
| nop_slide wrote:
| I've been rediscovering rails recently and this article is super
| timely as I've begun using SQLite more, thanks!
| rootedbox wrote:
| Anyone that does work with SQLite should read this article.
| Independent of language / framework you are using.
|
| I had to figure most of this stuff out on my own years ago. Thank
| you for writing this!
| nikisweeting wrote:
| 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.
| DataDive wrote:
| I recall a post by Simon Willison
|
| https://simonwillison.net/2022/Oct/23/datasette-gunicorn/#be...
| BiteCode_dev wrote:
| Yes, there is: https://gcollazo.com/optimal-sqlite-settings-
| for-django/
| richjdsmith wrote:
| Anyone who is looking at using SQLIte + Rails should check out
| the work done by Oldmoe (X/Github) on his Litestack project.
|
| 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!
|
| https://github.com/oldmoe/litestack
| dwheeler wrote:
| Awesome, I'm always glad to see when someone figures out
| integration problems and helps the rest of us. I hope he manages
| to get these fixes into the default Rails confug.
|
| 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.
| aeze wrote:
| If you're using SQLite on Rails are you effectively constrained
| to one machine/server?
___________________________________________________________________
(page generated 2024-09-11 23:00 UTC)