Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Someone had the idea a while back of pre-freezing inserted tuples in
> the WAL-bypass case.
I'm sure I'm not the one who thought up the idea and first posted
about it, but I'm certainly an advocate for it.
> It seems like in theory you could have a background process that
> would iterate through dirty shared buffers and freeze tuples
> opportunistically before they are written back to disk, but I'm not
> sure that it would really be worth it.
We have routinely been doing a database-level VACUUM FREEZE after a
pg_dump | psql copy of a database, because:
(1) Otherwise, users experience abysmal performance running routine
queries as every tuple scanned has its hint bits set during simple
SELECT statements. The massive disk write levels during SELECTs was
very confusing at first, and if you search the archives, I'm sure
you'll find that I'm not the only one who's been confused by it.
(2) Otherwise, there looms a point where every tuple restored, which
is not subsequently updated or deleted, will need to be frozen by
autovacuum -- all at the same time. Unless you're paying
extraordinary attention to the issue, you won't know when it is
coming, but the day will come. Probably in the middle of some
time-critical process which is doing a lot of work.
(3) We want to get this done before starting the WAL archiving, to
prevent having massive quantities of WAL to transmit across the WAN.
(4) With our improved backup processes we have another reason -- our
PITR base backup space requirements and WAN bandwidth usage will be
higher if we don't start from a frozen state and stay frozen.
So really, we'd be pretty silly *not* to make sure that all tuples are
frozen and have hint bits set after a pg_dump | psql copy. It would
speed the process somewhat if the tuples could be written in that
state to start with.
-Kevin