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

41–50 of 135 posts

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

#41

Post by devmor »

mywittyname wrote: post #35
devmor wrote: post #15

Earlier quoted context omitted.

I have to work with agents as a part of my job and the very first thing I did when writing MCP tools for my workflow was to ensure they were read only or had a deterministic, hardcoded stopgap that evaluates the output. I do not understand the level of carelessness and lack of thinking displayed in the OP.

Even just having the agent write scripts to disk and run those works wonders. It keeps the agent from having to rebuild a script for the same tasks, etc.

That too! Every time the agent does something I didn't intend, I end up making a tool or process guidance to prevent it from happening again. Not just add "don't do that" to the context.

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

#42

Post by nop_slide »

I still haven't figured out a good way to due blue/green sqlite deploys on fly.io. Is this just a limitation of using sqlite or using Fly? I've been very happy with sqlite otherwise, rather unsure how to do a cutover to a new instance.

Anyone have some docs on how to cutover gracefully with sqlite on other providers?

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

#43

Post by runako »

crazygringo wrote: post #21

> Would We Choose SQLite Again? Yes. For a single-server deployment with moderate write volume, SQLite eliminates an entire category of infrastructure complexity. No connection pool tuning. No database server upgrades. No replication lag. These are weird reasons. You can just install Postgres or MySQL locally too. Connection pool tuning certainly isn't anything you have to worry about for a moderate write volume. You…

Have run PG, MySQL, and SQLite locally for production sites. Backups are much more straightforward for SQLite. They are running Kamal, which means "just install Postgres" would also likely mean running PG in a container, which has its own idiosyncrasies.

SQLite is not a terrible choice here.

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

#44

Post by hedora »

leosanchez wrote: post #4

> Backups are cp production.sqlite3 backup.sqlite3 I use gobackup[0] as another container in compose.yml file which can backup to multiple locations. [0]: https://gobackup.github.io/

Does cp actually work on live sqlite files? I wouldn’t expect it to, since cp does not create a crash-consistent snapshot.

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

#45

Post by simonw »

Retr0id wrote: post #34
simonw wrote: post #32

Earlier quoted context omitted.

Thanks for this, the anecdote with the lost data was very concerning to me. I think you're exactly right about the WAL shared memory not crossing the container boundary. EDIT: It looks like WAL works fine across Docker boundaries, see https://news.ycombinator.com/item?id=47637353#47677163 I don't know much about Kamal but I'd look into ways of "pausing" traffic during a deploy - the trick where a proxy pretends that…

> 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?

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

#46

Post by Retr0id »

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?

Perhaps they're using NFS or something - which would give them issues regardless of container boundaries.

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

#47

Post by jmull »

I don't know if it's just me, but this whole post seems to have time traveled forward from about 3-4 days ago.

It's not just a repost. The thread includes a comment I made at the time which now from "1 hour ago".

Makes me wonder if it's an honest bug or someone has hacked the hacker news front page to sell their t-shits, mugs, and AI starter kits.

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

#48

Post by Retr0id »

jmull wrote: post #47

I don't know if it's just me, but this whole post seems to have time traveled forward from about 3-4 days ago. It's not just a repost. The thread includes a comment I made at the time which now from "1 hour ago". Makes me wonder if it's an honest bug or someone has hacked the hacker news front page to sell their t-shits, mugs, and AI starter kits.

It's an artefact of the "second chance pool" mechanism.

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

#49

Post by simonw »

hedora wrote: post #37
simonw wrote: post #32

Earlier quoted context omitted.

Thanks for this, the anecdote with the lost data was very concerning to me. I think you're exactly right about the WAL shared memory not crossing the container boundary. EDIT: It looks like WAL works fine across Docker boundaries, see https://news.ycombinator.com/item?id=47637353#47677163 I don't know much about Kamal but I'd look into ways of "pausing" traffic during a deploy - the trick where a proxy pretends that…

Pausing requests then running two sqlites momentarily probably won’t prevent corruption. It might make it less likely and harder to catch in testing. The easiest approach is to kill sqlite, then start the new one. I’d use a unix lockfile as a last-resort mechanism (assuming the container environment doesn’t somehow break those).

I'm saying you pause requests, shut down one of the SQLite containers, start up the other one and un-pause.

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

#50

Post by Retr0id »

gcr wrote: post #38
chasil wrote: post #25

Earlier quoted context omitted.

This is the actual problem: "Kamal runs blue-green deploys — it starts a new container, health-checks it, then stops the old one. During the switchover, both containers are running. Both mount ultrathink_storage. Both have the SQLite files open." WAL mode requires shared access to System V IPC mapped memory. This is unlikely to work across containers. In case anybody needs a refresher: https://en.wikipedia.org/wiki/S…

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