Re: pg_upgrade —link does it remove table bloat

On 2/13/20 4:31 AM, Jason Ralph wrote:
> When using the pg_upgrade link method to upgrade Postgres a major
> version. Let’s say 9.3 to 11.6 on Centos Linux. Will table bloat carry
> over to the new version. I know using —link will use hard link pointers
> to the new data. So I assume all table bloat will carry over to the new
> version. I also know that pg_upgrade will reset statistics, so does the
> table remain bloated but statistics show otherwise? Can Someone please
> help me answer this? Or link where it’s outlined in the manual. Thanks
> as always.

Well table bloat and table statistics are two different things. Bloat is
the accumulation of dead or potentially dead tuples whose space has not
been marked as available for reuse by VACUUM or whose space has been
returned to the OS with VACUUM FULL. For more information see:

https://www.postgresql.org/docs/12/routine-vacuuming.html

I would think it would not matter if the files where copied or linked if
the space was being held open as result of regular VACUUM.

Statistics are just that statistics collected about the distribution of
values in the table. For more information see:

https://www.postgresql.org/docs/12/monitoring-stats.html

https://www.postgresql.org/docs/12/planner-stats-details.html

They are collected as part of the autovacuum process or by running
ANALYZE by itself or with VACUUM. FYI, pg_upgrade does not
automatically update the statistics, it just writes a script that you
can then run manually to do that:

https://www.postgresql.org/docs/12/pgupgrade.html

14.Statistics

Because optimizer statistics are not transferred by pg_upgrade, you will
be instructed to run a command to regenerate that information at the end
of the upgrade. You might need to set connection parameters to match
your new cluster.

>
> Jason Ralph

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com