xid wrap / optimize frozen tables?

From: Nils Goroll <slink(at)schokola(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: xid wrap / optimize frozen tables?
Date: 2015-05-23 13:46:33
Message-ID: 556084B9.5020502@schokola.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

as many before, I ran into the issue of a postgresql database (8.4.1)
- committing many transactions
- to huge volume tables (3-figure GB in size)
- running the xid wrap vacuum (to freeze tuples)

where the additional read IO load has negative impact to the extent of the
system becoming unusable.

Besides considering the fact that this can be worked around by exchanging
printed sheets of paper or plastic (hello to .au) for hardware, I'd very much
appreciate answers to these questions:

* have I missed any more recent improvements regarding this problem? My
understanding is that the full scan for unfrozen tuples can be made less likely
(by reducing the number of transactions and tuning the autovac), but that it is
still required. Is this correct?

* A pretty obvious idea seems to be to add special casing for "fully frozen
tables": If we could register the fact that a table is fully frozen (has no new
tuples after the complete-freeze xid), a vacuum would get reduced to just
increasing that "last frozen" xid.

It seems like Alvaro Herrera had implemented a patch along the lines of this
idea but I fail to find any other references to it:
http://grokbase.com/t/postgresql/pgsql-hackers/0666gann3t/how-to-avoid-transaction-id-wrap#200606113hlzxtcuzrcsfwc4pxjimyvwgu

Does anyone have pointers what happened to the patch?

Thanks, Nils

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2015-05-23 14:39:03 Re: xid wrap / optimize frozen tables?
Previous Message Michael Paquier 2015-05-23 08:23:47 Re: Change pg_cancel_*() to ignore current backend