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

111–120 of 167 posts

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

#111

Post by kentonv »

candiddevmike wrote: post #96
kentonv wrote: post #35

Earlier quoted context omitted.

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.

Edge compute platforms aim to take care of all that for you.

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

#112

Post by hinkley »

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.

Lots of small businesses invent write heavy workloads and don't realize how many thousands of dollars they are spending a month on being nosey.

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

#113

Post by srcreigh »

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 doesn’t support b-trees for primary storage, so data can’t be automatically clustered, which reduces efficiency for joins by 50x. MySQL and SQLite don’t have this issue.

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

#114

Post by xboxnolifes »

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?

The tooling around maintaining logic-in-db is worse than the tooling for logic-out-of-db.

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

#115

Post by chasil »

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.

The language for triggers in SQLite appears to be a fragment of SQL/PSM and Oracle PL/SQL.

Perhaps this will grow into a more thorough implementation.

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

#117

Post by MyneOutside »

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.

In our B2B space there are tons of custom business rules. For example, you place orders per manufacturer and each manufacturer has its own minimum and reorder amounts and reqs on an order, promotions, business rules, etc. This is for business that have been around 25-30 years.

We did an evaluation on several out of the box ecommerce solutions and none of them were able to meet the requirements that absolutely had to be there. Shopify flat out said no, they can't help us, etc.

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

#118

Post by oooyay »

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…

I recently implemented SQLite for a metadata application at work. My constraints in design were pretty concise; the application will only ever need to scale vertically, my data persistence story is a matter of speed and accessibility over longevity, I have plenty of options for scaling disk IO, and my read performance is much more paramount than write performance.

The outcome is that my persistence later is not adding $200/m immediate service overhead cost and my deployment was easy to manage, which is a strong promise made to the rest of the team. I think there's a place for SQLite, but just like any tool you need to know that your design constraints match the constraints of the tool.

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

#119

Post by simonw »

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?

I think the main thing is that most people still aren't working with a good migrations system to manage changes to their schema... which means logic held in database triggers and stored procedures quickly becomes a non-version-controlled not-properly-tested mess.

Good migration systems exist, and people should use them!

I held off on doing interesting things with triggers for more than a decade. In the past year I've started leaning into them much more heavily (actually using them in SQLite) because I have confidence that I can both write good tests for them and have good migrations automation in place for version-controlling my schema.

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

#120

Post by adzm »

srcreigh wrote: post #113
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 doesn’t support b-trees for primary storage, so data can’t be automatically clustered, which reduces efficiency for joins by 50x. MySQL and SQLite don’t have this issue.

this fact still blows me away
Post reply on HN