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

81–90 of 167 posts

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

#81

Post by jerrygenser »

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.

If you don't need SQL (relational data), but maybe have a schema per topic, I've used rocksdb as a cache for latest in tombstones topic. It has high write throughput for rebuilding state when playing forward a stream

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

#82

Post by benbjohnson »

rubenv wrote: post #19
benbjohnson wrote: post #10

Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.

What's the status on litestream? Does that have a future as well or is it LiteFS all the way?

Litestream definitely has a future. Our goal is to keep it as a simple single-node disaster recovery tool though so it won't see as much feature development as something like LiteFS. We've been focused a lot on LiteFS & LiteFS Cloud to get them in a good place but I'm looking forward to going back and updating Litestream more regularly.

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

#83

Post by JohnBooty »

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 glosses over it as a possibility. Almost nobody is even considering it as an option in 2023.

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

#84

Post by benbjohnson »

jjtheblunt wrote: post #15
benbjohnson wrote: post #10

Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.

Is there a recommendable way to feign a graph database within SQLite? (because read only replication would be fantastic on fly.io for us.)

SQLite has very little per-query overhead (as opposed to a database connection over a network) so I would think you could traverse a graph using multiple small queries rather than using a graph query language.

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

#85

Post by bradgessler »

Related: I wrote a piece last week on deploying Rails apps to production on Fly.io at https://fly.io/ruby-dispatch/sqlite-and-rails-in-production/

The work that’s made this possible is:

1. Litestack https://github.com/oldmoe/litestack runs everything on Sqlite

2. Fly.io’s work on the dockerfile-rails generator detecting Sqlite and Litestack in a Rails project, then setting up sane defaults for where that data is stored and persisted in production. This is all done behind the scenes with no intervention required from the person deploying.

3. Servers are overall faster and more powerful

I hope more Rails hosts make it easier and safer to deploy Sqlite to production. It will lower costs and reduce complexity for folks deploying apps.

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

#86

Post by sodapopcan »

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.

I've worked for such a place. Off-the-shelf solutions are trying to be everything to everyone and you can end up customizing the crap out of them to the point where it can become more onerous than just building your own. The place I worked was also print-on-demand service and had hundreds of thousands of SKUs as well as allowed customers to make custom products and we also hosted some peoples' shops. Shoehorning that into a custom solution was painful.

I work at a very similar place now that uses Shopify. Managing that many SKUs on Shopify is crazy painful.

The thing is is that custom ecom solutions really aren't that hard. The off-the-shelf ones are complex because, as stated above, they are trying to be everything to everybody.

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

#87

Post by parineum »

evantbyrne wrote: post #68
emodendroket wrote: post #60

Earlier quoted context omitted.

Why even build your own e-commerce Web site at all in that case? It’s undifferentiated work.

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.

> the typical business can use something off-the-shelf

I think we generally have this problem. It's not the typical business that can use that stuff, it's the average business. Unfortunately no business is the average business.

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

#88

Post by benbjohnson »

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…

Author here. The comparison was meant to be about how Postgres (or any client/server RDBMS) is typically deployed. Yes, you can deploy Postgres on the same machine but I wouldn't say it's common. Maybe I could have expanded more on that point or simply referenced client/server architecture rather than Postgres so it didn't seem like a straw man argument.

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

#89

Post by teaearlgraycold »

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…

If you want a read only database that’s too large to simply be a JSON file hosted on a CDN it makes sense. That’s kind of niche but not unheard of.

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

#90

Post by sodapopcan »

bcrosby95 wrote: post #79
emodendroket wrote: post #60

Earlier quoted context omitted.

Why even build your own e-commerce Web site at all in that case? It’s undifferentiated work.

I don't know about nowadays. 8 years ago we built our own ecommerce site + warehouse app (inventory tracking, fulfillment, receiving) for a few hundred orders per day. The goal was to be able to better see profit margins by product, track where the money was going/coming from in detail, along with cleaning up the inventory management part of the operation. The warehouse people loved the change because it really strea…

This is very close to my experience as well. Unfortunately, it was in reverse for me :( We had a custom solution and people were happy. A new tech lead came in and didn't like that the custom solution was PHP that still had some legacy spaghetti in it, so we switched to an off the shelf solution. It was very painful. People were unhappy.
Post reply on HN