Message-ID: <522A3442.3020507@nasby.net>
Date: Fri, 06 Sep 2013 15:00:02 -0500
From: Jim Nasby <jim@nasby.net>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: Gavin Flower <GavinFlower@archidevsys.co.nz>
CC: Robert Haas <robertmhaas@gmail.com>, Bruce Momjian <bruce@momjian.us>,
 Peter Geoghegan <peter@2ndquadrant.com>,
 Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
References: <50C8F1EC.9010801@dunslane.net> <15321.1355347953@sss.pgh.pa.us>
 <50C90154.8060103@dunslane.net> <50C904DB.9090305@dunslane.net>
 <20121213024837.GB22363@momjian.us> <23298.1355370698@sss.pgh.pa.us>
 <CAEYLb_U1dTg=bXkDL=yvFTrMw5oC13ivgZA=jdQXnbgaDJ+e-Q@mail.gmail.com>
 <20130904205655.GR21874@momjian.us> <20130906001437.GA29264@momjian.us>
 <CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
 <52292FFC.8020708@archidevsys.co.nz>
In-Reply-To: <52292FFC.8020708@archidevsys.co.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 9/5/13 8:29 PM, Gavin Flower wrote:
> How about a 'VACUUM AFTER' command (part of the 'BEGIN' transaction syntax?) that would:
>
>  1. only be valid in a transaction
>  2. initiate a vacuum after the current transaction completed
>  3. defer any vacuum triggered due to other criteria
>
> If the transaction was rolled back: then if there was a pending vacuum, due to other reasons, it would then be actioned.
>
> On normal transaction completion, then if there was a pending vacuum it would be combined with the one in the transaction.
>
> Still would need some method of ensuring any pending vacuum was done if the transaction hung, or took too long.

I *really* like the idea of BEGIN VACUUM AFTER, but I suspect it would be of very limited usefulness if it didn't account for currently running transactions.

I'm thinking we add a vacuum_after_xid field somewhere (pg_class), and instead of attempting to vacuum inside the backend at commit time the transaction would set that field to it's XID unless the field already had a newer XID in it.

autovac would then add all tables where vacuum_after_xid < the oldest running transaction to it's priority list.
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers