pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Is there a reason _not_ to vacuum continuously?
Date: Wed, 17 Sep 2003 21:59:43 -0400
Message-ID: <m3smmuonzk.fsf@wolfe.cbbrowne.com> (raw)
References: <LFEIJBEOKGPDHCEMDGNFEECHCDAA.matt@ymogen.net>
	<200309171517.26841.josh@agliodbs.com>

Oops! josh@agliodbs.com (Josh Berkus) was seen spray-painting on a wall:
>> I understand this needs an exclusive lock on the whole table, which is
>> simply not possible more than once a month, if that...  Workarounds/hack
>> suggestions are more than welcome :-)
>
> Would it be reasonable to use partial indexes on the table?

Dumb question...

... If you create a partial index, does this lock the whole table
while it is being built, or only those records that are affected by
the index definition?

I expect that the answer to that is "Yes, it locks the whole table,"
which means that a partial index won't really help very much, except
insofar as you might, by having it be restrictive in range, lock the
table for a somewhat shorter period of time.

An alternative that may or may not be viable would be to have a series
of tables:

 create table t1 ();
 create table t2 ();
 create table t3 ();
 create table t4 ();

Then create a view: 

  create view t as select * from t1 union all select * from t2 union
    all select * from t13 union all select * from t4;

Then you set this view to be updatable, by having a function that
rotates between the 4 tables based on a sequence.  

You do SELECT NEXTVAL('t_controller') and the entries start flooding
into t2 rather than t1, or into t3, or into t4, and after t4, they go
back into t1.

When you need to reindex t1, you switch over to load entries into t2,
do maintenance on t1, and then maybe roll back to t1 so you can do the
same maintenance on t2.
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://www3.sympatico.ca/cbbrowne/lisp.html
Linux is like a Vorlon. It is incredibly powerful, gives terse,
cryptic answers and has a lot of things going on in the background.



view thread (14+ messages)  latest in thread

Message-ID: <m3smmuonzk.fsf@wolfe.cbbrowne.com>
Permalink:  ../m3smmuonzk.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m3smmuonzk.fsf@wolfe.cbbrowne.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Is there a reason _not_ to vacuum continuously?
  In-Reply-To: <m3smmuonzk.fsf@wolfe.cbbrowne.com>
  References: <LFEIJBEOKGPDHCEMDGNFEECHCDAA.matt@ymogen.net> <200309171517.26841.josh@agliodbs.com> <m3smmuonzk.fsf@wolfe.cbbrowne.com>

* 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