Vacuuming Postgres database with a little disk space left

02 Sep 2019

When you are low on disk space, the regular --all --full vacuuming might fail. It is worth trying to free up space gradually.

Order by table size:

#!/bin/sh
DATABASE=mydatabasename

psql -tc "SELECT concat(table_schema, '.', table_name) FROM (SELECT nspname AS table_schema, 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') as sq ORDER BY total_bytes;" "${DATABASE}" | grep -v -e '^$' | while read tblname; do vacuumdb --full -e -j1 -d "${DATABASE}" -v -z -t "${tblname}"; done

Or order by the amount of dead tuples:

#!/bin/sh
DATABASE=mydatabasename

psql -tc 'SELECT ObjectName FROM (SELECT relname AS ObjectName ,pg_stat_get_live_tuples(c.oid) AS LiveTuples ,pg_stat_get_dead_tuples(c.oid) AS DeadTuples FROM pg_class c order by 3) as sq;' "${DATABASE}" postgres | while read tblname; do vacuumdb --full -e -j1 -d "${DATABASE}" -v -z -t "${tblname}"; done