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

"Josh Kupershmidt" <schmiddy(at)gmail(dot)com> wrote in message
news:4ec1cf760910190752q1256ce8u3eebbdc8a1a4d5cd(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> On Mon, Oct 19, 2009 at 6:33 AM, leela <its_leela(at)yahoo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>