Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I'm all-in on server-side SQLite (2022)

fly.io

Post reply on HN
Print view View original

31–40 of 167 posts

Re: I'm all-in on server-side SQLite (2022)

#31

Post by dimgl »

andrewstuart wrote: post #23

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres. Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that…

> any valid reason

Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.

Re: I'm all-in on server-side SQLite (2022)

#32

Post by endisneigh »

meitham wrote: post #24

I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.

You’re using SQLite and Kafka? Very ironic.

Re: I'm all-in on server-side SQLite (2022)

#33

Post by TylerE »

jmull wrote: post #29

I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading: > When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent…

That whole series of blog posts is an ad for fly.io.

Re: I'm all-in on server-side SQLite (2022)

#34

Post by giantrobot »

kijin wrote: post #12

> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query. Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing…

> Why compare the latency of a remote Postgres database with a local SQLite database?

The SQLite DB is just a flat file that can be packaged in a lambda or whatever cloud's object store. It makes sense for fast access to read-heavy or read-only data. Even a shitty query can return rows in less than a millisecond where a remote DB that same amount of data is tens of milliseconds away.

Re: I'm all-in on server-side SQLite (2022)

#35

Post by kentonv »

andrewstuart wrote: post #23

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres. Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that…

Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb.

This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one big database.

Many apps want to do that anyway! For them, Postgres makes sense. But in the growing world of global deployments and edge compute, the lots-of-small-databases approach is getting popular because it means you can store than data out on hundreds or thousands of edge locations, rather than a single central location. And many (not all) applications actually fit pretty well into a database-per-user or database-per-document model. Centralizing their storage only hurts performance for no benefit.

As a bonus, if you are able to run sqlite compiled directly into your app, not making any kind of network connection, it can be much faster than Postgres, especially in "N+1 select" situations (which are well-known to be a problem with most SQL databases, but are not a problem when using local sqlite). Postgres does not support running as a library like this.

Re: I'm all-in on server-side SQLite (2022)

#36

Post by ak39 »

SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.

Re: I'm all-in on server-side SQLite (2022)

#37

Post by giantrobot »

kijin wrote: post #25
morelisp wrote: post #18

Earlier quoted context omitted.

But why take on the operational overhead of a separate DB server (not to mention 200 more microseconds), plus the EC2 surcharge? I would rather run app+SQLite + dumb object storage, than app + MySQL + MySQL incremental backup and restore.

What separate DB server? I was talking about installing the RDBMS on localhost, right inside the server where your application runs. No other EC2 instance, no extra charges. Preferably connect to it over a Unix domain socket instead of TCP. That's the only way to compare SQLite performance with an RDBMS in an apples-to-apples way. The point about operational overhead makes sense, though, and IMO it's the only point i…

> No other EC2 instance, no extra charges.

Think Lambda (or equivalent) instead of EC2.

Re: I'm all-in on server-side SQLite (2022)

#38

Post by sodapopcan »

jmull wrote: post #29

I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading: > When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent…

> As if postgres and others don’t have a way to run application logic at the database.

I think it's reasonably fair of them not to specify this. The target audience of this article is people who are writing their applications in languages like Elixir, JS, Ruby, Python, and are not going to be interested in pushing all of their business logic to the db.

Re: I'm all-in on server-side SQLite (2022)

#39

Post by chungy »

ak39 wrote: post #36

SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.

SQLite is an in-process database. If you need language-agnostic encapsulation of data procedures, SQLite is not for you. I would suggest you consider PostgreSQL.

Re: I'm all-in on server-side SQLite (2022)

#40

Post by lib-dev »

sodapopcan wrote: post #28
erulabs wrote: post #17

I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”. Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or…

Lots of smaller businesses could do fine with this if they don't have a write-heavy workload. Like an ecomm shop, for instance.

Yeah it seems to make a lot of sense in ecomm. Product search and filtering on tables in the 1000s rather than the millions.
Post reply on HN