Current freesapce code gives a new block insert target (NBT) from
anywhere in table. That isn't very useful with bigger tables and it
would be useful to be able to specify different algorithms for
producing NBTs.
ALTER TABLE foo WITH (freespace = XXXX);
Three simple and useful models come to mind
* CONCURRENT
This is the standard/current model. Naming it likes this emphasises
why we pick NBTs in the way we do.
* PACK
We want the table to be smaller, so rather than run a VACUUM FULL we
want to force the table to choose an NBT at start of table, even at
the expense of concurrency. By avoiding putting new data at the top of
the table we allow the possibility that VACUUM will shrink table size.
This is same as current except we always reset the FSM pointer to zero
and re-seek from there. This takes some time to have an effect, but is
much less invasive than VACUUM FULL.
* RECENT
For large tables that are append-mostly use case it would be easier to
prefer NBTs from the last two 1GB segments of a table, allowing them
to be more easily cached. This is same as current except when we wrap
we don't go to block 0 we go to first block of penultimate (max - 1)
segment. For tables <= 2 segments this is no change from existing
algorithm. For larger tables it would focus updates/inserts into a
much reduced and yet still large area and allow better cacheing.
These are small patches.
...other possibilities, though more complex are...
* IN-MEMORY
A large table may only have some of its blocks in memory. It would be
useful to force a NBT to be a block already in shared_buffers IFF a
table is above a certain size (use same threshold as seq scans, i.e.
25% of shared_buffers). That may be difficult to achieve in practice,
so not sure about this one. Like it? Any ideas?
We might also allow a custom NBT policy though allowing user code at
that point could be dangerous.
Thoughts?
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services