Re: [PERFORM] encouraging index-only scans

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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net