From: Jeffrey W. Baker <jwbaker@acm.org>
To: Alan Stange <stange@rentec.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: unused item pointers?
Date: Mon, 22 Aug 2005 22:15:08 -0700
Message-ID: <1124774108.6978.7.camel@noodles> (raw)
In-Reply-To: <430A8F30.7050706@rentec.com>
References: <430A8F30.7050706@rentec.com>
On Mon, 2005-08-22 at 22:51 -0400, Alan Stange wrote:
> Hello all,
>
> what are unused item pointers and how do I get rid of them?
>
> We have a fairly large table which is vacuumed daily and reindexed every
> weekend.
> as you can see we have 235M unused item pointers in the main table and a
> few 10's of millions more in other associated tables.
>
> Please note that the advice "vacuum more often" is a non-starter as the
> total time here is already about 3 hours and this is just one table.
> This is a fairly active table to which about 20M rows are added and
> removed daily.
That may be so, but the answer is still to VACUUM more often. Try the
autovacuum. If it takes 3 hours with 90% wasted records, it would only
take 20 minutes when running properly.
You might be able to change your application to avoid generating so many
dead rows. For example, test before insert so you don't make a dead
tuple on duplicate insert.
To repair this table, you can try VACUUM FULL but this is likely to take
longer than you find reasonable. I would recommend dump and reload.
-jwb
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-performance@postgresql.org
Cc: jwbaker@acm.org, stange@rentec.com
Subject: Re: unused item pointers?
In-Reply-To: <1124774108.6978.7.camel@noodles>
* 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