Databases are configured for different priorities out of the box.

For example when you insert a row that result in a write to the WAL. That's a data safety measure sqlite unlikely to be taking.

You can configure Postgres to perform better with different tradeoffs - here's some thing to investigate:

  - synchronous_commit=off 
  - wal_level=minimal 
  - wal_writer_delay=200ms 
  - checkpoint_completion_target=0.9 
  - autovacuum=off 
  - bgwriter_delay=10000ms 
  - shared_buffers=512MB 
  - work_mem=64MB 
  - UNLOGGED tables
  - run from RAM Disk (/dev/shm)

Is that SQLite with default settings? There are a few tunables for SQLite like mmap_size and page_size which help in most situations: https://sqlite.org/mmap.html (as well as journal_mode=wal)

How much of it was because you didn't go through TCP/IP with sqlite?

Did you try with duckdb ?

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

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/

SQLite has the ability to do exactly this, minus the versioning: https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki

Implementing snapshot backups on top of that would be as simple as a VACUUM and S3 PUT

For point-in-time restores, mvsqlite is a cool solution: https://github.com/losfair/mvsqlite

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

It implements CRDT as SQLite extension.

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.

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.

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've wanted to use SQLite a few times for the simplicity. I always end up using Postgres though because I don't understand how multiple services / replicas can make use of it. If another piece of infrastructure is needed to support it (even nfs), that seemingly counters any simplicity gains.

Why not local storage or in browser db? If it is a local web app there is no need for backend.

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.

Check out https://sqlsync.dev/

Forget some roll-your-own stuff. Have a look at Replicache, Zero, and Evolu.

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).

Put your global tables in Postgres/CockroachDB/Vitess/etc. You don't need to force everything to be a SQLite table. Use the right tool for the job. SQLite can be the right tool, and it can not be the right tool.

> 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.

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.

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?

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

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

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.

Been there, have the scars from googling mongo error log messages to prove it.

I've been pleased at work to see some on-prem server apps switching to SQLite recently. Most recent that comes to mind is EFT Server (Windows SFTP/FTPS server), which switched from SQL Server Express to SQLite. SSE always makes me groan: huge install, pain to manage, etc. SQLite is just so easy to copy files around and make backups. I'm sure it's probably faster also.

Using mongodb for the controller was a very poor decision. I went through similar issues migrating my controller :(

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.

Author here, love this take.

I've chatted with a few medium-sized companies looking at Durable Objects for this reason. DB-per-tentant removes much of the need for another dedicated team to provision & maintain infrastructure for the services. It's almost like what microservices were trying to be but fell woefully short of achieving.

It's disappointing (but understandable) that "serverless" received a bad rap. It's never going to fully replace traditional infrastructure, but it does solve a lot of problems.

I remember there being an HN post a month or two ago about a solution to this exact problem and people using it seemed very happy with it.

I think of SQLite on the server as something that you would use when running in a traditional server (VPS or dedicated host) setup rather than an orchestrated, container-based one.

> But several times when I have been doing development work my file based database has become corrupted. I violated some constraint, the sql was poorly formed, etc. Always my fault. I simply remove the file that SQLite is using and create a new one or use a backup copy.

That really shouldn't be able to happen unless you're using the API wrong or have weakened safety with pragmas or database configurations that trade off resilience for performance. Even with crashes, attempts to violate constraints, poorly formed SQL, etc, the file should never become corrupt. If it does, that's a bug with SQLite, not you. Corruption bugs are exceptionally rare in SQLite these days.

Have you given pglite a try?

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)

I was thinking something very similar. Once you've accepted any need at all for global state the next move is to reorient to minimizing it with horizontally scalable point local state and a small targeting dataset and tiered caching system.

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?"

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.)?

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

Fair point, noted.

This seems to be the biggest hesitation I've heard over and over by far. There absolutely needs to be a good story here for both (a) ad-hoc cross-partition queries and (b) automatically building a datalake without having to know what ETL stands for.

However, this isn't so much different from Cassandra/DynamoDB which have a similar problem. You _can_ query cross-partition, but it's strongly discouraged and will strain any reasonably sized cluster.

Hopefully, it matures into a healthy open-source ecosystem that doesn’t rely on proprietary databases.

More companies than people realize are already building and scaling with DO SQLite or Turso internally. Almost every company I've talked to that chooses Postgres hits scaling issues around Series A — these companies aren’t.

If you care only about serverless, databases like PlanetScale, CockroachDB Cloud, and DynamoDB work well.

The biggest strength of using SQLite here is that it provides the benefits of a familiar SQL environment with the scaling benefits of Cassandra/DynamoDB.

Author here.

To clarify — is your concern that the only scaling options I listed are proprietary services?

If so, I completely agree. This article was inspired by a tool we're building internally, based on the same architecture. We knew this was the right approach, but we refuse to rely on proprietary databases, so we built our own in-house.

We’re planning to open-source it soon.

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...