I'd be fascinated to hear more about this.

Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many machines?

How are you handling backups and high availability? Are you using Litestream?

How many SQLite files do your application servers have open at any one time?

SQLite mentions NFS as problematic because SQLite makes a lot of use of advisory locking. Which is supposed to work on NFS, but apparently is often buggy.

Writing to a SQLite file on a remote filesystem only works with a heap of caveats: don't use WAL, and disable SHM.

SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file.

(Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)

I'm idly curious why you need to use replicas in a workaround context, and why arbitrary filesystems are a problem.

The local-replica approach is only applied when your library database is on a remote filesystem, which SQLite can't read/write to (due to the absence of shm and flock).

I think I get it now: you want people to be able to point PhotoStructure at a NAS and have it Just Work. That was the context I was missing.

I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case.

Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind...

It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D

(I already know I need to upgrade. Shhh.)

Also super interested in hearing more.

Also super interested, specially handling migrations and structure/schema syncs.

Interested in more info as well.

Also, how do you handle migrations, both schema as well as data?

Slack used to partition their databases per workspace (customer) and then moved away from it. The "Disadvantages" section lists reasons not to do that. https://slack.engineering/scaling-datastores-at-slack-with-v...

Two disadvantages that stand out for me are:

1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision.

2. You are stuck with customers being isolated and makes it much harder to develop features that cut across a group of customers.

2. can be a huge security feature. If your web application only has access to the database of this one customer, you practically can't leak data from other customers by accident.

Slack has cross-workspace connectivity and communication as a product feature. It’s a really cool feature.

My experience mirrors that.

If you have a dynamic set of databases or even tables, be ready to invest into tooling to recreate features you take for granted in a normal database.

> 1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision.

IIRC, Slack had a lot of scaling issues when they won a deal with IBM. Jumping from medium-size customers to such a behemoth will obviously be a challenge, but a lot of the difficulties they faced was non-technical and more business-related (“What do you mean, you want our product but not our monthly plan charged per seat?”). Anyway, that's a nice problem to have for a start-up ;).

IIRC this is the kind of problem that CockroachDB seems to solve well, given their implementation of range partitioning with automatic rebalancing. This ensures the partitions are evenly distributed even with an uneven customer distribuiton.

I have no affiliation with the product, but I have been comparing alternatives and it seems like that's where it shines.

2 can be a feature too!

This is a good post. Thanks for sharing!

3. By splitting, you can give your customers control on data residency, as mandatory in GDPR,

4. By splitting, you limit the exposure in case of SQL injection.

5. By splitting, you can have good performance for the customer who has 1M records, and good performance for the 100K customers with 10 records.

> Keeping the PG servers up to date is a pain, we simply don't have time for this

I've never used AWS RDS (or similar), but I had been thinking that part of the advantage of RDS was you didn't need to "keep the PG server up to date".

But apparently I was wrong! What does this look like on RDS?

(note: we use MySQL on RDS, but the docs for PG seem to be similiar enough)

Minor versions you can tell it a day and rough timeslot (few hours) for it to apply them. Major version upgrades always require you to start them, and the docs lay out prep work to do before hand (check for incompatibilities, setup any custom config before hand, take a backup/snapshot, do dry run)

One of the things I love with RDS is that last bit, doing a dry run with a clone of the database is fairly easy (can't say what it's like at the TB level of data, probably not as easy/quick) to test.

You mostly need to do that if you have a security division that's looking for reasons to justify their existence. One reason involving little actual work on their own side is to constantly nag other teams to install updates "for security reasons", regardless of how much this actually contributes to security. Which usually isn't that much in case of RDS not exposed to the public network at all.

This sounds super interesting, are you able to share any further detail?

I'm currently wrestling with a 35TB Aurora DB and considering options for future growth. The system is multi-tenant and one of the options on the table is breaking out the tenants and hosting them on either their own system or alongside smaller subset of tenants.

Done that before, where my largest clients are exponentially bigger than my smallest client. The key is to realize one-size-fits-all doesn't work, not in the long term. I'm talking about feature development, storage, etc.. In the end, we break out large clients onto their own instances of RDS, where smaller clients stay multi-tenants. They share the same schemas, etc.. We wrote a lot of utility scripts to migrate clients from small to big, and vice versa. Smallest clients get automatic minor and major upgrades to latest features almost immediate. Larger clients generally only get major upgrades.

The issue with this approach (other than isolation) is that the largest tenant may be orders of magnitude bigger than others and still need more sharding to perform well while their smallest still needs to share a db. Have you looked at TiDB (MySQL) or CRDB (PG)?

Absolutely.

Thanks for the suggestion of TiDB, haven't previously considered this but it looks interesting, will take a closer look.

I must admit I'm very curious as to what real-world thing(s) translate to 35TB of stored data (that presumably isn't large blobs, like video or ML models).

(And oh wow I just realized the billing must be amazing...)

Yep, the AWS cost footprint of this system is eye-watering. The compute spend is 2x the RDS spend!

Can't say too much, but it's Financial Services related.

Ah, a gigantic time series event firehose. (That was what I was curious about.)

I guess this cues the stereotypical "have you looked at kdb+" question, then, which you've probably already fielded :)

(https://kx.com)

IIUC, kdb+ does in fact have a real DB hiding in it, and it's not all just "weird programming language" - but it does seem to require/prefer a reasonable amount of buy-in to the way it does things to be valuable. I guess the lateral question is whether its performance obsession extends to smaller on-disk footprint, and whether the savings would be worth throwing out all the the existing Aurora integration work. (Answer: very probably not.)

How do you deal with common sqlite lock file when using it for multiple users?

I solved that once by having a service layer on top of the db. Just a simple REST-like API used by the redt of the app (in Spring).

Not a problem up to 100,000 customers imp

I used it 10 years ago and initially locks were a slight problem. Not sure if this has been solved somehow in sqlite itself.

I solved the problem by handling all the DB operations in a separate thread that handled write and read queues. It certainly was not as easy as using mysql/postgresql where server does everything for you. Nowadays there are probably plenty of libraries that handle that for you.

WAL mode has been added in the last 10 years which handles read concurrency really well IMHO. You’re still constrained to a single writer but that’s not usually a problem as long as you keep your write transactions short.

This pattern is what I did when I used SQLite in production. I used a lightweight actor model microframework to make it easier to route write requests from multiple areas of the code to the single writer thread and offer a continuation (or async/await) perform any follow up actions. Performance easily went beyond the requirements for the app.

I'm not sure how to express my reaction to this kind of design/architecture without being rude. Especially with performant open source databases being available. Was this some sort embedded app or toy project?

You'll probably come to regret that. A DB per customer is a maintenance nightmare.

I remember a discussion here a few years ago about it, and there were so many voices chiming up to say 'we did this, terrible idea'.

Why?

I'm very curious, how do you handle concurrency? Or is this why you have one db per client?

Sounds like a nightmare if you ever have to go under SOC2.

Very interested to hear your story and details!