From: Andrew Dunstan <andrew@dunslane.net>
To: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Fri, 28 May 2021 15:08:19 -0400
Message-ID: <9fa75894-d3be-e40b-cd89-f641bdf85df0@dunslane.net> (raw)
In-Reply-To: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
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://www.enterprisedb.com
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: andrew@dunslane.net, postgresql@mailpen.com, pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <9fa75894-d3be-e40b-cd89-f641bdf85df0@dunslane.net>
* 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