Back to blog

September 3, 2020

Fixing Slow Postgres Performance After heroku pg:upgrade

Chris White

Senior Software Engineer
Fort Collins, CO

We recently followed Heroku’s pg:upgrade instructions when upgrading a client’s database from Postgres 10 to Postgres 12. After a round of celebratory high fives at a smooth migration, reports from our client’s users started to roll in: **application performance was severely degraded.** Pages were loading slowly and their read-heavy reporting jobs were taking 50-100x longer to complete. 😱

## The Fix

```bash
heroku pg:psql
VACUUM;
ANALYZE;
```

After ANALYZE was complete, the application returned to normal. 🎉 Shout-out to Zeal colleague Jason Harrison for his last-minute rescue research.

From the [Postgres docs](https://www.postgresql.org/docs/current/sql-analyze.html): “ANALYZE collects statistics about the contents of tables in the database, and stores the results in the [pg_statistic](https://www.postgresql.org/docs/current/catalog-pg-statistic.html) system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.”

This is done regularly in Heroku Postgres by the AUTOVACUUM daemon. It appears as though pg:upgrade does not automatically migrate the pg_statistics system, so this application’s large queries on complex views were using unoptimized query execution plans.* ANALYZE; brings pg_statistics up to date and the effects were immediate.

_*citation needed 🤷‍♂️_

## More Resources

During the “panic look at everything possible” phase of troubleshooting, I came across the [heroku-pg-extras extension](https://github.com/heroku/heroku-pg-extras), which adds some database metric superpowers to Heroku CLI.

* **heroku pg:index-usage** helped to identify a very large index that was not in use.
* **heroku pg:blocking** gave us some visibility on the very large reads that were performing badly before the fix.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Up next