From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Fri, 28 May 2021 11:40:29 -0700
Message-ID: <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com> (raw)
In-Reply-To: <2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
<2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com, pgsql-general@postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox