Re: Hot Standby query cancellation and Streaming Replication integration

Bruce Momjian wrote:
>     5     Early cleanup of data still visible to the current query's
>           snapshot
>
> #5 could be handled by using vacuum_defer_cleanup_age on the master.
> Why is vacuum_defer_cleanup_age not listed in postgresql.conf?
>   

I noticed that myself and fired off a corrective patch to Simon 
yesterday, he said it was intentional but not sure why that is yet. 
We'll sort that out.

You are correct that my suggestion is targeting primarily #5 on this 
list. There are two problems with the possible solutions using that 
parameter though:

-vacuum_defer_cleanup_age is set in a unit that people cannot be 
expected to work in--transactions ids. The UI is essentially useless, 
and there's no obvious way how to make a better one. The best you can do 
will still be really fragile.

-If you increase vacuum_defer_cleanup_age, it's active all the time. 
You're basically making every single transaction that could be cleaned 
up pay for the fact that a query *might* be running on the standby it 
needs to avoid.

You can think of the idea of passing an xmin back from the standby as 
being like an auto-tuning vacuum_defer_cleanup_age. It's 0 when no 
standby queries are running, but grows in size to match longer ones. And 
you don't have to have to know anything to set it correctly; just toggle 
on the proposed "feedback xid from the standby" feature and you're safe.

Expecting that anyone will ever set vacuum_defer_cleanup_age correctly 
in the field in its current form is pretty unreasonable I think. Since 
there's no timestamp-based memory of past xid activity, it's difficult 
to convert it to that form instead, and I think something in terms of 
time is what people would like to set this in.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us