pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Otto Blomqvist <o.blomqvist@secomintl.com>
To: pgsql-performance@postgresql.org
Subject: Re: pg_autovacuum not having enough suction ?
Date: Fri, 25 Mar 2005 15:34:00 -0800
Message-ID: <d2270i$37v$1@news.hub.org> (raw)
References: <d1v0a7$iau$1@news.hub.org>
	<42430FA0.9080204@zeut.net>
	<002b01c530c5$ad5a1620$e602a8c0@blomqvist>
	<42435408.30303@zeut.net>
	<16054.1111791962@sss.pgh.pa.us>

ok, Thanks a lot for your time guys  ! I guess my table is pretty unusual
and thats why this problem has not surfaced until now. Better late then
never ;) I'll cron a "manual" vacuum full on the table.



"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:16054.1111791962@sss.pgh.pa.us...
> > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>





view thread (19+ messages)  latest in thread

Message-ID: <d2270i$37v$1@news.hub.org>
Permalink:  ../d2270i$37v$1@news.hub.org/
Also on:    postgresql.org/message-id/d2270i$37v$1@news.hub.org

 · 

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: o.blomqvist@secomintl.com
  Subject: Re: pg_autovacuum not having enough suction ?
  In-Reply-To: <d2270i$37v$1@news.hub.org>

* 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