>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 <n...@statshelix.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 <mle...@entrata.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? >> >