Around the World with SQLite3 and Rsync | Hacker News Reader

Around the World with SQLite3 and Rsync

fly.io

112 pointsLiriel25 comments

This feels like a pretty significant step down in terms of quality for technical content output from Fly.io?

To be fair, "explains a solution without explaining a problem" is one of my most common criticisms for technical content in general, but I am pretty sure this wouldn't have gotten to front page if it wasn't for fly's reputation. There's a lot of passive voice which makes it really hard to figure out who is doing what to what, and most importantly _why_?

Author here, and fly.io employee. I have an app that allows dance studios to schedule "heats" for competitions. Multiple dance studios, in separate locations, use this app with separate databases. I merely blogged some techniques that I found useful for my app. Most apps won't need any of this, but in the future when somebody has a related problem, I wanted something I could point to to show some techniques that might be helpful.

Why rsync and not Fly.io's own https://fly.io/docs/litefs/?

The short answer is that litefs uses a single primary node (see: https://fly.io/docs/litefs/proxy/). I work with Ben so I imagine that over time we'll find a configuration that works with multiple databases each with their own primary node, but until then, this works for now.

Never heard of them and that was exactly what I thought. "What problem are they trying to solve?".

This feels weird to say, but, honestly, and I feel sheepish saying this, when it comes to technology stuff I'm an HN person first and everything else second, so I guess I get to say it:

Can I ask you not to say things like this? Specifically: this idea that there's a bar for technical stunt driving that we need to clear to get things up on the blog. I know I can't demand that of anybody; I'm just asking.

I can't tell you how many things we haven't written on our main blog because we're worried about whether it's as technically interesting as the "Gossip Glomers" thing we did with Aphyr.

Sam's a good writer and this is a pragmatic post about an approach he took to building something. The world is incrementally better with this post in it than without it. Holding off until we could have integrated LiteFS and written a 4 paragraph coda about sqlite3 internals would not make the world a better place.

I worry all the time about "Fly.io fatigue" on HN, for what it's worth! But the only sane response we can have to do that is: we just write what we want to write, and we let the community do what it's going to do. If 'dang gave us a header we could use to dampen some of our posts, we'd use it. :)

If I'm crazy to ask this, say so. We've been grappling with this for years now; if we knew exactly how to handle it, I wouldn't have to write weird things like this.

In fairness (and like OP mentioned), you folks set the bar really high yourselves.

> But the only sane response we can have to do that is: we just write what we want to write, and we let the community do what it's going to do.

Move over paxos and sqlite3; HN needs FlyLLM and Fly vGPU to satiate its appetite now ;)

GPUs are coming! I lost a big chunk of 2023 to GPU driver stuff.

I nearly deleted my comment because I felt bad after the author replied and also because I generally want to encourage people to publish stuff they are not confident about -- and I would never have posted this about a personal blog.

But I decided to leave it because I think I stand by the implication that reputation for specific technical blogs can be earned or used up. Fly.io has earned a lot of reputation and I (and I guess others) clicked on a title I would not usually have because I saw the domain. In this case I feel like some reputation was "used up" and I am (slightly) less likely to click on a post solely because I see a fly.io domain in future.

Maybe I am just not the target audience for the post and it is better than my assessment, but if not then I think people value HN as an honest audience that doesn't hold back on criticism (I know I do and I have received harsh but valid criticism from hn on stuff I have written).

Fly.io can probably keep making front page based on reputation if there is a balance of very high quality content and other posts, but I feel like something like this could have been 1000% better with another revision round and some editing (probably only 20% more effort) so I will keep saying stuff like this I guess?

And just to be clear to your point of "not interesting enough" this is entirely about form, not the topic. If the content itself is interesting or not is hard for me to judge because of the obstacles presented by the form.

> I felt bad

Don't. I can honestly say that I didn't write this post targeting HN. I'll go further... this post wasn't meant for people who are unlikely to use https://github.com/fly-apps/dockerfile-rails#overview. I recently added some features to that gem whose usage may not be intuitively obvious. I wrote this post to explain some of the motivation for those features.

I don't know how to mark posts as not intended for HN (and truth be told, if there was such a feature, I'd be inclined to overuse it). I don't know where else I should have posted this content, but I'm not sure I would be inclined to move it. In any case, this post, as written, serves a purpose for me. Somebody not you and not me felt it belonged here. We can both second guess that decision. Either way, there is no reason for either of us to feel bad.

It's strange that you would even ask. HN comments are notoriously critical and the standard for good content shifts extremely quickly around here. Why should Fly.io get a pass when nobody else does?

I guess I'm saying everybody should get a pass? I sort of don't even know what a "pass" means here. We should be judging every post here on its merits, and not holding them up to a weird, inchoate standard that both impedes us from writing straightforward useful posts, and also makes us reconsider the implications of posting deep dive technical stuff.

I see what you're saying. It just seems odd to say that fly.io is considering posting less because HN users are expecting too much or too critical. Surely HN isn't the only source of traffic to your blogs, right?

I love fly.io and appreciate the content but that doesn't sit right with me.

It's just an incoherent and unfair criticism. It's not Sam's job to write a deep dive on sqlite internals (that's Ben's job!). Sam's a Rails person, he writes about Rails stuff, as a search for [sam ruby rails] will indicate.

I just worry about us writing less stuff because people assume there's going to be some stunt driving sequence or shootout or CGI or whatever, and not everything people write here is like that.

Minute later

(I may be getting pushed into responding adversarially here just by message board dynamics. Sorry. Like I said up front: it's a weird thing to write! But what am I going to do, filter my thoughts before writing them on HN?)

This can certainly work for files, if you don’t use a CDN. But also, you can just use a CDN!

As for the database that handles your business logic … 3 minutes of googling would show that rsync is a poor (or at least a brittle) substitute for multi-master replication when it comes to database engines like InnoDB for MySQL (which is what most websites on the Internet use):

https://dba.stackexchange.com/questions/91287/is-possible-to...

However, you can consider setting up multi-master replication, or if you want your app to be portable, you can set up a replication layer in the app itself. The order of operations should only matter “per activity” (eg chatroom, document etc) and not globally.

That’s what we set up at https://qbix.com/platform — except that the instances don’t all have to be under the control of one company. It’s basically a decentralized social networking platform. Federated, like Mastodon, but far more general-purpose, so you can build any applicatiom with it

We sell live http media streaming and use sqlite3 locally to solve most of the hairy race conditions you would expect when serving files while they are simultaneously being written to. Despite our advice and common wisdom, every now and then customers complain about corrupted databases and after talks we discover they are using rsync or NFS to replicate the sqlite DB file.

So, I was very interested in this article which seemed to suggest that fly.io, somehow found a way to make this work anyhow. TL;DR — looks like the answer is: they did not.

This is the blog post I want to read. What’s your setup for that?

So the absolute killer features that I would say SQLite offers are: 1. Embedded (single, small library, very fast) 2. Portable concurrency using native OS/FS abstractions. Full SQL support is nice, but I'd also be happy if it only offered plain key values. I love that you don't need to manage DB processes, network connections, FS partitions, shard memory, mutexes — just point your process or thread to a file and it magically fixes atomicity, consistency, transactions, the whole enchilada.

SQL databases are not great at storing huge chunks to data (petabytes of video) but those are easily handled by the native filesystem. Just make sure the write access is safely wrapped in a DB transaction, RAII style. This works and scales well enough except for the failure mode: over stressed egress (typically SELECT queries) will starve ingest (a more or less constant load of UPDATE & INSERT queries).

At that point, you want to scale horizontally. As mentioned, replicating the SQL backend file over NFS or rsync is simply a horrible idea. Surprisingly we ultimately discovered that SQL-level replication did not solve the problem reliably either: scale improved, but the starvation failure mode (read locks blocking DB writes) persisted (be it less frequently).

The replication layer we needed was at the application level where it's much easier to express that actually we did not need the real-time coupling between media ingest and egress. Eventual consistency is good enough: if the system is about to collapse, simply fail by adding latency so you can always keep ingest healthy.

Worst case, people watching a popular sports event will see players buffering. Which is bad — don't get me wrong — but far better than a catastrophically blocked pipeline and congested video encoders.

Use the backup api and expose that as a means of copying the database.

https://sqlite.org/backup.html

I am halfway through the article but had no idea what it was trying to accomplish. It's possible that it's because I am not deeply embedded in Rails ecosystem.

Is there a tl;dr to what this does and why?

Author, please rewrite this. My suggestions:

1) Delete the first two paragraphs.

2) Tell me what you're going to tell me, but without all the highlights and bullet points.

3) Sell me on why I want to read this article. Don't focus on the technical; focus on the human, what I as a person will get from it.

4) In later technical sections, don't give me instructions. Tell me what you are doing in general to solve some problem, then just show me the commands, and I will infer that I could also do X, do Y, do Z.

5) If you want to give a lot of technical detail and backstory, either put it in an expand, or in some kind of colored box so I have the visual context that I can probably skip this whole, continue the story, and come back to this later.

6) The recap shouldn't be larger than two paragraphs.

Slightly off topic, I wish websites would provide an auto-detected dark mode or at least test with dark reader. On my phone, a lot of the article looked redacted.

It’s not the most complicated part of the process but I wonder if they could use dqlite instead.

content marketing

Reply on news.ycombinator.com