[HACKERS] 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] Index size increases after VACUUM FULL
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index size increases after VACUUM FULL
On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas [EMAIL PROTECTED] 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] Index size increases after VACUUM FULL
Gurjeet Singh wrote: On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index size increases after VACUUM FULL
On Tue, Sep 30, 2008 at 4:49 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gurjeet Singh wrote: On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas [EMAIL PROTECTED] 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. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] Index size increases after VACUUM FULL
Gurjeet Singh wrote: On Tue, Sep 30, 2008 at 4:49 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gurjeet Singh wrote: On Tue, Sep 30, 2008 at 3:09 PM, Heikki Linnakangas [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
