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

Hi All,  Iam Leela from India. I am new to using PGSQL.. 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? Thanks & Regards, Leela

---

From cricket scores to your friends. Try the Yahoo! India Homepage!

On Mon, Oct 19, 2009 at 6:33 AM, leela <its_leela@yahoo.com> wrote:

Leela,
File size increases automatically.  

However, not every SQL insert or SQL update will make a file larger, and not every SQL delete will make it smaller.  As data in the file becomes obsolete because of a delete or update, that section of the file is marked unused.  Later, when new data is inserted into the file, the new data can be appended to the end of the file or inserted into one of the places that contained obsolete data. 

-Mike

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
duringinstallation 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;

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

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