Thread: TOAST - why separate visibility map
Why do Toast tables have it's own visibility map and xmin, xmax columns etc?
Isn't it increasing row size in a toast table and adding more complexity?
Ideally all the vacuum cleanup on a TOAST can be done based on Primary table xmin,xmax and VM info. Yes, that makes any cleanup on TOAST to be glued up with the Primary table.
Another point that currently manual VACUUM job does cleanup/freeze on primary table first and then toast table. It looks easy pick to possibly have a configurable parameter to run it on both the tables in parallel.
On Sat, Nov 20, 2021 at 12:15 AM Virender Singla <virender.cse@gmail.com> wrote:
Why do Toast tables have it's own visibility map and xmin, xmax columns etc?Isn't it increasing row size in a toast table and adding more complexity?Ideally all the vacuum cleanup on a TOAST can be done based on Primary table xmin,xmax and VM info. Yes, that makes any cleanup on TOAST to be glued up with the Primary table.
Virender Singla <virender.cse@gmail.com> writes:
> Why do Toast tables have it's own visibility map and xmin, xmax columns etc?
> Isn't it increasing row size in a toast table and adding more complexity?
There are advantages to having the same low-level format for toast tables
as regular tables --- for example, that you can look into a toast table
for debugging purposes with normal SQL queries. Even if we weren't tied
to that format for disk-storage-compatibility reasons, I'd be disinclined
to change it.
It might be feasible to drop the visibility map for toast tables, though.
I agree that's not buying much, since ordinary queries don't consult it.
Not sure if there'd be a win proportional to the added code complexity.
regards, tom lane
Hi, On November 19, 2021 12:31:00 PM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Virender Singla <virender.cse@gmail.com> writes: >> Why do Toast tables have it's own visibility map and xmin, xmax columns etc? >> Isn't it increasing row size in a toast table and adding more complexity? Given the size of toasted data, the overhead is unlikely to be a significant overhead. It's much more an issue for the maintable, where narrow rows are common. We don't want to use the main table visibility information for vacuuming either - that'd practically prevent HOT cleanup,and we'd a new expensive way of doing cleanup in toast tables using the main row's visibility information. >There are advantages to having the same low-level format for toast tables >as regular tables --- for example, that you can look into a toast table >for debugging purposes with normal SQL queries. Even if we weren't tied >to that format for disk-storage-compatibility reasons, I'd be disinclined >to change it. > >It might be feasible to drop the visibility map for toast tables, though. >I agree that's not buying much, since ordinary queries don't consult it. >Not sure if there'd be a win proportional to the added code complexity. I think it be a bad idea - the VM is used by vacuum to avoid rereading already vacuumed ranges. Loosing that for large toasttables would be bad. Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Andres Freund <andres@anarazel.de> writes:
> On November 19, 2021 12:31:00 PM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It might be feasible to drop the visibility map for toast tables, though.
> I think it be a bad idea - the VM is used by vacuum to avoid rereading already vacuumed ranges. Loosing that for
largetoast tables would be bad.
Ah, right. I was thinking vacuuming depended on the other map fork,
but of course it needs this one.
In short, there are indeed good reasons why it works like this.
regards, tom lane
"Given the size of toasted data, the overhead is unlikely to be a significant overhead. It's much more an issue for the main table, where narrow rows are common."
Completely agree, row size should not be a big concern for toast tables.
However write amplification will happen with vacuum freeze where transactions id need to freeze in wider toast table tuples as well. I have not explored if TOAST has separate hint bits info as well. In that case it means normal vacuum (or SELECT after WRITE) has to completely rewrite the big toast table tuples along with the small main table to set the hint bits (commit/rollback).
I believe B tree Index does not contain any seperate visibility info so that means the only work VACUUM does on Indexes is cleaning up dead tuples.
With maintaining one visibility info, above operations could be way faster. However now the main table and TOAST vacuuming process will be glued together where optimization can be thought about like two synchronized threads working together for main and TOAST table to do the cleanup job. Agree that hot updates are gone in TOAST if there is a common VM.
Overall this looks complex.
On Sat, Nov 20, 2021 at 9:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andres Freund <andres@anarazel.de> writes:
> On November 19, 2021 12:31:00 PM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It might be feasible to drop the visibility map for toast tables, though.
> I think it be a bad idea - the VM is used by vacuum to avoid rereading already vacuumed ranges. Loosing that for large toast tables would be bad.
Ah, right. I was thinking vacuuming depended on the other map fork,
but of course it needs this one.
In short, there are indeed good reasons why it works like this.
regards, tom lane