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>VACUUMpostgres=> 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.comMail sent from my BlackLaptop device
