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

Re: Index size increases after VACUUM FULL

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
Cc: PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Index size increases after VACUUM FULL
Date: 2008-09-30 15:22:33
Message-ID: 48E24439.70509@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Gurjeet Singh wrote:
> On Tue, Sep 30, 2008 at 4:49 PM, Heikki Linnakangas <
> heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> 
>> Gurjeet Singh wrote:
>>
>>> On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas <
>>> heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>>
>>>> That's normal. VACUUM FULL creates new index pointers for the tuples it
>>>> moves, which can lead to a bigger index. If it bothers, REINDEX will pack
>>>> the indexes tighter again.
>>>>
>>>
>>> That explains it... and yes, REINDEX did bring the index size back to
>>> normal.
>>>
>>> Would it make sense to mention this in docs of VACUUM FULL? Either at
>>>
>>> http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html
>>>
>>> or at
>>>
>>> http://www.postgresql.org/docs/8.3/static/sql-vacuum.html
>>>
>> Yeah, maybe. Want to suggest a wording?
> 
> 
> VACUUM FULL may cause a noticeable increase in size of the indexes of the
> tables that are vacuumed; this is because the VACUUM operation makes new
> entries in the index for the tuples/rows that have just been moved.
> 
> OR
> 
> VACUUM FULL may cause a noticeable increase in size of the indexes, that are
> on the  tables being vacuumed; this is because the VACUUM operation makes
> new entries in the index for the tuples/rows that have just been moved.
> 
> Followed By:
> 
> An appropriate REINDEX command (REINDEX database|table|index ) can reduce
> the size of such indexes.
> 
> 
>     I think it makes sense to put this on both the above mentioned URLs.

Looking closer, we do already have this in the 8.4devel version of the docs:

http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html#VACUUM-BASICS

"... Another disadvantage of VACUUM FULL is that while it reduces table 
size, it does not reduce index size proportionally; in fact it can make 
indexes larger."

and in the next section:

"... Also, moving a row requires transiently making duplicate index 
entries for it (the entry pointing to its new location must be made 
before the old entry can be removed); so moving a lot of rows this way 
causes severe index bloat. "

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2008-09-30 16:59:41
Subject: Re: Function management in PG
Previous:From: Dave PageDate: 2008-09-30 14:00:51
Subject: Re: FSM rewrite committed, loose ends

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