Re: Preventing free space from being reused

On Fri, Feb 12, 2021 at 6:21 PM Noah Bergbauer <noah(at)statshelix(dot)com> wrote:
>
> A btree index on the same column is 700x the size of BRIN, or 10% of
relation itself. It does not perform significantly better than BRIN. The
issue here is twofold: not only does slotting these tuples into older pages
significantly reduce the effectiveness of BRIN, it also causes
fragmentation on disk. Ultimately, this is why CLUSTER exists. One way to
look at this situation is that my data is inserted exactly in index order,
but Postgres keeps un-clustering it for reasons that are valid in general
(don't waste disk space) but don't apply at all in this case (the file
system uses compression, no space is wasted).
>
> Any alternative ideas would of course be much appreciated! But at the
moment HEAP_INSERT_SKIP_FSM seems like the most practical solution to me.

I would suggest to take a look at the BRIN opclass multi-minmax currently
in development. It's designed to address that exact situation, and more
review would be welcome:

https://commitfest.postgresql.org/32/2523/

--
John Naylor
EDB: http://www.enterprisedb.com