The structure of each schema is identical, the tenant ID is the name of the schema.
You’ve hit the main reason why the scheme you choose is usually avoided. Better to just add tenant_id to your tables in the first place. And use partitioned tables if you desire physical separation.
The above solution seems to work, my questions are:
- Is there a better way to achieve the same functionality? Maybe without using JSON as an intermediate representation?
In-database, I doubt it (though I didn’t study your specific solution in depth). Json provides the easiest way to generate the virtual tables you need.
Otherwise maybe try something with say bash scripting and psql scripts; or some other client-side setup where you separate the query and the metadata lookups so the queries just return normal results and the client takes them are merges them.
David J.
