Comment by robertclaus

> Letting users create full custom PostgreSQL tables can get complex - do you want to manage tens of thousands of weird custom tables in a PostgreSQL schema somewhere?

Yea, I'd be fine with that - postgres has the concept of databases and schemas within those databases. If you really want to build a product like that I'd suggest starting with per-tenant schemas that leverage table inheritance as appropriate. The permissions would be pretty easy to manage.

Though, in a lot of cases I've actually seen this done every client ends up with a dedicated server (or container - whatever tech you use to do it, something completely isolated from other instances) because user version management ends up being a huge issue. When you're building something that custom it's highly likely that version migrations need to be done with client oversight to ensure everything actually works.

I have yet to find an actual real world case where the inner-platform effect is the right solution. Usually when tools like that are selected the software ends up being so generic and flexible that's it's useless. Custom application/BI environment development relies on really judiciously telling users they can't have most features - with the hard part being figuring out which features are necessary and which ones you can cut to reduce bloat.

Why not use jsonb for this kind of thing? Store the schema in one table, one per client, or perhaps one per table per client, and then store the data for that in another table, segregated by customer and table type, with row data stored in a JSONB field using that table's schema.

I normally don't like using JSONB when I could have a rigorous schema, but this sort of application seems reasonable.

If by solving multinenancy they mean

  CREATE TABLE tenants (
    id     BIGINT NOT NULL,
    database SQLITE DEFAULT execute_sqlite(
        empty_sqlite(),
        'CREATE TABLE users (etc.)'
        and all the other tables
        for each tenant
    )
  );

then they don't need to make joins between sqlite dbs.

Your other concerns are very real. Those sqlite dbs could become very large. I prefer the use case depicted in another reply: preparing sqlite dbs before shipping them to their own devices. Or maybe receiving them and performing analysis, maybe after having imported it in overall psql tables. Or similar scenarios in which all the db is read or written at once. Anyway, once we have a tool we start using it.

Each of the columns is an instance of a SQLite database, so I assume (without looking at the source) that they properly multi-thread as needed.

So there's not cross-SQLite-database connections or multiple writers going on.

You could join over them, but not really in the way you're thinking.

Each of the columns that are databases would be updated when the functions execute.

You could do weird crap like INSERT/DELETE as part of a postgres level SELECT.

Mixmaster Mike, what’cha got say??