pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Clark <matt@ymogen.net>
To: Christopher Browne <cbbrowne@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Re: reindex/vacuum locking/performance?
Date: Sat, 4 Oct 2003 00:29:55 +0100
Message-ID: <LFEIJBEOKGPDHCEMDGNFAEOCCFAA.matt@ymogen.net> (raw)
In-Reply-To: <60d6de9fah.fsf@dev6.int.libertyrms.info>

> > In summary, I suspect that it is better from a UI perspective to
> > bring down the app on Sat at 3 a.m and reimport with a fixed time
> > period than to live through reindexing/vacuuming which may deadlock.
> > Am I missing something?
>
> Consider running pg_autovacuum, and thereby do a little bit of
> vacuuming here and there all the time.  It DOESN'T block, so unless
> your system is really busy, it shouldn't slow things down to a major
> degree.

My real world experience on a *very* heavily updated OLTP type DB, following
advice from this list (thanks guys!), is that there is essentially zero cost
to going ahead and vacuuming as often as you feel like it.  Go crazy, and
speed up your DB!

OK, that's on a quad CPU box with goodish IO, so maybe there are issues on
very slow boxen, but in a heavy-update environment the advantages seem to
easily wipe out the costs.

Matt

p.s.  Sorry to sound like a "Shake'n'Vac" advert.




view thread (37+ messages)  latest in thread

Message-ID: <LFEIJBEOKGPDHCEMDGNFAEOCCFAA.matt@ymogen.net>
Permalink:  ../LFEIJBEOKGPDHCEMDGNFAEOCCFAA.matt@ymogen.net/
Also on:    postgresql.org/message-id/LFEIJBEOKGPDHCEMDGNFAEOCCFAA.matt@ymogen.net

 · 

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: matt@ymogen.net, cbbrowne@libertyrms.info
  Subject: Re: reindex/vacuum locking/performance?
  In-Reply-To: <LFEIJBEOKGPDHCEMDGNFAEOCCFAA.matt@ymogen.net>

* 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