SQLite-on-the-Server Is Misunderstood: Better at Hyper-Scale Than Micro-Scale

Maybe I am misunderstanding which part you want in the cloud, but that sounds like litestream. Let’s you transparently backup a live SQLite database to a remote destination.

https://litestream.io/

Have your tried CR-SQLite ? https://vlcn.io/docs/cr-sqlite/intro

It implements CRDT as SQLite extension.

SQLite has a session extension that can record changes on a local database into a changeset and you can replay those changes on another SQLite instance. Note that it replays what the changes were, not the queries that resulted in the changes. When applying changes you provide a conflict handler. (You can also invert changesets making a handy undo/redo feature.)

You can save conflicts to another changeset. There is also a rebaser to help deal with multiple way syncing.

https://www.sqlite.org/sessionintro.html - overview

https://www.sqlite.org/session/sqlite3changeset_apply.html - conflict information

https://www.sqlite.org/session/rebaser.html - rebaser

Isn't the simplest way to "sync" to just replace the remote database file with the local database file? One of the nice things about each database being encapsulated as a single file.

I too think that CRDT databases are probably something you should explore. You generally have the whole database locally, and changes get synced pretty easily (but you have to live within the rules of your CRDT).

The one I thought of (mostly because I worked there before they went under/bought by MongoDB) is RealmDB: https://en.wikipedia.org/wiki/Realm_(database)

I have long since lost touch with the state of it, but at the time the syncing to their server was fast and had worked with a long list of environments/languages.

The one thing I will caution: their model was that you almost had to have a database-per-customer. You could have a second one that contained common information, but they had no concept of only syncing part of a database based on some logic. So many customer implications had the clients syncing multiple databases, and then a back-end client that would aggregate the data from all of those databases into one for backend processes. Extra complexity that I always thought was a real killer.

Offline-first databases are a hard problem because there isn't just one copy of the database on the user's side, there are N copies - every browser tab or device on which the user can open the local database and make an edit. It's basically an AP multi-master database (= the same row can be edited at different nodes at the same time), and you likely cannot achieve good results without a database that natively supports multi-master operations.

A simple, manual backup would be fine I think. You can just put an "upload" or "backup to cloud" button to allow user push a full version with timestamp to S3.

Synchronization may introduce a lot more problems, especially when you want to automatically sync the database to some other place. You will need to deal with sync errors, inconsistency, version conflicts, rollbacks...

If your users could accept that, a simple full version backup is the best solution.

How about: Have 1 + N separate SQLite database-files.

Each user would have their own database-file which contains only information about that user. Then 1 shared database-file which contains info needed for all users.

Users would update their own data, which is a small database file which can be easily uploaded. They would not need to update the shared data.

Not knowing your app I don't know what the shared data would contain, presumably something. Perhaps the shared data-file would be updated on the server based on what individual user-data the users upload.

Global tables with anything but a tiny rate of write operations is going to bring Amdahl's Law in to ruin several years of your life while you fight against it.

I had a service that was adding 30 ms to TTFB because most of the other decisions we made during page render were predicated on this response. I would later find about a quarter of this time was in bookkeeping code that got out of hand, but that's a story for another day.

The biggest problem was that this data requires a capital-P Process to change, so it took about 20 minutes to change it and fifteen seconds to roll it back. There had been plans to have the service do more but in the end it looked more like a remote request for a feature toggle. We were already broadcasting feature toggle changes through Consul, which is great for this kind of data. So I did more of the same and got to decom a cluster.

Moral of the story is, it matters what kind of global data you require to build your system, and that's the sort of fact you should keep in mind while designing the system because you can pick a design that will scale or one that absolutely won't, because you've given the users features that make the entire system 10x more expensive per request.

>I've never worked on a product where 100% of the schema could be partitioned like this

SaaS for B2B works well with this, you partition by organization. Each organization has their own DB. Most organizations are typically small (no more than 100 users), and it greatly simplifies things. However, it's still problematic with large organizations (200k-300k is where it starts to fall apart).

Agree with your overall point, but for user session specifically, once a user has entered an identifier can't you use their specific DB for sessions? You also have things like JWTs/PASETO that can carry the DB mapping in the token.

> The obvious caveat here is any situation where you need global tables

A lot of people still end up storing data that's not frequently updated in a traditional OLTP database like Postgres.

However:

I think it always helps to think about these problems as "how would you do it in Cassandra/DynamoDB?"

In the case of Cassandra/DynamoDB, the relevant data (e.g. user ID, channel ID, etc) is always in the partitioning key.

For Durable Objects, you can do the same thing by building a key that's something like:

``` // for a simple keys: env.USER_DO.idFromName(userId);

// or for composite keys: env.DIRECT_MESSAGE_CHANNEL_DO.idFromName(`${userAId}:${userBId}`); // assumes user A and B are sorted ```

I've spoken with a lot of companies using _only_ this architecture for Durable Objects and it's working well.

Is it even realistic to depend on transactional guarantees, with hundreds of services hammering the DB(s) more or less concurrently? Don’t they need to coordinate themselves outside of transactions?

You could have another sqlite with this global information related to users / sessions / passwords etc

would redis/in memory DB be a better suited for this task? I don't think a regular rdbms will meet this requirement at scale

this is nice. i like the idea which has been tried in a few places of running sqlite in the browser directly/locally. the only thing that is really missing to make this work at a bigger scale for read-heavy databases is a very cheap or free static hosting service which does range requests, allows you control of CORS and doesn't have the file size limitations of gist or github pages. maybe this exists already? S3 would do i guess?

you can do kinda magic things like this and build websites that connect to multiple different databases around the web and... well, i'll leave the rest up to your imagination.

go here: https://just.billywhizz.io/sqlite/squeel/

hit CTRL/CMD + Q on your keyboard.

paste in this sql

``` attach database 'https://raw.githubusercontent.com/just-js/just.billywhizz.io...' as chinook ;

select * from albums ; ```

and hit CTRL/CMD + g to run the queries.

I mean if you only have a few thousand records you barely need a database at all.

Author here.

Agreed — I think adding some comparisons to other database partitioning strategies would be helpful.

My 2 cents, specifically about manually partitioning Postgres/MySQL (rather than using something like Citus or Vitess):

SQLite-on-the-server works similarly to Cassandra/DynamoDB in how it partitions data. The number of partitions is decoupled from the number of databases you're running, since data is automatically rebalanced for you. If you're curious, Dagster has a good post on data rebalancing: https://dagster.io/glossary/data-rebalancing.

With manual partitioning, compared to automatic partitioning, you end up writing a lot of extra complex logic for:

- Determining which database each piece of data lives on (as opposed to using partitioning keys which do that automatically)

- Manually rebalancing data, which is often difficult and error-prone

- Adding partitions manually as the system grows

- (Anecdotally) Higher operational costs, since matching node count to workload is tricky

Manual partitioning can work fine for companies like Notion, where teams are already invested in Postgres and its tooling. But overall, I think it introduces more long-term problems than using a more naturally partitioned system.

To be clear: OLTP databases are great — you don’t always need to reach for Cassandra, DynamoDB, or SQLite-on-the-server depending on your workload. But I do think SQLite-on-the-server offers a really compelling blend of the developer experience of Postgres with the scalability of Cassandra.

If you think this is a good fit for your case, you should embed SQLite in your application and shard your application. An embedded SQLite is faster and uses less memory than a PostgreSQL running as a separate process and possibly on a different machine.

I think the other comments have the application-level approaches covered.

However, I suspect the infrastructure will provide this natively as it matures:

- Cloudflare will probably eventually add read replicas for Durable Objects. They're already rolling it out for D1 (their other SQLite database offering). [1]

- Turso has their own story for read replicas. [2]

[1] https://blog.cloudflare.com/building-d1-a-global-database/#s... [2] https://docs.turso.tech/features/embedded-replicas/introduct...

One of the classic solutions to this was to put the data behind a cluster and route by verb. All the GET requests go to the replicas and everything else goes to the writeable instance.

there is no need in read replica, because every micro sqlite partition has very low workload (single digit queries per second)

In your chat channel example, you have a table for messages, and a table for participants. How do you join the participants in this chat channel database with whichever database the participants are actually defined in, so the application would be able to show participant details (name, avatar, etc.)?

I love the idea of Durable Objects, and have thoughts about it in the health space (although having a single provider of them is probably a bad thing) but cross-cutting questions such as this seem to be quite difficult to achieve.

From the chat logs example in your article: how do you cope with a requirement such as "I as a user want to see all my latest thread activity in one place, across all my chat rooms?"

Interesting read, thank you!

Do you use any special tools to manage all these separate databases, track performance and debug problems?

Randomly noticed your post is dated in the future - December 16, 2025

DuckDB is an OLAP (analytical) query engine, sqlite is an OLTP (transactional) database. Modern OLAP engines store and represent data in columnar formats, which makes them very fast at queries that touch many rows (particularly if only a few columns are needed). Queries like "sum all sales for the past month by store."

But they're slow (or incapable) of doing inserts, updates, and deletes, because the columnar formats are typically immutable. They're also relatively slow at operations that need to look at all of the data for a particular row.

OLTP databases are much better for use cases where you're frequently inserting, updating, and accessing individual rows, as for the database backing a web application.

A common pattern is to use an OLTP database (like postgres) to back your application, then replicate the data to an OLAP store like Clickhouse or a data lake to run analytical queries that would overwhelm postgres.

DuckDB crushes SQLite in heavy data workloads according to ClickBench by 915x. (Link below since it's looong.)

DuckDB also has a WASM target: https://duckdb.org/docs/stable/clients/wasm/overview.html

I don't know enough about DuckDB to understand the tradeoffs it made compared to SQLite to achieve this performance.

https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQi...