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.

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
6
  • 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
    Commented Jun 14, 2017 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
    – user1822
    Commented Jun 14, 2017 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
    Commented Jun 14, 2017 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.
    – user1822
    Commented Jun 14, 2017 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.
    – user1822
    Commented Jun 14, 2017 at 14:14

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.