Skip site navigation (1) Skip section navigation (2)

Re: Database size with postgres 7.2

From: Oli Sennhauser <oli(dot)sennhauser(at)bluewin(dot)ch>
To: Robert <rjyoung(at)scs(dot)carleton(dot)ca>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Database size with postgres 7.2
Date: 2004-01-09 09:27:21
Message-ID: 3FFE73F9.80208@bluewin.ch (view raw or flat)
Thread:
Lists: pgsql-admin
Hi Robert

> Thanks for the good start, it worked without a hitch in 7.2.  :)  So 
> that query lists the size (in kilobytes?) all of the relations and the 
> index's of the relations as well?

As you can see in the result header it gives the size in kb. Caution 1: 
The script is written a little dirty. I just multiply??? blocksize with 
8. Because standard blocksize is actually 8 kb. In future or probably 
depending of your OS it can give some wrong values if blocksize is not 
8kb anymore or only. I do not (yet) know how to find out actual block 
size dynamically.
Caution 2: As you can see in the comment you have to VACUUM the database 
before running the script. Otherwise the values are completely wrong!!!

Why for each user a separate db? Why do you not work with schemas? In my 
opinion it eases maintenance (and also uses less ressources)

> 1, to show the disk usage of each table of a given database

Hmmm, if this makes sense to only show the space of tables? Typically 
user asked me the total amount for a user/schema or an application, etc. 
and not for tables only.

> 2, show the total disk usage for each database (ie db in the 
> pg_database table.)

select sum(relpages)*8 AS "size_kb" from pg_class;

> The only problem is, this shows all of the pg_* tables in each 
> database - is there any way to not show these relations?

Caution: I did not clear what happens with "global objects". See:


  Chapter 43. System Catalogs

... A few catalogs are physically shared across all databases in a 
cluster; these are marked in the descriptions of the individual catalogs.

In my opinion pg_class and all dependant views etc are per db and not 
per cluster. An so I do NOT see a simple solution to show something like:

select dbname, sum(replpages)*8 from pg_databases, pg_class, where 
pg_database.oid = pg_class_db...

But I might be wrong.

> The second problem looks like it's going to be more difficult: I think 
> I'm going to have to open a connection to each DB seperatly to poll 
> for the sizes of the relations within each, again I'm going to run 
> into the same problem of needing to only count up the space taken up 
> by the relations in the current database, and not the extra pg_* ones.
> Is there any way to determine which records in pg_class are related to 
> a pg_database record?  Hmm...  Looks like I could use a ERD of the 
> postgres system tables...

If you find something to this point I would be verry interested in.

Regards Oli

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli(dot)sennhauser(at)bluewin(dot)ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

In response to

pgsql-admin by date

Next:From: Сизых Павел ЛеонидовичDate: 2004-01-09 10:02:44
Subject: Re: problems installing postgres7.2 in redhat 9 fedara1
Previous:From: Michael StenitzerDate: 2004-01-09 08:19:16
Subject: cannot configure postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group