Re: Preventing free space from being reused

> (My reaction to your previous thread was that it was simply a question
> of blindly insisting on using BRIN indexes for a case that they're quite
> badly adapted to. The better answer is to not use BRIN.)

Apologies, perhaps I am completely misunderstanding the motivation for BRIN?

From the docs:
>BRIN is designed for handling very large tables in which certain columns
have some natural correlation with their physical location within the table.
>[...]
>a table storing a store's sale orders might have a date column on which
each order was placed, and most of the time the entries for earlier orders
will appear earlier in the table

My table is very large, and the column in question has a strong natural
correlation with each tuple's physical location. It is, in fact, a date
column where entries with earlier timestamps will appear earlier in the
table. To be honest, if this isn't a use case for BRIN, then I don't know
what is. The only exception to this is a small proportion of tuples which
are slotted into random older pages due to their small size.

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.

On Fri, Feb 12, 2021 at 10:43 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Noah Bergbauer <noah(at)statshelix(dot)com> writes:
> > I am working on a project where I do not want Postgres to reuse free
> space
> > in old pages (see
> >
> https://www.postgresql.org/message-id/flat/CABjy%2BRhbFu_Hs8ZEiOzaPaJSGB9jqFF0gDU5gtwCLiurG3NLjQ%40mail.gmail.com
> > for details). I found that the HEAP_INSERT_SKIP_FSM flag accomplishes
> this.
> > For a long-term solution I see two options:
> > 1. Introduce a reloption for this.
> > 2. Implement it as a custom table access method in an extension.
>
> TBH, I can't believe that this is actually a good idea. If we introduce
> a reloption that does that, we'll just be getting users complaining about
> table bloat ... but probably only after they get to a state where it's
> going to be horribly painful to get out of.
>
> (My reaction to your previous thread was that it was simply a question
> of blindly insisting on using BRIN indexes for a case that they're quite
> badly adapted to. The better answer is to not use BRIN.)
>
> regards, tom lane
>