Re: Table partitioning for cloud service?

------ Mensagem original------
De: Ravi Krishna
Data: qui, 21 de mai de 2020 18:50
Para:
Cc:PostgreSQL Mailing Lists;
Assunto:Re: Table partitioning for cloud service?

>
> The database/schema per tenant solution can be tedious when you want to modify something on the structure and you have numerous tenants.
> Therefore I used the "tables with tenant_id" version in a similar situation but with a slight twist. One of the biggest issue of this solution is that if you forget to add the tenant_id to the where clause you are going to reveal one tenant's data to another.
> I came up with the solution that the database user have no privileges for accessing the base tables. Instead of that I generate views for each tenant and they can access their own data in the underlying table through these views. Now if forget to address the right tenant in my client code(it still happens sometimes) and try to directly access the base tables I get a strongly worded reminder from the server.

1. If you have 50 tables and say 100 tenants, we are talking about 5,000 views. I am not sure
whether it is any more elegant than having 100 schemas.
2. In your approach you can do any phased DDL upgrade. It is typical to do rolling upgrades
in a multi tenant databases, starting with least risky tenant.

Be carefull to plan your backup/recovery strategy. How do you plan to recover one customer from backup without interfering with the others?

What will be your disaster recover strategy? Have you considered replication?

Large databases take longer to backup.

Keep schemas in sync can be easily solved.

Regards,

Edson