On 24 April 2015 at 22:36, Jim Nasby <jim.na...@bluetreble.com> wrote:
> Instead of adding forcefsm, I think it would be more useful to accept a > target block number. That way we can actually control where the new tuple > goes. For this particular case we'd presumably go with normal FSM page > selection logic, but someone could chose to to do something more > sophisticated if they wanted. > > [1] http://postgresql.org/message-id/3409.1253147...@sss.pgh.pa.us > [2] http://postgresql.org/message-id/3631.1253149...@sss.pgh.pa.us I don't think specifying exact blocks will help, it will get us in more trouble in the long run. I think we need to be able to specify these update placement strategies * TARGBLOCK_SAME - try to put the update on the same block if possible - default * TARGBLOCK_NEW - always force the update to go on a new block, to shrink table rapidly and these new block selection strategies * FSM_ANY - Any block from FSM - default, as now * FSM_NEAR - A block near the current one to maintain clustering as much as possible - set automatically if table is clustered * FSM_SHRINK - A block as near to block 0 as possible, while still handing out different blocks to each backend by reselecting a block if we experience write contention I would suggest that if VACUUM finds the table is bloated beyond a specific threshold it automatically puts it in FSM_SHRINK mode, and resets it back to FSM_ANY once the bloat has reduced. That will naturally avoid bloat. fsm modes can also be set manually to enforce bloat minimization. We can also design a utility to actively use TARGBLOCK_NEW and FSM_SHRINK to reduce table size without blocking writes. But this is all stuff for 9.6... -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services