Re: We want to monitor total size of database

Thank you for a reply.

And I am sorry as explanation was lacking.

> > We understand that the following is acquirable in
> > pg_total_relation_size().
> > *Total Size of database
> >
>
> no, this will give you the size of a table and all its indexes and toast data...
>
> > However, now, the following is unacquirable.
> > *Size of used space of the entire database
>
> pg_database_size(name)
>
> or you mean the size of all databases?
>
> select sum(pg_database_size(datname)) from pg_database;
>
> > *Size of free space of the entire database
>
> free space? all available in disk
>
> > *Size of fragmentation area of the entire database
> >
>
> VACUUM FULL is used to avoid fragmentation as much as possible... so i
> suppose there is a way to know that...
>

We understand that the size of the whole database is acquirable
by carrying out pg_total_relation_size() to all the tables in a database.

For example, in pgstattuple(), a result is outputted as follows.
*Size of the table
*Used space by the table
*Free space of the table
*Fragmentation area of the table

testDB=# select * from pgstattuple('test1');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
49152 | 9 | 4072 | 8.28 | 8 | 3240| 6.59 | 41244 | 83.91
(1 row)

Although only the size of a database is acquirable in pg_database_size(),
since we want to know used space by the database, free space of the
database, and fragmentation area of the database, we use pgstattuple(),
and are totaling and using the result.

However, neither space of the index nor the toasted space
is included in the result of pgstattuple().

We want to know whether there is any method of acquiring the same
information as pgstattuple() also including space of the index
or the toasted space to the result in V8.1.0.

Thanks.