Re: Transaction Wraparound Help-Understanding

On Mon, 2020-07-13 at 10:40 -0400, Jerome Talbert wrote:
> I'm trying to understand why autovacuum runs to prevent wraparound on tables that should
> be static and don't change. For example, we have tables, in one of our databases, that
> get created every day. Those tables are then actively updated throughout the day.
> Once a new day begins, a new table is created for that days data. The previous days
> table is no longer changed or updated and used for historical queries only.
>
> So, the way I understand transaction wrap around, I would think the only table needing
> vacuuming to prevent wraparound, would be the active table? But, I also notice the older
> tables getting vacuumed to prevent wrap around as well. Can someone help me understand this?

Every table row that is not frozen contains transaction IDs that determine if it is
visible or not. So even an insert-only static table hast te be vacuumed once,
unless it has been loaded with COPY (FREEZE).

You can do that yourself with VACUUM (FREEZE) or wait for the anti-wraparound vacuum.

Once that has happened, the table will still reveive anti-wraparound vacuum runs,
but these won't do anything.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com