pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Otto Blomqvist <o.blomqvist@secomintl.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pg_autovacuum not having enough suction ?
Date: Fri, 25 Mar 2005 18:06:02 -0500
Message-ID: <16054.1111791962@sss.pgh.pa.us> (raw)
In-Reply-To: <42435408.30303@zeut.net>
References: <d1v0a7$iau$1@news.hub.org>
	<42430FA0.9080204@zeut.net>
	<002b01c530c5$ad5a1620$e602a8c0@blomqvist>
	<42435408.30303@zeut.net>

> Otto Blomqvist wrote:
>> This table is basically a queue full of records waiting to get transfered
>> over from our 68030 system to the PG database. The records are then moved
>> into folders (using a trigger) like file_92_myy depending on what month the
>> record was created on the 68030. During normal operations there should not
>> be more than 10 records at a time in the table, although during the course
>> of a day a normal system will get about 50k records. I create 50000 records
>> to simulate incoming traffic, since we don't have much traffic in the test
>> lab.

Really the right way to do housekeeping for a table like that is to
VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after
discarding a batch of records.  The VACUUM FULL will take very little
time if it only has to repack <10 records.  Plain VACUUM is likely to
leave the table nearly empty but physically sizable, which is bad news
from a statistical point of view: as the table fills up again, it won't
get physically larger, thereby giving the planner no clue that it
doesn't still have <10 records.  This means the queries that process
the 50K-record patch are going to get horrible plans :-(

I'm not sure if autovacuum could be taught to do that --- it could
perhaps launch a vacuum as soon as it notices a large fraction of the
table got deleted, but do we really want to authorize it to launch
VACUUM FULL?  It'd be better to issue the vacuum synchronously
as part of the batch updating script, I feel.

			regards, tom lane



view thread (19+ messages)  latest in thread

Message-ID: <16054.1111791962@sss.pgh.pa.us>
Permalink:  ../16054.1111791962@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16054.1111791962@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, matthew@zeut.net, o.blomqvist@secomintl.com
  Subject: Re: pg_autovacuum not having enough suction ?
  In-Reply-To: <16054.1111791962@sss.pgh.pa.us>

* 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