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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > What bothers me about this is that we have the original page with the
> > old data. It would be nice if we could write the new page in a
> > different location, make the new page active and recycle the old page at
> > some later time.
>
> I don't see how that reduces the total amount of disk traffic?
>
> It's also kind of unclear how to do it without doubling (or worse) the
> amount of table space used in many common scenarios. I doubt many
> people will be happy if "DELETE FROM foo" requires transient space equal
> to twice the original size of foo.

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. Sounds a little like
VACUUM, but for pages.

With DELETE FROM foo, let's suppose you have 10 pages in the table. To
modify page 1, you write to page 11, then record in WAL that page 1 is
inactive. To write page 2, you write to page 1 and record page 2 as
inactive, etc. You basically are writing your new data one behind.

One problem I see is that you don't really know the pages are on disk so
I am not sure how to be safe when over-writing the inactive pages.

Of course, I am just throwing out ideas, looking for a solution. Help!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026