This is the #1 reason my exuberant technical mind likes that we use SQLite for all the things. Latency is the exact reason you would have a problem scaling any large system in the first place. Forcing it all into one cache-coherent domain is a really good way to begin eliminating entire universes of bugs.
Do we all appreciate just how much more throughput you can get in the case described above? A 100x latency improvement doesn't translate directly into the same # of transactions per second, but its pretty damn close if your I/O subsystem is up to the task.
I know TFA says that you can "soon" automagically replicate your sqlite db to another server, but it only allows writes on a single server and all other will be readers. Now you need to think about how to move all write traffic to a single app server. All writes to that server will still take several milliseconds (possibly more, since S3 is eventually consistent) to propagate around all replicas.
In short, 100x latency improvement for reads is great but a bit of a red herring since if you have read-only traffic you don't need sqlite replication. If you do have write traffic, then routing it through S3 will definitely not give you a 100x latency improvement over Postgres or MySQL anymore. Litestream is definitely on my radar, but as a continuous backup system for small apps ("small" meaning it runs and will always run on a single box) rather than a wholesale replacement of traditional client-server databases.
PS: Congrats Ben!
We have a feature for redirecting HTTP requests that perform writes to a single VM. This makes Litestream + replicas workable for most fullstack apps: https://fly.io/blog/globally-distributed-postgres/
It's not a perfect setup, though. You have to take the writer down to do a deploy. The next big Litestream release should solve that, and is part of what's teased in the post.
Over in-memory data structures,SQLite gives you:
- Persistence
- Crash tolerance
- Extremely powerful declarative querying capabilities
> if you have read-only traffic you don't need sqlite replication.
I agree with you that the main use-case here is backup and data durability for small apps. Which is pretty big deal, as a database server is often the most expensive part of running a small app. That said, there are definitely systems where latency of returning a snapshot of the data is important, but which snapshot isn't (if updates take a while to percolate that's fine).
I do not understand why SQLite must also handle intense write load with HA, failover, etc.
I would rather have the best of both worlds: a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node.
(My ideal case would be some kind of natural sharding where each node keeps its own updates, or just a highly available data browsing app, with data in SQLite files updated as entire files, like a deploymen.)
I'm talking more about sqlite itself than any given product around it at this point, but I still think it's an interesting thought experiment in this context.
With SQLite, you would instead have one database (one file) per user as close to the user as possible that has all of the user's data and you would just read/write to that database. If your application needs to aggregate multiple user's data, then you use something like Litestream to routinely back it up to S3, then when you need to aggregate data you can just access it all there and use a distributed system to do the aggregation on the SQLite database files.
I use SQLite heavily, and have evaluated litestream and rqlite but not deployed them, so bear that in mind.
If the application is set up so that it serves a user for a session, so a given session ID is reading and writing from the same SQLite database, there are many opportunities to replicate that data optimistically, so that you won't lose it if a meteor hits the server, but it might not live in all the replicas right away, since applying patchsets off the gossip network happens in downtime.
If concerns can't be isolated like this then yes, dedicated swarms of database servers are the way to go. Frequently they can be, and using SQLite punches way above its weight here.
The dirty secret is that a lot of systems that require very high write traffic are essentially systems built for narcissists. "Social websites" have higher write traffic than simpler consumption based systems, but we've gone beyond those initial steps into very aggressive systems that are based on recording every interaction with the user and providing them instant gratification for many of those.
These applications don't scale in a way that others do, easily. And maybe it's a feature, not a bug, if the tools I use discourage me from jumping into the maelstrom by making it difficult to even consider doing so. Constraints are where creativity comes from, not possibility.
Let's not forget why we started using separate database server in the first now…
A web server does quite a lot of things: Parsing/formatting HTTP/JSON/HTML, restructuring data, calculating stuff. This is typically very separate from the data loading aspect and as you get more requests you'll have to put more CPU in order to keep up (regardless of the language).
By separating the web server from the database server you introduce more latency in favor of enabling scalability. Now you can spin up hundreds of web servers which all talk to a single database server. This is a typical strategy for scalability: decouple the logic and scale up individually.
If you couple them together it's more difficult to scale. First of all, in order to spin up a server you need a full version of the database. Good luck autoscaling on-demand! Also, now every write will have to be replicated to all the readers. That's a lot more bandwidth.
There are definitely use cases for Litestream, but it's far from a replacement for your typical Node + PostgreSQL stack. I can see it being useful as a lower-level component: You can use Litestream to build your "own" database server with customized logic which you can talk to using an internal protocol (gRPC?) from your web servers.
The point the post is making is that we think people would be surprised how far SQLite can get a typical application. There's a clear win for it in the early phases of an application: managing a database server is operationally (and capitally) expensive, and, importantly, it tends to pin you to a centralized model where it really only makes sense for your application to run in Ashburn --- every request is getting backhauled their anyways.
As the post notes, there's a whole ecosystem of bandaids --- err, tiers --- that mitigate this problem; it's one reason you might sink a lot of engineering work into a horizontally-scaling sharded cache tier, for instance.
The alternative the post proposes is: just use SQLite. Almost all of that complexity melts away, to the point where even your database access code in your app gets simpler (N+1 isn't a game-over problem when each query takes microseconds). Use Litestream and read-only replicas to scale read out horizontally; scale the write leader vertically.
Eventually you'll need to make a decision: scale "out" of SQLite into Postgres (or CockroachDB or whatever), or start investing engineering dollars into making SQLite scale (for instance: by using multiple databases, which is a SQLite feature people sleep on). But the bet this post is making is that the actual value of "eventually" is "surprisingly far into the future", "far enough that it might not make sense to prematurely optimize for it", especially early on when all your resources, cognitively and financially and temporally, are scarce.
We might be very wrong about this! There isn't an interesting blog post (or technical bet) to make about "I'm all in on the n-tier architecture of app servers and database servers". We're just asking people to think about the approach, not saying you're crazy if you don't adopt it.
If you're a language like Node.js then horizontal scaling makes a lot of sense, but I've been working with Rust a lot recently. And Rust is so efficient that you typically end up in a place where a single application server can easily saturate the database. At that point moving them both onto the same box can start to make sense.
This is especially true for a low-traffic apps. I could probably run most of my Rust apps on a VM with 128MB RAM (or even less) and not even a whole CPU core and still get excellent performance. In that context, sticking a SQLite database that backs up to object storage on the same box becomes very attractive from a cost perspective.
Access to monstrous machines is easy today and you have very fast runtimes like Go and the JVM that can leverage this hardware.
So far.
The two important things here are:
1. Fly.io makes it really easy to write through a single primary application instance
2. There are ways to solve this problem so your application doesn't have to worry about it.
Right now, you have to be a little careful bouncing app instances. If you bounce the writer, you can't perform writes for 15s or whatever. This is a big problem during deploys.
There are a tremendous number of Fly.io users that are fine with this limitation, though. It's pretty valuable for some segment of our customers right now.
[1]Local-First Software:You Own Your Data, in spite of the Cloud:
The best solution depends on the unit economics of the problem you are trying to solve. If you have a small number of high value users, then these approaches are premature optimisation, just use Postgres. If your business model is ad eyeballs then squeezing every last drop begins to seem very attractive because you can multiply your profitability (potentially).
Or, from another angle, what would your “local cache” be?
downside of course is the complexity added in synchronization, which is what they're tackling here.
personally i like the idea of per-tenant databases with something like this to scale out for each tenant. it encourages architectures that are more conducive for e2ee or procedures that allow for better guarantees around customer privacy than big central databases with a customer id column.
So do I. And that type of architecture has come up a few times now in this comment thread. Given that Fly has the lead developer of the Phoenix web framework on staff, maybe it would make sense for him to work on integrating this type of architecture, with Litestream-based replication and the ability to have different master regions for different tenants, into Phoenix.
How is that different than whats commonly happening? Android and iOS do this... right? ... but its still accessing the filesystem to use it.
Am I missing something or is what they are describing just completely commonplace that is only interesting to people that use microservices and never knew what was normal.
I'm half joking but I've witnessed many devs use databases when a binary file will do. I've done this personally for years for most of my 'fits-in-RAM', non-transactional, denormalized datasets, which is almost all of them.
Better yet, use both if you have both types of data. The performance benefits are enormous and well worth the complexity tradeoff in my experience.
I don't want to have to deal with locks if at all possible. Binary works fine if each file is atomic, but that does not sound like the case you are advocating.
Not always. It depends on the architecture and your hosting strategy. I think it’s more likely for an instance of a web app to receive more requests than it can handle, causing the app to not service any requests.
My thought is that if you can see consumer changes depending on latency (for example on amazon or google) it is equally important for internal tools. Employee time is expensive.
The latency is not reduced, it is shifted elsewhere.
Why do everyone seem so hyped on this when it can't really work properly IRL? If you have large amounts of data that need to be stored the app would die instantly, or leave all your users waiting for their changes to be saved.
What am I missing?
"Lots of readers few writers" is an extremely common application property tho. Your average HN has significantly more reads than it has writes, especially if you bump the "worthless" writes (e.g. vote counts) out of using the DB and only flush them to the durable DB one in a while, for instance.
And with SQLite's WAL support it's supported even better: while it still has a singular writer, the writer doesn't block the readers anymore, which is a workload issue in the exclusive case (as the single writer would bring read concurrency down to 0).
- "Large" applications can usually be sharded by account. This means 1 file per account, and can easily be put on the most optimal geolocation of the account
- If you defer locking until commit, allowing multiple writers ( https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi... ). This is good enough for most applications anayway.
- Sqlite simple, fast enough for almost anything, supports a good set of features and datatypes, is very easy to embed.
Simon Willison has written about it and coined the term “baked data”: https://simonwillison.net/2021/Jul/28/baked-data/
Mozilla.org uses this architecture, Django app running off SQLite with the db rsync’ed to each application server.
Whereas there is another different kind of Engineering which I probably will never be a part of (simply due to mathematics of available positions doing it) where you are scaling something up for millions of users but the app is much simpler like a Twitter or Reddit, and the challenge is in the scaling.
Edit: forgot to mention that yes a major part of that is batching writes into fewer, bigger transactions; AFAIK you can't really get around that.
>INSERT is really slow - I can only do few dozen INSERTs per second
>Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.
>By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction.
>Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Many sites are Read (almost) Only. For sites where users interactively query/view/explore the data, but (almost) never write their own, it works great.
If you're doing something that multiple systems have had millions of hours of development to do, just use one of those.
https://glyph.twistedmatrix.com/2008/06/this-word-scaling.ht...
So now you need one database connection per user...
Do you 'init' (restore) the db from each app process? When one app makes a write, is it instantly reflected on the other app's local sqlite?
The WAL documentation [1] says "The wal-index greatly improves the performance of readers, but the use of shared memory means that all readers must exist on the same machine. This is why the write-ahead log implementation will not work on a network filesystem."
So it seems that we can't have 2 Node.js servers accessing the same SQLite file on a shared volume.
I'm not sure how to do zero downtime deployment (like starting server 2, checking it works, and shutting down server 1, seems risky since we'll have 2 servers accessing the same SQLite file temporarily)
AFAIK, you either:
1) Don't, and eat a few seconds of downtime (f.ex if the clients re-try in the background, or..)
2) Start two processes on the same machine (believe that's always safe)
3) Share the database over the network in a way that's safe with sqlite3. Think it's possible, but at this point things are getting too complicated to be worth it IMO.
I'm still a little murky on the tradeoffs with Fly (and litestream). @ben / @fly, you should write a tutorial on hosting a todo app using rails with litestream and any expected hurdles at different levels of scale (maybe comparing to Heroku).
Last time somebody from fly said they'd look into it, but alas. It was related to IPv6 on their end, was as far as I could tell.
Maybe I missed it but where in the article does it say Fly acquired Litestream?
EDIT: Ben Johnson says he just joined Fly. Nothing about Fly “acquiring” Litestream.
https://mobile.twitter.com/benbjohnson/status/15237489883352...
I'm honestly not sure whether we should change it or not - minimizing complaints is the goal - what's it called when a function has two points that it keeps unstably jumping between?
As far as I understood it, Fly.io hired the person working on Litestream and pays them to keep working on Litestream.
Ben Johnson confirms how you framed it here:
https://mobile.twitter.com/benbjohnson/status/15237489883352...
Very bottom of the post. Technically, Litestream remains an open-source project, so it's more accurate to say that Fly.io acquired the brand IP and the owner of that IP.
A client told me that they will use a DigitalOcean droplet for a web app. Because the database was very small I chose to use SQLite3.
After delivery the client said their devops guy wasn’t available they would like to deploy to Heroku. Heroku being a ephemeral cloud service couldn’t handle the same directory SQLite3 db I had there. The only solution was to use their Postgres database service.
For some reason, it was infuriating that I have to use a database like that to store few thousand rows of data. Moreover, I would have to rewrite a ton of stuff accommodate the change to Postgres.
I ended up using firestore.
---
I think something like this could have saved me a ton of hassle that day.
Just curious if you’ve ever had to migrate data out of firestore.
> ...people use Litestream today is to replicate their SQLite database to S3 (it's remarkably cheap for most SQLite databases to live-replicate to S3).
Cloudflare R2 would make that even cheaper. Cloudflare set to open beta registration this week.
And if you squint just enough, you'd see R2, S3 et al are nosql KV store themselves, masquerading as disk drives, and used here to back-up a sql db...
> My claim is this: by building reliable, easy-to-use replication for SQLite, we make it attractive for all kinds of full-stack applications to run entirely on SQLite.
Disruption (? [0]) playing out as expected? That said, the world reliable is doing a lot of heavy lifting. Reliability in distributed systems is hard (well... easy if your definition of reliability is different ;) [1])
> And if you don't need the Postgres features, they're a liability.
Reminds me of WireGuard, and how it accomplishes so much more by doing so much less [2].
Congratulations Ben (but really, could have taken a chance with heavybit)!
----
[0] https://hbr.org/2015/12/what-is-disruptive-innovation
[1] God help me, the person on the orange site saying they need to run Jepson tests to verify Litestream WAL-shipping. Stand back! You don’t want to get barium sulfated!, https://twitter.com/tqbf/status/1510066302530072580
[2] "...there’s something like 100 times less code to implement WireGuard than to implement IPsec. Like, that is very hard to believe, but it is actually the case. And that made it something really powerful to build on top of*, https://www.lastweekinaws.com/podcast/screaming-in-the-cloud...
Any source? I wait for a long time.
Cloudflare R2 has free egress. The read and write operations themselves are not that much cheaper than S3.
When I see these startups making a business that is so heavily based on open-source software (like Tailscale on top of Wireguard), I have to wonder what these companies do to actually support the author(s) of the software that so much of their company is based on.
We have also given OSS authors advisor equity. A couple of folks wrote libraries that were important to keeping us going, and we've granted them shares the same way some startups would to MBA advisors.
That's a fantastic idea. In retrospect it's a really obvious idea but I've never heard of anyone doing it before. Is this a common thing that I'm just oblivious to?
That said SQLite has a business model of selling support and premium features like encryption: https://www.sqlite.org/prosupport.html
It's like when RedHat when public and offered pre-IPO stock to open source developers.
> it won't work well on ephemeral, serverless platforms or when using rolling deployments
That's... a lot of new applications these days.
I assumed that was what Fly was hiring Ben to work on.
This is really useful and fun, thanks! Godspeed on this new part of the journey!
We have a small server[0], running since 2016, pushing a great amount of data incredibly fast, with BoltDB as backend. In the past two months we have been restructuring it to use SQLite, it will come online with more data in June. It looks like we are going to continue using your software... knowing first hand the quality of BoltDB, I will have no problems trusting your work with SQLite!
I now wish that I had engaged with this idea that was very similar to litestream that I had about a year and half ago. I always thought SQLite just needed a distribution layer to be extremely effective as a distributed database of sorts. Its flat file architecture means its easy to provision, restore and backup. SQLite also has incremental snapshotting and re-producible WAL logs that can be used to do incremental backups, restores, writes etc. It just needs a "frontend" to handle those bits. Latency has gotten to the point where you can replicate a database by its continued snapshots (which is, on a high level, what litestream appears to be doing) being propagated out to object / blob storage. You could even achieve brute force consensus with this approach if you ran it in a truly distributed way (though RAFT is probably more efficient).
Reason I didn't do this? I thought to myself - why in the world in 2020 would someone choose to use SQLite at scale instead of something like Firebase, Spanner, Fauna, or even Postgres? So after I did an initial prototype (long gone, never pushed it to GitHub) I just felt like...there was no appetite for it.
Now I regret!
Just a long winded way of saying, congrats! This is awesome! Thanks for doing exactly what I wanted to do but didn't have the guts to follow through with.
- https://github.com/rqlite/rqlite - https://github.com/chiselstrike/chiselstore - https://dqlite.io/
I’m sure there’s more, those are just the ones I remember.
it also means that it is the application itself (via the SQLite library) that reads and modifies that database file. There is no separate database process.
Fly.io is very nice. It’s what I hoped Hyper.sh would be, except it isn’t dead. That said, there are a couple things I worry about… like, there’s no obvious way to resize disks, you pretty much need to make a new disk that’s larger, launch a new instance with it mounted, and transfer data from an existing instance. If it was automated, I probably wouldn’t care, though a zero downtime way of resizing disks would be a massive improvement. Another huge concern is just how good the free tier is. I actually am bothered that I basically don’t get billed. Hyper.sh felt a bit overpriced, and by comparison Fly.io does scale up in price but for small uses it feels like theft.
Yes, this sucks right now. Resizable disks is on our list, we just need somebody to spend a few days on it. Luckily we're hiring platform engineers [1] to work on fun problems like that.
> I actually am bothered that I basically don’t get billed.
We actually had a bug that skipped charging a bunch of accounts. :) Regardless, we're not overly concerned about making $1/mo from small accounts. Large customers more than make up for it. Turns out building something devs _choose_ to use on their free time often leads to using it at work too.
If I may, really need to hire sudhirj back or get someone doing the tedious work of answering dumb/advanced questions in the forums and doing follow-ups! Even if it doesn't scale, this high-touch forum engagement may not only help inform the product roadmap but help eventually cultivate a stronger community.
Another way I think about it (I'm sure Ben may have other ideas!) is that if you want to add a layer of reliability to a SQLite-based application, Litestream will work very well and is quite elegant. But if you have a set of data that you absolutely must have access to at all times, and you want to store that data in a SQLite database, rqlite could meet your needs.
Check out the rqlite FAQ for more.
https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md
https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#How-...
I know that Fly also likes Elixir and Phoenix; they hired Chris McCord, after all. So would it make sense for Phoenix applications deployed in production on Fly to use SQLite and Litestream? Is support for SQLite in the Elixir ecosystem, particularly Ecto, good enough for this?
Why yes it is. I maintain the `exqlite` and `ecto_sqlite3` libraries and it was just integrated in with `kino_db` which is used by `livebook`.
Postgresql and every other server/process should have first class support for a single CLI command that: spins up the DB that slurps up the config and the data storage, takes the SQL command provided through the CLI arguments, runs it, returns results and terminates. Effectively, every server/process software should be a library first, since it's easy to make a server out of a library and the reverse is anything but.
https://github.com/JoshuaWise/better-sqlite3
Author is all over the issues section, and seems very knowledgeable about how SQLite works.
I wrote a database called BoltDB before and I have no idea how widespread it is exactly. It's used in a lot of open source projects like Consul & etcd but I don't know anything about non-public usage.
I don't know their user count, but they are growing well and just raised their Series B.
Context from https://www.sqlite.org/datatype3.html: "SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. Flexible typing is a feature of SQLite, not a bug."
That would be epic. DuckDB speed is crazy fast when it comes to aggregate/analysis queries.
- All changes stored as diff trees with signed cryptographic hashes. I want to check out the state of the world at a specific commit, write a change, a week later write another change, revert the first change 3 weeks later. And I want it atomic and side-loaded with no performance hit or downtime.
- Register a Linux container as a UDF or stored procedure. Use with pub/sub to create data-adjacent arbitrary data processing of realtime data
- Fine-grained cryptographically-verified least-privilege access control. No i/o without a valid short-lived key linked to rules allowing specific record access.
- Virtual filesystem. I want to ls /db/sql/SELECT/name/IN/mycorp/myproduct/mysite/users/logged-in/WHERE/Country/EQUALS/USA. (Yes, this is stupid, but I still want it. I don't want to ever have to figure out how to connect to another not-quite-compatible SQL database again.)
This is sort of like temporal tables but with the ability to branch from a previous point of history. I'm not sure it would play well with foreign keys.
You could branch the entire database with either point-in-time recovery or with the file-system using ZFS. Postgres.ai turned this into a product.
I'm interested in how this performs and particularly, what are the tradeoffs relative to the other options above.
I do not understand how one implements the multi-role access system on top of SQLite that postgresql gives you for free.
Other than do it from scratch (eeek!) on the app side.
Just as an example, think of the smallest db backed factory situation you can imagine... as small as you like. There will need to be multiple roles if more than one role accesses the database tables.
Nevertheless, if you're building an app that takes advantage of database auth features, that's a powerful reason to keep on using Postgres. You actually have one of the major problems Postgres solves for!
Congrats to the author though, no matter what! I wish everyone could be so successful.
Woah. Rails is really old
I'd be curious to hear reactions to/experiences with that suggestion/technology, inside or outside the context of fly.io.
I've been building all of my projects for the last year with SQLite + fly.io + Litestream. It's already such a great experience, but I'm excited to see what develops now that Litestream is part of fly.
Another way of saying it: I trust the SQLite's team statements of stability for SQLite because of history and a track-record for following stringent development processes. The same is not true of the Lighstream team. Does anybody know how much any potential damage introduced by the Lightstream code could affect the integrity of my data on disk -- obviously replication added by Lightstream will be only as good as the Lighstream team makes it, but to what degree is the local data-store affected?
We thought so, too, but as our business started to grow, we had to spend months, if not years, rewriting and fine-tuning most of our queries because every day there were reports about query timeouts in large clients' accounts... Some clients left because they were disappointed with performance. Another issue is growing the development team. We made the application stateless so we can spin up additional app instances at no cost, or move them around between nodes, to make sure the load is evenly distributed across all nodes/CPUs (often a node simply dies for some reason). Since they are stateless, if an app instance crashes or becomes unstable, nothing happens, no data is lost, it's just restarted or moved to a less busy node. DB instances are now managed by the SRE team which consists of a few very experienced devs, while the app itself (microservices) is written by several teams of varying experience and you worry less about the app bringing down the whole production because microservice instances are ephemeral and can be quickly killed/restarted/moved around. Simple solutions are attractive but I'd rather invest in a more complex solution from the very beginning, because moving away from SQLite to something like Postgres can be costlier than investing some time in setting up 3-tier if you plan your business to grow, otherwise eventually you can end up reinventing 3-tier, but with SQLite. But that's just my experience, maybe I'm too used to our architecture.
This will be even more brilliant than it already is when fly.io can get some slick sidecar/multi-process stuff.
I ended up back with Postgres after my misconfigs left me a bit burned with S3 costs and data stuff. But I think a master VM backed by persistent storage on fly with read replicas as required is maybe the next step: I love the simplicity of SQLite.
Congratulations, Ben, on making a great product and on the sale!
One thing I have had in the back of my mind, but have not had the time to pursue is using SQLite replication to make something similar to CloudFlare's durable objects but more open.
A "durable object" would be an SQLite database and some program that processes requests and accesses the SQLite database. There would be a runtime that transparently replicates the (database, program) pair where they are needed and routes to them.
That way, I can just start out locally developing my program with an SQLite database, and then run a command and have it available globally. At the same time, since it is just accessing an SQLite database, there would be much less risk of lockin.
Be careful with this approach. Frameworks like Django have DB engine specific features[1]. When you start using them in your application you can no longer use a different DB (SQLite) to run your unit tests.
[1] https://docs.djangoproject.com/en/4.0/ref/contrib/postgres/f...
Would this make lightstream a possible fit to sync a mobile device to a users own silo of data on 'server'? Would need a port of lightstream to Dart.
https://github.com/seancorfield/honeysql
I used it to quickly iterate on the development of migration SQL scripts for a MySQL DB, which was running in production on RDS.
I might have switched to H2 DB later, because that was more compatible with MariaDB, but I could use the same Clojure code, representing the SQL queries, because HoneySQL can emit different syntaxes. Heck, we are even using it to generate queries for the SQL-variant provided by the QuickBooks HTTP API! :)
https://www.hugsql.org/ it's pretty good too, btw! it's just a bit too much magic for me personally :)
Also, you should really look into JetBrains database tooling, like the one in IntelliJ Ultimate or their standalone DataGrip product! It's freaking amazing, compared to other tools I tried. If you are an Emacs person, then I think even with some inferior shells to the command-line interfaces of the various SQL system, you can go very far a lot more conveniently, than thru some ORMs.
Either way, one secret to developing SQL queries comfortably is to utilize some more modern features, like the WITH clause, to provide test data to your queries: https://www.sqlite.org/lang_with.html
You can use it to just type up some static data, but you can also compute test data dynamically and even randomly!
Other little-known feature is the RETURNING clause for INSERT/UPDATE/DELETE: https://www.sqlite.org/lang_returning.html
It can highly simplify your host-code, which embeds SQL, because you don't have to introduce UUID keys everywhere, just so you can generate them without coordination.
We will make up for those latency losses by throwing more microservices in our fat microservices architectures, add more message brokers in the flow. For sure will find a way to bring those milliseconds back. :)
Let's say we're running a vendored application (forking it is not an option) utilizing WAL and want to store the db on one of those filesystems not traditionally suitable for WAL'd sqlite.
Would dropping in Litestream on the db allow us to do so safely?
And while current trend is to implement sharing by applications I expect this to change in the future as it is much more economical to use RDBMS to share data.
Via Kubernetes you could have a cross region cluster that will deal with nodes going offline and like the author said, you would have a couple of seconds downtime with speeds nowadays. Which you could resolve by smarter frontends.
For many, giving up consistency would be a big deal.
This gives is the speed is SQLite plus easy replication and a single source of truth.
Chapeau!!!
More complex use cases?
I remember I could do this on azure at one point in time with app services, not Sure if it’s still a thing.. but heavy writes and scaling of those types of apps would lead to to rethink this approach right?
do you now need your nodes to be clustered + electing a leader and shipping writes there?
know fly.io did this with PG + Elixir but BEAM makes this type of stuff pretty easy
how true in so many fields.
Acknowledged writes must not be lost.
For example, if a user hits "Delete my account", and gets a confirmation "You account was deleted", that answer must be final. It would be bad if the account reappeared afterwards. Similarly, if a user uploads some data, and gets a confirmation (say via HTTP 200), they should be able to assume that the data was durably stored on the other side, and that they can delete it locally.
Most applications make this assumption, and that makes sense: Otherwise you could never know how how much longer a client needs to hold onto the data until being sure that the DB stored it.
This can only be achieved reliably with a server-side network roundtrip on write ("synchronous replication"), because a single machine can fry any time.
The approach presented in the article does not provide this guarantee. It provides low latency by writing to the local SSD, acknowledging the write to the client, and then performing "asynchronous replication" with some delay afterwards. If the server dies after the local SSD write, but before the WAL is shipped, the acknowledged write will be lost. It will still be on the local SSD, but that is not of much use if the server's mainboard is fried (long time to recovery) and another server with old data takes over as the source of truth.
This is why I think it's justified that some other commenters call this approach a "cache" when compared with a multi-AZ DB cluster doing synchronous replication.
The Litestream approach seems to provide roughly the same properties as postgres-on-localhost with async replication turned on. (I also wonder if that would be an interesting implementation of this approach for Fly.io -- it should provide similar microsecond latency while also providing all features that Postgres has.)
As I understand it, Fly.io provides Postgres with synchronous replication (kurt wrote "You can also configure your postgres to use synchronous replication", https://community.fly.io/t/early-look-postgresql-on-fly-we-w...), and https://fly.io/docs/reference/postgres/#high-availability explains that it uses Stolon, which does support synchronous replication if you turn it on. But the "Postgres on Fly" page doesn't seem to explain whether sync or async is the default, and how exactly I can turn on sync mode on Fly.
So I think it would be helpful if the article stated clearly "this is asynchronous replication", thus making clear that it will likely forget acknowledged writes on machine failure, and maybe link to Fly's Postgres offering that provides more guarantees.
1. Compute and storage should be decoupled because the compute vs storage hardware performance increases at different rate over generations of hardware and if our application is coupled, then choosing an efficient shape of the server hardware is very difficult.
2. We know making a single server highly reliable is very difficult (expensive) but making a bunch of servers in aggregate reliable is much much easier. Hence, we should spread our workload on a bunch of servers to reduce the blast radius of any one single server failing.
3. We know making a single server very big (scale vertically) and utilise it efficiently is also very difficult (again, read: expensive). But using a bunch of smaller servers efficiently is relatively easier and more cost effective. Here, big vs small is relative at any given point in time – the median/average size server is whatever is most popularly used – hence it is mass manufactured and sold at volume-pricing-margins and popular software has caught up to use it efficiently (read: linux kernel and popular server software).
4. We know data is ever growing and application is ever more hungry to use more data in 'smart' ways. Hence, overall size of data upon which we want to operate is ever increasing. Hence scalable data architectures are very crucial to keep up with the market competition. (Even if you believe your app can be dumb and simple, the market competition forces will move you towards becoming more data 'smart').
5. We know a lot of business models are viable only at huge scale of users. At smaller scales, the margins are so low that it isn't viable to operate. Again this is due to competition. Only scale operator survives. Hence, we know building architectures that doesn't scale to "millions of users" (even in enterprise software world) isn't viable anymore.
6. We know such scale brings more complexity – multi-tenancy, multiple regions, multiple jurisdictions etc. Internet world is becoming very complex, geo-politically etc. Multi-tenant usage based pricing models bring interesting challenges w.r.t usage metering, isolation, utilisation efficiency and security challenges. Multi-region and multi-jurisdiction brings interesting challenges w.r.t high-availability/continuity and traffic routing and cross-region data storage/replication along with encryption and key-management.
7. With all this, we have learned that layered architecture is critical to managing complexity while providing both feature agility and non-functional stability. Hence we know a lot of these complex capabilities should be solved by the lower layers in a reusable high-leverage way and not be tied to application layers. This is crucial for application layer to rapidly iterate on features to find product-market fit without destabilising these crucial non-functional core capabilities.
8. We know being able to refactor your application domain logic rapidly and efficiently is a super power for a startup hunting product market fit, for a big tech keeping up the innovation speed or any company in between just surviving the competition everyday. This refactoring super-power is crucial for keeping tech debt in control (and being able to take tech debt strategically) and not blowing up your engineering budget by having to hire like crazy (throwing bodies a the problem).
We know all this..and more.. but I'll stop here... for now.
