1

I would like to provide forum hosting as a service, for forum software I'm developing. The data is stored in a PostgreSQL database, in tables with a forum_id column. That is, all forums share the same tables in one single database, and I know to which forum a row belongs, by looking at the forum_id column.

How do I go about estimating how much disk storage space each forum is using?

I cannot simply use the total size of any particular table, since each table contains data for many forums. What I'm thinking about, is to populate a test database with lots of test data, then add 1000 "typical" rows, and measure how much the relevant tables and and indexes grow because of these new rows, and in that way estimate row & index sizes per row. Then, to estimate how much storage a certain forum uses, I could multiply the number of rows for that forum with the average row & index sizes for the database table.

But this feels like an inexact method and I imagine that the actual storage space used could easily be 5x more or less than what my calculations would indicate. — Something I could do to make the above approach more exact, could be to try to take into account e.g. how long each comment that is posted is, and not just the number of comments. I could add 1000 forum comments with 100 characters, and measure the size growth of the comments table. Then add 1000 comments with 10 000 characters, and again measure size growth. And, given this data, try to estimate row size as a function of number of characters per comment, and use that when estimating total size. But this seems like lots of work.

Yet another approach is to ignore the actual on disk database size, and instead count number of characters written, when comments are posted. The total length of all combined comments for a certain forum, would then be considered the database size for that forum. This seems a lot simpler, but I would still have to estimate table sizes as a function of forum sizes, in order to know how much disk would be needed.

How do service providers that give you e.g. 500 MB storage calculate how much storage a tenant has actually used? Assuming they use tables/collections with a tenant ID column (but not dedicated one-per-tenant databases).

  • "Assuming they use tables/collections with a tenant ID column" - I'd be surprised if that's the norm for the case you describe. Potentially look into partitioning (though I'm not familiar with the way Postgres does that). – Mat Feb 7 '15 at 10:19
  • 1
    See the pg_column_size and octet_length functions. You can use pg_column_size on a whole tuple, e.g. select userid, sum(pg_column_size(mytable)) from mytable group by userid – Craig Ringer Feb 7 '15 at 10:28
  • @Mat Doesn't have to be forums. What about GMail — they offer 1G storage space, and hardly one database per user :-) How do they calculate storage space. But that question might be more suited for StackOverflow – KajMagnus Feb 7 '15 at 19:34
  • @KajMagnus: I seriously doubt GMail is powered by a traditional RDBMS. More likely some custom magic on top of BigTable or such. – Mat Feb 7 '15 at 19:52
  • 1
    @KajMagnus - Did you ever find an answer to this? Other users are interested.... – RDFozz Jun 14 '17 at 15:28

Your Answer

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

Browse other questions tagged or ask your own question.