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 10:11 AM, in message
<1197562283(dot)4255(dot)1829(dot)camel(at)ebony(dot)site>, Simon Riggs <simon(at)2ndquadrant(dot)com>
wrote: 
> On Thu, 2007-12-13 at 09:46 -0600, Kevin Grittner wrote:
>  
>> The data was inserted through a Java program using a prepared
>> statement with no indexes on the table.  The primary key was then
>> added, and now I've started a vacuum.  The new table wound up being
>> the first big table vacuumed, and I noticed something odd.  Even
>> though there have been no rollbacks, updates, or deletes on this
>> table, the vacuum is writing as much as it is reading while dealing
>> with the TOAST data.
> 
> Writing hint bits. Annoying isn't it? :-(
 
Surprising, anyway.  If it allows subsequent operations to be
faster, I'll take it; although to a naive user it's not clear what
is known at vacuum time that the INSERT into the empty table
couldn't have inferred.  Bulk loads into empty tables are a pretty
common use case, so if there was some way to set the hints on
insert, as long as the table started the database transaction
empty, nobody else is modifying it, and only inserts have occurred,
that would be a good thing.  I'm speaking from the perspective of a
user, of course; not someone who would actually try to wrangle the
code into working that way.
 
Thanks for the explanation.
 
-Kevin