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>
 <70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
Date: Fri, 28 May 2021 15:06:10 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
In-Reply-To: <70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
Content-Type: multipart/alternative;
 boundary="------------BB813B508886C0C87B262C0A"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/d73e2731-31ba-df14-9b9c-974342200ae8%40mailpen.com>
Precedence: bulk

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

On 2021-05-28 12:38, Ron wrote:
> 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.

Just now did.  No change in EXPLAIN ANALYZE output.


--------------BB813B508886C0C87B262C0A
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 2021-05-28 12:38, Ron wrote:<br>
    <blockquote type="cite"
      cite="mid:70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      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.</blockquote>
    <br>
    Just now did.  No change in EXPLAIN ANALYZE output.<br>
    <br>
  </body>
</html>

--------------BB813B508886C0C87B262C0A--