Re: RFE: Make statistics robust for unplanned events

Hi,

On 2021-04-21 14:38:44 +0200, Magnus Hagander wrote:
> 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.

Yea. I think the main benefit from the shared memory stats patch that
would make this a easier is that it tracks (with one small hole that can
probably be addressed) dropped objects accurately, even across crashes /
replication. Having old stats around runs into danger of mixing stats
for an old dropped object being combined with stats for a new object.

I don't think making pgstat.c fully durable by continually storing the
data in a table or something like that is an option. For one, the stats
for a replica and primary are independent. For another, the overhead
would be prohibitive.

But after we gain the accurate dropping of stats we can store a stats
snapshot corresponding to certain WAL records (by serializing to
something like pg_stats_%redo_lsn%) without ending up with dropped stats
surviving.

A big question around this is how often we'd want to write out the
stats. Obviously, the more often we do, the higher the overhead. And the
less frequently, the more stats updates might be lost.

Patrik, for your use cases, would loosing at most the stats since the
start of last checkpoint be an issue?

Greetings,

Andres Freund