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

How (and why) to run SQLite in production

fractaledmind.github.io

Post reply on HN
Print view View original

11–20 of 85 posts

Re: How (and why) to run SQLite in production

#11

Post by abbbi »

duped wrote: post #10
abbbi wrote: post #9

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-i... Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest…

What'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.

WAL 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.

Re: How (and why) to run SQLite in production

#12

Post by wokwokwok »

abbbi wrote: post #9

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-i... Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest…

What 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.

Re: How (and why) to run SQLite in production

#13

Post by stpn »

I've been running sqlite and litefs in production for my side project over the last year or so now.

It really is quite nice how much database you get for the cost and maintenance overhead required when litefs is thrown in the mix.

Re: How (and why) to run SQLite in production

#14

Post by skybrian »

I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1].

It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started?

[1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/pricing/spaces-object-storage

Re: How (and why) to run SQLite in production

#15

Post by jitl »

duped wrote: post #10
abbbi wrote: post #9

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-i... Really. At the point you are experiencing database locked in your productive app that uses sqlite as backend, i would strongly suggest…

What'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.

WAL allows one writer concurrent with any number of readers. It does not allow two concurrent writers.

Re: How (and why) to run SQLite in production

#17

Post by thrdbndndn »

hyperadvanced wrote: post #4
thrdbndndn wrote: post #2

Might want to proofread it. There are a few paragraphs that are repeating and out of place: > Why? So, let’s explore that together. Who here is running or has run an application in production with SQLite? Who has experimented with SQLite for an app, but not shipped it to production? There are a couple hands up, but not many. So, let’s turn this question around.

I think this is paraphrased or exact copy from a conference talk. Not that I love the rhetorical question mode of communication, but it’s more understandable.

I was not saying this particular paragraph itself is repeating.

It literally appears twice in the article at different places.

Re: How (and why) to run SQLite in production

#18

Post by thrdbndndn »

lxgr wrote: post #5
thrdbndndn wrote: post #2

Might want to proofread it. There are a few paragraphs that are repeating and out of place: > Why? So, let’s explore that together. Who here is running or has run an application in production with SQLite? Who has experimented with SQLite for an app, but not shipped it to production? There are a couple hands up, but not many. So, let’s turn this question around.

This pretty clearly seems to be a transcript of a conference talk, and redundancy and repetition are quite useful for getting a point across in spoken language. Maybe it could be more accessible when rewritten as a blog post, but if it’s a useful talk I’d much rather have an official transcript than only a video link and maybe Youtube’s auto-transcript. So much good content is buried in video form for me (I really do…

This paragraph appears twice in the article at different places.

I don't think the speaker said it twice word by word in the talk.

Re: How (and why) to run SQLite in production

#19

Post by stpn »

skybrian wrote: post #14

I went through this presentation looking for "how do you do backups" and it glosses over it. But the author blogged about that separately [1]. It seems he uses Litestream with DigitalOcean Spaces for this. Looks like they start at $5 per month for 250 GB [2]. Would that be the best way for a hobbyist to get started? [1] https://fractaledmind.github.io/2023/09/09/enhancing-rails-s... [2] https://www.digitalocean.com/p…

Fwiw, I've been using hosted LiteFS Cloud[0] (written by the same person who wrote litestream) on fly.io. The pricing starts at $5/mo for the first 10GB (kind of steep) but it's been working out so far.

(no affiliation to fly/litefs, just a fan)

[0]: https://fly.io/blog/litefs-cloud/

Post reply on HN