> 6) Failovers, at least in Aurora, apparently cause pg_stat_reset() to be run, at least judging by the timestamp I'm
seeingin pg_stat_bgwriter.stat_reset. We haven't done a failover in the data center in a while, and it's less trivial
forme to test there atm, so I'm not certain whether open-source Postgres failovers also reset statistics.
Just wanted to chime in here - on this version of Aurora, there’s nothing special or different from open source
communityPostgreSQL that I know of. I believe that a failover in RDS MAZ PostgreSQL or in your own data center with
repmgrwould have the same characteristics. Actually nothing is calling pg_stat_reset() in any of these cases - it’s
justthat these statistics are maintained only in memory and not replicated through the WAL stream. (Aurora is slightly
differentw shared storage between the nodes, but same principle applies.) So after failover, you’re on a new physical
machinewhich needs to start over fresh on populating the memory structures.
> Is it recommended practice to manually VACUUM ANALYZE the whole database after a failover? Or is resetting stats
aftera failover just an Aurora thing? I'm sorry I'm asking the latter question instead of testing, but I've been
orderednot to spend time on improving our vacuuming and statistics until 5 other large projects are done, and I'm
spendinga minimal amount of time anyway just to see how badly frequent failovers might be affecting us and if there's
anyaction we need to take.
If you’re seeing frequent failovers - whether in your own data center or somebody else’s - I think you should
investigateit. I don’t think you should accept that from your own staff or from any vendor.
That said, I don’t think the impact here is really that severe on bloat or statistics; any frequently modified table
willstill get vacuumed - albeit with one slightly longer interval due to a failover. It won’t exceed double the normal
intervalunless there are multiple failovers in quick succession. And a table that isn’t getting changed wasn’t going
toget vacuumed anyway.
-Jeremy