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

61–70 of 87 posts

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

#61

Post by persedes »

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

Hope this becomes a cultural norm outside of HN. You can have AI written text that had some actual human effort put into it [1], but sloppy AI written content should only be meant for anohter AI consumers and not humans.

1 - https://www.ilfoglio.it/il-foglio-ai/2025/03/22/news/a-first...

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

#62

Post by adhamsalama »

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…

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

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

#63

Post by FLeXMurphy »

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…

> This is the new witch hunt, or virtue trolling.

The site has had that forever though. You missed out on the last witch hunt of going after straight white men as the root of all evil.

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

#64

Post by Defletter »

adhamsalama wrote: post #62
rdsubhas wrote: post #60

Earlier quoted context omitted.

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…

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

"You must accept the truth from whatever source it comes." - Maimonides

I feel like "this is AI" needs to join the ranks "it's not that deep" and "this is a Wendy's" as an annoying thought-terminating cliche.

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

#65

Post by simonw »

simonw wrote: post #41
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…

A handy trick for column types is check constraints. You can define constraints on a column that ensure it is text that's valid JSON for example: CREATE TABLE documents ( id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK ( json_valid(data) json_type(data) = 'object' ) ); Or to ensure specific keys: CREATE TABLE documents ( id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK ( json_valid(data) AND json_type(data) = 'objec…

Correction to the above: it should use

  json_type(data, '$.name') IS 'text'
Using = fails because a missing key returns null and in SQLite null = 'text' is null: https://latest.datasette.io/_memory/-/query?sql=select+null%...

A CHECK constraint only fails when the expression is false, NULL counts as passing.

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

#66

Post by ForHackernews »

prirun wrote: post #54
ForHackernews wrote: post #24

Earlier quoted context omitted.

Postgres has transactional DDL: you can be applying migrations in one transaction while serving live traffic from the old schema in another. By tying the schema changes directly to the application deployment it becomes harder to apply a big migration without downtime. You can't apply the migration and then cut over traffic to new app instances once the migration is complete.

SQLite has transactional DDL: you can start a transaction, do a bunch of create tables, copy data from old tables into the new tables. If an error occurs during this and a rollback occurs, everything will be just like it was before the transaction started. If a commit occurs, the migration succeeds and everyone sees the new schema on their next transaction. In rollback mode, only the migration thread can be active be…

If you can accept downtime and you really don't need db concurrency, then that's great and SQLite is probably a good fit for you. There are many applications for which that isn't the case.

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

#67

Post by tracker1 »

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…

FWIW, in a lot of cases you aren't expecting even thousands of simultaneous users, so SQLite is a perfectly valid option. Not to mention services like Cloudflare D2 and Turso which build on SQLite as a core with different features for scale/concurrency.

A lot of people manage to run several containerized applications on a single VPS behind a reverse proxy for personal or small groups. Managing a full rdbms takes work supporting multiple applications, or spinning up multiple instances per app in said containerized flows takes up excess resources, where SQLite would do the job just fine.

Not everything is going to be running 5+ nines of operation with distributed workloads. Plenty of real things run on a decent server with a good enough backup system in place.

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

#68

Post by chasil »

dzonga wrote: post #42
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…

> - You have limited options for dealing with schema migrations. That's the biggest pain of dealign with SQLite.

Date and time are a deeper loss for me.

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

#69

Post by chasil »

tnodir wrote: post #49

> PRAGMA synchronous = NORMAL; > In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. No, this is not safe. You can lose the latest co…

Then WAL mode is not safe for you.

"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."

https://sqlite.org/lang_attach.html

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

#70

Post by tredre3 »

adhamsalama wrote: post #62
rdsubhas wrote: post #60

Earlier quoted context omitted.

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…

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!"
Post reply on HN