From: Kristian Eide <kreide@online.no>
To: Ken Corey <ken@kencorey.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: VACUUM not doing its job?
Date: Sun, 4 Aug 2002 00:40:57 +0200
Message-ID: <037901c23b3e$d542a140$6b97f181@speed> (raw)
References: <035d01c23b2f$c5d49420$6b97f181@speed>
<1028412884.28635.22.camel@kenlinux.bithub.org>
> In my case, it was the fact that indexes don't release the space of
> indexes of deleted rows. So, if you have a table that has a lot of
> inserts/deletes, your indexes will grow incredibly fast.
>
> The way to see what your biggest items are:
> select * from pg_class order by relpages desc;
Yes, I already suspected this could be at least part of the reason, and your
SQL query confirms it. However, dropping and re-creating my biggest indexes
only reclaims about 500MB, this still leaves about 1GB unaccounted for and I
can't see how my remaining (small) indexes can be responsible for this (btw:
do you know how much diskspace one 'relpage' use?).
Given that I have lots of deletes/inserts, is there anything besides the
indexes which could use this much space?
> The only way that I know to recover this space is to drop the indexes
> and recreate them. Vacuum didn't touch them for me.
This is not good as the database is in use 24/7, and without the indexes
everything comes to a screeching halt. This means I probably will have to
stop the programs using the database for the time it takes to re-create the
indexes; this is better than having to dump/restore everything however :)
Are there any plans to also vacuum the indexes in a future version of
Postgre (otherwise an excellent piece of software!) ?
Regards,
Kristian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kreide@online.no, ken@kencorey.com
Subject: Re: VACUUM not doing its job?
In-Reply-To: <037901c23b3e$d542a140$6b97f181@speed>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox