From: Steven Flatt <steven.flatt@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Wed, 22 Aug 2007 12:55:28 -0400
Message-ID: <357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com> (raw)
In-Reply-To: <357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
<11801.1186624308@sss.pgh.pa.us>
<357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com>
>
> It makes more sense for us to have ~1 hour's worth of reindexing
> afterwards during which read performance on that partition is "compromised".
>
So, based on the docs, I was expecting read performance to be compromised
during a reindex, specifically reads would not be allowed to use the index:
"REINDEX locks out writes but not reads of the index's parent table. It also
takes an exclusive lock on the specific index being processed, which will
block reads that attempt to use that index."
However I'm seeing that all readers of that table are blocked until the
reindex finishes, even reads that do not attempt to use the index. Is this
a problem with the docs or a bug?
I'm considering creating a new index with the same definition as the first
(different name), so while that index is being created, read access to the
table, and the original index, is not blocked. When the new index is
created, drop the original index and rename the new index to the original,
and we've essentially accomplished the same thing. In fact, why isn't
reindex doing this sort of thing in the background anways?
Thanks,
Steve
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: steven.flatt@gmail.com, tgl@sss.pgh.pa.us, wmoran@collaborativefusion.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.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