1

I am using Django and postgres for a multi-tenant webapp. We have around 100 tables and each table has a tenant_id column, identifying the tenant of that particular row. I need a solution that would calculate the total space per tenant. Is there a possibility of doing that? And can it be done dynamically?

For the background: we need that so as to limit per user disc space upto a limit of X GB.

Thank you.

1

Assuming that the average size of a single row is somewhat comparable between tenants, something like the following:

select tenant_id,
       pg_total_relation_size('the_table')::decimal * (count(*)::decimal / (select count(*) from the_table)) as approx_tenant_size
from the_table
group by tenant_id;

That will calculate size per tenant based on the percentage of rows for that tenant.

This will however return completely wrong estimates if you have large text or varchar columns and some tenants only store short values in there and some really large values.

  • While most of the tables have nearly same data, around 7 tables have variable data. One thing that I thought is possible would be select all tables and then find out size per table (inside a loop for all tables). Is it scalable, after each table update should I do a complete search and update the tenant space used data? Or is there a better option? Thanks, Mr. Unnamed Horse!! :) – Sayantan Jun 14 '17 at 13:41
  • 1
    @SayantanGanguly: I don't think that would be "scalable" as the query has to go through all rows in order to be able to do the count(*) and the group by – a_horse_with_no_name Jun 14 '17 at 13:57
  • Eaxctly. How do they do it then (they: all those SaaS out there). Another option that I think I can try doing is store a field: tenant_size and then update that field every time a date entry is done by querying that particular table while filter the result with tenant_id. Is that doable? – Sayantan Jun 14 '17 at 14:10
  • @SayantanGanguly Well the overall ratio of rows per tenant won't change drastically after every insert I guess. So if you calculate that only once per day, you could use that as a factor to the total size. – a_horse_with_no_name Jun 14 '17 at 14:12
  • 1
    @SayantanGanguly: another option would be to use use partitioning to create one partition for each tenant. Then you can simply check the size of each partition. However partitioning still has some annoying limitations (and will have even in Postgres 10) , mainly that you can't have incoming foreign keys or globally unique indexes. – a_horse_with_no_name Jun 14 '17 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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