Re: When/if to Reindex

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Steven Flatt <steven(dot)flatt(at)gmail(dot)com>, Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>, Gregory Stark <stark(at)enterprisedb(dot)com>, Bill Moran <wmoran(at)collaborativefusion(dot)com>, Vivek Khera <vivek(at)khera(dot)org>, Pgsql performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: When/if to Reindex
Date: 2007-09-14 04:23:00
Message-ID: 200709140423.l8E4N0601374@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > So, can we simply trust what's in pg_class.relpages and ignore looking
> > directly at the index?
>
> No, we can't. In the light of morning I remember more about the reason
> for the aforesaid patch: it's actually unsafe to read the pg_class row
> at all if you have not got lock on the index. We are reading with
> SnapshotNow in order to be sure we see up-to-date info, and that means
> that a concurrent update of the row (eg, for REINDEX to report the new
> relfilenode) can have the following behavior:
>
> 1. REINDEX inserts the new modified version of the index's pg_class row.
>
> 2. Would-be reader process visits the new version of the pg_class row.
> It's not committed yet, so we ignore it and continue scanning.
>
> 3. REINDEX commits.
>
> 4. Reader process visits the old version of the pg_class row. It's
> now committed dead, so we ignore it and continue scanning.
>
> 5. Reader process bombs out with a complaint about no pg_class row for
> the index.
>
> So we really have to have the lock.
>
> > This is a fairly serious concern for us, that
> > reindex is blocking all readers of the parent table.
>
> I'm afraid you're kinda stuck: I don't see any fix that would be
> practical to put into 8.2, or even 8.3 considering that it's way too
> late to be thinking of implementing REINDEX CONCURRENTLY for 8.3.
>
> You might be able to work around it for now by faking such a reindex
> "by hand"; that is, create a duplicate new index under a different
> name using CREATE INDEX CONCURRENTLY, then exclusive-lock the table
> for just long enough to drop the old index and rename the new one
> to match.
>
> It's probably worth asking also how badly you really need routine
> reindexing. Are you certain your app still needs that with 8.2,
> or is it a hangover from a few releases back? Could more aggressive
> (auto)vacuuming provide a better solution?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message El-Lotso 2007-09-14 05:51:41 Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
Previous Message Tom Lane 2007-09-14 03:33:23 Re: Index files