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