Suprising gained disk space after backup & restore PostgreSQL databases

Question

We backed up, deleted and restored several databases. Surprisingly, the databases occupy 20% less storage space afterwards. Have we lost data or what could have happened here? Autovacuum runs regularly.

Example:

df -h /var
pg_dump -Fc -f <file> <database>
dropdb <database>
createdb <database>
pg_restore -d <database> <file>
df -h /var

Answer 1

You had some bloated tables or indexes in the original database.

To diagnose bloat, use the pgstattuple extension.

To get rid of bloat, use VACUUM (FULL).

To avoid bloat, keep your transactions short, tune autovacuum to be fast enough and avoid mass deletions.

• Titus: Thank you for your explanation.

Answer 2

The default setting of autovacuum_vacuum_scale_factor is 0.2, so 20% empty space is entirely unremarkable when autovacuum is running as designed.