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