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

91–100 of 167 posts

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

#91

Post by morelisp »

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…

> What separate DB server?

It's still normal to refer to e.g. "database server", "application server", etc. processes even when running on a single machine. Re. EC2, I'm referring to the surcharge of having a dedicated instance at all, vs. working at the container / object storage level of abstraction.

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

#92

Post by Micoloth »

JohnBooty wrote: post #83
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 mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

Honest question.. Why?

I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps.

I know there are several options to do it, but I always assumed Postgres did not support it.

What do people have against it?

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

#93

Post by morelisp »

endisneigh wrote: post #32
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.

Kafka for consistency/durability and local storage for speed/structure is a common architecture, and a really good one any time you can tolerate async writes.

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

#94

Post by candiddevmike »

Micoloth wrote: post #92
JohnBooty wrote: post #83

Earlier quoted context omitted.

As if postgres and others don’t have a way to run application logic at the database. I mean... This is probably the least popular possible thing you can possibly suggest as an engineer in 2023. Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. But don't tell anybody I said that. I might get beaten up. That's probably why fly.io sort of…

Honest question.. Why? I’m always thinking that a db that can also run business logic would be the ultimate backend solution for crud apps. I know there are several options to do it, but I always assumed Postgres did not support it. What do people have against it?

Few reasons:

- Enforce application logic with constraints so you don't have to duplicate it

- Use triggers and get access to things like old and new without having to create a bunch of transactions

- If you have multiple apps sharing a DB, you either keep your business logic consistent across them vs just doing it in the database

- You can version your business logic with your schema

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

#95

Post by laurencerowe »

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…

> The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.

In my experience SQL databases are pretty poor at executing the kinds of deeply nested joins needed to return all of the data needed to render more complex UIs. It almost always ends up being faster to simply make nested selects in batches. So latency ends up mattering in these cases.

You can work around this by denormalizing the data, but this often explodes your data size.

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

#96

Post by candiddevmike »

kentonv wrote: post #35
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 b…

Now your app is stateful, you need storage for it, a backup strategy, a free space monitoring strategy, a way to have the storage follow the app, etc. Depending on your situation, that could be harder than just getting a Postgres database.

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

#97

Post by sodapopcan »

endisneigh wrote: post #63
sodapopcan wrote: post #57

Earlier quoted context omitted.

They aren't necessarily going to have all this. Lots of smaller ecom shops can run on a single server per region. If you're a North American company selling a few hundred t-shirts per day in NA and EU, it could probably be fine, no? I'll admit I'm not speaking from experience. Rather, I have experience in everything I just said only I was using pg, not sqlite. But I've been very interested in sqlite recently.

It’s not about the size. Issues result in lost sales which means lost revenue. You would want fault tolerance regardless of how big you are.

That's something for me to look into. Like what would it would look like in each? I've never been the dba-type in past jobs. I'm relatively well-versed in SQL but not administration.

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

#98

Post by bajsejohannes »

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…

> I can’t see any valid reason not to use Postgres at the back end

I'm using sqlite in production for a backend service. There are plenty of downsides, but to focus on the positives:

- I can run all tests in a database in memory. It's incredibly fast to "spin up" and I can use a separate database per test

- Related, I find that I write more tests against the database instead of mocking a database, which cuts down on time writing tests.

- I don't need to start a database to run the backend

- I can have snapshots of the databases in a single files for various scenarios

All in all, the development process feels a lot faster. When something takes a millisecond instead of seconds, you do things differently.

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

#99

Post by ngrilly »

laurencerowe wrote: post #95
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…

> The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems. In my experience SQL databases are pretty poor at executing the kinds of deeply nested joins needed to return all of the data needed to render more complex UIs. It alm…

Which is exactly where SQLite shines, but I guess that was your point: https://www.sqlite.org/np1queryprob.html

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

#100

Post by sarchertech »

emodendroket wrote: post #72
evantbyrne wrote: post #68

Earlier quoted context omitted.

As with anything, the typical business can use something off-the-shelf, but a certain percentage are doing things differently enough that custom development becomes practical. I had to custom build the billing system for Beaker Studio to properly meter customers. Even Stripe's metering API wasn't flexible enough to handle per-hour metering.

I find it hard to imagine a reason a shop selling 100 tee shirts in a day would need any custom functionality since this is basically the exact use case all these OOB e-commerce tools are built for.

One of the first commercial projects I worked on nearly 20 years ago was a tshirt shop. And the precious company I worked for was a comparatively huge logistics startup.

I’m fairly confident I could spend a month or so writing a custom solution for a shop selling and shipping a few hundred tshirts a day that would save them enough money to break even on the software in a few years (compared to off the shelf solutions).

If I was starting my own tshirt company, I’d definitely do it.

Post reply on HN