The Story:
Client reported that one of the reports which was getting generated in 15-20 mins is not taking ours.
We tried analysing the RDS performance insight and came to know IO:DataFileRead is taking time.
Recently we upgraded the Postgres version from 12 to 15 on two of our projects.
We had two possible causes(still a theory)
Recently we upgraded the Postgres version from 12 to 15 on two of our projects. It can be the root cause.
Client ingested more data into the system which caused query performance to degrade.
So I turned to looking at shared buffer memory size and thought it can be optimised.
But we were not sure whether it was a good idea or not. That’s why we got into call with the AWS team.
During the call realised its already set to its maximum value.Then told AWS about the recent DB version upgrade.
Then AWS told us about a few post upgrade activities to be done.
We did those and Voila
the query again started giving the same performance.
About the post DB upgrade Activity:
Post DB migration we are supposed to run the ANALYZE VERBOSE; command so that PG updates the table statistics and the query planner can use those statistics to determine the most efficient query plan.
References:
https://aws.amazon.com/blogs/database/amazon-aurora-postgresql-parameters-part-1-memory-and-query-plan-management/https://www.postgresql.org/docs/10/sql-analyze.html