Working with Amazon Aurora PostgreSQL: what happened to the stats?

If you reboot your PostgreSQL aurora instance, your distribution stats will still be there, but your monitoring stats will not. Official PostgreSQL documentation states that if you do a normal shutdown, both distribution and monitoring stats will still be there when you restart your instance (Monitoring Stats):

“When the server shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset.”

This does not work however for the current aurora engine versions for PostgreSQL. This is a known bug in aurora. As of this date, April 27, 2020, these are the versions of the Aurora engine that have this bug:

Aurora PostgreSQL
3.1         11.6
3.0        11.4
2.4        10.11
2.3        10.7
2.2        10.6
2.1        10.5
2.0       10.4
1.6        9.6.16
1.5        9.6.12
1.4        9.6.11
1.3        9.6.9
1.2        9.6.8

So until this is fixed, you need to run vacuum analyze on all your tables whenever you reboot, or the autovacuum daemon will think that vacuum was never done.

The monitoring stats are the ones that get reset by calling pg_stat_reset(). The pg_stat_ and pg_statio_ tables fall into this category. The distribution stats are the ones that track distribution of data – number of distinct values, most common values in the column, histogram of data etc., which are more durably stored in the database.

Regarding this bug, the Amazon Aurora team states:
“Unfortunately, the monitoring stats like ‘pg_stat_all_tables’ and ‘pg_stat_user_tables’ gets reset after an instance reboot but the data distribution stats will not be impacted. This is a known bug in Aurora PostgreSQL. There is a feature request pending with engineering teams to preserve the stats after reboot. Unfortunately there isn’t any ETA on it.”

Michael Vitale, Team Elephas