> An interesting answer, if there needs to be shared data, is for the shared data to go in its own database, and use a Foreign Data Wrapper to have each tenants' database access it <
https://www.postgresql.org/docs/12/postgres-fdw.html>
For my application I went the schema-per-tenant route, but I have a need to have a single login which will work for all tenants you've been given access to. Not all tenants are required to be on the same database host, so I broke that piece out into it's own database and used postgres fdw to make it seem local to each tenant.
So i've got first hand experience with this for the past ~5 years, but this approach has serious tradeoffs. Queries that need to access the remote table can just fall on their face sometimes. You will also need to deal with practically every connection spawning 1-or-more new connections which will stay open taking resources the first time a query is issued that accesses foreign data.
As an optimization I just worked on for my database earlier this week, I decided to logically replicate that table from my main authentication database into a each cluster, and I replaced all references to the FDW for read-only queries to use the logically replicated table. All write queries still hit the FDW as before.
This was acceptable for my use case, and drastically improved performance for some queries where I had previously had to use a CTE to force materialization to get acceptable performance due to the nature of going over the FDW for that data.
It's a very cool tool, just be careful about how it can impact performance if you don't measure for your specific use case.
