1. 11

  2. 9

    This is the shortest and most informative piece I’ve read on PostgreSQL vacuuming. I only have one complaint—the author states:

    When a database has processed more transactions than that, the transaction ID overflows, starting at 0 again. If no further action is undertaken, nearly all rows will suddenly become invisible, because they have positive transaction IDs.

    This misleads the reader to believe that every row in the database must have an xmin of FrozenTransactionId in order to be seen once the transaction ID wraps around. The relevant PostgreSQL docs correctly explain how this works:

    Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there are two billion XIDs that are “older” and two billion that are “newer”; another way to say it is that the normal XID space is circular with no endpoint. […] If the row version still exists after more than two billion transactions, it will suddenly appear to be in the future.