Thread: Rollback capacity

Once again, I'm trying to translate my knowledge of Informix to
PostgreSQL. I tried the manual and Google, but could not find anything
relevant.

Informix keeps transaction logs in a dedicated, pre-allocated disk
area that, until very recent versions, could not grow dynamically. It
is the DBA's responsibility to continually backup these transaction
logs so the space may be recycled. As such, Informix is limited in the
size of a transaction that it can roll back, because it eventually has
to re-use existing transaction log space. If it were to overwrite the
log space containing the beginning of the transaction, it could not
rollback from the internal logs. So if you do something crazy, like
delete 4 million rows, there's a good chance Informix will just throw
an error "long transaction aborted" and roll it back when the
transaction reaches a pre-set high water mark.

How does PostgreSQL handle big transactions and potential rollbacks.
Since the WAL is not strictly pre-allocated space, can it just keep
going until the WAL files fill up the free disk space? What would be
the consequences of such an incident (filling up disk space with WAL
files)? Is the WAL even relevant to rollbacks?

I am aware of the statement_timeout parameter which could prevent huge
transactions, but there is no useful correlation between the time a
statement takes and the server's capacity to roll it back.

Thanks,

Jeff

"Jeff Larsen" <jlar310@gmail.com> writes:
> Informix keeps transaction logs in a dedicated, pre-allocated disk
> area that, until very recent versions, could not grow dynamically. It
> is the DBA's responsibility to continually backup these transaction
> logs so the space may be recycled. As such, Informix is limited in the
> size of a transaction that it can roll back, because it eventually has
> to re-use existing transaction log space.

Yeah, Oracle has that problem too.

Postgres keeps the old row versions in the main data area, so the disk
space cost of a long transaction is paid out of your main data store,
not any dedicated area.  Once the transaction is committed or rolled
back, a subsequent VACUUM will eventually make the now-redundant space
available for re-use.

Our way has its pluses and minuses compared to the other, but it's
definitely quite different.  Instead of worrying about transaction
log size, you worry about how often to VACUUM.

> Since the WAL is not strictly pre-allocated space, can it just keep
> going until the WAL files fill up the free disk space? What would be
> the consequences of such an incident (filling up disk space with WAL
> files)? Is the WAL even relevant to rollbacks?

It's not; the amount of WAL space needed is determined only by the
checkpoint spacing.  (You can have transactions that run much longer
than the checkpoint interval.)

If you do run out of space for WAL, the database PANICs and shuts
down (but without losing any committed transactions, so you can restart
once you've cleared off some space).  One advantage to keeping WAL and
main data area on separate partitions is that then bloat of the main
data area cannot lead to a PANIC of this type, though out-of-space
in the data area is still going to lead to failures of inserts and
updates.

            regards, tom lane