Hacker News Reader

Comment by zackify | Hacker News Reader

I think this skips one mega benefit for apps.

I’ve been using liteFS in production for a couple months.

Your web app is able to resolve db queries instantly.

You don’t need loading states if you’re using complex charts and other frontend JS that waits for data.

All the data is resolved so fast and you can just return all your data like more traditional apps, and the load times are insane.

If you’re multi region you can deploy one app instance there. Instead of 2-3. Postgres read replica, maybe something like redis.

It really replaces both of those, assuming you have a read heavy app it works great.

View on HN

I think you always need loading states to account for slow network, or am I missing something?

The SQLite database is located on the application server, so there is no network between the DB and the app.

Assuming you're serving a frontend that makes network calls to a backend, you'll need to handle loading states in the frontend regardless of how the backend retrieves its data.

The idea is that you're not doing that.

Unless your database is in the browser, you are always going to be at mercy of network latencies talking to the backend.

You're just saying, even if all you were doing was fetching a static JSON blob from the memory of the frontend server, you'd still want load states, right? (That makes sense, I'm just checking my understanding.)

Yup, exactly. Phones change wifi networks, routers drop packets, load balancers get overloaded. Hard to fully eliminate tail latencies.

The key here is to make a single API call to the backend which then runs 100+ SQL queries at once and combines the results into a single JSON response - that way you're only paying the network cost once.

See https://www.sqlite.org/np1queryprob.html

I've implemented GraphQL on top of SQLite and found it to be an amazingly good match, because the biggest weakness of GraphQL is that it makes it easy to accidentally trigger 100s of queries in one request and with SQLite that really doesn't matter.

that doesn't eliminate need for loading states at all, and is already solved by things like react server components or remix (see waterfall removal graphic https://remix.run/)

i think this discussion is confusing the use of sqlite in local-first apps, where there's no loading states because the database is in the browser. you can use sqlite on your server, but you still need a "loading state".

even with postgres, if your data and server are colocated, the time between the 2 is already almost 0

now maybe the argument is your servers are deployed globally each with an sqlite db. that's not all that different from global postgres read replicas

If the data and the server are colocated on the same machine, the database network overhead is almost zero. But that's not necessarily true otherwise. For an HTTP request that incurs a single query, you can round it to zero, but you have to be careful not to write request handlers that incur lots of queries if you're trying to stay below the 100ms threshold.

> I've implemented GraphQL on top of SQLite and found it to be an amazingly good match

Could you give a pointer to the repository, or is this part of Datasette?

Nevermind, found it. Just don't know the license.

https://github.com/simonw/datasette-graphql

It's Apache 2 (there is a badge in the README) but I realize now I forgot to drop a copy of the license into the repo - fixing that now.

FYI: We're offering a SQLite hosting service which automatically creates a full-fledged GraphQL endpoint for your database: https://www.airsequel.com

You can with sqlite in webassembly

In theory, but there are still a lot of sharp edges. e.g. I wanted to use sqlite in-browser with an ORM, but few ORMs support such a setup.

And your users are sitting in front the application server?

>to account for slow network

Or a slow anything else, e.g., SQLite queries. This thread has focused on the network aspects, and they do stand out since there can be such a large gap between a network call vs a local SSD read. But we're still talking about a database, which could be huge (presumably it's the main, single DB for the whole app). And there is still all of the actual SQLite work that needs to happen to execute a query, plus opportunities for really bad performance b/c of bad queries, lack of indexes, all the usual suspects. Not to mention the load on the owning process itself. So, I'm agreeing that a loading state is needed.

I think this strongly depends on the application and use-case. I've worked at two businesses so far, and they target small and medium-sized companies in a tenant-style manner.

All data for one customer that is important enough to load easily fits within less than 5MB. That is of course not counting logs and such, but it's all "important" user-specific data. It's not -that- dissimilar from a small to medium-sized redux store in complexity. Lots of toggles, forms, raw text and some relations.

Of course this architecture doesn't scale to the enterprise level, or to other certain heavily data-driven applications (like imagine running the entirety of your sentry database in-browser?), but that's what architecture is -for-! Pick one that synergizes well with your use-case!

I “use the platform” so clicking a new tab shows the browser loading state. If you’re on a slow network, that’s the way I go about it.

You can run postgres on the same host as the web server too. Isn't that going to get you most of that same benefit in speed?

It is. I do this on plenty of hobby Laravel apps.

Me too, but I guess there could be even higher gains with SQLite when it's in-process...

the cool thing with sqlite is that you can compile your whole app into a single binary, so you don't need docker for example (or it's trivial to dockerize it afterwards if you really insist)

Postgres is great, but managing a fleet of them (one in each web server) and ensuring they are all working fine would bring a lot of operational complexity.

SQLite, on the other hand, skips all of that with its simple in-process model.

I just start postgres and it's solid. I don't understand this statement. With Dokku it's trivial.

It’s better than running it on a separate server but if you’re using Postgres you’re always hitting the network stack on the machine, with SQLite that isn’t the case.

How is it for writes? Would a CRM type system benefit from liteFS setup?

In Fly's implementation;

> LiteFS’ use of FUSE limits the write throughput to about 100 transactions per second so write-heavy applications may not be a good fit.

https://fly.io/docs/litefs/faq/#what-are-the-tradeoffs-of-us...

Seems that 100 writes per second is more than enough for a typical CRM (for a local business).

The article says "LiteFS supports roughly 100 writes per second".

Reply on news.ycombinator.com