Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete

Note also that 15.6 is about 18 months old. Upgrading really does only
take a few minutes, if you download the binaries before installation.

On Wed, Jun 4, 2025 at 2:37 PM Matthew Tice <mjtice(at)gmail(dot)com> wrote:

>
> > On Jun 3, 2025, at 6:23 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> >
> > On Wed, 4 Jun 2025 at 07:22, Matthew Tice <mjtice(at)gmail(dot)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
>
> Thanks, David.
>
> This table is relatively large (1.6B records, 1.5TB, 38 columns). The
> `default_statistics_target` is set to 300 - so I think that 90000 may not
> be enough to gather accurate statistics.
>
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!