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
