5 ms·
another article praising sqlite in production and working around a known sqlite limitation: concurrent writes. As they link in the article: > But, my favorite
by abbbi 2y ago
another article praising sqlite in production and working around a known sqlite limitation: concurrent writes.
As they link in the article:
> But, my favorite feature of the gem is its improved concurrency support.
> [..] https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-improving-concurrency/ https://fractaledmind.github.io/2023/12/11/sqlite-on-rails-i...
Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest to use another database backend that was designed with concurrent writes in mind.
Nothing against sqlite in production, its nice, as long as your workload meets its feature set.
- duped 2y agoWhat's wrong with PRAGMA journal_mode=WAL? It enables concurrent writes, at the expense of disk. Which to my understanding is the same as any other database backend with write ahead logging to enable concurrent writers. Anecdotally, I've seen the WAL file grow way too large even after all writes have finished and should shrink, but that's manageable.
- abbbi 2y agoWAL does not help with "database locked" situations. At some point you will see them even with WAL enabled, and your application frontend code has to deal with the timeout and retry or whatever.
- deleted 2y ago[deleted]
- adius 2y agoYou just need to set busy_timeout > 0, and you'll basically never have database locked situations. It's just unfortunate, that it's not activated per default: https://sqlite.org/forum/info/7e456bf5544ab128 https://sqlite.org/forum/info/7e456bf5544ab128
- abbbi 2y agoyeah, but that will slow down your app and may help in a short run, but you basically just shift the problem and make it even worse if your workload gets even higher.
- gwd 2y agoYou should only be waiting if someone else is writing. And yes, disallowing any concurrent writes, as SQLite does, is certainly going to be a bottleneck at some point, but unless you have a particularly write-heavy workload, it will probably get you pretty far.
- duped 2y agoThen use a different busy handler
- gwd 2y agoI'm relatively new to the DB field, and have only really used SQLite; but it seems obvious to me that if you're doing a transaction, particularly a complicated one, then you should expect your transaction to fail occasionally due to concurrent changes, and be executing your transaction in a loop. This should be true for any database. If you loop-retry all transactions which fail due to transitory effects, then you won't have a problem with "database locked" situations. Abysmally documented, but this is what I use for golang + sqlite: https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-20231226123807-692e47d03443/txutil https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312... EDIT: Typo
- jitl 2y agoWAL allows one writer concurrent with any number of readers. It does not allow two concurrent writers.
- wokwokwok 2y agoWhat both articles are saying is that concurrent writes under a certain threshold or heavy read-only activity is perfectly acceptable usage pattern for sqlite in production. ...the problem here is that historically rails (and others) use deferred transactions and that causes sqlite to fail even under trivial load conditions without simply waiting for the db to free for the next write, because people who've written the drivers don't understand how to use sqlite. If you use sqlite correctly under heavy load it's slow not unreliable. > as long as your workload meets its feature set. Sure... but to be fair that probably covers a lot of microservices and probably a lot of apps too. Obviously as you scale, it won't, so sure, it's a limited use case... but, heck, I've seen dozens of microservices each with their 'own database' (ie. read same RDS, with different database instances) that all fail at once when that instance goes down. Woops. Better? Worse? Hm. Sqlite is insanely reliable. I like isolated reliable services. It's not for everything, but nothing is... I think it's suitable for more use cases than you're giving it credit for.
- simonw 2y agoIn my experience most SQLite writes take less than 1ms. Do your writes really need to be concurrent if they run that fast? Hard to get upset about waiting for the current write to complete before you get your turn when we are talking delays measured in thousandths of a second. If you have more than 1000 writes per second then maybe this is something to worry about. The solution there is probably to run a slightly more powerful server!
- nikisweeting 2y agoSure the actual write might only take 1s but the transaction might lock for 10ms. Does SQLite support overlapping write transactions with locks on different rows? Also do you know if WAL2 mode changes anything? https://www.sqlite.org/cgi/src/timeline?r=wal2 https://www.sqlite.org/cgi/src/timeline?r=wal2
- nikisweeting 2y ago*typo, meant 1ms not 1s
- abbbi 2y agoback in the days where we hit this issue (mostly on windows systems) i used to create a little stress tool, you would be surprised how fast you reach the database-locked state. ive just put it here: https://github.com/abbbi/sqlitestress https://github.com/abbbi/sqlitestress maybe its useful for some people to simulate their workloads.
- swah 2y agoAnd once its locked do you have to hold all operations for X ms?
- ihateolives 2y agoTested it repeatedly on Hetzner CPX11 (2 vCPU, 2GB RAM) instance, got one lock around row 49000, then it resumed until the end.
- keyle 2y agoBefore you even need to consider postgres, you can batch your writes to sqlite! I am no sqlite fanboy, although I might be, but I found the industry seems to run to postgres for just about anything. I prefer simplicity first.
- mattmanser 2y agoHow it that simplicity? To batch updates makes the code far more complex. To install any full strength DB is trivial. I don't get the 'simplicity'?
- vidarh 2y agoThat depends entirely on what you're doing. If your workload is heavily transactional, then sure, that might add complexity. The simplicity is not having a separate process that can fail, and that requires fail over, and monitoring.
- mattmanser 2y agoYou're misunderstanding. No-one mentioned transactions, that's not what we're talking about. We're talking about concurrent writes. We're talking about batching inserts. Because SQLite can't handle a high throughput, so you batch a bunch of inserts together. If the only way to get performance is to batch inserts, then you've got to write a whole load of manual queue code to queue up X number of inserts to insert them all at once. Worse still, if your server crashes, bug, etc. you've just lost all those inserts. But you've already responded with 201s! So if you want any sort of guarantee, you've got to write even more code to cache them on disk or redis or something. You're basically re-implementing features of postgre, badly, to make up for SQLite's deficiencies, It really doesn't matter HOW you do it, it's the fact you have to do it at all. It's not simpler, it's more complicated. Installing/using a fully fledged DB is trivial these days.