Re: Preventing free space from being reused

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?
>