Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-general@lists.postgresql.org
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
 <2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
 <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
Date: Fri, 28 May 2021 14:38:02 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
Content-Type: multipart/alternative;
 boundary="------------200A3325192972F716091A1F"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/70edb767-6ba0-1065-2f74-f01440e3caea%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------200A3325192972F716091A1F
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:
> On 2021-05-28 08:12, Adrian Klaver wrote:
>> On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote:
>>> I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at 
>>> one point), gradually moving to v9.0 w/ replication in 2010.  In 2017 I 
>>> moved my 20GB database to AWS/RDS, gradually upgrading to v9.6, & was 
>>> entirely satisfied with the result.
>>>
>>> In March of this year, AWS announced that v9.6 was nearing end of 
>>> support, & AWS would forcibly upgrade everyone to v12 on January 22, 
>>> 2022, if users did not perform the upgrade earlier.  My first attempt 
>>> was successful as far as the upgrade itself, but complex queries that 
>>> normally ran in a couple of seconds on v9.x, were taking minutes in v12.
>>
>> Did you run a plain 
>> ANALYZE(https://www.postgresql.org/docs/12/sql-analyze.html) on the 
>> tables in the new install?
>
> After each upgrade (to 10, 11, 12, & 13), I did a "VACUUM FULL ANALYZE".  
> On 10 through 12, it took about 45 minutes & significant CPU activity, & 
> temporarily doubled the size of the disk space required.  As you know, 
> that disk space is not shrinkable under AWS's RDS.  On v13, it took 10 
> hours with limited CPU activity, & actually slightly less disk space 
> required.

Under normal conditions, VACUUM FULL is pointless on a freshly-loaded 
database; in RDS, it's *anti-useful*.

That's why Adrian asked if you did a plain ANALYZE.

-- 
Angular momentum makes the world go 'round.

--------------200A3325192972F716091A1F
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote:<br>
    <blockquote type="cite"
      cite="mid:88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">On 2021-05-28 08:12, Adrian Klaver
        wrote:<br>
      </div>
      <blockquote type="cite"
        cite="mid:2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com">On
        5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote: <br>
        <blockquote type="cite">I started to use PostgreSQL v7.3 in 2003
          on my home Linux systems (4 at one point), gradually moving to
          v9.0 w/ replication in 2010.  In 2017 I moved my 20GB database
          to AWS/RDS, gradually upgrading to v9.6, &amp; was entirely
          satisfied with the result. <br>
          <br>
          In March of this year, AWS announced that v9.6 was nearing end
          of support, &amp; AWS would forcibly upgrade everyone to v12
          on January 22, 2022, if users did not perform the upgrade
          earlier.  My first attempt was successful as far as the
          upgrade itself, but complex queries that normally ran in a
          couple of seconds on v9.x, were taking minutes in v12. <br>
        </blockquote>
        <br>
        Did you run a plain ANALYZE(<a class="moz-txt-link-freetext"
          href="https://www.postgresql.org/docs/12/sql-analyze.html"
          moz-do-not-send="true">https://www.postgresql.org/docs/12/sql-analyze.html</a>)
        on the tables in the new install? <br>
      </blockquote>
      <br>
      After each upgrade (to 10, 11, 12, &amp; 13), I did a "VACUUM FULL
      ANALYZE".  On 10 through 12, it took about 45 minutes &amp;
      significant CPU activity, &amp; temporarily doubled the size of
      the disk space required.  As you know, that disk space is not
      shrinkable under AWS's RDS.  On v13, it took 10 hours with limited
      CPU activity, &amp; actually slightly less disk space required. <br>
    </blockquote>
    <br>
    Under normal conditions, VACUUM FULL is pointless on a
    freshly-loaded database; in RDS, it's <b>anti-useful</b>.<br>
    <br>
    That's why Adrian asked if you did a plain ANALYZE.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------200A3325192972F716091A1F--