Re: Vacuum full: alternatives?

John R Pierce <pierce@xxxxxxxxxxxx> · Mon, 20 Jun 2016 09:06:10 -0700



    On 6/20/2016 8:51 AM, David G. Johnston
      wrote:

    
        incorrect.   in fact, an update is performed identically to an
        INSERT + DELETE(old)

        
      Except for heap-only-tuple optimization, right?  We cannot
        build a HOT chain if the user requests a delete separately since
        their is no longer an association to trace from the old record.
      

      I suspect this affects free space usage to some degree as
        well but I agree and believe that the reclaimed space is not
        forbidden to be used (I wouldn't rely on my word though and
        haven't tried to find relevant documentation).
    
    
    yeah, HOT only works on updates that don't modify any indexed
      fields, and only if there's adequate free space in the same
      block.   If you have a update intensive table thats a candidate
      for HOT, I've been recommending setting that table's fill factor
      to 50-70% prior to populating it to leave freespace in every
      block.

    
    -- 
john r pierce, recycling bits in santa cruz