Get the top HN stories in your inbox every day.
bob1029
WJW
How do any writes end up on other horizontally scaled machines though? To me the whole point of a database on another machine is that it is the single point of truth that many horizontally scaled servers can write to and read each others' updates from. If you don't need that, you might as well read the entire dataset into memory and be done with it.
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!
mrkurt
Litestream does a couple of things. It started as a way to continuously back sqlite files up to s3. Then Ben added read replicas – you can configure Litestream to replicate from a "primary" litestream server. It's still limited to a single writer, but there's no s3 in play. You get async replication to other VMs: https://github.com/fly-apps/litestream-base
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.
throwoutway
> 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/
Thereby making it a constraint and (without failover) a single point of failover? What’s the upper limit here?
nicoburns
> If you don't need that, you might as well read the entire dataset into memory and be done with it.
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).
jbergens
There are in-memory db's that also write to disk. They can offer both persistence and crash tolerance.
I tried o e that even had a better solution to the object-relational mismatch, you just got objects, which made development very easy.
mbreese
I’d argue that persistence also includes data portability. It’s very handy to be able to just copy your data around in a SQLite file. That’s not really a feature that’s terribly useful in a remote deployed application, but very handy if you have multiple applications all reading the same data.
nine_k
I do understand the point of running SQLite in-process to speed up reads.
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.)
jrochkind1
> a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node.
Having your replicas be different database software than your master seems to me like asking for, at least, weird edge case bugs.
Is this something anyone does? Interested to hear experiences if so!
jethro_tell
I've worked on an 'eventually consistent' system with read/write SQLite dbs on each host with a background worker that replayed the log into a central source of truth db and workers that made updates across each host instance of SQLite.
It could have been made a lot faster, I think the replication sla for the service was 10 minutes usually done in seconds. But our specific workflow only progressed in one direction, so replaying a step wasn't a huge issue now and again though that was quite rare. If you were to put a little more effort than we did into replication layer and tuning your master db, it could be a really effective setup.
One of the best parts is that when instances are stopped or isolated, they were also almost isolated from everything that used the service so if you go into a black box with your clients, you work as normal and when connection or other hosts are brought back up they replay the db before accepting connections. We could take entire availability zones offline and the workers and clients would keep humming and update neighbors later.
coredog64
One of the ideas I tried to sell at my last company was to bake SQLite into the lambda images used to run the app. It wouldn’t have been for transactional data, but for the mess of other data you wind up needing (country codes, currency codes, customer name to ID, etc.). It was all stuff that changed infrequently if ever. Unfortunately nobody wanted to do it. I think they wanted to have DynamoDB on their resume.
bob1029
What if, due to ridiculous latency reductions, your business no longer requires more than 1 machine to function at scale?
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.
toolz
I have to imagine having your service highly available (i.e. you need a failover machine) is far more likely to be the reason to need multiple machines than exhausting the resources on some commodity tier machine.
WJW
I'll point out that the ridiculous latency reductions don't apply to replicating the writes to S3 and/or any replica servers, that still takes as long as it would to any other server across a network. The latency reductions are only for pure read traffic. Also, every company I ever worked at had a policy to run at least two instances of a service in case of hardware failure. (Is this reasonable to extrapolate this policy to a company which might want to run on a single sqlite instance? I don't know, but just as a datapoint I don't think any business should strive to run on a single instance)
This write latency might be fine, although more than one backend app I know renewed the expiry time of a user session on every hit and would thus do at least one DB write per HTTP call. I don't think this is optimal, but it does happen and simply going "well don't do write traffic then" does not always line up with how apps are actually built. Replicated sqlite over litestream is very cool, but definitely you need to build your app around and also definitely something that costs you one of your innovation tokens.
ok_dad
With Postgres, you might have one server, or one cluster of servers that are coordinated, and then inside there you have tables with users and the users' data with foreign keys tying them together.
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.
atombender
This sounds a lot like ActorDB [1], which is a horizontally replicated and sharded SQLite.
With ActorDB, each shard (what it calls actors) is its own dedicated SQLite database (but efficiently colocated in the same block storage as all the others, so essentially a virtual database). The intention with the sharding is to allow data that logically belongs together to be stored and replicated together as a shard; for example, all of a single user's data. When you want to run an SQL query you tell it which shard to access (as part of the query), and it routes the request to the right server.
It has some pretty clever stuff to allow querying across multiple such "actors", so you can still get a complete view of all your data.
Sadly, I don't think it's maintained anymore. Last commit was in 2019.
danappelxx
Hold on, doesn’t one-database-per-user totally absolve all ACID guarantees? You can’t do cross-database transactions (to my knowledge), which means you can end up with corrupted data during aggregations. What am I missing?
avinassh
How it would handle conflicts?
samatman
A lot depends on your consistency requirements and data model here.
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.
hinkley
There are many systems that have much higher read to write traffic and so writes only need logarithmic scaling or perhaps with the square root of the system size. Waiting for faster hardware worked for these system for a long time, and to a small extent, still does.
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.
jolux
S3 is strongly consistent now: https://aws.amazon.com/s3/consistency/
fizwhiz
What a ridiculous marketing term. This is a RYW (Read your writes) level of consistency which is a far cry from Strong consistency (see https://jepsen.io/consistency). Seems like eventual consistency with some affinity bolted on.
judofyr
> Latency is the exact reason you would have a problem scaling any large system in the first place.
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.
tptacek
I don't think anyone's seriously arguing that the n-tier database architecture is, like, intrinsically bankrupt. Most applications are going to continue to be built with Postgres. We like Postgres; we have a Postgres offering; we're friends with Postgres-providing services; our product uses Postgres.
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.
mr-wendel
I just wanna equivocate about a single phrase: scale out.
I have a few years of experience w/ SQLite as a backend for dev/test purposes, and the biggest lesson has been in reinforcing best practices... the kind Postgres demand and you're lucky if MySQL reminds you about.
So my newb two cents is that Litestreams makes some unique and significant contributions: it's not "scale out" but "pivot out" to reflect that you've got great backup/replica solutions with impressive agnosticism around what the future solution is.
Thats a lot of leeway to prove that "X's brainchild is a viable product" while minimizing "Y's devops team and infrastructure" without compromising on durability essentials, especially where the solution to so many things is "dump/serve a(n) [encrypted] copy to/from S3" already.
Eager to drink some more kool-aid. :-)
theptip
Does Fly give some magic around splitting reads vs. writes to instances? In a typical Django/Rails app I’m not sure how I’d mark some API endpoints as routed to the single node which is allowed to write to the DB. (I know you guys have some sauce around how you route requests between regions, maybe this falls out of your existing infra here?)
I’m just not seeing how I can operate my traditional Django app in this model, other than just having one instance. I’m probably missing something though!
nicoburns
> There are definitely use cases for Litestream, but it's far from a replacement for your typical Node + PostgreSQL stack
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.
judofyr
This is "vertical scaling" and that is indeed a very valid approach! You just have to be aware that vertical scaling has some fundamental limits and it's going to suck big time if it comes at a surprise to you.
undefined
ithrow
As they say, "you are not twitter" ;)
Access to monstrous machines is easy today and you have very fast runtimes like Go and the JVM that can leverage this hardware.
closeparen
This is a large part of what Rich Hickey emphasizes about Datomic, too. We're so used to the database being "over there" but it's actually very nice to have it locally. Datomic solves this in the context of a distributed database by having the read-only replicas local to client applications while the transaction-running parts are remote.
abraxas
Only trouble with that particular implementation is that the Datomic Transactor is a single threaded single process that serializes every transaction going through it. As long as you don't need to scale writes it works like a charm. However, the workloads I somehow always end up working with are write heavy or at best 50/50 between read and write.
carry_bit
That's another trait it would share with SQLite + litestream, until SQLite's BEGIN CONCURRENT is released.
carry_bit
It's exciting to see Datomic's architecture realized using more conventional technology.
throwaway894345
If you're pushing the database up into the application layer, do you have to route all write operations through a single "master" application instance? If not, is there some multi-master scheme, and if so, is it cheaper to propagate state all the time than it is to have the application write to a master database instance over a network? Moreover, how does it affect the operations of your application? Are you still as comfortable bouncing an application instance as you would otherwise be?
mrkurt
The answer is: yes, you do have to write through a single primary application instance.
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.
throwaway894345
It definitely seems like it could be useful for some use cases; I'm just trying to get my head around the constraints. :)
lucasyvas
What are some ways alluded to in number 2?
funstuff007
This is exactly the reason I am so skeptical of the cloud. I don't care how easy it is to stand up VMs, containers, k8s, etc. What I need to know is how hard is it to lug my data to my application and vice a versa. My feelings on this are so strong as I work mostly on database read-heavy applications.
teleforce
Local-first software is the future:
[1]Local-First Software:You Own Your Data, in spite of the Cloud:
sanderjd
What confuses me about this architecture I guess is: why have a SQL database at all? This sounds like a local cache. Which sure, of course those are super fast. But why does it need to be relational if all the data fits on the edge?
zarzavat
You get SQL and ACID. If you don't need those then you pay a performance price for having them. If you do need them, then you pay a price for not having them.
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).
Kinrany
You usually want ACID, but with N+1 queries not being a problem you no longer need SQL. The database can now be a really low-level key-value store with your favorite query language on top.
pgwhalen
Most data is relational, so why not store it that way?
Or, from another angle, what would your “local cache” be?
sanderjd
Objects / documents / "key-value". If the data is small enough to fit on a single application server node, it seems to me that the relational model is likely overkill. But I don't doubt that there are good counter-examples to this, it's just my intuition.
a-dub
if you can tolerate eventual consistency and have the disk/ram on the application vms, then sure, keeping the data and the indices close to the code has the added benefit of keeping request latency down.
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.
mwcampbell
> personally i like the idea of per-tenant databases with something like this to scale out for each tenant.
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.
yawaramin
Not sure that would make sense. Phoenix uses the Erlang OTP platform, which ships with its own distributed database, Mnesia: https://www.erlang.org/doc/man/mnesia.html
vmception
> SQLite isn't just on the same machine as your application, but actually built into your application process.
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.
mrkurt
This is how client apps use sqlite, yes. Single instance client apps. Litestream is one method of making sqlite work for server side apps. The hard part on the server is solving for multiple processes/vms/containers writing to one sqlite db.
nicoburns
> the hard part on the server is solving for multiple processes/vms/containers writing to one sqlite db.
I feel like if you have multiple apps writing to the database then you shouldn't be using SQLite. That's where Postgres etc completely earn their place in the stack. Where litestream is really valuable is when you have a single writer, but you want point-in-time backups like you can get with postgres.
vmception
interesting, such a weird way to describe it then. but I guess some people are more familiar with that problem.
tlb
It's normal (and HN does something similar, working from in-process data) for systems that don't have to scale beyond one server. If you need multiple servers you have to do something, such as Litestream.
wasd
Fly is putting together a pretty great team and interesting tech stack. It's the service I see as a true disruptor to Heroku because it's doing something novel (not just cheaper).
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).
the_biot
If only they could keep their website reachable, that would be the icing on the cake. Like every time I see them linked on HN, I click and cannot connect to their website.
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.
mrkurt
We have been chasing this down for weeks and can't find the actual bug/workaround here. It's definitely IPv6 related, we think having something to do with weird MTUs. Are you using an IPv6 tunnel or connecting via a vpn by chance?
scottlamb
Weird MTUs: DSL with PPPoE encapsulation, maybe? iirc they tend to use 1492 or 1452 rather than the usual 1500.
mwcampbell
According to Fly's latest logbook post [1], they now have a workaround for this.
the_biot
Nope.
baisq
Disable ipv6 like everybody does.
quickthrower2
How does Vercel fit in? I am having a lot of pleasure using their free tier and would be happy to pay if needed. My only concern is the pricing model being 0/20/Call us. I think clear usage-based pricing plans going 0-infinity should be the norm.
purplerabbit
Render is more of the successor IMO. Fly is a bit of a wildcard — they are bleeding edge, certainly, but they seem to shy away from focusing on implementation of some of the “boring” but extremely useful features present in most managed services (e.g., scaling volumes for Postgres)
michaeldwan
We're not shying away from "boring" stuff at all. We just have a small team with bigger priorities that's spread too thin. There's a million things like resizable volumes we need to ship and we're aggressively hiring to get them done.
steve_adams_86
Are the job listings on your site the source of truth, or are there other listings out there? I’ve been keeping my eye out for a senior full stack role but no luck yet.
tptacek
There's no one "successor to Heroku". The successor to Heroku is a collection of different companies that work well together. What's important is the Heroku idea of what an application is, as a developer-first prospect rather than an ops-first prospect like Kubernetes running on a cloud platform.
dsincl12
Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really.
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?
masklinn
> Uhm... experience from a large project that used SQLite was that we where hit with SQLite only allowing one write transaction at a time. That is madness for any web app really.
"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).
littlecranky67
Another pattern to avoid "worthless" writes is using statistical sampling. I.e. if you have votes in the >100.000 range, generate a random number p in [0, 1], and only perform a write if p < 0.01 - when reading votes, multiply by 100 etc. Of course, you have to assess individually if its feasible for your operation, hence the "worthless".
pc86
Is there something you can point to that explains this "flush them to the durable DB once in a while" pattern in more detail?
onelesd
Instead of running each small write query as the event occurs (a vote), collect them in memory and at some time interval (1m, 5m, or whatever your UX will tolerate) write them to the DB in batch with one (or a few) writes.
jbverschoor
- Most transactions are read-only
- "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.
tidenly
Why would I bake all of those assumptions and limitations into my system though just on the hope it won't ever become a problem
onetom
because you can move faster and explore you problem domain cheaper and validate your solution earlier. then, if you "struck gold" and happened to arrive to some product market fit, then even if you would need to rewrite big chunks of your solution, to swap out your persistence layer, you have a solid specification to follow, which is your sqlite-based implementation!
such a rewrite is a lot more predictable endeavor, then building the initial solution, that it's a great problem to have :)
meanwhile, your UI don't have to change and a lot of your other glue code or business-logic code don't have to change either, IF you haven't hardcoded direct calls to SQLite everywhere in your program :)
eg. I used HoneySQL with great success! My queries are safely assembled from Clojure data structures and I had a single function, which I used to format them to the desired SQL dialect H2DB/SQLite/MySQL/Postgres, execute them and parse the results back into Clojure data structures and even take care of lazily paginating through long result sets, without burdening the consumer of the data with such details.
danuker
Depending on your expected lifecycle of the app, you should use an adapter between the DB and the rest of your app.
Have an interface made up of clear methods which you do all your business through. That way, if you want to change the DB, you only need to rewrite the adapter.
I believe SQLite is a great, low-insallation-needs portable DB you can start your project with. If it turns out your startup gets millions of users, you can afford to switch.
_fizz_buzz_
This questions goes both ways: Why put effort into something that might never even be a problem?
jbverschoor
Simpler backups. Simpler restores. Easier multi tenancy. Easier to have data+app near customer. No firewalls. No passwords. No keys.
Why do you assume you'll run into problems? The moment you're running into problems, you better have a good plan with any RDBMS.
whiddershins
How hard is it to migrate if/when.
samwillis
Quite right it’s not one size fits all but for any site that’s mostly read only it’s a brilliant solution.
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.
quickthrower2
The confusion is probably a lot of us work at smaller companies that serve a wide solution to a niche customer, and that kind of app has a lot of reads and writes but doesn't need to scale. This app might be doing the invoicing/shipments/specialist parts of a business for example.
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.
samwillis
Even for those small niche apps for businesses there are a huge number that are unlikely to be doing more than the 10-20 write transactions / second that SQLite can support.
phaedrus
The default settings of SQLite are very conservative and essentially enforce serial writes. With tuning and loosening that enforcement, you can go from 50 writes per second to 50,000.
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.
andai
https://www.sqlite.org/faq.html#q19
>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.
dagw
What am I missing?
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.
unicornporn
Speaking of this, I really wish there was SQLite support in WordPress...
samwillis
A blog is the perfect example of where SQLite should be used other a DB server.
beberlei
use more than one SQLite file? we have one per day and project for example.
smt88
I don't know if you're joking or not, but this would just be reinventing the Postgres/SQL Server/Oracle/MySQL wheel using duct tape and wishes.
If you're doing something that multiple systems have had millions of hours of development to do, just use one of those.
daliusd
You are wrong. Using multiple SQLite files solves quite many problems, e.g. let's say your user can have multiple products where each product has its own properties. This can be presented as 3 tables in SQL database: users, products, properties. Now with SQLite you can drop one table (users). This simplifies queries, depending on your situation you can use simpler indexes and etc.
Extra benefit: you can allow your user to download all his SQLite db as per GDPR requirement.
sph
But why? That seems such an unnecessary hack.
voganmother42
Until we know how they are used, we are just speculating: I for one am curious ;)
undefined
paulhodge
Wow Litestream sounds really interesting to me. I was just starting on an architecture, that was either stupid or genius, of using many SQLite databases on the server. Each user's account gets their own SQLite file. So the service's horizontal scaling is good (similar to the horizontal scaling of a document DB), and it naturally mitigates data leaks/injections. Also opens up a few neat tricks like the ability to do blue/green rollouts for schema changes. Anyway Litestream seems pretty ideal for that, will be checking it out!
mwcampbell
An architecture like yours has certainly been done before, though AFAIK it never went mainstream. In particular, check out this post from Glyph Lefkowitz of Twisted Python fame, particularly the section about the (apparently dead) Mantissa application server:
https://glyph.twistedmatrix.com/2008/06/this-word-scaling.ht...
hantusk
Same pattern is ActorDB: https://github.com/biokoda/actordb
freedomben
I actually did something very similar to this for an app that produced a lot of data. I wrote a small middleware that automatically figured out which shard to use so the app logic could pretend that it was all just one big db. The app ultimately ended up in the can so it never needed to scale, but I always wonder how it would have gone.
undefined
Scarbutt
Each user's account gets their own SQLite file.
So now you need one database connection per user...
tptacek
And? It's SQLite; it's a file handle and some cache, not a connection pool.
mwcampbell
Depending on how you define "account", that can be quite reasonable. In a B2B application, each business customer could get their own SQLite database, and the number of SQLite connections would likely be quite manageable, even though some customers have many users.
freedomben
Without knowing details about the app, it's hard to know if that would matter. If a small number of concurrent users would ever be using it, I would think it would be NBD.
robertlagrant
If by connection you mean in-process database.
no_wizard
This a great and interesting offering! I think this fits well with fly.io and their model of computing.
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.
evntdrvn
there’s some stuff out there:
- 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.
epilys
I implemented exactly this setup, in Rust, last year for a client. Distributed WAL with write locks on a RAFT scheme. Custom VFS in Rust for sqlite3 to handle the IO. I asked the client to opensource it but it's probably not gonna happen... It's definitely doable though.
ComputerGuru
Did you write your own rust raft implementation or reuse something already available?
epilys
Reused a well known library that uses raft. I don't know if I should mention any more details since it was a private project.
tiffanyh
@dang, the actual title is “ I'm All-In on Server-Side SQLite”
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...
dang
Elsewhere in this thread he says "the project was acquired" which is more or less "Fly.io Buys Litestream" (the submitted title).
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?
mrkurt
that function is correct when it agrees with me.
undefined
lnsp
> Litestream has a new home at Fly.io, but it is and always will be an open-source project. My plan for the next several years is to keep making it more useful, no matter where your application runs, and see just how far we can take the SQLite model of how databases can work.
As far as I understood it, Fly.io hired the person working on Litestream and pays them to keep working on Litestream.
tiffanyh
That’s how I understood it and that’s radically different than how this HN post got titled.
Ben Johnson confirms how you framed it here:
https://mobile.twitter.com/benbjohnson/status/15237489883352...
tptacek
We wrote a different title for this blog post, and we did in fact buy Litestream (to the extent that anyone can "buy" a FOSS project, of course).
gamblor956
"Litestream has a new home at Fly.io, but it is and always will be an open-source project"
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.
bussetta
The tweet[1] links the blog post and says Litestream is part of fly.io now.
jchw
This is interesting! I like using Fly.io today, but I’m currently using a single node for most stuff with SQLite. Having some kind of failover and replication would be pretty awesome. I have yet to try Litestream and it does sound like there’s some issues to work out that could be pretty nasty, but I’ll definitely be watching.
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.
michaeldwan
> there’s no obvious way to resize disks
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.
ignoramous
> Yes, this sucks right now.
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.
tyingq
Dqlite is also interesting, and in a similar space. It seems to have evolved from the LXC/LXD team wanting a replacement for Etcd. It's Sqlite with raft replication and also a networked client protocol.
tptacek
There's also rqlite. There's definitely a place for this kind of stuff. But we already use a bunch of stuff that does distributed consensus in our stack, and the experience has left us wary of it, especially for global distribution. We almost used rqlite for a statekeeping feature internally, but today we'd certainly just use sqlite+litestream for the same kinds of features, just because it's easier to reason about and to deal with operationally when there's problems.
otoolep
rqlite author here. Anything else you can tell me about why you decided against it? Just simpler, as you say, to avoid a distributed system when you can (something I understand).
tptacek
We like rqlite a lot. There's some comments in your issue tracker from Jerome about it at the time. The decision wasn't against rqlite as a piece of software so much as it was us deliberately deciding not to introduce more Raft into our architecture; any place there is Raft, we're concerned we'll essentially need to train our whole on-call rotation on how to handle issues.
The annoying thing about global consensus is that the operational problems tend to be global as well; we had an outage last night (correlated disk failure on 3 different machines!) in Chicago, and it slowed down deploys all the way to Sydney, essentially because of invariants maintained by a global Raft consensus and fed in part from malfunctioning machines.
I think rqlite would make a lot of sense for us for applications where we run multiple regional clusters; it's just that our problems today tend to be global. We're not just looking for opportunities to rip Raft out of our stack; we're also trying to build APIs that regionalize nicely. In nicely-regionalized, contained settings, rqlite might work a treat for us.
jrochkind1
While the title is about a business acquisition, the article is mostly about the technology itself -- replicating SQLite, suggested as a superior option to a more traditional separate-process rdbms, for real large-scale production workloads.
I'd be curious to hear reactions to/experiences with that suggestion/technology, inside or outside the context of fly.io.
obiwanpallav1
In which scenario would you use litestream[1] vs rqlite[2]?
otoolep
rqlite author here. The way I think about it is that both systems add reliability to SQLite, but in addition rqlite also offers high-availability. Another important difference is that Litestream does not require you to change how your application interacts with the SQLite database, but rqlite does.
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-...
benbjohnson
Litestream author here. I agree with Philip. Litestream relaxes some guarantees about durability and availability in order to make it simpler from an operational perspective. I would say the the two projects generally don't have overlap in the applications they would be used for. If your application is ok with the relaxed guarantees of Litestream, it's probably what you want. If you need stronger guarantees, then use rqlite.
otoolep
Agreed, they generally solve different problems. It's important to understand that rqlite's goal is not to replicate SQLite per-se. Its primary goal is to be the world's "easiest to operate, highly-available, distributed relational database". :-) It's trivial to deploy, and very simple to run. As part of meeting that goal of simplicity it uses SQLite as its database engine.
kall
I am as obsessed with sub 100ms responses as the people at fly.io, so I think the one writer and many, many readers architecture is smart and fits quite a few applications. When litestream adds actual replication it will get really exciting.
> it won't work well on ephemeral, serverless platforms or when using rolling deployments
That's... a lot of new applications these days.
mwcampbell
> it won't work well on ephemeral, serverless platforms or when using rolling deployments
I assumed that was what Fly was hiring Ben to work on.
mrkurt
Yes. Yes it is.
emptysea
Yeah the rolling deployments gotcha really stuck out to me. I think most PaaS will provide that by default anyways because who wants downtime during deploys?
mwcampbell
mrkurt specifically mentioned that a solution for that is in the works. https://news.ycombinator.com/item?id=31319544
swaraj
Looks v cool, but I feel like I'm missing a big part of the story, how do 2 app 'servers/process' connect to same sqlite/litestream db?
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?
judofyr
Each server would have one copy of the SQLite database. Only one of the server would support writes — and those write will be replicated to the other server. Reads in the other server will be transactionally safe, but might be slightly out of date.
zepolen
I don't think you understand what transactionally safe means. SQLite used in this manner is not a database, it's a cache. Thinking otherwise will give you a bad time when the value you're writing is based on the stale value you read.
swaraj
This is my main q: are the writes replicated in real-time? Do the apps that just need read access have to repeatedly call 'restore'?
mikkei
This is what I understand as well. On top of that, you cannot call restore on the same sqlite file, so you have to create a new db each restore and make your reader app aware of the new db file.
Edit: As the blog is saying, it's mainly for single-node application so you should treat the project as a way to constantly backup your sqlite db and being able to recover from disaster.
trh0awayman
You have to call a "restore" function, according to their docs. I'm not sure I understand the use case, but perhaps it's just for structured user-only data.
johnrrk
I also investigated SQLite and it's not clear how we can use it with multiple servers.
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)
tptacek
The point of Litestream is that you don't have multiple servers accessing the same SQLite file. They all have their own SQLite databases. Of course, you only write to one of them, but that's a common constraint for database clusters.
gizzlon
> I'm not sure how to do zero downtime deployment
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.
Get the top HN stories in your inbox every day.
> SQLite isn't just on the same machine as your application, but actually built into your application process. 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.
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.