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

31–40 of 135 posts

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

#31

Post by Retr0id »

chasil wrote: post #25
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…

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…

> This is unlikely to work across containers.

Why not?

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

#32

Post by simonw »

chasil wrote: post #25
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…

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…

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 a request is taking another second to finish when it's actually held in the proxy while the two containers switch over.

From https://kamal-deploy.org/docs/upgrading/proxy-changes/ it looks like Kamal 2's new proxy doesn't have this yet, they list "Pausing requests" as "coming soon".

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

#33

Post by chasil »

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

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…

You might consider taking the database(s) out of WAL mode during a migration.

That would eliminate the need for shared memory.

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

#34

Post by Retr0id »

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

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

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

#35

Post by mywittyname »

devmor wrote: post #15
cadamsdotcom wrote: post #8

The fix appears to nicely asking the forgetful unreliable agent to please (very closely pretty please!) follow the deploy instructions (and also please never hallucinate or mess up, because statistics tells us an entity with no long term memory and no incentive to get everything right will do the job right 99.99999999% of the time, which is good enough to run an eshop) not deploy too often per hour. With one simple i…

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.

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

#36

Post by NewEntryHN »

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…

It's a spectrum. Installing Postgres locally is not 100% future-proofing since you'll still need to migrate your local Postgres to a central Postres. Using Sqlite is not 0% future-proofing since it's still using the SQL standard.

If the only argument for a piece of tech in comparison to another one is "future-proofing", that's pretty much acknowledging the other one is simpler to setup and maintain.

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

#37

Post by hedora »

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

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

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

#38

Post by gcr »

chasil wrote: post #25
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…

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

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

#39

Post by hedora »

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

It would explain the corruption:

https://sqlite.org/wal.html

The containers would need to use a path on a shared FS to setup the SHM handle, and, even then, this sounds like the sort of thing you could probably break via arcane misconfiguration.

I agree shm should work in principle though.

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

#40

Post by add-sub-mul-div »

jmull wrote: post #11

Redis, four dbs, container orchestration for a site of this modest scope… generated blog posts. Our AI future is a lot less grand than I expected.

Ironically, AI de-skilling results in a robust-sounding resume.
Post reply on HN