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

>