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

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

micrologics.org

Post reply on HN
Print view View original

71–80 of 87 posts

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#71

Post by tredre3 »

yomismoaqui wrote: post #34
yladiz wrote: post #3

I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…

SQLite supports ALTER TABLE: https://www.sqlite.org/lang_altertable.html On Go you can embed your migrations in your binary: https://oscarforner.com/blog/2023-10-10-go-embed-for-migrati...

ALTER TABLE is very limited in sqlite3, though they've been improving it (search the page for " 3." to find the version-specific changes). Any significant schema change is usually simpler to create a new table and copy the data.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#73

Post by osinix »

andersmurphy wrote: post #4

Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process. > To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).

I have some experience with different databases and system designs. In one of my last projects I used SQLite. However, I used only one dedicated process for writing and other processes for read only. This might help.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#74

Post by hinkley »

firesteelrain wrote: post #47

Where I part ways a little with the recommendation is in the busy_timeout + BEGIN IMMEDIATE suggestion. On an embedded system, that's not really sufficient; I maintain a caching service that is used by thousands of clients. It ingests data over MQTT and has a web interface for queries. In my design, there is a MQTT thread and pruning thread. The MQTT ingestion thread and the pruning thread can both end up "contending…

One of the lessons I picked up from my very brief foray into the world of realtime software is the idea of amortization. If you have one activity that is possibly time-unbounded, then the solution is to trade away a bit of throughput for certainty by taxing the frequent task with making incremental progress on the cleanup. The first real-time garbage collectors worked this way. Every allocation had to do a bit of sweeping work, which generally keeps all but the largest allocations from every being in danger of hitting a pause (in real time systems large allocations are typically done at bootstrapping time to avoid this issue entirely)

> Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.

Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#75

Post by persedes »

rdsubhas wrote: post #60
smartmic wrote: post #32

Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated): - https://sqlite.org/whentouse.html - https://sqlite.org/different.html - https://sqlite.org/quirks.html

I have to disagree with this as off topic. The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice. I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at th…

Try some of the suggestions there and see what works vs doesn't, hell feed it to an LLM and ask what in there is correct.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#76

Post by andersmurphy »

andersmurphy wrote: post #43
mikeocool wrote: post #36

Earlier quoted context omitted.

I dunno, not losing data and minimizing unnecessary downtime is table-stakes for a pretty significant class of businesses. If for no other reason, dealing with these at any scale often distracts from running your actual business.

Sqlite with litestream has a smaller default window of data loss than RDF. 1s vs 5 minutes.

RDS*

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#77

Post by ezst »

rdsubhas wrote: post #60
smartmic wrote: post #32

Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated): - https://sqlite.org/whentouse.html - https://sqlite.org/different.html - https://sqlite.org/quirks.html

I have to disagree with this as off topic. The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice. I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at th…

I'm more than aware that my comment won't move the needle on the AI debate a slight bit, but I do think that the readership deserves some honesty and transparency there. No, not a single LLM ever has "deployed SQLite in Production", had to be on duty during the weekend, had to pop open a terminal to a misbehaving service while on family vacation, nor meaningfully suffer the anguish and stress to hotfix a botched or misconfigured deployment. Only humans have. What makes or breaks a software product, costs you your holiday, and perhaps even your job, probably won't be found in some generic piece of common knowledge rehashed and repackaged with some stylistic tweaks. It's the fine details, the one-off things, those weird edge-cases and compounded factors that in hindsights were obvious but had to be learned the hard way, and hopefully, shared by benevolent contributors.

That's the whole reason why many here put the effort to read/post articles and argue over comments: so there's something non-obvious to be learned. LLMs are effectively "knowledge averagers". They will put together a pretty article that's not worth reading, except maybe if the problem space is completely new to you. And that's the kind of disclaimer I would hope to see ahead of anything LLM-produced, and particularly on this website.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#79

Post by deterministic »

smartmic wrote: post #32

Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated): - https://sqlite.org/whentouse.html - https://sqlite.org/different.html - https://sqlite.org/quirks.html

> Unfortunately written by an AI - that completely takes the wind out of the content

I don't think the all-too-common "written by AI" remarks are helpful.

The article was informative and useful. That's what matters IMHO.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#80

Post by skavi »

tredre3 wrote: post #70
adhamsalama wrote: post #62

Earlier quoted context omitted.

Even your comment looks like it's generated by AI.

I think this is deliberate to make a point. Even an AI doesn't use that many em dashes. GP wants you to question it as a "haha see? I'm human yet you called me an AI just like I thought!"

an unprompted LLM would not incorrectly add spaces on either side of their em dashes.
Post reply on HN