6 ms·
SQLite on Rails: The how and why of optimal performance
- rbranson 2y agoThis 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 2y agoCan'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 2y agoWAL mode makes those redundant. Just use WAL mode.
- adamtaylor_13 2y ago…what? No it doesn’t. Go read the article. Every optimization listed addresses a different performance aspect of SQLite.
- rbranson 2y agoHow does busy_timeout address a performance aspect of SQLite?
- fractaledmind 2y agoAs 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
- hu3 2y agoWe 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_busy_in_wal_mode https://sqlite.org/wal.html#sometimes_queries_return_sqlite_...
- rbranson 2y agoYeah, this almost never happens in practice. It’s not even worth being concerned about.
- nikisweeting 2y agoHappened all the time to me before I did some tuning Depends how much write contention you have and how long open transactions take to finish.
- rbranson 2y agoThe list of circumstances for WAL-mode busy errors is in the doc linked by one of the posters above. It has nothing to do with transactions.
- ncruces 2y agoThat list lists reasons queries might return busy; queries aka. reads. Reads returning busy is rare under WAL, but WAL mode does very little for writer-writer contention.
- NortySpock 2y agoI 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...)
- deleted 2y ago[deleted]
- hu3 2y agoI 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 2y agomuch easier just to use pg
- jilles 2y agoNot 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 2y agoI'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.
- tracker1 2y agoWhile 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.
- vidarh 2y agoDocker compose itself introduces a lot of complexity I don't need with that setup.
- tracker1 2y agoIf you're comparing to SQLite, sure... if you're comparing to installing and configuring an RDBMS server on a host OS for development work, I'm going to hard disagree. Most services already have a hosted docker container configured, usually by the developers of said service. Getting that running is often as simple as googling "service-name docker-compose example" and "docker compose up". And once you do understand docker-compose, it becomes second nature. I'd be willing to state that dealing with a merge conflict with source control is more difficult than docker-compose.
- Alifatisk 2y agoSo this is Rails + Litstream, cool!
- iambateman 2y agoGeneral 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 2y agoSQLite 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 2y agoThanks!
- rodgerd 2y agoWrite events in parquet format and use DuckDB for your analytics?
- notpushkin 2y agoThis. If you’re looking for something portable, DuckDB is hard to beat. It’s pretty much the SQLite for analytics. I’ve had good experience with ClickHouse, too, but it feels a bit more like Postgres rather than SQLite in terms of portability.
- tosh 2y agoTake a look at chDB and clickhouse-local. Clickhouse can do a lot even without running it as a server. https://clickhouse.com/docs/en/chdb https://clickhouse.com/docs/en/chdb https://clickhouse.com/docs/en/operations/utilities/clickhouse-local https://clickhouse.com/docs/en/operations/utilities/clickhou...
- nop_slide 2y agoI’ve been rediscovering rails recently and this article is super timely as I’ve begun using SQLite more, thanks!
- rootedbox 2y agoAnyone 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 2y agoThis 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 2y agoI recall a post by Simon Willison https://simonwillison.net/2022/Oct/23/datasette-gunicorn/#benchmarking-sqlite https://simonwillison.net/2022/Oct/23/datasette-gunicorn/#be...
- nikisweeting 2y agoThanks, he's even building a similar product as me on the same stack as me! shotscraper looks very similar to ArchiveBox.
- BiteCode_dev 2y agoYes, there is: https://gcollazo.com/optimal-sqlite-settings-for-django/ https://gcollazo.com/optimal-sqlite-settings-for-django/
- nikisweeting 2y agoAwesome, thanks. I think @flexterra (aka gcollazo) should also add `"check_same_thread": False` to the recommended OPTIONS, right? Unless they left it out intentionally?
- richjdsmith 2y agoAnyone 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 https://github.com/oldmoe/litestack
- rcaught 2y agoRails 8 will by default use the DB for cache, queues and WebSocket broadcasting - https://fly.io/ruby-dispatch/the-plan-for-rails-8/ https://fly.io/ruby-dispatch/the-plan-for-rails-8/
- sharps_xp 2y agothough, sqlite will not be used for the websocket broadcasting
- fractaledmind 2y agoNot so fast https://github.com/rails/rails/pull/52889 https://github.com/rails/rails/pull/52889
- dwheeler 2y agoAwesome, 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.
- fractaledmind 2y agoI’m very excited that yes indeed we have the four major pillars in Rails 8, which is releasing soon, but can be used now via the main branch. The default, out-of-the-box experience with Rails 8 will go all in on SQLite, a database will be the only dependency, and you will have a production-ready app with jobs, cache, web sockets, and a primary database ready from `rails new`. I’ll be talking more about this all at Rails World in a couple weeks and that talk will be on YouTube sometime after that. But exciting times are ahead for Rails, for sure.
- aeze 2y agoIf you're using SQLite on Rails are you effectively constrained to one machine/server?
- klabb3 2y agoNo, 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.
- rcaught 2y agohttps://guides.rubyonrails.org/active_job_basics.html https://guides.rubyonrails.org/active_job_basics.html
- fractaledmind 2y agoThis 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 machine as long as possible and extract as much value from that operational simplicity before you trade that simplicity for some kind of horizontal scale
- klabb3 2y ago> 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.
- hk1337 2y agoI like SQLite and I like Rails but this seems synonymous with using MS Access in a production environment.
- bryan2 2y agoI mean this works for Pieter Levels. Definitely will cause issues when you get above a certain threshold of users.
- eric-hu 2y agohttps://levels.io/remote-ok/ https://levels.io/remote-ok/ 2015 > It’d analyze the feed, see which jobs were remote, then normalize the data and then push it into a simple SQLite database (yes, I’m not using JSON text files as a database anymore, thank you :P). https://levels.io/how-i-build-my-minimum-viable-products/ https://levels.io/how-i-build-my-minimum-viable-products/ 2014 > This includes all data used by the app. I actually shy away from using real database systems in the 12 startups. Instead I use JSON text files. I was hoping to find data on Pieter's current projects using Sqlite and their loads. (For all I know, RemoteOK still does, but I can't find more recent posts about it).
- notpushkin 2y agoI would love to have something like MS Access but for web apps. I’ve tried various website builder products but nothing seems as straightforward as Access was.
- tptacek 2y agoWhy is that? Especially if you have a read-heavy application, what is the scaling limit you're worried about?
- tracker1 2y agoIt'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.
- VWWHFSfQ 2y agoI'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would bloat the code and the size of the embedded object. So you're stuck with "everything is a string". Ref integrity can be enabled, but even those constraint options are very limited. Not sure why people are still trying to shoe-horn it into a role that it's not meant to be in, and not even really supported to be.
- randomdata 2y agoWe eventually realized that the "API server" was actually just a DBMS all along. And once that was realized, it was realized that a DBMS sitting beside another DMBS that deals with the exact same data is rather silly, which is now leading to: 1. Let the clients connect to Postgres directly. - or - 2. Cut Postgres out of the picture and double down on the homegrown DMBS. Some are going the #1 route, others #2. Where #2 is opted, SQLite is a convenient engine on which to build upon. It may not be perfect but it is what we have. Keep in mind that this realization on a grand scale (I'm sure some noticed many years ago) is fairly recent, so there is a lot of experimenting going on to figure out what works and what doesn't. It's the natural cycle of computing. What is old is new again. (Replace Postgres with MySQL, MSSQL, Oracle, or other DBMS as you see fit.)
- tptacek 2y agoFirst off, I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. Second: the reasons are straightforward: * For read-heavy access patterns, SQLite is crazy fast. * It's fast enough that you can often simplify your database access code; for instance, N+1 queries are often just not a problem in practice. * SQLite removes a whole tier from the N-tier architecture, which in turn removes a whole set of things that can go wrong (and if you've ever managed your own Postgres or MySQL: things do go wrong). It's not a perfect fit for every application, or even the majority of applications, but the push you're seeing is a correction against the pretty clearly false idea that SQLite is well suited only for "tiny embedded client-side application databases".
- fulafel 2y ago> By design, the sqlite3-ruby gem does not release the GVL when calling SQLite. For the most part, this is a reasonable decision [...] Following the issue comment link https://github.com/sparklemotion/sqlite3-ruby/issues/287#issuecomment-615346313 https://github.com/sparklemotion/sqlite3-ruby/issues/287#iss..., it sounds like they they had a suspicion about a significant cost of reacquiring the lock but didn't validate it. Sounds iffy especially given all this workaround effort. I feel in eg Python extensions culture this would have gotten designed the other way (maybe someone knows how it's done there?). edit: also, there's this other comment in the linked issue: > The extralite gem is an alternative SQLite client which releases the GVL during blocking, see note on concurrency here: https://github.com/digital-fabric/extralite?tab=readme-ov-file#concurrency https://github.com/digital-fabric/extralite?tab=readme-ov-fi.... It is both significantly faster than this gem in general and doesn't have concurrency issues.
- fractaledmind 2y agoYou can read more discussion here: https://github.com/sparklemotion/sqlite3-ruby/pull/528 https://github.com/sparklemotion/sqlite3-ruby/pull/528 and here: https://github.com/digital-fabric/extralite/pull/46 https://github.com/digital-fabric/extralite/pull/46 to see how it was validated that simply releases the GVL for every `step` in the SQLite VM majorly hurts single-threaded performance. Finding a middle ground for both single threaded and multi-threaded performance is tricky. In Rails, we know it is multi-threaded because of the connection pool. But the lower level gem is used in many other libraries and tools where it is used in a single threaded environment
- shafyy 2y agoVery informative article and well written! I wonder why the default `busy_timeout` method has this exponential delay that punishes old old queries. Why does this make sense as the default?
- thih9 2y agoThat was a satisfying and very informative read - thanks and congrats. Feature request: a similar article for other DBs, starting with PostgreSQL.
- tracker1 2y agoI'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).
- JodieBenitez 2y agoSome 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 https://kerkour.com/sqlite-for-servers
- tosh 2y agoWhat is your opinion on cache_size vs mmap_size?
- JodieBenitez 2y agoSorry, no opinion, I just copied these from the article and it works fine enough for me.