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>
 <d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
Date: Fri, 28 May 2021 18:51:18 -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: <d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
Content-Type: multipart/alternative;
 boundary="------------D93F7831D045ED2C6362A303"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d%40gmail.com>
Precedence: bulk

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

On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote:
> 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.

Did it run in less than 10 hours?

-- 
Angular momentum makes the world go 'round.

--------------D93F7831D045ED2C6362A303
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 5:06 PM, Dean Gibson (DB Administrator) wrote:<br>
    <blockquote type="cite"
      cite="mid:d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      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>
    </blockquote>
    <br>
    Did it run in less than 10 hours?<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------D93F7831D045ED2C6362A303--