Thread: Index size increases after VACUUM FULL

<div dir="ltr">Hi All,<br /><br />    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: <span style="font-family: courier new,monospace;">uk_table-1_url</span>):<br /><br /><span
style="font-family:courier new,monospace;">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;</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">        relname        | pg_size_pretty | pg_size_pretty</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">-----------------------+----------------+----------------</span><brstyle="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> table_1               | 90 MB          | 153
MB</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> url                  | 67 MB          | 101 MB</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> uk_table-1_url        | 63 MB          | 63 MB</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> uk_url_url            |
34MB          | 34 MB</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> link_prefix_pkey     | 16 kB          | 16 kB</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> random_url_seq        | 8192 bytes     | 8192 bytes</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> link_prefix           |
8192bytes     | 32 kB</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">(7rows)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=> vacuum full;</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">WARNING:  skipping
"pg_type"--- only table or database owner can vacuum it</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"><a lot of similar warnings></span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">VACUUM</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">postgres=> select relname, pg_size_pretty(
pg_relation_size(oid ) ), pg_size_pretty( pg_total_relation_size( oid ) ) from pg_class where relnamespace = ( select
oidfrom pg_namespace where nspname = 'web' ) order by pg_relation_size( oid ) desc;</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">        relname        | pg_size_pretty |
pg_size_pretty</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">-----------------------+----------------+----------------</span><brstyle="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> table_1               | 75 MB          | 147
MB</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> uk_table-1_url       | 72 MB          | 72 MB</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> url                   | 67 MB          | 101 MB</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> uk_url_url            |
34MB          | 34 MB</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> link_prefix_pkey     | 16 kB          | 16 kB</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> random_url_seq        | 8192 bytes     | 8192 bytes</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> link_prefix           |
8192bytes     | 32 kB</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">(7rows)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier
new,monospace;">postgres=>                                                      </span><br clear="all" /><br />   
Shouldwe treat this as expected behaviour, or do we dig deeper? There was absolutely no other activity on the database
duringall this.<br /><br />Best regards,<br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail |
hotmail| indiatimes | yahoo }.com<br /><br />EnterpriseDB      <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/><br />Mail sent from my BlackLaptop device<br
/></div>

Gurjeet Singh wrote:
>     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. 

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.

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

Gurjeet Singh wrote:
> On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas <
> heikki.linnakangas@enterprisedb.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?


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

Gurjeet Singh wrote:
> On Tue, Sep 30, 2008 at 4:49 PM, Heikki Linnakangas <
> heikki.linnakangas@enterprisedb.com> wrote:
> 
>> Gurjeet Singh wrote:
>>
>>> On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas <
>>> heikki.linnakangas@enterprisedb.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