Hi! I keep thinking about this. Hannu, have you thought about a mixed approach? In [1] above Michael mentioned the very serious drawback of using direct TIDs - while vacuuming the TOAST table we have to modify the original table as well, so I don't see a way to avoid using some kind of index at all. My direct TOAST patch was just a PoC to show it is possible.
On Thu, Jul 30, 2026 at 10:30 AM Hannu Krosing <[email protected]> wrote: > Re-sending my last reply to the list > > On Thu, Jul 30, 2026 at 8:24 AM Nikita Malakhov <[email protected]> wrote: > > > > Hi Hannu! > > > > Haven't looked at the code yet but want to mention - I've tried 'direct' > approach some time ago [1] > > using list-like representation. Despite giving speedup for relatively > small values and does not having > > TOAST index table at all, the performance degraded over standard TOAST > with large ones, > > For a linked list, this degradation was to be expected , as you can > only start requesting the next page once you have fetched the previous > one. > > This is why I implemented it as tidarray (or N-ary tree of TID arrays > for larger data), which does not have this fundamental problem. > > You get a large span of tids for pages at once, an could be at least > as fast as index scan and possibly faster, as you get the tid array > slightly cheaper > > > and dependency on TIDs have the immediate effect of VACUUM FULL breaking > the overall structure, > > so it should be modified accordingly too. > > Yes, VACUUM FULL of the toast table is one known thing that has to be > modified for this > > OTOH the current behaviour of VACUUM FULL is not even very useful for > fixing heavily out-of-order toast table anyway, as it just compacts > free space but does not do anything about putting the toasted fields > in same order as main heap fields, so your slow queries stay slow even > after VACUUM FULL. > -- Regards, Nikita Malakhov Postgres Professional The Russian Postgres Company https://postgrespro.ru/
