Hacker News Reader

Comment by fyresala | Hacker News Reader

You won't gain much by the combination of golang, sqlite and the cloud. You can't scale out and the cloud layer only makes sqlite slower. I can't see any reason not using RDS other than it's more expensive.

I would only say this is a quick way to run up an application with a cheap VPS for a beginner.

View on HN

Litestream/LiteFS author here. I agree that "cloud" is a bit ambiguous but Go & SQLite are quite powerful together and I don't think it's only for beginners. Both are fast and have low overhead. In addition to lower cost versus RDS, there's near-zero query latency which eliminates a lot of performance problems. You can comfortably run tens or hundreds of requests per second on minimal hardware (e.g. 256MB or 512MB instances). There's a lot of room for scaling up before you hit a performance ceiling.

Tens of requests a second?

He's being generous. Indeed, your service will be lucky if it sees tens of requests per day.

:-|

Normally I'm on the side of 'do you actually get that much traffic?' but yeah, a dashboard view can generate dozens of requests alone, each with multiple queries.

A request can have a large range of queries within it so I was trying to account for that. If your requests are lightweight and mostly reads, you can do 1,000+ req/sec on a 256MB instance. YMMV.

Do you mean tens or hundreds of thousands of requests per second?

There aren’t many apps that have 100,000+ req/sec and you certainly can’t run them on modest hardware.

If your app averages 100 req/sec then that’s 8.4M requests per day. That’s more than most applications out there.

I just watched your gophercon video on SQLite in production after seeing it in the article. Great talk. Anyone else who's interested can watch here: https://www.youtube.com/watch?v=XcAYkriuQ1o

Thanks!

This is a pretty strong claim without any numbers, to be honest. You were probably already running your single database instance so being limited to a single sqlite instance isn't terribly different. This does limit you to a single app server, which unless you're CPU bound is also fine. Even if you are, you can get a lot of cores in a single instance these days.

Most things will never need to be scaled up

AFAIK, sqlite3 doesn't support multi-core, so you can only do vertical scaling. In cloud, people rather do horizontal scaling. That being said, I ran a django based webapp for internal users and we saw core being pegged by sqlite3 when the queries were complex. However, under normal usage, it was fine for about 100 concurrent users including machine API calls.

> doesn't support multi-core

Can you expand on that? I thought it supported multiple readers, but only a single writer. They do all have to be on the same host, though.

Since you mentioned Django - I think there are some complexities where the Python driver can't really do concurrent access if using threads instead of processes, but this is due to Python/GIL limitations, not sqlite limitations.

sqlite3 is a single core application, so even if you have multiple cores on a box, sqlite3 isn't going to take advantage of that. If you want sqlite3 to go faster, you need better single core box. That being said, I do believe sqlite3 allow multiple processes to read the same database file.

Our django app sits behind Gunicorn that will spin up multiple django instances, as long as Sqlite3 has WAL mode on, concurrent access (read) isn't a problem. For write, we basically use a lock.

When you embed it in Go, you can have concurrent reads. Each HTTP handler request gets a separate goroutine, so you can definitely take advantage of multiple cores.

This is confusingly worded but for onlookers: sqlite as you think of it isn't an application at all, it's a C library. There does exist a binary called sqlite3 which is an interface to that library, and that binary is (mostly, though not exclusively) single-threaded.

But unless you're using that specific tool, and you're probably not unless you yourself are typing "sqlite3" into a bash shell, you're using the library rather than this query tool. And that library interacts in a similar way to a socket connect or a fopen call. You can have multiple connections open to the same sqlite database, and those connections can operate concurrently (subject to various limitations, but "you need better single core box" is not the summary)

It is even a little better than that these days. Beginning a transaction normally locks the whole database, but with Wal mode they have added a Begin Concurrent command so there can be multiple transactions going at once. However when you get to the commit it still requires the exclusive lock. I bet after a few years even that restriction will be gone.

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

https://sqlite.org/forum/info/2ad28cb1e0356816e7b0bd2ab458f7...

I don't think BEGIN CONCURRENT is out yet. AFAIK it's only available if you build the experimental wal2 branch yourself.

I guess I miss read the branch list, I thought the begin-concurrent-3.39 branch said it had merged to trunk but that is the line above it.

> Python driver can't really do concurrent access if using threads instead of processes, but this is due to Python/GIL limitations

Confusingly you can't run 2 lines of Python code at the same time, but you can run 2 SQL queries (sqlite, remote server, etc) at the same time. Python threads are true pthreads, they just need to hold the GIL while running Python code. They release it when running C code or doing I/O. So you can have 2 queries running at the same time no problem, but you'll only get a single core of compute processing their results.

SQLite actually scales far better than the memes would generally tell you and that "lite" implies. It's frustrating how easily these memes spread by people just repeating what they heard once. If there’s anything HN has taught me it’s to check the docs instead of believing these ambient notions

It's more complicated than "doesn't support multi-core" which you can see in the other replies here: generally unlimited concurrent readers are allowed with single or at least finite writers. Depending on a bunch of settings (e.g. WAL) you might also get concurrent writers, multicore sorting, and some other things that can cross cores too. Those things do have their own tradeoffs.

But my actual claim is that most things don't need to be "scaled" at all. And if you do get there, and again statistically you won't, then you're definitely going to be doing some other rearchitecting anyway and moving sqlite->postgres might as well be part of that.

I never said it's "lite". I can say for certain that in my experience, when writes start to go up, such as stock market event stream coming in, sqlite3 doesn't perform as well as postgres, and scaling writes is hard in sqlite3. But I have no issue scaling Postgres for those data.

If you say, sqlite3 is good enough for most webapps, then I probably agree. But I would not use it for a lot of things that I use Postgres for such as pubsub, or really high transaction rate.

Not saying SQLite wasn't the issue here (impossible to tell based on the information provided), but I've seen a lot of webapps with highly inefficient queries because developers either didn't understand what the ORM was doing or didn't bother to optimize. In a Django app, prefetch_related can make a huge difference (or joinedload when using SQLAlchemy or better initial filtering when using raw SQL).

We used mostly raw SQL. Django ORM is good for CRUD app, but anything analytic style app, it's more painful than it should be. I'm not blaming Django, just we picked the framework for CRUD work and had to retrofitted to do analytic because of requirements.

See my followup-article "Distributed SQLite with LiteFS" for the scale-out part: https://www.golang.dk/articles/distributed-sqlite-with-litef...

And IMO, what you lose in the cloud layer you gain by having a deployment really close to your user. Speed of light and all that.

Plus SQLite is super cool and fun! :D

Once you need to graduate from one large server (which will take you a long way in many cases), there are tools like [rqlite](https://github.com/rqlite/rqlite) that can handle clustering. With WAL mode enabled, SQLite can handle a surprising amount of traffic that would fit a lot of use cases. If latency is important to you, it's going to be hard to beat SQLite for many workloads.

It's a little weird that you consider bringing up an application on bare metal something for a beginner. :) I think manually handling the nitty-gritty of an OPS setup is a more senior task than clicking you way into setting up some AWS infrastructure.

Depends on your application.

On the opposite I've seen many cases where sqlite3 scales better than postgres, depending on the scale and the application.

If you're doing horizontal scaling, sqlite3 is a very good alternative.

> I can't see any reason not using RDS other than it's more expensive.

We don’t all operate with unlimited VC money.

You gain low latency, which allows you to write your code to a 'pure' relational model (at least as close as SQL allows), without having to rely on code complicating hacks to deal with round-trip issues. Less complicated code can improve delivery time and reduce faults. While there are abstractions that can be used to help with those hacks, they come with their own tradeoffs. Pick your poison, as always.

Conceptually, RDS is little more than SQLite with a clever networking layer built on top. In context, your application is also just a clever networking layer, so the middleman doesn't necessarily add any value. Of course it depends on exactly what you are trying to do and what tradeoffs you are willing to accept. There is no free lunch.

At what point would you expect to need to scale out?

Reply on news.ycombinator.com