From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Heavy write activity on first vacuum of fresh TOAST data
Date: Thu, 13 Dec 2007 10:26:58 -0600
Message-ID: <476108F2.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1197562283.4255.1829.camel@ebony.site>
References: <4760FF69.EE98.0025.0@wicourts.gov>
<1197562283.4255.1829.camel@ebony.site>
>>> On Thu, Dec 13, 2007 at 10:11 AM, in message
<camel@ebony.site/">1197562283.4255.1829.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, simon@2ndquadrant.com
Subject: Re: Heavy write activity on first vacuum of fresh TOAST data
In-Reply-To: <476108F2.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox