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