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

[MichaelDBA <MichaelDBA@xxxxxxxxxxx>]

Hi Lance,

Did you customize the PG 12 DB Parameter group to be in sync as much as possible with the 9.6 RDS version?  Or are you using PG12 default DB Parameter group?

Are you using the same AWS Instance Class?

Did you vacuum analyze all your tables after the upgrade to 12?

Regards,
Michael Vitale

Campbell, Lance wrote on 5/28/2021 3:18 PM:

Also, did you check your RDS setting in AWS after upgrading?  I run four databases in AWS.  I found that the work_mem was set way low after an upgrade.  I had to tweak many of my settings.

 

Lance

 

From: Andrew Dunstan <andrew@xxxxxxxxxxxx>
Date: Friday, May 28, 2021 at 2:08 PM
To: Dean Gibson (DB Administrator) <postgresql@xxxxxxxxxxx>, pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 2:48 PM, Dean Gibson (DB Administrator) wrote:
> [Reposted to the proper list]
>
> 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.
>
> I didn't have the time in March to diagnose the problem, other than
> some futile adjustments to server parameters, so I reverted back to a
> saved copy of my v9.6 data.
>
> On Sunday, being retired, I decided to attempt to solve the issue in
> earnest.  I have now spent five days (about 14 hours a day), trying
> various things, including adding additional indexes.  Keeping the v9.6
> data online for web users, I've "forked" the data into new copies, &
> updated them in turn to PostgreSQL v10, v11, v12, & v13.  All exhibit
> the same problem:  As you will see below, it appears that versions 10
> & above are doing a sequential scan of some of the "large" (200K rows)
> tables.  Note that the expected & actual run times both differ for
> v9.6 & v13.2, by more than *two orders of magnitude*. Rather than post
> a huge eMail (ha ha), I'll start with this one, that shows an "EXPLAIN
> ANALYZE" from both v9.6 & v13.2, followed by the related table & view
> definitions.  With one exception, table definitions are from the FCC
> (Federal Communications Commission);  the view definitions are my own.
>
>
>

Have you tried reproducing these results outside RDS, say on an EC2
instance running vanilla PostgreSQL?


cheers


andrew



--
Andrew Dunstan
EDB: https://urldefense.com/v3/__https://www.enterprisedb.com__;!!DZ3fjg!tiFTfkNeARuU_vwxOHZfrJvVXj8kYMPJqa1tO5Fnv75UbERS8ZAmUoNFl_g2EVyL$