I Migrated from a Postgres Cluster to Distributed SQLite with LiteFS

I get that relational dbs like both Postgres and SQLite are extremely battletested and reliable, incredible pieces of tech, with tons of features. But what I’m reading here is a minefield of footguns, for fairly trivial logic, so I’m wondering if these tools were really designed for these use cases.<p>Now, I don’t know if every server-side app NEEDS to run in 10 regions, auto-refresh session cookies, notify when someone starts typing a comment, report metrics every time the user moves the mouse pointer (or whatever else the kids do for fun these days). But let’s assume for a moment that&#x27;s the common case.<p>My understanding is that these relational dbs require a lot of config, awareness of the difficult distributed issues, and other layers such as caching, batching and so on, all of which is incredibly error-prone.<p>In fact, the most important parts of the ground laws of data manipulation isn’t even part of a schema! I could worry less about enums and nulls, in a world with orders of magnitude more dangerous creatures: losing writes because I wrote to a replica, reading or writing inconsistent state, duplicate writes etc etc. It feels like we have a lot of structure around the easy stuff, but no structure around the hard parts.<p>As a mere a dabbler with databases, can someone help calm my nerves?

I have a couple of questions...<p>- So LiteFS turns sqlite into a client server database. But SQLite being embedded is what gives it its low latency in the first place. So, I kind of have to ask - what&#x27;s point? Just prefering the way sqlite does SQL over postgres?<p>- What language is that database model under &quot;Evaluating the existing data&quot;?

Seems there is a bit of vendor lock-in here with flyio, the way he&#x27;s throwing custom http error codes for their infra. Can I just run LiteFS on a few virtual machines and forward writes back to master?<p>With LiteFS, what happens if master goes down? Can a new one be elected?

Good writeup on using LiteStream at this early stage, though I wouldn&#x27;t say it&#x27;s production-ready based on a skim, though it might be a good fit for read-heavy apps which don&#x27;t have a lot of data.<p>One thing that&#x27;s not clear to me is whether Litestream+SQLite works in-memory. If Litestream wrote to disk on the primary node, but kept the full DB in-memory for facilitating reads, I can definitely see how it would be a lot faster than Postgres (as the author claims).<p>Though I&#x27;d also like to know if any RDBMSs have been designed for that use case, as I&#x27;d imagine traditional DB optimizations have been made for high incidence of disk access that would no longer hold true if the entire DB was in-memory

Interesting article covering the nitty gritty details of production deployments. I can’t help but think something is wrong with anonymous users though?<p>Loading the site was noticeably slow in MEL despite a SYD deployment. Took ~8s to load the page on a fast home fibre connection, abnormally slow for local sites, typical for US-only deployments.<p>I wonder if anonymous users are unnecessarily being given a session for tracking purposes, and that is causing the slowness. Cycling session IDs for authenticated users makes sense for the UX benefit of not having to log in again, but that speed issue is terrible for low intent anonymous users landing on the site for the first time.

Off topic I guess, but I&#x27;ve been thinking about a collaborative spreadsheet that uses something like Sqlite over LiteFS. Where each spreadsheet is just a sqlite file. Not sure what the architecture would look like though. Ideally the main server would just keep the files in sync, perhaps with some way to update each user without having to send the whole file. This has the advantage of programabilty and a familiar UI. All at just the cost of hosting the file and a webserver.

Does this mean an entire copy of the DB is stored in-app? Isn&#x27;t that a bad thing for large sites?

Great writeup. Thanks for covering DB writes. I was curious how that worked with SQLite and LiteFS.

Site takes several seconds to load. Author completely overhauls an application just over one year old which was marketed at the time as a prescription for modern web development. Migration plagued by errors and implemented with obvious inefficiencies in data handling. Feature parity with old application not yet reached. Handling analytics with a relational db.