Tom Lane wrote: > August Zajonc <[EMAIL PROTECTED]> writes: >> The key is how lightweight the setup could be, which matters because >> clients are not always willing to pay for a PITR setup. The low overhead >> would mean you'd feel fine about setting guc to 1hr or so. > > This would have exactly the same performance consequences as always > having an hour-old open transaction. I'm afraid that describing it > as "low overhead" is mere wishful thinking: it would cripple vacuuming > of high-update tables and greatly increase the typical load on pg_clog > and pg_subtrans. We already know that pg_subtrans contention can be a > source of context-swap storms, with the size of the window back to > GlobalXmin being the controlling factor for how bad it gets. > > It's possible that this last could be addressed by separating the > concept of "old enough to be vacuumed" from GlobalXmin, but it's > certainly not a trivial thing.
Isn't globalxmin for open transactions? I thought the idea was that everything goes as usual, but you can flip a knob and say that vacuum doesn't vacuum anything more recent then GlobalXmin less x transactions. Then you can look at your transactions per second and get a rough window to work within. Or if there are timestamps on commits, that would switch to a time interval more user friendly. You end up simply delaying when 1hrs worth of transactions gets vacuumed. For folks doing nightly cron job vacuums, not too bad. Autovacuum isn't on by default :) Of course, this will be clumsy if not per database. But the thought might be to take advantage of the flashback data already present under the MVCC model as long as vacuum hasn't hit things (and being willing to stop activity on a database etc). Given that you are delaying a vacuum rather then being more aggressive, and know you can already vacuum up to a more recent transaction xmin, I dunno... Does anything depend (other then performance) on vacuum actually vacuuming as far as it can? - August ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
