Re: Long-running and non-finishing VACUUM ANALYZE on large table

On 10/07/2015 01:53 AM, Tom Lane wrote:
> 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:

table_len    1484828352512  / 958853496832
tuple_count    11353611882  / 11353611882
tuple_len    874228114914 / 874228114914
tuple_percent    58.88 / 91.17
dead_tuple_count    63187655 / 0
dead_tuple_len    4810998304 / 0
dead_tuple_percent    0.32 / 0
free_space    495246133064 / 1872767456
free_percent    33.35 / 0.2


And the table sizes (before / after vacuum):

-- 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:

public.protein_hsps    1383 GB  / 893 GB
public.protein_hsps_clustidx_on_origin    499 GB  / 238 GB
public.protein_hsps_pkey    494 GB  / 238 GB


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.

Again, many thanks for the advice!

Kind regards,
Jan