Robert Haas <robertmhaas@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
