Re: pg_xlog is getting bigger

On 12/19/2012 04:55 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
>> On 12/19/2012 04:12 PM, Tom Lane wrote:
>>> Idle transactions shouldn't have anything to do with pg_xlog bloat.
>>> What causes xlog bloat is inability to release old WAL because either
>>> (a) we're not able to complete checkpoints, or (b) WAL archiving is
>>> enabled but malfunctioning, and the old WAL segments are being kept
>>> pending successful archiving.
>
>> Its obvious I am missing something important about WAL.
>> Scenario:
>> 1) Transaction is opened and say many UPDATEs are done.
>> 2) This means there is now an old tuple and a new tuple for the previous
>> row.
>> 3) The transaction is not committed.
>
>> I assumed the WAL logs contained information necessary to either go
>> forward to the new on commit or go back to the old on rollback.
>
> No, you're thinking about an Oracle-style WAL implementation. We don't
> do rollback; both tuple versions are kept around until VACUUM decides it
> can reclaim one or the other. WAL only need be kept back to the latest
> checkpoint, even if there are open transactions that are much older.

So basically the WAL mechanism just records the raw data, including
transaction information, to the log segments and leaves the cleanup to
VACUUM. So uncommitted transactions can lead to table bloat not WAL bloat.

>
> regards, tom lane
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com