Thread

TOAST - why separate visibility map

Virender Singla <virender.cse@gmail.com> — 2021-11-19T18:45:40Z

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.

Re: TOAST - why separate visibility map

Virender Singla <virender.cse@gmail.com> — 2021-11-19T19:48:58Z

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

Re: TOAST - why separate visibility map

Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-19T20:31:00Z

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

Re: TOAST - why separate visibility map

Andres Freund <andres@anarazel.de> — 2021-11-20T06:29:10Z

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 main table, 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 toast tables would be bad.

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: TOAST - why separate visibility map

Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-20T16:16:22Z

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

Re: TOAST - why separate visibility map

Virender Singla <virender.cse@gmail.com> — 2021-11-25T14:22:01Z

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