2

We have an application that uses the tenant schema architecture, one database, has multiple schema per "tenant", each tenant is a replica with same tables etc.

Sometimes we don't know the tenant or have to make global stats, in both case we need the aggregate of a table from each tenant.

The solution is a stored procedure that will create a view, the view is generated by looping over the tenant names, querying from it and using an union.

The view's definition ends up being something like : select * from tenant1.table union all ... select * from tenant2.table etc.

The stored procedure is ran every night in case a new tenant is added to update the view's declaration.

First request on the view is always slow (more than 1 minute) but then cache hits and it takes 3 seconds.

Optimizations have been done on the where clauses of the request by adding indexes, as well as union all instead of using union since duplicates are impossible.

I was wondering if there were better ways, performance-wise to do multi-tenant data aggregates ?

Database => tenant1_schema => actors, table2, ...
            tenant2_schema => actors, table2, ...
            ...
            public_schema  => aggregateview

1 Answer 1

1

Create a partitioned table and define the tenant's tables as partitions of that table. Then querying that partitioned table will return data for all tenants.

7
  • Assuming I'd like to have the same current behaviour, which is an extra row with the tenant to differentiate the entities, which partitioning type would you recommend ? Which one favors read performance ? Also, my view uses the public schema, do I have to update all table of each tenant to add "partition of public.myaggregatetable" ? What if I a new tenant gets added ?
    – adaba
    Commented Apr 29, 2022 at 12:25
  • It would be list partitioning (tenant ID). I don't understand the part about the "extra row". Commented Apr 29, 2022 at 12:34
  • how's the performance compared to the view that unions all tables ?
    – adaba
    Commented Apr 29, 2022 at 12:41
  • The performance is identical, because it does the same thing (if you use UNION ALL, not UNION). Commented Apr 29, 2022 at 12:45
  • So the benefit of this, is that using the partitioned table, a query on the partitioned table with a where clause on tenant would be very fast unlike my view using unions since it'll fetch data from unnecessary tenant. My question is when querying the whole data set (all tenant included), would it be faster since there won't be the union slowdown anymore ?
    – adaba
    Commented Apr 29, 2022 at 12:55

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.