Re: Queuing all tables for analyze after recovery

On 10/19/2017 10:54 PM, Tom Lane wrote:
> Tomasz Ostrowski <tometzky+pg(at)ato(dot)waw(dot)pl> writes:
>> Some (maybe all) row statistics are lost after the database has
>> recovered after a failover. So it's recommended to ANALYZE all databases
>> in a cluster after recovery.
>
> Uh ... recommended by whom? pg_statistic has exactly the same reliability
> guarantees as the rest of the system catalogs.
>
> I don't deny that there might be cases where this is worth doing, but
> it does not seem so likely that it should be part of one's standard
> checklist. Much less something that we should expend a great deal
> of effort to automate.

For data statistics, sure. One thing I'm unhappy about is that
pg_stat_all_tables is blank.

An idea I've been throwing around in my head is to have autovacuum work
on tables that have vacuum_count and autovacuum_count both zero (and
likewise for analyze).

This will cause a flurry of activity after failover or crash, but the
alternative is autovacuum not knowing anything about the state of the
tables and allowing massive bloat to potentially occur.

For example, if you have a 1 billion row table, and crash when there are
199,999,999 dead tuples, you currently get to wait for another 200
million to die before anything gets cleaned up.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support