Re: Plans for solving the VACUUM problem

Tom Lane wrote:

> 
> Hm.  On the other hand, relying on WAL for undo means you cannot drop
> old WAL segments that contain records for any open transaction.  We've
> already seen several complaints that the WAL logs grow unmanageably huge
> when there is a long-running transaction, and I think we'll see a lot
> more.
> 
> It would be nicer if we could drop WAL records after a checkpoint or two,
> even in the presence of long-running transactions.  We could do that if
> we were only relying on them for crash recovery and not for UNDO.

In Oracle the REDO and UNDO are separate.  REDO in oracle is limited in 
size to x log files y big (where x and y are parameters at database 
creation).  These x log files are reused in a round robin way with 
checkpoints forced when wrapping around.

REDO in oracle is done by something known as a 'rollback segment'.  
There are many rollback segments.  A transaction is assigned one 
rollback segment to write its UNDO records to.  Transactions are 
assigned to a rollback segment randomly (or you can explicitly assign a 
transaction to a particular rollback segment if you want).  The size of 
rollback segments are limited and transactions are aborted if they 
exceed that size.  This is why oracle has the option to assign a 
specific rollback segment to a transaction, so if you know you are going 
to update/insert tons of stuff you want to use an appropriately sized 
rollback segment for that transaction.

Things I like about the Oracle approach vs current WAL REDO and proposed 
UNDO:

1 Sizes allocated to REDO and UNDO in Oracle are known and 
configurable.  WAL sizes are unknown and not constrained.
2 Oracle allows for big transactions by allowing them to use 
specifically sizied rollback segments to handle the large transaction.
While WAL could be enhanced to fix 1, it appears difficult to have 
different limits for different types of transactions as Oracle supports 
in 2.

Thinks I don't like about the Oracle approach:

1 Not only updates, but also long running queries can be aborted if the 
rollback segment size it too small, as the undo is necessary to create 
an snapshot of the state of the database at the time the query started.

thanks,
--Barry

>