From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Natalie Wenz <nataliewenz@ebureau.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Kevin Grittner <kgrittn@gmail.com>
Subject: Re: Database 'template1' vacuum
Date: Mon, 08 Aug 2016 12:45:10 -0400
Message-ID: <7326.1470674710@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwZK7jtxLDGam0uWvC3L2XpY=r3TMTMUSg_KxGd+d1yLqQ@mail.gmail.com>
References: <95F2FD17-7813-4718-B99F-165B177EA6E9@ebureau.com>
<CACjxUsNKtvt86HPotr=2PJQAhSMbRQVaR+8+uwH2UJ2=qj4ABA@mail.gmail.com>
<DDFF9509-FB63-49C4-AAC8-F5813DD8B00E@ebureau.com>
<CACjxUsMUetfqxGvdbQdXL_k_RMtYOAJ0fWo2KRqUSJyDA2-psw@mail.gmail.com>
<0FF1E841-EC1C-473A-8A43-E696D8F247A2@ebureau.com>
<CAKFQuwZK7jtxLDGam0uWvC3L2XpY=r3TMTMUSg_KxGd+d1yLqQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 8, 2016 at 10:43 AM, Natalie Wenz <nataliewenz@ebureau.com>
> wrote:
>> How are static tables handled? Once every row in a table is marked as
>> frozen, and there are no further inserts, deletes, updates, etc to the
>> table, does the vacuum have to do anything to that table when advancing the
>> relfrozenxid? Is there a way that the vacuum can see "Oh, this whole table
>> is frozen, my work here is done!" or does it still have to scan every page
>> (or worse, every row)?
> Seems so:
> https://wiki.postgresql.org/wiki/VacuumHeadaches
FWIW, this is due to get better in 9.6. Per release notes:
Avoid re-vacuuming pages containing only frozen tuples (Masahiko
Sawada, Robert Haas, Andres Freund)
Formerly, an anti-wraparound vacuum had to visit every page of a
table whether or not there was anything to do there. Now, pages
containing only already-frozen tuples are identified in the
table's visibility map, and can be skipped by vacuum even when
it's doing transaction wraparound prevention. This should greatly
reduce the cost of maintaining large tables containing
mostly-unchanging data.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, nataliewenz@ebureau.com, kgrittn@gmail.com
Subject: Re: Database 'template1' vacuum
In-Reply-To: <7326.1470674710@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox