Re: WAL write of full pages

Tom Lane wrote:
> Your analysis is missing an important point, which is what happens when
> multiple transactions successively modify the same page.  With a
> sync-the-data-files approach, we'd have to write the data page again for
> each commit.  With WAL, the data page will likely not get written at all
> (until a checkpoint happens).  Instead there will be per-transaction
> writes to the WAL, but the data volume will be less since WAL records
> are generally tuple-sized not page-sized.  There's probably no win for
> large transactions that touch most of the tuples on a given data page,
> but for small transactions it's a win.


Well said.  I had not considered that the granularity of WAL entries was 
different than that of dirtying data pages.

I have no doubt that all of these issues have been hashed out before, 
and I appreciate you sharing the rationale behind the design decisions.

I can't help but wonder if there is a better way for update intensive 
environments, which probably did not play a large role in design decisions.

Since I live it, I know of other shops that use an industrial strength 
RDBMS (Oracle, Sybase, MS SQL, etc.) for batch data processing, not just 
transaction processing.  Often times a large data set comes in, gets 
loaded then churned for a few mintes/hours then spit out, with 
relatively little residual data held in the RDBMS.

Why use an RDBMS for this kind of work?  Because it's 
faster/cheaper/better than any alternative we have seen.

I have a 100 GB Oracle installation, small by most standards, but it has 
well over 1 TB per month flushed through it.

Bulk loads are not a "once in a while" undertaking.

At any rate, thanks again.
Marty