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

SQLite in Production: Lessons from Running a Store on a Single File

ultrathink.art

Post reply on HN
Print view View original

61–70 of 135 posts

Re: SQLite in Production: Lessons from Running a Store on a Single File

#61

Post by jmull »

simonw wrote: post #45
Retr0id wrote: post #34

Earlier quoted context omitted.

> I think you're exactly right about the WAL shared memory not crossing the container boundary. I don't, fwiw (so long as all containers are bind mounting the same underlying fs).

I just tried an experiment and you're right, WAL mode worked fine across two Docker containers running on the same (macOS) host: https://github.com/simonw/research/tree/main/sqlite-wal-dock... Could the two containers in the OP have been running on separate filesystems, perhaps?

I dug into this limitation a bit around a year ago on AWS, using a sqlite db stored on an EFS volume (I think it was EFS -- relying on memory here) and lambda clients.

Although my tests were slamming the db with reads and write I didn't induce a bad read or write using WAL.

But I wouldn't use experimental results to override what the sqlite people are saying. I (and you) probably just didn't happen to hit the right access pattern.

Re: SQLite in Production: Lessons from Running a Store on a Single File

#63

Post by jmull »

Retr0id wrote: post #50
gcr wrote: post #38

Earlier quoted context omitted.

The SQLite documentation says in strong terms not to do this. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... See more: https://sqlite.org/wal.html#concurrency

They tell you to use a proper FS, which is largely orthogonal to containerization.

WAL relies on shared memory, so while a proper FS is necessary, it isn't going to help in this case.

Re: SQLite in Production: Lessons from Running a Store on a Single File

#65

Post by qingcharles »

infamia wrote: post #14

SQLite has a ".backup" command that you should always use to backup a SQLite DB. You're risking data loss/corruption using "cp" to backup your database as prescribed in the article. https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_...

"I know about the .backup command, there's no way I'm using cp to backup the SQLite db from production."

Oh.

Guess I know what I'm fixing before lunch. Thank you :)

Re: SQLite in Production: Lessons from Running a Store on a Single File

#66

Post by rogerbinns »

infamia wrote: post #14

SQLite has a ".backup" command that you should always use to backup a SQLite DB. You're risking data loss/corruption using "cp" to backup your database as prescribed in the article. https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_...

Related, there is also sqlite3_rsync that lets you copy a live database to another (optionally) live database, where either can be on the network, accessed via ssh. A snapshot of the origin is used so writes can continue happening while the sqlite3_rsync is running. Only the differences are copied. The documentation is thorough:

https://sqlite.org/rsync.html

Re: SQLite in Production: Lessons from Running a Store on a Single File

#67

Post by rcakebread »

crabmusket wrote: post #5
yokuze wrote: post #3

> The technical fix was embarrassingly simple: stop pushing to main every ten minutes. Wait, you push straight to main? > We added a rule — batch related changes, avoid rapid-fire pushes. It's in our CLAUDE.md (the governance file that all our AI agents follow): > Avoid rapid-fire pushes to main — 11 pushes in 2h caused overlapping Kamal deploys with concurrent SQLite access. Wait, you let _Claude_ push your e-commer…

Patient: doctor, my app loses data when I deploy twice during a 10 minute interval! Doctor: simply do not do that

Bob Newhart did it best https://www.youtube.com/watch?v=LhQGzeiYS_Q

Re: SQLite in Production: Lessons from Running a Store on a Single File

#68

Post by NewsaHackO »

Natfan wrote: post #10

llm generated article. please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece

But then how would they get people to buy their $99 AI CEO package?

Re: SQLite in Production: Lessons from Running a Store on a Single File

#69

Post by pullshark91 »

NewsaHackO wrote: post #68
Natfan wrote: post #10

llm generated article. please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece

But then how would they get people to buy their $99 AI CEO package?

Huh, and here I thought it was a joke...
Post reply on HN