Skip site navigation (1) Skip section navigation (2)

Re: Heavy write activity on first vacuum of freshTOAST data

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Heavy write activity on first vacuum of freshTOAST data
Date: 2007-12-13 16:26:58
Message-ID: 476108F2.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
>>> 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
 



In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-12-13 16:35:21
Subject: Re: Heavy write activity on first vacuum of fresh TOAST data
Previous:From: Simon RiggsDate: 2007-12-13 16:23:45
Subject: Re: Limited performance on multi core server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group