> 6) Failovers, at least in Aurora, apparently cause pg_stat_reset() to be > run, at least judging by the timestamp I'm seeing in > pg_stat_bgwriter.stat_reset. We haven't done a failover in the data center in > a while, and it's less trivial for me 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 community PostgreSQL that I know of. I believe that a failover in RDS MAZ PostgreSQL or in your own data center with repmgr would have the same characteristics. Actually nothing is calling pg_stat_reset() in any of these cases - it’s just that these statistics are maintained only in memory and not replicated through the WAL stream. (Aurora is slightly different w shared storage between the nodes, but same principle applies.) So after failover, you’re on a new physical machine which 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 after a failover just an Aurora > thing? I'm sorry I'm asking the latter question instead of testing, but I've > been ordered not to spend time on improving our vacuuming and statistics > until 5 other large projects are done, and I'm spending a minimal amount of > time anyway just to see how badly frequent failovers might be affecting us > and if there's any action we need to take. If you’re seeing frequent failovers - whether in your own data center or somebody else’s - I think you should investigate it. 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 will still get vacuumed - albeit with one slightly longer interval due to a failover. It won’t exceed double the normal interval unless there are multiple failovers in quick succession. And a table that isn’t getting changed wasn’t going to get vacuumed anyway. -Jeremy
