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

On Tue, Oct 20, 2009 at 9:07 AM, Eric Comeau <Eric(dot)Comeau(at)signiant(dot)com> 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