Re: Does pg_stat_get_live_tuples() matter?

> 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