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
***********************************************************************

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: