Thread: measuring disk usage of records
We've had a chance to talk to some of our competitors who have recently gone out of business. One of the major failings that contributed to their collapse was not accurately understanding how much disk space and bandwidth they were using up. Therefore, they often lost money unexpectedly which, as most of you know, is not sustainable. Therefore, we'd like to avoid falling into the same trap and get a good understanding of how much storage space our customer's data is actually using. Here's the problem... A customer's data may be scattered across multiple tables. Some customers have X,000 records in table A, some have Y,000 records in table B and etc. There's no way it can be as simple as saying "database takes X GB for Y customers so usage is X/Y" Does anyone have any suggestions that can help me to get access to this information? Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! matt@followers.net | http://www.followers.net/isp
Use contrib/dbsize, then you'll be able to
select relation_size('<table_name>');
select database_size('<database_name>');
Answers come back in bytes....
If the customer records are mixed with different customer
data in the same tables, it's a bit more complicated.
Matthew Nuzum wrote:
> Therefore, we'd like to avoid falling into the same trap and get a good
> understanding of how much storage space our customer's data is actually
> using.
>
> Here's the problem... A customer's data may be scattered across multiple
> tables. Some customers have X,000 records in table A, some have Y,000
> records in table B and etc. There's no way it can be as simple as saying
> "database takes X GB for Y customers so usage is X/Y"
>
> Does anyone have any suggestions that can help me to get access to this
> information?
--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************
Thanks for the very quick reply... I didn't know about the dbsize stuff, they may help. Unfortunately, the records are mixed together. I may have to use some judicious estimating... I'm just wondering if there's something more scientific. Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by www.followers.net | recomending Elite CMS to your customers! matt@followers.net | http://www.followers.net/isp -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of P.J. "Josh" Rovero Sent: Wednesday, December 31, 2003 4:20 PM To: Matthew Nuzum Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] measuring disk usage of records Use contrib/dbsize, then you'll be able to select relation_size('<table_name>'); select database_size('<database_name>'); Answers come back in bytes.... If the customer records are mixed with different customer data in the same tables, it's a bit more complicated. Matthew Nuzum wrote: > Therefore, we'd like to avoid falling into the same trap and get a good > understanding of how much storage space our customer's data is actually > using. > > Here's the problem... A customer's data may be scattered across multiple > tables. Some customers have X,000 records in table A, some have Y,000 > records in table B and etc. There's no way it can be as simple as saying > "database takes X GB for Y customers so usage is X/Y" > > Does anyone have any suggestions that can help me to get access to this > information? -- P. J. "Josh" Rovero Sonalysts, Inc. Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North Work: (860)326-3671 or 442-4355 Waterford CT 06385 *********************************************************************** ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
> Thanks for the very quick reply... I didn't know about the dbsize stuff, > they may help. Unfortunately, the records are mixed together. If user tables are grouped by schema, you should be able to pull the dbsize data by schema. That raises an interesting question. Can pg be configured so that user tables MUST be in user schemas, i.e., nobody can put tables in the PUBLIC schema? -- Mike Nolan
On Wed, 31 Dec 2003, Mike Nolan wrote: > That raises an interesting question. Can pg be configured so > that user tables MUST be in user schemas, i.e., nobody can put tables > in the PUBLIC schema? Sure, you can simply drop the public schema, or "revoke all on schema public from public"; Kris Jurka
"Matthew Nuzum" <cobalt@bearfruit.org> writes:
> Thanks for the very quick reply... I didn't know about the dbsize stuff,
> they may help. Unfortunately, the records are mixed together.
When measuring the on-disk size of a table, don't forget to include its
indexes and its toast table (and the toast table's index).
If you are using large objects, you'll also need to think sbout charging
for the space occupied by pg_largeobject.
regards, tom lane
If you have a language installed (like pl/pgsql), then dropping the public schema also drops the language. Ouch. Maybe there is a solution to that one though... John Sidney-Woollett Kris Jurka said: > > > On Wed, 31 Dec 2003, Mike Nolan wrote: > >> That raises an interesting question. Can pg be configured so >> that user tables MUST be in user schemas, i.e., nobody can put tables >> in the PUBLIC schema? > > Sure, you can simply drop the public schema, or "revoke all on schema > public from public"; > > Kris Jurka > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >