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.klaver@xxxxxxxxxxx
