Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite on Rails: The how and why of optimal performance

fractaledmind.github.io

Post reply on HN
Print view View original

81–90 of 98 posts

Re: SQLite on Rails: The how and why of optimal performance

#81

Post by JodieBenitez »

Some tweaks that I keep for my personal toy webservices:

    PRAGMA journal_mode = WAL;
    PRAGMA busy_timeout = 5000;
    PRAGMA synchronous = NORMAL;
    PRAGMA cache_size = 1000000000;
    PRAGMA foreign_keys = true;
    PRAGMA temp_store = memory;
And use BEGIN IMMEDIATE transactions.

https://kerkour.com/sqlite-for-servers

Re: SQLite on Rails: The how and why of optimal performance

#82

Post by klabb3 »

fractaledmind wrote: post #79
klabb3 wrote: post #32

Earlier quoted context omitted.

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 sea…

This is my perspective as well. You certainly can horizontally scale with SQLite, but I strongly recommend that you vertically until you hit an actual limit there. If you know you will absolutely need multiple app nodes on day 1 or day 10, I think you will probably be better served by choosing a client/server database like MySQL or PG instead. So, you aren’t limited to single machine, but you should stay single machi…

> If you know you will absolutely need multiple app nodes on day 1 or day 10

Yes, but there are a few options even then. First, you can of course tune http caching etc, find traditional bottlenecks.

Second, you can also break the business logic into a separate API endpoint that runs only SQLite + business logic + API responses. Then you can add more frontend nodes in case rendering and other things are most expensive.

The main downside is all logic practically has to be written in the same language as a monolith.

Re: SQLite on Rails: The how and why of optimal performance

#83

Post by tosh »

JodieBenitez wrote: post #81

Some tweaks that I keep for my personal toy webservices: PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA cache_size = 1000000000; PRAGMA foreign_keys = true; PRAGMA temp_store = memory; And use BEGIN IMMEDIATE transactions. https://kerkour.com/sqlite-for-servers

What is your opinion on cache_size vs mmap_size?

Re: SQLite on Rails: The how and why of optimal performance

#84

Post by sharps_xp »

rcaught wrote: post #59
richjdsmith wrote: post #25

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,…

Rails 8 will by default use the DB for cache, queues and WebSocket broadcasting - https://fly.io/ruby-dispatch/the-plan-for-rails-8/

though, sqlite will not be used for the websocket broadcasting

Re: SQLite on Rails: The how and why of optimal performance

#85

Post by fractaledmind »

sharps_xp wrote: post #84
rcaught wrote: post #59

Earlier quoted context omitted.

Rails 8 will by default use the DB for cache, queues and WebSocket broadcasting - https://fly.io/ruby-dispatch/the-plan-for-rails-8/

though, sqlite will not be used for the websocket broadcasting

Not so fast https://github.com/rails/rails/pull/52889

Re: SQLite on Rails: The how and why of optimal performance

#86

Post by fractaledmind »

klabb3 wrote: post #82
fractaledmind wrote: post #79

Earlier quoted context omitted.

This is my perspective as well. You certainly can horizontally scale with SQLite, but I strongly recommend that you vertically until you hit an actual limit there. If you know you will absolutely need multiple app nodes on day 1 or day 10, I think you will probably be better served by choosing a client/server database like MySQL or PG instead. So, you aren’t limited to single machine, but you should stay single machi…

> If you know you will absolutely need multiple app nodes on day 1 or day 10 Yes, but there are a few options even then. First, you can of course tune http caching etc, find traditional bottlenecks. Second, you can also break the business logic into a separate API endpoint that runs only SQLite + business logic + API responses. Then you can add more frontend nodes in case rendering and other things are most expensive…

Very true. There isn’t an actual limit. You can horizontally scale with SQLite if you want to or need to. I just think it is worth pushing vertical scaling as far as possible as long as possible. And I don’t actually believe that SQLite is the right tool for every problem or web app. Some apps absolutely should use managed PG/MySQL or serverless PG/MySQL. I think they are the statistical exception and 80% of web apps would be well served with SQLite. But for the other 20%, probably simpler to just start with PlanetScale

Re: SQLite on Rails: The how and why of optimal performance

#87

Post by fractaledmind »

rbranson wrote: post #42
adamtaylor_13 wrote: post #36

Earlier quoted context omitted.

…what? No it doesn’t. Go read the article. Every optimization listed addresses a different performance aspect of SQLite.

How does busy_timeout address a performance aspect of SQLite?

As I explain in the post, if you have multiple connections and consistent write load, the timeout will penalize older quereres and noticeably harm your long tail latency

Re: SQLite on Rails: The how and why of optimal performance

#88

Post by tracker1 »

hk1337 wrote: post #43

I like SQLite and I like Rails but this seems synonymous with using MS Access in a production environment.

It's not too dissimilar, that said, SQLite is much more performant than Jet (MS Access) for mostly read scenarios. Not to mention that computers and disk speed are much better today than decades ago, where Access use was larger. You can pretty easily hit tens of thousands of rps with SQLite and probably even Jet for mostly-read usage.

Most applications don't have even hundreds of thousands of simultaneous users, so SQLite can be a great fit. Where SQLite also shines in that it has clients in just about every platform/language you're likely to want to use.

Archival/backup/portability are also very nice use cases for SQLite. I've worked on projects where there are specific, time-boxed data input and had actively pushed for using SQLite per box and still feel it would have been better. Vs having a very complex schema with export/archive functionality as custom code. My idea would have allowed to simply copy a file as archive/backup and schema changes over time would not necessarily need to be accounted for as deeply.

YMMV, but it's definitely a decent solution for many problems. Much in that using PostgreSQL or another RDBMS is often a better solution over using a more scalable no-sql option for most applications. There has been a tendency to over-engineer things, and we're approaching a level of compute/io that is less and less likely to justify those efforts.

Re: SQLite on Rails: The how and why of optimal performance

#89

Post by tracker1 »

thih9 wrote: post #75

That was a satisfying and very informative read - thanks and congrats. Feature request: a similar article for other DBs, starting with PostgreSQL.

I've found that with a traditional RDBMS that schema and query structure often count for as much or more over specific tweaks. I have pushed for using a single-node CockroachDB configuration for local/development instances and then using hosted (cloud provided) PostgreSQL for production. The use of CDB is to allow for the potential of future scaling as needed, where PG is more widely available in an externally supported means from the start with CockroackLabs (cloud) as an optional step as well as self-managed.

Just my own $.02, you can definitely tweak an RDBMS, but it's definitely going to vary by use case and more work can definitely be needed (indexing in particular is a bit of a dark art).

Re: SQLite on Rails: The how and why of optimal performance

#90

Post by tracker1 »

vidarh wrote: post #10
dorianmariefr wrote: post #7

much easier just to use pg

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.

While I'm fine with using SQLite for these things, I would counter that a docker-compose file makes using a db with your app roughly as easy as a sqlite file, only in that you'd have have a data directory as a volume mount for the db. PostgreSQL and MySQL/MariaDB in particular are pretty easy to launch with a configured user/pass for the db/app specifically with docker/compose.
Post reply on HN