On Wed, Apr 21, 2021 at 5:02 PM Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > > > > On 4/21/21 2:38 PM, Magnus Hagander wrote: > > On Tue, Apr 20, 2021 at 2:00 PM Patrik Novotny <panov...@redhat.com> wrote: > >> > >> Hello PostgreSQL Hackers, > >> > >> is it possible to preserve the PostgreSQL statistics on a server crash? > >> > >> Steps to reproduce the behaviour: > >> 1) Observe the statistics counters, take note > >> 2) Crash the machine, e.g. with sysrq; perhaps kill -9 on postgresql will > >> already suffice > >> 3) After recovery, observe the statistics counter again. Have they been > >> reset to zero (Bad) or are they preserved (Good). > >> > >> Resetting the counters to zero harms execution planning and auto_vacuum > >> operations. That can cause growth of database as dead tuples are not > >> removed > >> at the right time. In the end the database can go offline if autovacuum > >> never runs. > > > > The stats for the planner are store persistently in pg_stats though, > > but autovacuum definitely takes a hit from it, and several other > > things can too. > > > >> As far as I've checked, this would have to be implemented. > >> > > I think the problem with planner stats is that after reset of the > runtime stats we lose info about which tables may need analyze etc. and > then fail to run ANALYZE in time. Which may have negative impact on > performance, of course. > > >> My question would be whether there is something that would make > >> this impossible to implement, and if there isn't, I'd like this to > >> be considered a feature request. > > > > I'm pretty sure everybody would *want* this. At least nobody would be > > against it. The problem is the potential performance cost of it. > > > > Andres mentioned at least once over in the thread about shared memory > > stats collection that being able to have persistent stats could come > > out of that one in the future. Whatever is done on the topic should > > probably be done based on that work, as it provides a better starting > > point and also one that will stay around. > > > > Right. I think the other question is how often does this happen in > practice - if your instance crashes often enough to make this an issue, > then there are probably bigger issues.
Agreed. I think the bigger problem there is replication failover, but that's also a different issue (keeping the statistics from the *standby* wouldn't help you much there, you'd need to replicate it from the primary). -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
