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@xxxxxxxxxxx> 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.
