Re: PITR, checkpoint, and local relations

Sounds like a win all around; make PITR easier and temp tables faster.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > There is debate on whether the local buffers are even valuable
> > considering the headache they cause in other parts of the system.
>
> More specifically, the issue is that when (if) you commit, the contents
> of the new table now have to be pushed out to shared storage. This is
> moderately annoying in itself (among other things, it implies fsync'ing
> those tables before commit). But the real reason it comes up now is
> that the proposed PITR scheme can't cope gracefully with tables that
> are suddenly there but weren't participating in checkpoints before.
>
> It looks to me like we should stop using local buffers for ordinary
> tables that happen to be in their first transaction of existence.
> But, per Vadim's suggestion, we shouldn't abandon the local buffer
> manager altogether. What we could and should use it for is TEMP tables,
> which have no need to be checkpointed or WAL-logged or fsync'd or
> accessible to other backends *ever*. Also, a temp table can leave
> blocks in local buffers across transactions, which makes local buffers
> considerably more useful than they are now.
>
> If temp tables didn't use the shared bufmgr nor did updates to them get
> WAL-logged, they'd be noticeably more efficient than plain tables, which
> IMHO would be a Good Thing. Such tables would be essentially invisible
> to WAL and PITR (at least their contents would be --- I assume we'd
> still log file creation and deletion). But I can't see anything wrong
> with that.
>
> In short, the proposal runs something like this:
>
> * Regular tables that happen to be in their first transaction of
> existence are not treated differently from any other regular table so
> far as buffer management or WAL or PITR go. (rd_myxactonly either goes
> away or is used for much less than it is now.)
>
> * TEMP tables use the local buffer manager for their entire existence.
> (This probably means adding an "rd_istemp" flag to relcache entries, but
> I can't see anything wrong with that.)
>
> * Local bufmgr semantics are twiddled to reflect this reality --- in
> particular, data in local buffers can be held across transactions, there
> is no end-of-transaction write (much less fsync). A TEMP table that
> isn't too large might never touch disk at all.
>
> * Data operations in TEMP tables do not get WAL-logged, nor do we
> WAL-log page images of local-buffer pages.
>
>
> These changes seem very attractive to me even without regard for making
> the world safer for PITR. I'm willing to volunteer to make them happen,
> if there are no objections.
>
> regards, tom lane
>

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