[jelder]

We did this with MotherDuck, and without introducing a new language. Every tenant has their own isolated storage and compute, so it’s trivial to grant internal users access to specific tenants as needed. DuckDB’s SQL dialect is mostly just Postgres’ with some nice ergonomic additions and a host of extra functionality.

[raw_anon_1111]

This is explicitly not the problem they are trying to solve. In a single tenant database you don’t have to by definition worry about multi tenant databases

[DangitBobby]

I guess the question then becomes, what problem does a multi-tenancy setup solve that an isolated database setup doesn't? Are they really not solving the same problem for a user perspective, or is it only from their own engineering perspective? And how do those decisions ultimately impact the product they can surface to users?

[raw_anon_1111]

Off the top of my head, managing 100 different database instances takes a lot more work from the business standpoint than managing 1 database with 100 users.

The article also mentioned that they isolate by project_id. That implies one customer (assume a business) can isolate permissions more granulary.

[mattaitken]

Yes it’s exactly this. There’s not a neat permission boundary when you have users, orgs, projects, environments. Let alone when you add RBAC too.

[steveBK123]

With multi-tenant vs multi-database decision one driver would be the level of legal/compliance/risk/cost/resource drivers around how segregated users really are.

Multi-database is more expensive generally but is a more brain dead guaranteed way to ensure the users are properly segregated, resilient across cloud/database/etc software releases that may regress something in a multi-tenant setup.

Multi-tenant you always run the risk of a software update, misconfiguration or operational error exposing existence of other users / their metadata / their data / their usage / etc. You also have a lot more of a challenge engineering for resource contention.

[zie]

Agreed, in Multi-tenant, where the user/customer owns the data, I always reach for SQLite first. Each user/customer gets their own SQLite DB. Then you have a common PG/SQLite DB for any common metadata, billing, etc.

That way when a customer leaves or they want a backup copy of their data, it's a rm <customer>.sqlite3 or .backup away.

Sometimes you can't do that for various (almost always non-technical) reason(s), but it's always my 1st choice.

[mattaitken]

In a system with organizations, projects and advanced user access permissions having separate databases doesn’t full solve the problem. You still need access control inside each tenanted database. It also makes cross-cutting queries impossible which means users can’t query across all their orgs for example.

The DSL approach has other advantages too: like rewriting queries to not expose underlying tables, doing automatic performance optimizations…