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

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

From: "Eric Comeau" <ecomeau(at)signiant(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Does pgsql database (file) size increases automatically as we put data?
Date: 2009-10-20 12:34:14
Message-ID: hbkao5$vto$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-novice
"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
> 



In response to

pgsql-novice by date

Next:From: Josh KupershmidtDate: 2009-10-21 22:06:44
Subject: Re: Does pgsql database (file) size increases automatically as we put data?
Previous:From: Josh KupershmidtDate: 2009-10-19 14:52:58
Subject: Re: Does pgsql database (file) size increases automatically as we put data?

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