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

Reply via email to