multi tenancy - schema per tenant

[Gilad Moscovitch]

Hi
I have found some old posts and a good blog post (https://influitive.io/our-multi-tenancy-journey-with-postgres-schemas-and-apartment-6ecda151a21f) regarding multi tenancy in postgresql. They all raise a lot of concerns about creating a lot of schemas, and in each schemas, a lot of tables and indices

I was wondering if something has improved recently, in the way postgresql is handling many many tables and indices.

We have 2000 tenants, and each tenant has 200 tables. each table usually has 3-4 indices =>
that leads to 400K tables, and 1.6M indices, if creating a schema per tenant.

The first question - Will pg_catalog suffer from the number of indices, as well as from the number of tables? Meaning, is the cost of adding a table, and adding an index is the same, regarding the number of obejcts in the db.

Second question - Is postgresql saving a file for each table/index? Will this file have an open descriptor as long as the db is up? (That's what happens in MongoDB and it costs in a lot of memory).

Third question - a shared-table model might lead to a very big tables, and we are afraid of maintaining tables of 1 B rows.
So are there any directions of a hybrid model?


Thanks
Gilad