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

81–90 of 135 posts

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

#81

Post by 66yatman »

littlestymaar wrote: post #78
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…

> Wait, you let _Claude_ push your e-commerce code straight to main which immediately results in a production deploy? Yikes. Thank you I'm not going to read “Lessons learned” by someone this careless.

The issue wasn’t done by the ai but their lack of architectural knowledge

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

#82

Post by PunchyHamster »

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

None of these is needed if you run sqlite sized workloads...

I like SQLite but right tools for right jobs... tho data loss is most likely code bug

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

#84

Post by PunchyHamster »

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…

> WAL mode requires shared access to System V IPC mapped memory.

Incorrect. It requires access to mmap()

"The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption."

> This is unlikely to work across containers.

I'd imagine sqlite code would fail if that was the case; in case of k8s at least mounting same storage to 2 containers in most configurations causes K8S to co-locate both pods on same node so it should be fine.

It is far more likely they just fucked up the code and lost data that way...

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

#85

Post by PunchyHamster »

hedora wrote: post #39
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).

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.

Not how SQLite works (any more)

> The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption. Other methods for creating nameless shared memory blocks are not portable across the various flavors of unix. And we could not find any method to create nameless shared memory blocks on windows. The only way we have found to guarantee that all processes accessing the same database file use the same shared memory is to create the shared memory by mmapping a file in the same directory as the database itself.

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

#86

Post by PunchyHamster »

jp0001 wrote: post #73

I took three weeks off from tech, read books from last century, and travelled Europe. Coming back, reading LLM generated content and code feels like nails on a chalkboard. Taste, it does not have taste.

It is so tiring...

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

#88

Post by heikkilevanto »

I use SqLite for a small hobby project, fine for that. Wanted to read the article to see why I should not, but it attacked me with a "subscribe" popup, so I stopped there. The comments here seem to be based on daydreaming on scaling to a lot of users who need 24/7 uptime, which is not always the case.

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

#89

Post by burnt-resistor »

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…

I suspect they don't wear helmets or seatbelts either. Sigh. The "I'm so proud and ignorant of unnecessarily risky behaviors" meme is tiring.

The Meta dev model of diff reviews merge into main (rebase style) after automated tests run is pretty good.

Also, staging and canary, gradual, exponential prod deployment/rollback approaches help derisk change too.

Finally, have real, tested backups and restore processes (not replicated copies) and ability to rollback.

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

#90

Post by literallyroy »

PunchyHamster wrote: post #86
jp0001 wrote: post #73

I took three weeks off from tech, read books from last century, and travelled Europe. Coming back, reading LLM generated content and code feels like nails on a chalkboard. Taste, it does not have taste.

It is so tiring...

It’s strange how easy it is to spot.
Post reply on HN