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

151–160 of 167 posts

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

#151

Post by pmarreck »

sodapopcan wrote: post #90
bcrosby95 wrote: post #79

Earlier quoted context omitted.

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.

> that still had some legacy spaghetti in it

I'm going to take a wild ass guess here that you are wildly understating the "bit of legacy spaghetti"

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

#152

Post by pmarreck »

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…

I completely avoided the need for maintaining an additional dependency (ElasticSearch) simply by taking advantage of fulltext search in Postgres. This did involve some writing of triggers and stored procedure code (which was generated by server-side code, but anyway), which is technically application logic.

There are no perfect solutions in engineering, only tradeoffs.

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

#153

Post by pmarreck »

totallywrong wrote: post #143
vsareto wrote: post #107

Earlier quoted context omitted.

>Me? I actually think pushing app logic to the DB is a solid, underrated, and possibly even optimal solution for a lot of scenarios. The languages for writing it are not as comfy as traditional programming languages, which affects how expressive and maintainable your code will be. The tools for debugging a regular language might also be better than debugging application logic in SQL. Having done some of this in T-SQL…

I've never done it so I won't comment, but Postgres supports Perl, Tcl, and Python besides SQL.

If you peek outside the box, Postgres supports a lot more than those, now:

https://wiki.postgresql.org/wiki/PL_Matrix

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

#154

Post by geysersam »

endisneigh wrote: post #52
sodapopcan wrote: post #28

Earlier quoted context omitted.

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

Any self respecting e-commerce site would want fault tolerance and strong consistency even with potential network partitions, so definitely not SQLite as described in article

Why would they need strong consistency? What's shown on the page the user sees is never strongly consistent with the db anyway.

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

#155

Post by kijin »

morelisp wrote: post #91
kijin wrote: post #25

Earlier quoted context omitted.

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.

Sure, you can download an SQLite database from S3 and use it in your Lambda if your dataset is small enough and, more importantly, you either don't do any writes or have access to a highly reliable solution that synchronizes writes across copies and persists them back to S3.

Unfortunately, Litestream (which OP is promoting in TFA) needs to run as an independent process inside a VM and/or container in order to sync your writes. It's not very different from a traditional RDBMS in that regard. Even the title says "server-side" SQLite, not "server-less" SQLite.

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

#156

Post by rubenv »

benbjohnson wrote: post #82
rubenv wrote: post #19

Earlier quoted context omitted.

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.

Not much feature development is perfectly fine if it works! Things don't have to evolve.

Planning to use litestream as a library to dynamically swap in/out dozens of databases in a process. Looking at the code it'll easily allow that (super clean, kudos!).

So many thanks, it's going to enable a lot of new things!

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

#157

Post by eternityforest »

jeromegn wrote: post #141
eternityforest wrote: post #62

Earlier quoted context omitted.

I don't see any timestamps in the data. If two peers write to the same row, does it not use latest-wins logic?

There’s a col_version column in a clock table used for last-write-wins. In case a tie, the “biggest” value wins.

Oh nice. Looks like on closer inspection they're using Lamport Clocks, which track causation, but if ignore time, although time is mentioned somewhere as a possibility in hybrid systems someday, if I'm understanding it?

Looks like only a 2MB binary for the extension, so you could in theory just pack it with your app too.

I'm particularly interested because it seems like(For very small databases) you could use SyncThing as the sync backend by just periodically dumping your data to files(And making a new one once the file got too big).

I don't know how you could ever garbage collect the old files aside from some kind of manual "Delete everyone else's stuff and output your own big merged log" command, but it would be really cool to be able to make apps with P2P sync.

It also seems like you could put them in an http server and use it like an RSS feed. Or even serve them via torrents.

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

#158

Post by mediumsmart »

Me too. It’s just a .db file on the server. The same as MySQL but this one is on the same server like the clients site or my site. Get it? It’s a file! How crazy is that. If you wanted to outsource the sqlite the same way you do the myposmongresdb databases with separate login, scale from zero to ipo and all the trimmings you would have to put it on another server or a service even. Then you can call it long distance and have a dedicated dbdudeuser like with a grown up database and you get networklattemacciato for free! Endless possibilities and constellations.

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

#159

Post by otoolep »

pmarreck wrote: post #146

How does Litestream compare to rqlite?

Litestream adds reliability to a system using SQLite by periodically backing-up the SQLite database to something like AWS S3. If you lose the node running your SQLite database, you must restore it from your backup.

rqlite, in contrast, adds reliability and high-availability via clustering. This means that any application talking to rqlite shouldn’t notice if a node fails because other nodes in the cluster automatically take over. But rqlite is not a drop-in replacement for SQLite.

https://rqlite.io/docs/faq/#how-is-it-different-than-litestr...

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

#160

Post by jeromegn »

eternityforest wrote: post #157
jeromegn wrote: post #141

Earlier quoted context omitted.

There’s a col_version column in a clock table used for last-write-wins. In case a tie, the “biggest” value wins.

Oh nice. Looks like on closer inspection they're using Lamport Clocks, which track causation, but if ignore time, although time is mentioned somewhere as a possibility in hybrid systems someday, if I'm understanding it? Looks like only a 2MB binary for the extension, so you could in theory just pack it with your app too. I'm particularly interested because it seems like(For very small databases) you could use SyncThi…

We’re using cr-sqlite as part of our distributed state propagation system. It is indeed easy to bundle in the app!

https://github.com/superfly/corrosion

It would be possible to distribute cr-sqlite changes in many different ways (like you said, http or torrents, etc.) since any change can be applied out of order.

Post reply on HN