MIME-Version: 1.0
References: <006D484F-66AC-4734-A7E5-2EFBB6CEC1FA@gmail.com>
In-Reply-To: <006D484F-66AC-4734-A7E5-2EFBB6CEC1FA@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 4 Jun 2025 12:23:40 +1200
Message-ID: <CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA@mail.gmail.com>
Subject: Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
To: Matthew Tice <mjtice@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpcoOPq1JF_-jvNzuYigDQ_mJsXMaOjyux0agaabO3YUA%40mail.gmail.com>
Precedence: bulk

On Wed, 4 Jun 2025 at 07:22, Matthew Tice <mjtice@gmail.com> wrote:
> Tue 03 Jun 2025 07:13:11 PM UTC (every 1s)
> n_dead_tup          | 5038
> autoanalyze_count   | 3078

> Tue 03 Jun 2025 07:13:12 PM UTC (every 1s)
> n_dead_tup          | 1290579
> autoanalyze_count   | 3079

> I don't understand where this large increase is coming from when there are no corresponding inserts, updates, or deletes (at the magnitude).  This entire process repeats itself and, as mentioned, the same thing is happening on other observed tables.

I imagine it's from the auto-analyze that ran. Analyze will try to
estimate the live and dead rows, but since analyze only samples some
blocks, it may come up with something that's not too accurate if the
blocks it happened to sample don't contain similar percentages of dead
rows than the entire table.

See [1].

David

[1] https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/analyze.c#L1318