From: | Keith Fiske <keith(at)omniti(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index corruption with CREATE INDEX CONCURRENTLY |
Date: | 2017-02-17 16:23:18 |
Message-ID: | CAG1_KcD-SDV7CjrNiDbY9qhz9daN2CC-KXuSJ=UNW8LaKjN=gA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Feb 17, 2017 at 11:12 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:
> Keith Fiske wrote:
>
> > Was just curious if anyone was able to come up with any sort of method to
> > test whether an index was corrupted by this bug, other than just waiting
> > for bad query results? We've used concurrent index rebuilding quite
> > extensively over the years to remove bloat from busy systems, but
> > reindexing the entire database "just in case" is unrealistic in many of
> our
> > cases.
>
> As stated, if the CREATE INDEX operates on columns that are previously
> already indexed (which is normally the case when you rebuild because of
> bloat) then there is no chance of index corruption.
>
> Scanning indexes+tables is just as load-intensive as rebuilding the
> indexes anyway. You don't save any work. I suppose it can be a problem
> if you have an index big enough that it doesn't fit on your remaining
> free space (but in that case you have a pre-existing problem which you
> should solve anyway).
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
It's not the load I'm worried about, it's the locks that are required at
some point during the rebuild. Doing an index rebuild here and there isn't
a big deal, but trying to do it for an entire heavily loaded,
multi-terabyte database is hardly trivial. And I'd say doing a scan is far
less invasive than actually rebuilding the index since little to no writing
is actually being done.
I can understandable if it's simply not possible, but if it is, I think in
any cases of data corruption, having some means to check for it to be sure
you're in the clear would be useful.
Keith
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Khandekar | 2017-02-17 16:26:04 | Re: Parallel Append implementation |
Previous Message | Tom Lane | 2017-02-17 16:21:35 | Re: Help text for pg_basebackup -R |