We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/access.
Each of these customer databases will have some access to a few tables in a root database via foreign data wrappers using the postgres_fdw extension. Also highly probable that we'll have a few triggers on the local tables that are referencing the foreign tables in the root db (I'm not sure how the root database syncs with the databases which could be a scaling issue).
Will we find any unexpected issues if we have 1,000s of databases using postgres_fdw to connect to a root database in the same postgres instance?
Best Answer
As your comment rightly suspects, that would lead to many connections to the shared database, so that you'd probably need a connection pooler in between.
It might be better to put the data into different schemas in a single database. An alternative would be cascading logical replication to keep consistent copies of the shared data in all databases.