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.