Re: Heavy write activity on first vacuum of fresh TOAST data

["Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>]

>>> On Thu, Dec 13, 2007 at  3:40 PM, in message
<1197582010(dot)4255(dot)1912(dot)camel(at)ebony(dot)site>, Simon Riggs <simon(at)2ndquadrant(dot)com>
wrote: 
> On Thu, 2007-12-13 at 15:19 -0600, Kevin Grittner wrote:
>> >>> On Thu, Dec 13, 2007 at 12:12 PM, in message
>> <1197569564(dot)4255(dot)1855(dot)camel(at)ebony(dot)site>, Simon Riggs <simon(at)2ndquadrant(dot)com>
>> wrote: 
>> > On Thu, 2007-12-13 at 11:46 -0600, Kevin Grittner wrote:
>> >> If the hint bit changes are written to the WAL ...
>> > 
>> > They're not.
>>  
>> So one would expect a write-intensive initial vacuum after a
>> PITR-style recovery?
> 
> Very perceptive. I was just thinking about that myself. An interesting
> issue when running with full_page_writes off.
> 
>> What impact would lack of the hint bits have until a vacuum?
> 
> Vacuum isn't important here. Its the first idiot to read the data that
> gets hit.
 
OK, I want to understand this well enough to recognize it when I
see it.  (As always, I appreciate the helpful answers here.)
 
Assuming no data is toasted, after a bulk INSERT or COPY into the
database, a subsequent SELECT COUNT(*) would figure out the correct
hint bits and rewrite all rows during execution of the SELECT
statement?
 
The same is true following a PITR-style recovery?
 
Toasted data would not be rewritten unless accessed (whether that
be for selection criteria, sort order, results, or whatever)?
 
A database VACUUM is going to run into every page not previously
accessed and make all hint bits correct?
 
Would a VACUUM FREEZE of a bulk-loaded table do one write for both
the hint bits and the transaction ID?  (I know that hackers
generally prefer that people leave the transaction IDs unfrozen
for a long time to aid in debugging problems, but that seems less
useful in a large table which has just been bulk-loaded, true?)
 
-Kevin