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

[leela-3]

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!

[Mike Swierczek]

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

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!

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

[Josh Kupershmidt]

On Mon, Oct 19, 2009 at 6:33 AM, leela <its_leela@...> 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;

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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Eric Comeau-2]

"Josh Kupershmidt" <schmiddy@...> wrote in message
news:4ec1cf760910190752q1256ce8u3eebbdc8a1a4d5cd@......

> On Mon, Oct 19, 2009 at 6:33 AM, leela <its_leela@...> 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@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

[Josh Kupershmidt]

On Tue, Oct 20, 2009 at 9:07 AM, Eric Comeau <Eric.Comeau@...> wrote:

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

If you're puzzled why the primary key index job_run_stat_pkey is using
more space than the table itself without indexes, read here first:
http://www.postgresql.org/docs/current/static/routine-reindex.html

I was able to reproduce your symptom of an index taking up more space
than the table alone in PG 8.3.7 by creating and populating a dummy
table like so. Insert calls to the pg_size_* query in between to watch
what happens to the table and index sizes.

  -- create dummy table with just a single column, in an attempt to
reproduce symptom
  CREATE TABLE nums_table (num serial PRIMARY KEY);
  INSERT INTO nums_table (num) SELECT newnum FROM generate_series(100,
100000) as newnum;

  -- cause some table bloat:
  UPDATE nums_table SET num = num * -1;
  UPDATE nums_table SET num = num * -1;
  UPDATE nums_table SET num = num * -1;
  -- now: table alone = 14 MB, nums_table_pkey alone also = 14 MB

  -- run a VACUUM FULL ANALYZE to get rid of table bloat, but not index bloat
  VACUUM FULL ANALYZE nums_table;

  -- finally, bring index size down to normal:
  REINDEX INDEX "nums_table_pkey";

If you get the same results I got, you should notice that after the
VACUUM FULL ANALYZE, nums_table without indexes takes 3.5 MB, while
the nums_table_pkey by itself takes 14 MB. After issuing REINDEX, the
pkey goes down to 2.2 MB, and the table without indexes goes down to
3.5 MB. YMMV -- different runs produced slightly different numbers for
me, but the overall idea is the same.

 Josh

--
Sent via pgsql-novice mailing list (pgsql-novice@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice