Re: Feedback on getting rid of VACUUM FULL

On 24 April 2015 at 22:36, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)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.1253147817@sss.pgh.pa.us
> [2] http://postgresql.org/message-id/3631.1253149221@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