Support for per-database primary on different nodes (regions)?

[mattbnz]

Would it be possible for litefs to manage primary status on a per-database rather than per-node basis to enable different databases within the same application (e.g. one database per SaaS tenant) to prefer their primary in the region that is closest to their primary users but still allow replication/failover if that region fails?

AFAICT from a quick skim of the code the "node is primary" concept seems pretty heavily baked into a lot of the code, so I suspect the answer is not easily - but I also don't see any fundamental reason or constraint on why it should be at the node level, rather than the database level (maybe # of consul leases is a consideration at some scale, but not for a while) - given how useful this type of functionality could be vs the alternative of having to run multiple different litefs instances/clusters for each "primary" region otherwise.

Thoughts?

[benbjohnson]

Yes, per-database primaries is something we are implementing. That's how we have LiteVFS working and we'll port similar functionality over to LiteFS.

I'm always curious about use cases though. Are you thinking you'd want to configure which databases are primary in each region at startup? Or do you have a more complex use case?

[mattbnz]

Excellent news, thanks!

Ideally it would be tunable at runtime (e.g. app server can make RPC to lifefs server in preferred region to say "take primary now if you can please") rather than needing to be specified at startup, but even having to configure it at start-up would be feasible.

The main use-case for the more dynamic configuration that I see is optimising write-latency for the currently awake users of the app - e.g. imagine 3 different tenants with differing users, tenant a has a team in SYD and LON, tenant b has a team in SFO and NYC, tenant c has a team in ORD. Tenant C is obviously the easy case, app server/primary runs in ORD all the time, but for Tenants A and B, there's clear times of the day where having the primary in one of the locations is going to significantly improve the experience for the users of the app, so I'd like to have a simple layer that for each tenant watches where writes are primarily coming from (e.g. via the Fly-Region header) averages that over some period and then has the app server in whichever of my serving regions is "closest" to that location become primary.

With a handful of tenants configuration at start-up would be OK to manage, but with hundreds/thousands+ of tenants, each with different write patterns, it would be far more optimal to just run a single pool of appservers with common litefs configuration, but be able to hint/trigger a primary handover for a specific DB from within the app as desired.

This is admittedly all a prototype/early idea at the moment, so maybe there's a giant issue/loophole I haven't spotted yet :)

[anderspitman]

Came here to open an issue to request exactly this functionality. Somewhat different use case though.

Imagine a blogging platform with a built-in CMS. You want fast reads globally, but generally writes are going to be coming from a single region close to the author. If I could have a single sqlite db per author, and dynamically select the primary for that db based on where writes are coming from, it would optimize the author's experience, while also replicating globally so reads remain fast.

Note that for a lot of blogging you can just host static pages on a CDN. I'm wanting to build something that supports protocols like ActivityPub (Fediverse), atproto, WebFinger, WebMention, etc which require a server component.