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

Index size increases after VACUUM FULL

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Index size increases after VACUUM FULL
Date: 2008-09-30 08:43:23
Message-ID: 65937bea0809300143n1492bc09hc410b7ef5eb8e42@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi All,

    I noticed something strange today, and thought I should report it. I
vacuumed a database, and as expected, one of the table's size decreased
(other table were VACUUMed individually earlier); but o my astonishment, the
size of the UNIQUE KEY index on one of the columns increased. Here's the
session log ( the table is: table_1, and the index is: uk_table-1_url):

postgres=> select relname, pg_size_pretty( pg_relation_size( oid ) ),
pg_size_pretty( pg_total_relation_size( oid ) ) from pg_class where
relnamespace = ( select oid from pg_namespace where nspname = 'web' ) order
by pg_relation_size( oid ) desc;
        relname        | pg_size_pretty | pg_size_pretty
-----------------------+----------------+----------------
 table_1               | 90 MB          | 153 MB
 url                   | 67 MB          | 101 MB
 uk_table-1_url        | 63 MB          | 63 MB
 uk_url_url            | 34 MB          | 34 MB
 link_prefix_pkey      | 16 kB          | 16 kB
 random_url_seq        | 8192 bytes     | 8192 bytes
 link_prefix           | 8192 bytes     | 32 kB
(7 rows)

postgres=> vacuum full;
WARNING:  skipping "pg_type" --- only table or database owner can vacuum it
<a lot of similar warnings>
VACUUM
postgres=> select relname, pg_size_pretty( pg_relation_size( oid ) ),
pg_size_pretty( pg_total_relation_size( oid ) ) from pg_class where
relnamespace = ( select oid from pg_namespace where nspname = 'web' ) order
by pg_relation_size( oid ) desc;
        relname        | pg_size_pretty | pg_size_pretty
-----------------------+----------------+----------------
 table_1               | 75 MB          | 147 MB
 uk_table-1_url        | 72 MB          | 72 MB
 url                   | 67 MB          | 101 MB
 uk_url_url            | 34 MB          | 34 MB
 link_prefix_pkey      | 16 kB          | 16 kB
 random_url_seq        | 8192 bytes     | 8192 bytes
 link_prefix           | 8192 bytes     | 32 kB
(7 rows)

postgres=>

    Should we treat this as expected behaviour, or do we dig deeper? There
was absolutely no other activity on the database during all this.

Best regards,
-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2008-09-30 09:39:35
Subject: Re: Index size increases after VACUUM FULL
Previous:From: KaiGai KoheiDate: 2008-09-30 08:36:36
Subject: [BUG] pg_ctl restart at the vanilla v8.3.4

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