Re: Preventing free space from being reused

>I wonder how hard it would be to hack up a table access method that is
just a copy of heap where HEAP_INSERT_SKIP_FSM is always set...

Update: I think this actually works. It's awful because I just copied the
entire builtin heap code into an extension and then renamed a few functions
so they don't collide during dynamic linking, but after changing the
table's access method to the one from the extension there is no more
overlap!

Before:
{2021-02-12 14:21:24.93711+01 .. 2021-02-12 15:14:28.564695+01}
{2021-02-12 15:10:22.832827+01 .. 2021-02-12 20:45:34.918054+01}
{2021-02-12 15:50:50.768549+01 .. 2021-02-12 20:51:20.487791+01}
{2021-02-12 16:25:01.224667+01 .. 2021-02-12 17:07:31.95343+01}
{2021-02-12 16:51:30.007498+01 .. 2021-02-12 18:15:42.377372+01}
{2021-02-12 17:30:17.943716+01 .. 2021-02-12 18:55:00.030094+01}
{2021-02-12 18:08:39.488203+01 .. 2021-02-12 20:55:00.012211+01}
{2021-02-12 19:05:35.495433+01 .. 2021-02-12 20:20:00.088014+01}

After:
{2021-02-12 20:00:32.61514+01 .. 2021-02-12 20:45:23.617548+01}
{2021-02-12 20:45:23.617548+01 .. 2021-02-12 20:51:05.098479+01}
{2021-02-12 20:51:05.219331+01 .. 2021-02-12 20:57:56.93961+01}
{2021-02-12 20:57:57.000953+01 .. 2021-02-12 21:02:10.245561+01}

On Fri, Feb 12, 2021 at 6:38 PM Noah Bergbauer <noah(at)statshelix(dot)com> wrote:

> I'm experimenting with that right now. Setting storage to MAIN appears to
> be counterproductive, whereas EXTERNAL with toast_tuple_target = 128 is
> definitely an improvement. In theory, this configuration might eliminate
> the problem, but due to the toast_tuple_target bug (
> https://www.postgresql.org/message-id/flat/20190403063759.GF3298%40paquier.xyz)
> plenty of 1kB tuples are still being stored inline. As a result I'm
> averaging around 11.5 tuples per page, when it should be >200 (one tuple is
> 35 bytes when stored out of line). A small test query shows ~7000 tuples
> removed by index recheck, but based on my statistics only ~1500 would be
> expected (in the ideal case where tuple disk order matches insertion order).
>
> On the other hand, wouldn't the toast table still run into the disk
> fragmentation issue? Also, the 4-byte oid may actually become an issue a
> few months down the road.
>
> I wonder how hard it would be to hack up a table access method that is
> just a copy of heap where HEAP_INSERT_SKIP_FSM is always set...
>
> On Fri, Feb 12, 2021 at 5:49 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
>> If you have no updates or deletes, then I would wonder about setting
>> fillfactor LOWER such that new rows are less likely to find a gap that is
>> acceptable. Depending how/when you use the json, lowering
>> toast_tuple_target may be appropriate to store (nearly?) all out of line
>> and making the data stored in the main relation be more uniform in size.
>> Are you seeing significant toast usage currently?
>>
>