Re: TOAST performance (was Re: [GENERAL] Delete Performance)

Tom Lane wrote:
>
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> I don't see how that reduces the total amount of disk traffic?
>
> > Well, right now we write the pre-image to WAL, then write the new page
> > over the old one. In my case, you just write the new, and somewhere
> > record that the old page is no longer active.
>
> The devil is in the details of that last little bit. How is "mark a
> page inactive" cheaper than "mark a tuple dead"? More specifically,
> how do you propose to avoid WAL-logging the page you are going to do
> this marking in? Seems you still end up with a WAL page image for
> something.

Assuming that we WAL with the granularity of disk sector (512b) I think
that restructuring of database heap page (8kb) would be a big win for
delete/update.

The idea is to move metadata (oid,tableoid,xmin,cmin,xmax,cmax,ctid)
to the beginning of heap page to the same space with tuple pointers.
It's easy (<grin>) as all of it is fixed length.
Then a change in metadata like setting xmax for deleted/updated tuple
will dirty only the first disk page and not all of them.

The new structure of ItemId will be (
itemId-pointer nbits
itemId-flags 32-n bits
oid,
tableoid,
xmin,
cmin,
xmax,
cmax,
ctid
)

Assuming that we do account of dirty pages and WAL with the granularity
of database page we may get a big win by just moving to smaller
gramularity.

The win from increasing cranularity was not very big before WAL, as the
database pages are continuous on disk, but will be significant when we
have to log all dirty pages.

------------------
Hannu