Обсуждение: Database size with postgres 7.2

I'm hoping to find a easy way to find the disk space used by each
database on a given postgres v7.2 server - I've been looking through the
docs and have seen some references to oid2name, but that doesn't really
help my situation.

Any suggestions?
Robert Young

Hi Robert

> I'm hoping to find a easy way to find the disk space used by each
> database on a given postgres v7.2 server - I've been looking through
> the docs and have seen some references to oid2name, but that doesn't
> really help my situation.

This script gives you the used size per object. Unfortunately only for
the actual db. If it also works on 7.2: I do not know (old stuff)

--
-- Amount of space per object used after vacuum
--
\echo
\echo 'Caution: This skript does only print usefull information'
\echo '         if you run VACUUM before!'
\echo
--VACUUM;
SELECT c1.relname AS "tablename", c2.relname AS "indexname",
       c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
  AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tablename, indexname DESC, size_kb;

Does it help?

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.sennhauser@bluewin.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

Oli Sennhauser wrote:

> Hi Robert
>
>> I'm hoping to find a easy way to find the disk space used by each
>> database on a given postgres v7.2 server - I've been looking through
>> the docs and have seen some references to oid2name, but that doesn't
>> really help my situation.
>
>
> This script gives you the used size per object. Unfortunately only for
> the actual db. If it also works on 7.2: I do not know (old stuff)
>
> --
> -- Amount of space per object used after vacuum
> --
> \echo
> \echo 'Caution: This skript does only print usefull information'
> \echo '         if you run VACUUM before!'
> \echo
> --VACUUM;
> SELECT c1.relname AS "tablename", c2.relname AS "indexname",
>       c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
> FROM pg_class c1, pg_class c2, pg_index i
> WHERE c1.oid = i.indrelid
>  AND i.indexrelid = c2.oid
> UNION
> SELECT relname, NULL, relpages * 8, relfilenode
> FROM pg_class
> WHERE relkind = 'r'
> ORDER BY tablename, indexname DESC, size_kb;
>
> Does it help?
>
> Regards Oli
>

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?

I'm trying to write a few queries for a postgres server with a bunch of
users, where each user is in charge of one database: I have two goals:
1, to show the disk usage of each table of a given database, and 2, show
the total disk usage for each database (ie db in the pg_database
table.)  I believe I can basically accomplish 1 with the query below
(although I'm not sure if it accounts for records with the relkind = 'S'):

SELECT tablename, SUM( size_kb )
FROM
( SELECT c1.relname AS "tablename",
c2.relpages * 8 AS "size_kb"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
 AND i.indexrelid = c2.oid
UNION
SELECT relname, relpages * 8
FROM pg_class
WHERE relkind = 'r' ) AS relations
GROUP BY tablename;

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

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

Thanks for the help so far,
Robert Young

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.sennhauser@bluewin.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