By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,177 Members | 976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,177 IT Pros & Developers. It's quick & easy.

measuring disk usage of records

P: n/a
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!
ma**@followers.net | http://www.followers.net/isp

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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: ro****@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

Nov 12 '05 #2

P: n/a
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!
ma**@followers.net | http://www.followers.net/isp
-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of P.J. "Josh" Rovero
Sent: Wednesday, December 31, 2003 4:20 PM
To: Matthew Nuzum
Cc: pg***********@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: ro****@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #3

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

P: n/a


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

Nov 12 '05 #5

P: n/a
"Matthew Nuzum" <co****@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.