Re: RFE: Make statistics robust for unplanned events

On 4/21/21 2:38 PM, Magnus Hagander wrote:
> On Tue, Apr 20, 2021 at 2:00 PM Patrik Novotny <panovotn(at)redhat(dot)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.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company