On Fri, Feb 17, 2017 at 11:12 AM, Alvaro Herrera <alvhe...@2ndquadrant.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
