Re: Recommendation to run vacuum FULL in parallel

From: Perumal Raj <perucinci@gmail.com>

So conclude the requirement here , The only way to parallelism is multiple script. And no need to do REINDEX exclusively.

Question : Do we need to consider  Table dependencies while preparing script in order to avoid table locks during vacuum full ?

 

We have a small bash script (see below) that get the list of tables and their sizes, sorted smallest to largest, and do “vacuum full” one at a time because (as someone else pointed out) this is very I/O intensive. That order also helps to ensure we finish because some of our installs are at the edge of running out of space (an issue we’re dealing with). I probably wouldn’t have a problem doing 2 at a time, but we do this in the middle of the night when activity is lowest and it only takes 1-2 hours, so we’re good with it. It sounds like you have a lot more data though.

 

You might also consider putting the data into different tablespaces which are spread over multiple disks to help I/O. If you can, use SSD drives, they help with speed quite a bit. 😊

 

Don’t worry about table dependencies. This is a physical operation, not a data operation.

 

HTH,

Kevin

 

    $PGPATH/psql -t -c "

        WITH s AS (SELECT nspname || '.' || relname AS TABLE_NAME, pg_total_relation_size(c.oid) AS total_bytes

                  FROM pg_class c

                  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

                  WHERE relkind = 'r' AND nspname NOT IN ( 'pg_catalog', 'information_schema' )

                  ORDER BY 2 )

        SELECT table_name FROM s

        " |

    while read t ; do echo "" ; echo $t; $PGPATH/vacuumdb -w -z -f -t $t ; done

###

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.