Thread: Tables with lots of dead tuples despite autovacuum

Tables with lots of dead tuples despite autovacuum

From
Wells Oliver
Date:
We have a few tables that are updated nightly on the order of deleting and inserting 500k records.

I assumed autovacuum would do its thing and clean up the dead tuples, but in looking at pg_stat_user_tables, I notice there are lots and lots of dead tuples, on the order of a 500-600k.

What can I do about this? Why isn't autovacuum cleaning these tables? Is this number of dead tuples acceptable?

Lastly, would it make sense to do a weekly full manual vacuum + analyze?

Thanks.

--
Wells Oliver
wellsoliver@gmail.com

Re: Tables with lots of dead tuples despite autovacuum

From
John R Pierce
Date:
On 09/12/12 5:00 PM, Wells Oliver wrote:
> We have a few tables that are updated nightly on the order
> of deleting and inserting 500k records.
>
> I assumed autovacuum would do its thing and clean up the dead tuples,
> but in looking at pg_stat_user_tables, I notice there are lots and
> lots of dead tuples, on the order of a 500-600k.
>
> What can I do about this? Why isn't autovacuum cleaning these tables?
> Is this number of dead tuples acceptable?
>
> Lastly, would it make sense to do a weekly full manual vacuum + analyze?
>

how old is the oldest transaction in pg_stat_activity ?   vacuum can't
free any tuples newer than this.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast



Re: Tables with lots of dead tuples despite autovacuum

From
Jeff Janes
Date:
On Wed, Sep 12, 2012 at 5:00 PM, Wells Oliver <wellsoliver@gmail.com> wrote:
> We have a few tables that are updated nightly on the order of deleting and
> inserting 500k records.

Out of how many in total?

> I assumed autovacuum would do its thing and clean up the dead tuples, but in
> looking at pg_stat_user_tables, I notice there are lots and lots of dead
> tuples, on the order of a 500-600k.

That is one day's worth.  If the tables undergo churn once a day, and
get vacuum once a day, that seems like a nice steady state.  I
wouldn't worry about it.

Cheers,

Jeff