Do huge number of schema hurt my PostgreSQL performance?

Question

This is a question about how number of schema impacts the performance of a DB cluster.

Let's say I run a PostgreSQL cluster, and inside that cluster: I have 1 databases, and each database has thousands schema (I create one schema for each of my app tenant), and each schema contains the same 8 tables (say, users, orders ... invoice)

I know this is NOT a typical multiple tenant application design at all -- in this way, I am using schema as tenant isolation; the alternative (probably normal way) would be I have just 8 tables in a single schema and have tenant ID designed into the table keys.

My question is whether this design make my database server performance awful (e.g., can potentially blow up my memory, CPU consumption...) and why. If the answer is yet, then is there any back of envelope formula for me to project my memory consumption?

EDIT: Looks like @Neil McGuigan gave the following statement from performance perspective in 2015

"The most common use case for using multiple schemas in a database is building a software-as-a-service application wherein each customer has their own schema. While this technique seems compelling, we strongly recommend against it as it has caused numerous cases of operational problems. For instance, even a moderate number of schemas (> 50) can severely impact the performance of Heroku’s database snapshots tool" devcenter.heroku.com/articles/heroku-postgresql – Neil McGuigan

Question: Is that statement still a valid one in 2023?

Answer 1

The biggest problem is that some metadata queries might become slower, but with a few thousand tables you are probably still OK. You might need way more autovacuum_max_workers.

Consider using a data model that uses row level security to implement multi-tenancy.