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?