On Wed, Feb 18, 2015 at 4:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> But max_standby_streaming_delay, max_standby_archive_delay and >> hot_standby_feedback are among the most frequent triggers for >> questions and complaints that I/we see. >> > Agreed. > And a really bad one used to be vacuum_defer_cleanup_age, because > of confusing units amongst other things. Which in terms seems > fairly close to Kevins suggestions, unfortunately.
Particularly my initial suggestion, which was to base snapshot "age" it on the number of transaction IDs assigned. Does this look any better to you if it is something that can be set to '20min' or '1h'? Just to restate, that would not automatically cancel the snapshots past that age; it would allow vacuum of any tuples which became "dead" that long ago, and would cause a "snapshot too old" message for any read of a page modified more than that long ago using a snapshot which was older than that.
I like this thought. One of the first things I do in a new Pg environment is setup a cronjob that watches pg_stat_activity and terminates most backends over N minutes in age (about 5x the length of normal work) with an exception for a handful of accounts doing backups and other maintenance operations. This prevents a stuck client from jamming up the database.
Would pg_dump be able to opt-out of such a restriction?
