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

21–30 of 135 posts

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

#21

Post by crazygringo »

> 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 don't ever need to upgrade the database if you don't want to, since you're not publicly exposing it. There's obviously no replication lag if you're not replicating, which you wouldn't be with a single server.

The reason you don't usually choose SQLite for the web is future-proofing. If you're totally sure you'll always stay single-server forever, then sure, go for it. But if there's even a tiny chance you'll ever need to expand to multiple web servers, then you'll wish you'd chosen a client-server database from the start. And again, you can run Postgres/MySQL locally, on even the tiniest cheapest VPS, basically just as easily as using SQLite.

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

#22

Post by xnorswap »

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'm fairly confident they let it write the blog post too.

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

#23

Post by xnorswap »

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…

Yeah, it's weird "they" don't consider any middle ground between SQLite and replicated postgres cluster.

Locally running database servers are massively underrated as a working technology for smaller sites. You can even easily replicate it to another server for resiliency while keeping the local performance.

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

#24

Post by pjc50 »

pavel_lishin wrote: post #6
crabmusket wrote: post #5

Earlier quoted context omitted.

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

Doctor: solution is simple, stop letting that stupid clown Pagliacci define how you do your work! Patient: but doctor,

pAIgliacci: as a large language model, I am unable to experience live comedy.

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

#25

Post by chasil »

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/Shared_memory

https://en.wikipedia.org/wiki/CB_UNIX

https://www.ibm.com/docs/en/aix/7.1.0?topic=operations-syste...

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

#26

Post by talkingtab »

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…

This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.

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

#27

Post by kaibee »

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…

Yeah a PG Docker container is basically magic. I too went down a rabbit-hole of trying to setup a write-heavy SQLite thing because my job is still using CentOS6 on their AWS cluster (don't ask). Once I finally got enough political capital to get my own EC2 box I could put a PG docker container on, so much nonsense I was doing just evaporated.

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

#28

Post by BartjeD »

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

The bottom part of the article mentions they use .backup - did they add that later or did you miss it?

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

#29

Post by kaibee »

talkingtab wrote: post #26
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…

This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.

They're using AI Agents to do it in either case and using docker. There was no reason to choose SQLite.

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

#30

Post by BartjeD »

BartjeD wrote: post #28
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_...

The bottom part of the article mentions they use .backup - did they add that later or did you miss it?

The post now says they changed it due to feedback from Hacker news. All good.
Post reply on HN