pg_stat_user_tables has n_live_tup = 0 and last_autonalyze = null for most tables - why are tables not getting analyzed?

Question

Similar to What are the possible meanings of NULL and zero for autovacuum_count, last_autovacuum, and autoanalyze_count in PostgreSQL? except in my case it's PostgreSQL 15.1 and there are no deletes - data is only inserted.

I have a DB with some partitioned tables and a few non-partitioned ones as well. Some of the partitions have hundreds of millions of rows, but n_live_tup is 0 for them and vacuum_count, autovacuum_count, analyze_count, autoanalyze_count are all 0.

Only the latest 2 tables created (both partitions) have autoanalyze_count = 1 and 4, respectively, and have n_live_tup > 0. There are, however, a few other tables with n_live_tup > 0 (despite no analyze being run on them ever).

        select relname, n_live_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count
        from pg_catalog.pg_stat_user_tables
        where schemaname = 'myschema'

Note that n_live_tup for event_59 is wildly wrong here and the table actually has >1.1 billion rows (that's the biggest one).

I tried setting log_autovacuum_min_duration = 0 as suggested in the linked question, but it doesn't seem to have logged anything so far.

Why are my tables not getting analyzed?

Edit 1: Now even the statistics for those tables have been reset:

The server has not crashed since - pg_postmaster_start_time() returns 2023-02-13 17:02:32.365095+00 (that was when I restarted it). As you can see in the first screenshot, autoanalyze ran soon after that. The logs show it ran again at 20:10:18, but despite this all the analyze/vacuum counts are 0 and n_live_tup has decreased.

I manually ran analyze event_241 and n_live_tup became accurate. I restarted PG afterwards and that did not reset the count, so I'm not sure why it got reset overnight.

Edit 2: It turns out the server is crashing every night when pg_dump backups run, but apparently without the postmaster process being restarted, because pg_postmaster_start_time() keeps returning the same value. I can easily repro the crash by pg_dumping a specific database (not the one this post is about).

2023-02-17 09:04:16.607 UTC [27083] postgres@problematic_database LOG:  connection authorized: user=postgres database=problematic_database application_name=pg_dump
2023-02-17 09:04:16.609 UTC [27083] postgres@problematic_database PANIC:  could not open critical system index 2662
2023-02-17 09:04:16.610 UTC [11922] LOG:  server process (PID 27083) was terminated by signal 6: Aborted
2023-02-17 09:04:16.610 UTC [11922] LOG:  terminating any other active server processes
2023-02-17 09:04:16.645 UTC [11922] LOG:  all server processes terminated; reinitializing
2023-02-17 09:04:17.176 UTC [27945] LOG:  database system was interrupted; last known up at 2023-02-17 09:03:24 UTC

Fixing that crash is a separate topic, but for this question I'm wondering:

1. What's the best way to monitor for this happening? We could scan the logs for keywords, but it seems better to directly check for statistics being reset, since that will catch this as well as other errors. Is it just a matter of manually running analyze on at least one table and then regularly polling for analyze_count = 0 or is there a better way?
2. If we do detect that the statistics were reset (due to a crash) it seems like a good idea to analyze manually to re-populate the statistics, right? But then why does PG not do this automatically after recovering from a crash?
3. Or maybe it's a good idea to just run ANALYZE regularly anyway, say, weekly or even nightly? (It currently takes ~200 seconds on this DB - way less time than the backup!)

Answer 1

There are several possible explanations:

• a crash reset the statistics

• somebody called pg_stat_reset() or a related function to reset the statistics

• the database was upgraded with pg_upgrade and nobody ran ANALYZE afterwards

To check when the statistics were last reset for your database, run

SELECT pg_stat_get_db_stat_reset_time(oid)
FROM pg_database
WHERE datname = current_database();

If that is NULL, the statistics have never been reset explicitly.

---

Your further investigation showed that you have catalog data corruption, which crashes the database daily. If you have a good backup, restore that. Otherwise, take down the database immediately and perform a cold backup of all files. You can try to fix the problem by starting PostgreSQL with the -P option, then try to rebuild the index as superuser:

REINDEX INDEX pg_catalog.pg_class_oid_index;

The goal is to get the database into a state where you can dump it successfully. Then restore it to a newly created cluster on good hardware. Don't continue working with this cluster, even if it seems OK again.