Re: pg_upgrade —link does it remove table bloat

On 2/13/20 10:04 AM, Jason Ralph wrote:
>> 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:
>
> Thanks for the helpful response @Adrian Klaver,
> Let me try to rephrase my question,
> If a table has bloat before the upgrade, autvacuum was not aggressive enough, once pg_upgrade is complete, the same table will contain the same amount of bloat(dead tuples)? Meaning its not the same as pg_dump / pg_restore since it’s a hard link to the previous data location. Pg_upgrade with link will not recreate the table.

Yes pg_upgrade is a transfer of the binary data(sort of) whereas
pg_dump/restore is a logical transfer. So pg_upgrade via copy/clone/link
will not recreate user tables. For a good overview see IMPLEMENTATION
file in src:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_upgrade/IMPLEMENTATION;h=69fcd70a7c526d9f27b43a49f71e9b591a6b889f;hb=HEAD7

>
> Jason Ralph

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