0

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?

Improve this question
1
  • I noticed from docs: "postgres_fdw establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and re-used for subsequent queries in the same session. However, if multiple user identities (user mappings) are used to access the foreign server, a connection is established for each user mapping." -- does this mean it never closes?
    – pyramation
    Commented May 21, 2020 at 3:07

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • my current implementation uses schemas, but seems that databases would provide more enterprise-grade isolation. For the logical replication, which seems like a good solution, looks like max_replication_slots has a max value of 262143, which means theoretically this would work up into the 100,000s of subscribers...would you think this is more efficient with resources/connections than postgres_fdw? If so, this would work!
    – pyramation
    Commented May 21, 2020 at 20:31
  • You wouldn't have all databases be logical standbys of a single database, but use cascading replication to "fan out": e.g., the original database has 10 standbys, each of them again has 10 standbys and so on. But isolation is just as good with schemas. The advantage of replication would be that you can easily scale horizontally.
    – Laurenz Albe
    Commented May 21, 2020 at 20:53
  • that makes sense! thank you! Yea the schemas are working great right now, logically 1000s of schemas could work and then it's just one connection. I do like the idea of one db per customer for some things, like being able to do a pg_dump and literally the customer can pick up the result and it's simply all theirs. I suppose I need to weigh out if the logical replication overhead is worth it just to have many dbs ;)
    – pyramation
    Commented May 21, 2020 at 21:07
  • Yes, with 1000 schemas you are hitting the linits when things get difficult, so sharding might be a good idea.
    – Laurenz Albe
    Commented May 21, 2020 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.