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

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?

> 
> 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.  Keeping the v9.6 data online for web users, I've 
> "forked" the data into a new copy, & updated it 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 for v9.6 & v13.2 both differ 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.
> 




-- 
Adrian Klaver
adrian.klaver@aklaver.com