measuring disk usage of records

[Matthew Nuzum]

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

[P.J. \Josh\ Rovero]

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: [phone] or 442-4355 Waterford CT 06385
************************************************** *********************
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Matthew Nuzum]

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: [phone] 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

[Mike Nolan]

> 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

[Kris Jurka]

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

[Tom Lane]

"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

[John Sidney-Woollett]

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