Re: When do vacuumed pages/tuples become available for reuse?

rihad <rihad@xxxxxxx> · Fri, 12 Apr 2019 14:40:17 +0400



    On 04/12/2019 08:39 AM, Michael Lewis
      wrote:

    
            Way to many indexes. I'm going to
              have a hard time convincing our programmers to get rid of
              any of them )
          
          
          You can create (concurrently) an identical index with a
            new name, then drop old version concurrently and repeat for
            each. It doesn't help you figure out the root cause and how
            to prevent it from happening again, but gets you to a fresh
            start at least.
        
      
    Thanks for the tip. I believe other than increasing load
      temporarily creating a new index, dropping the old one and
      renaming the new one to the old one are transparent and (almost)
      lock-less operations? What would happen to user queries during
      DROP INDEX? Would they block on it, or, being unable to read it
      without blocking, will they pick the new one based on the same
      column(s)? And, likewise, is ALTER INDEX ... RENAME ... an instant
      operation, I hope?