Re: AWS forcing PG upgrade from v9.6 a disaster

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.