Hacker News Reader

Comment by jemmyw | Hacker News Reader

I don't really understand the current trend of using sqlite in place of a traditional database. It has it's place, I use it in a client side project and it's really great, although even there I have to be aware of the one writer only limitation.

20 years ago it was MySQL and now it's usually postgres, and these are both still fine. Easier than ever to get setup and going in production. Outside of quite niche needs, I can't understand why one would put up with the pain of using sqlite. Plus it's setting up an even more painful migration later if you do grow.

View on HN

> I don't really understand the current trend of using sqlite in place of a traditional database. It has it's place, I use it in a client side project and it's really great

I think its more about the issue that databases like postgres are too much "magic". With Sqlite you see your database file, its a physical something. You want to move it to another server, its just a copy action, no dump/restore. You want to "upgrade", there is no mess like with postgres.

You want to create client separation, ... its just a file per client.

> although even there I have to be aware of the one writer only limitation.

The writer limit has not been a limit forever. You can easily do 20k writes per second, what is way beyond what most system anyway. You want 100k? A slightly different access pattern and voila. You want 1 million ... There are ways around it. If you put Sqlite vs Postgres for instance, ironically, seen Sqlite winning against postgres despite that single writer.

The whole hands on "we know what file is, where it is, and how to gain access to it" is really amazing. The whole "do not need to think about how to secure it on the network" also helps a lot.

Do i use Sqlite in production? No ... Postgres its extension support just rules. But i see the appeal for many.

> I think its more about the issue that databases like postgres are too much "magic"

The problem with that thought is that in order for sqlite to be as full featured as it is, it also has a lot of "magic". I've also had to dive into the postgres code a few times to understand how it's doing something, and I've generally found it well structured and easy to navigate, and surprisingly terse - there's not that much "magic" really.

> You want to "upgrade", there is no mess like with postgres.

Sure, if you don't mind downtime?

Yeah sqlite runs circles around postgresql when it comes to writes if you know what you are doing. Single writer lets you batch and batching is king for writes.

> You can easily do 20k writes per second, what is way beyond what most system anyway.

Do you have any resources on this? I'm working on a project with sqlite, with multiple threads writing, and want to squeeze out a little more performance.

I’ve been using SQLite for a lot of my backends since way before it’s cool. Back then I was frowning at all the people/tutorials spinning up MySQL/Postgres/heaven forbid Mongo for 1 read per second, 1 write per 10 seconds projects.

Now I’m frowning at people doing all sorts of gymnastics to beat SQLite into shape.

It’s always the same, people without a mind of their own looking at blog posts from cool kids and deciding they have to do it themselves too (not all the adopters of course, but most of them).

All databases are painful in their own way. I've used all three at various times in my career, and I think SQLite behaves quite predictably, which has made it a lot easier for me personally to administrate.

If I had to start something new, I'd use SQLite until at least high 5 digit queries per second. Maybe more.

One reason I use SQLite in production is because of Litestream. I can setup 2 or more VPSes hosting my Django app backed by the same SQLite database behind a load balancer for redundancy.

Thanks to Litestream, it's easier to scale horizontally when using SQLite than with other databases when self-hosting.

I thought litestream was just for streaming backups? How does it enable you to coordinate with multiple instances of an application? Is one instance the "primary" where writes all go and either can serve as a reader?

I'm interested in knowing more about this too as I like the database localised with my app server (no network RTT!) but I also like to have a standby for HA and not being paged with everything down.

How litestream handles master-master or promoting a slave and switching back later isn't clear to me.

The closest thing I could find on the parent commenters blog was this

https://joshkaramuth.com/blog/django-deployment/

But it doesn't go over multi-instance coordination

This is a quirk that could cause data loss in rare scenarios but basically, in most cases, you'll have a single instance of your application because your load balancer will only send traffic to the other instances if the main instance isn't responding. But the load balancer will have to be setup to send traffic to a single instance at a time, and only try other nodes if the current node isn't responding.

The other instances are mostly redundant. That's how sites like Hacker News tend to operate, and why I chose this architecture. It's simple and stupid but doesn't work in scenarios where you have multiple servers in different locations for example. There's LiteFS to solve this problem but I don't have experience with it yet.

Ah got it, thanks for explaining and clarifying!

Cheers

For a serious web project it's hard to imagine picking SQLite, unless you're using a proper "serverified" SQLite service like Turso or Cloudflare D1. I'd think zero-downtime deployments alone would be a dealbreaker for many. Then there's high-availability and horizontal scaling.

I love that SQLite lets me run the app locally with no "setup". And so I actually kinda like Turso and D1 for bridging the gap. I can have the best of both worlds: no setup, AND primetime-ready deployment.

You can do zero downtime deployments with SQLite just fine if your app can gracefully handle the database file being locked. Just spin up your new version talking to the same database, be mindful about how you handle migrations, and shut down the old version.

You can do zero downtime using Systemd socket activation. I tried this with a Go server using SQLite and it was perfect.

https://bunrouter.uptrace.dev/guide/go-zero-downtime-restart...

For Rails specifically, its an intentional swinging of the pendulum back towards YAGNI by default. So, the default rails install, out of the box, can ship to prod running an extremely minimal set of "other" dependencies and begin your MVP journey. Its not meant to be the end game, its meant to get you from 0 to 1.

There are two things which I think combine to make postgresql look "hard" and sqlite "easy": needing a separate process (possibly a separate server) for the database, and access management. They aren't particularly big deals in the long run, but they can be just enough of a mental hurdle to make the simplicity of "just open a file" look tempting.

Reply on news.ycombinator.com