Re: Make autovacuum sort tables in descending order of xid_age

From: David Fetter <david(at)fetter(dot)org>
To: Mark Dilger <hornschnorter(at)gmail(dot)com>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>, Christophe Pettus <xof(at)thebuild(dot)com>
Subject: Re: Make autovacuum sort tables in descending order of xid_age
Date: 2019-11-30 22:23:15
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Sat, Nov 30, 2019 at 10:04:07AM -0800, Mark Dilger wrote:
> On 11/29/19 2:21 PM, David Fetter wrote:
> > On Fri, Nov 29, 2019 at 07:01:39PM +0100, David Fetter wrote:
> > > Folks,
> > >
> > > Per a suggestion Christophe made, please find attached a patch to
> > > $Subject:
> > >
> > > Apart from carefully fudging with pg_resetwal, and short of running in
> > > production for a few weeks, what would be some good ways to test this?
> >
> > Per discussion on IRC with Sehrope Sarkuni, please find attached a
> > patch with one fewer bug, this one in the repalloc() calls.
> Hello David,
> Here are my initial thoughts.
> Although you appear to be tackling the problem of vacuuming tables
> with older Xids first *per database*,

Yes, that's what's come up for me in production, but lately,
production has consisted of a single active DB maxing out hardware. I
can see how in other situations--multi-tenant, especially--it would
make more sense to sort the DBs first.

> have you considered changing the logic in building and sorting the
> database list in get_database_list and rebuild_database_list? I'm
> just curious what your thoughts might be on this subject.

I hadn't, but now that you mention it, it seems like a reasonable
thing to try.

> As far as sorting the list of tables in an array and then copying
> that array into a linked list, I think there is no need. The
> copying of table_ages into table_oids is followed immediately by
> foreach(cell, table_oids)
> and then table_oids seems not to serve any further purpose. Perhaps
> you can just iterate over table_ages directly and avoid the extra
> copying.

I hadn't looked toward any optimizations in this section, given that
the vacuums in question can take hours or days, but I can see how that
would make the code cleaner, so please find that change attached.

> I have not tested this change, but I may do so later today or perhaps
> on Monday.

Thanks for looking at this!

David Fetter <david(at)fetter(dot)org>
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres:

Attachment Content-Type Size
v3-0001-Autovacuum-tables-in-descending-order-of-age.patch text/x-diff 4.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2019-11-30 23:01:31 Re: Using multiple extended statistics for estimates
Previous Message Mahendra Singh 2019-11-30 21:10:47 Re: [HACKERS] Block level parallel vacuum