Re: Does pgsql database (file) size increases automatically as we put data?

"Josh Kupershmidt" <schmiddy@gmail.com> wrote in message
news:4ec1cf760910190752q1256ce8u3eebbdc8a1a4d5cd@mail.gmail.com...
> On Mon, Oct 19, 2009 at 6:33 AM, leela <its_leela@yahoo.com> wrote:
>>
>> I have a basic query about the FILE SIZE of database file..
>>
>> Does pgsql database (file) size increases automatically as we put data or
>> are there any things specific which we do during installation that
>> restricts file size, etc?
>
> Here's a query that will show how much disk space is being used by the
> tables and indexes in a given schema. Just substitute your schema name
> for 'your_schema_name'.
>
> select relname, pg_size_pretty( pg_relation_size( oid ) ) AS size_alone,
>  pg_size_pretty( pg_total_relation_size( oid ) ) AS
> total_size_incl_indexes
> from pg_class where relnamespace =
>  ( select oid from pg_namespace where nspname = 'your_schema_name')
> order by pg_relation_size( oid ) desc;

Thanks for this query, I ran it on one of our QA servers and the results
were interesting when I compare the table size to the primary-key size..

                relname                 | size_alone |
total_size_incl_indexes
----------------------------------------+------------+-------------------------
 job_run_stat_interval                  | 329 MB     | 603 MB
 job_run_stat_interval_idx              | 274 MB     | 274 MB
 job_run_stat_pkey                      | 155 MB     | 155 MB
 job_run_stat                           | 67 MB      | 222 MB



>
> You might also want to keep an eye on how much disk space is being
> consumed by your PGDATA directory if you're worried about running out
> of space.
>
> Josh
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>