That might sound confining, but it's what people already do with Postgres: read replicas, single write master.
And the benefits aren't academic: having your database in-process not only simplifies your database code (because N+1 queries are no longer perf emergencies), but can also drastically speed up response times, because you're not dealing with compounding intra-DC latency responding to an HTTP request that incurs multiple database queries.
And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement.
Don't get me wrong, I think it's still pretty niche-y as an alternative to standard n-tier app databases. You'd use it deliberately; it's unlikely to be a full-stack default. But people have definitely been sleeping on SQLite, and I think what you're seeing is some of those people beginning to wake up.
This is what I'm saying - I keep hearing this, which popular website is using SQLite as their main database?
I don't have any doubt you can serve 1000 users, or even 10,000 current users using SQLite. There's a power law distribution with the popularity of sites. The most popular sites have multiple orders of magnitude.
Can SQLite serve 500,000 current users with 75% reads and writes with the consistency you'd expect?
> And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement.
Sounds reasonable in theory. How do you maintain consistency between all of these databases? Schema migrations? Backups and restores and maintaining referential consistency?
By the time you implement all of this, and you certainly can, why not just use Postgres?
First, I don't think this is a good metric for software quality or use-case analysis. Just because people don't use X doesn't mean X wouldn't be a superior tool for their use-case.
> How do you maintain consistency between all of these databases?
You don't, in the GP's example, you would shard the database schema. Sharding is easy to figure about, because many database services do this already to spread data and analysis across several servers. You might use Litestream to then send those thousands/millions of database files to some S3 bucket somewhere. Then, you could analyze the shards together in that bucket using one of the many available solutions for such things, like Apache Hive or whatever the BigCo clouds provide.
> By the time you implement all of this, and you certainly can, why not just use Postgres?
Because you analyzed the pros and cons to SQLite vs. Postgres for your specific use-case and determined SQLite will be better in the long run. Perhaps your particular use-case will turn up "Postgres" and you won't use SQLite, or maybe you'll use both of them, but for different data stores.
No one technology is "the chosen one", sometimes you need several different options.
I'm not sure if you're being serious. Sharding isn't easy at all. Especially if you're doing it across different files that need to be synced in tandem with schema migrations that also need to be applied in tandem.
> Because you analyzed the pros and cons to SQLite vs. Postgres for your specific use-case and determined SQLite will be better in the long run. Perhaps your particular use-case will turn up "Postgres" and you won't use SQLite, or maybe you'll use both of them, but for different data stores.
The use case is already defined in my original post. A popular app with a lot of concurrent users. People claim SQLite is superior to Postgres for such a scenario, I'm curious to read about and use a service using SQLite for an app with a lot of users (>1M 75% reads)
From looking at the issues for Litestream on GitHub I'm not really convinced it's production ready. For some small app I'm sure it's fine, I'm talking about an app with a lot of users generating a lot of money and you need high availability.
All of this makes it sound like I dislike SQLite, I really don't - I've used it successfully for local apps on the web, Android and iOS. However I feel this popularity for SQLite on the web may be misplaced.
Only idiots would claim some technology is superior without doing some use-case analysis and experimentation.
> I'm not sure if you're being serious...
I am, but still it depends on use case. If you have an application that can support it for the use-case, then you can do simple things like making one DB per user and then doing aggregate functions on Apache Hive or something. If you have a schema that will absolutely NOT support sharding, then I guess it might not work in that case. I would argue I could design a SQLite solution for 95% of the applications that need a SQL database, but there are some which, for whatever reason, may require other solutions. I am also NOT saying that all of those designs would be the best for that use-case, but it would work.
Unequivocally: Your use cases define the tools you need to use.
> The use case is already defined in my original post. A popular app with a lot of concurrent users. People claim SQLite is superior to Postgres for such a scenario, I'm curious to read about and use a service using SQLite for an app with a lot of users (>1M 75% reads).
Still, you didn't define it. What's the data schema? What is the application? I don't know what to say, if your app is a TODO list app that's wildly popular, then I guarantee I can do what you want with SQLite easily. If it's Spotify-level stuff with special aggregation and data analysis, maybe not?
sigh. any app based on SQLite with more than 1 million concurrent users, 75% reads.
Here are a few other sources I googled:
https://www.sqlite.org/famous.html
This is the best I can do, because I have no idea what else you want. If I had the time, I could throw up a simulated million users doing a TODO app with SQLite as the backing DB engine, but I don't have time, I have a 2 year old. Do your own research from here, or don't and just use Postgres until you retire, I don't really care that much.
I don’t think your example is a good fit for SQLite as it’s not a good fit for any sql store that I know of.
https://www.uber.com/blog/building-ubers-fulfillment-platfor...
The parent asked about use cases where there wasn’t an _external_ (which I interpret to mean “handled explicitly by the app, not the database”) sharding / caching layer.
Spanner internally handles splits based on load / size.
That having been said, more careful reading of the Uber post shows that they still do employ additional app-level explicit sharding to resolve hot-spots, so it’s probably a fair point that this doesn’t qualify as an example the parent was looking for.
Legitimately interested as this is something I have more than a little professional interest.
That said, “show me an app doing 5k concurrent writes with SQLite” seems like a much more reasonable standard.
Definitely not the use case for SQLite unless you can easily do sharding but as you said, why bother?
I like finding hints of our architecture scattered across these SQLite threads :D
It's very sketchy trying to slam all users into a single physical database. If you can slice up the users along unit of work, session, customer, account, etc., you can go horizontal very quickly.
Look no further than sqlite's own source control system: <https://fossil-scm.org>
i've used that site almost daily since the end of 2007 and have encountered maybe (maybe) three "db is locked" errors.
SQLite has been deployed on billions of devices, so "academic" is an odd term here.
We've been doing this in production for over half a decade now. It goes so well it becomes boring very quickly.
Our most heavily loaded SQLite instance probably sees 100 writes per second. In aggregate, we can handle tens of thousands of requests per second.
I presume you've designed your application to accommodate only the data types offered by SQLite and without any concurrent writing?
Using SQLite for any serious webservices is bad thing because you can only have a single app. Then you have other tools on top of SQLite for those shortcoming.
Perhaps more appropriately, sqlite's own forum is sqlite-backed: <https://sqlite.org/forum> (running the Fossil SCM's forum interface, which itself exclusively uses sqlite for storage: <https://fossil-scm.org> (that whole site is an instance of fossil, serving all content from an sqlite db))
It just seems so academic. I’d like to use a web service or app backed primarily by SQLite and see how it goes.