On Apr 22, 2008, at 8:35 AM, Alvaro Herrera wrote:
> Roberts, Jon wrote:
>
>>>> -With autovacuum, does it skip these rows still being referenced
>>>> in a transaction or does it wait?
>>>
>>> It skips them, the idea being that a future vacuum will remove them.
>>
>> Awesome. In a large data warehouse, the snapshot too old error is
>> very
>> annoying and I'm glad PostgreSQL is superior to Oracle in this
>> regard.
>> :)
>
> Well, the disadvantage of the PostgreSQL way is that it keeps dead
> rows
> around for longer than they're actually needed, and so it causes some
> problems in pathological conditions -- for example when setting up
> large
> replication sets with Slony, or during a pg_dump, no dead rows can be
> removed. Since the Slony thing can take a very long time, dead rows
> start to pile up in a way that can really harm performance.
In addition or rather, another potential issue, if you have a REALLY
long transaction running then you can risk transaction id wraparound.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com