Re: Preventing free space from being reused

This is on version 12.5. The usage pattern is inserts only, no updates or
deletes at all. Hence, fillfactor is set to 100.

It just seems extremely unfortunate in this particular case that Postgres
goes to all the trouble of tetris-ing new tuples into existing pages, only
to cripple performance in two different ways (BRIN and disk fragmentation).
In other words, if there is no workaround for this problem, then I believe
that an option to essentially just disable free space maps on a
per-relation basis should be added.

On Fri, Feb 12, 2021 at 4:58 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> What version are you using? What is your usage pattern for
> insert/update/deletes? If sometimes the JSON data gets too big and the data
> is moved from in-line storage to TOASTED, then that would be opening up
> gaps. Or if you are doing deletes. Perhaps adjusting your fillfactor
> and/or TOAST_TUPLE_TARGET would influence that behavior in the direction
> you want to go. As best I understand though if you are doing deletes, you
> won't be able to prevent those gaps from showing up and being re-used. I
> don't believe there is any way to influence Postgres to append-only the
> tuples and pages.
>