Hacker News Reader

Comment by Kalanos | Hacker News Reader

It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.

View on HN

> However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests

That’s not really accurate any longer.

Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).

So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.

https://rivet.dev/blog/2025-02-16-sqlite-on-the-server-is-mi...

If you need sqlite over the network you can look at https://turso.tech/ it’s a almost drop in replacement for sqlite (https://github.com/tursodatabase/turso/blob/main/COMPAT.md)

At this point you're building your own networked database using sqlite just as a backing store. You should really reconsider if it's easier than using something designed for it.

If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.

sharding locally defeats the purpose of sharding; splitting it up across machines to get more resources

Is this something that the authors of SQLite actually claim? I don't think anyone else can decide what something is meant for.

Once you release software to the world, the world can choose use it however it wants. Still, the author's of SQLite document their intention for it to be used locally on their "when to use" page.

> SQLite strives to provide local data storage for individual applications and devices.

https://www.sqlite.org/whentouse.html

To be fair they also say

> Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

So about one per second (up to ten, less conservatively). I concur. But if you think your site might ever scale beyond that, do yourself a favor and use Postgres from the get-go.

The vast, vast majority of websites never see anything even close to that, so it's a safe bet unless you have some specific reason to expect it to reach that kind of traffic, or you are dealing with workloads that SQLite really does not handle well, e.g. many concurrent writes. And if your workload is mostly reads, then you probably can use a cache layer, which allows SQLite to go further still.

Adding a cache layer to keep using the wrong database is an architecture failure - if you use Postgres from the get go you will not need the cache until way way later on anyway. (Side note, I’m team MySql but the same point applies)

What exactly is wrong about a solution that perfectly fits the most common use case out there, at a fraction of the cost/complexity?

SQLite can happily handle thousands of reads and writes per second even on modest hardware.

They also say it seems to work well up to 500k a day, which is quite a bit.

I have 8+ million rows added daily to a 100+GB DB. There is a limit somewhere but I haven't found it yet.

I have a 100GB SQLite DB in use that gets 8+ million rows added dally. It's on an off-the-shelf nvme SSD in a "server" that I build from 5+ year old parts.

Those are writes not hits so not directly comparable.

So many problems in tech would be solved with $250 spent on an NVMe drive.

The authors have explained that sqlite is meant to compete with fopen more than it's intended to compete with Postgres.

Use WAL (yes this should be the default, or at least explained much better) and you can have one writer, many readers.

Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.

Of course if you are building a startup you must consider scaling.

I tried WAL, but like you said, it froze up during multiple writes (when creating records, not updating). I guess what I needed was a write queue - not sure if that exists.

I am curious what is your write pattern? How long do you keep the transactions open for?

There are definitely cases where a single lock does not fit. Even though you can only write one at a time, the transactions ideally should be open for a very short period. That way the throughput, as measured in writes per second, should still be really high.

If there is some other network call in there, SQLite is not a good fit for it and you should definitely move to some other database.

As for write queue, SQLite has that built in if you do transactions — it should unlock other transactions when one is closed.

If you specify IMMEDIATE as the transaction type, it should try to get an exclusive write lock and will timeout after the default time of 30 seconds.

Reply on news.ycombinator.com