Re: Max compact as an FSM strategy

On Tue, Jul 26, 2022 at 3:04 PM Simon Riggs
<simon(dot)riggs(at)enterprisedb(dot)com> wrote:
>
> A long time ago, Tom Lane came up with the idea that when tables get
> bloated, tables might be allowed to shrink down again in size
> naturally by altering the way FSM allocates blocks. That's a very good
> idea, but we didn't implement it back then...
>
> This patch allows the Heap to specify what FreeSpaceStrategy it would
> like to see.
>
> (extract from attached patch...)
> +typedef enum FreeSpaceStrategy
> +{
> + FREESPACE_STRATEGY_MAX_CONCURRENCY = 0,
> + /*
> + * Each time we ask for a new block with freespace this will set
> + * the advancenext flag which increments the next block by one.
> + * The effect of this is to ensure that all backends are given
> + * a separate block, minimizing block contention and thereby
> + * maximising concurrency. This is the default strategy used by
> + * PostgreSQL since at least PostgreSQL 8.4.
> + */
> + FREESPACE_STRATEGY_MAX_COMPACT
> + /*
> + * All backends are given the earliest block in the table with
> + * sufficient freespace for the insert. This could cause block
> + * contention for concurrent inserts, but ensures maximum data
> + * compaction, which will then allow vacuum truncation
> to release
> + * as much space as possible. This strategy may be appropriate
> + * for short periods if a table becomes bloated.
> + */
> +} FreeSpaceStrategy;

I think this is a really interesting idea. So IIUC this patch enables
an option to select between the strategy but don't yet decide on that.

> All we need is a simple heuristic to allow us to choose between
> various strategies.

I think it would be really interesting to see what would be the exact
deciding point between these strategies. Because when we switch from
CONCURRENCY to COMPACT it would immediately affect the insert/update
performance but it would control the bloat. So I am not sure whether
the selection should be completely based on the heuristic or there
should be some GUC parameter where the user can decide at what point
we should switch to the COMPACT strategy or it should not at all
switch?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com