Обсуждение: Re: Does pgsql database (file) size increases automatically as we put data?
Re: Does pgsql database (file) size increases automatically as we put data?
От
Josh Kupershmidt
Дата:
On Tue, Oct 20, 2009 at 9:07 AM, Eric Comeau <Eric.Comeau@signiant.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