Such an UPDATE should have left the table 50% dead tuples, since every row would leave behind a dead version. On the other hand, an ALTER COLUMN TYPE operation should rewrite the whole table and leave no dead tuples behind. No matter which one you did last, it doesn't square with 0.32% dead tuples.
My best guess at this point is that what you did last is an UPDATE, so you have 50% dead tuples, and for some reason pgstattuple is not telling you the truth about that. But the VACUUM is showing reality.
How long did those UPDATEs and ALTER TABLEs take? If an ALTER seemed tolerable then maybe what you want to do is VACUUM FULL, which would be roughly the same cost.
Tom, as you predicted, the VACUUM FULL finished relatively quickly yesterday after about 31h. Here is the verbose output:
INFO: vacuuming "public.protein_hsps" INFO: "protein_hsps": found 63187655 removable, 11353611882 nonremovable row versions in 181253461 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 2814.17s/8479.90u sec elapsed 15451.26 sec. Total query runtime: 114969739 ms.
Here is the output from pgstattuple (before / after vacuum):
-- SELECT * FROM pgstattuple('protein_hsps'); -- -- yielded:
-- SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" -- FROM pg_class C LEFT JOIN pg_namespace N ON N.oid = C.relnamespace -- WHERE relname IN ('protein_hsps', 'protein_hsps_clustidx_on_origin', 'protein_hsps_pkey') -- -- yielded:
The only thing which I currently not understand is the pgstattuple output, which tells me that there is 0.2% free space left. Actually there are 7.3T (i.e., 71%) left on the device so this is either a wrong display or this value refers to something other than the free disk space on the drive.
Actually, it is the free space inside the table's files. It has nothing to do with your device (PostgreSQL doesn't know how much free space is available on your disks).