Index size increases after VACUUM FULL
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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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
On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas <
heikki.linnakangas@enterprisedb.com> wrote:
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.
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
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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
Yeah, maybe. Want to suggest a wording?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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
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.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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
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