0

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

2 Answers 2

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.

1
  • Thank you for your explanation.
    – Titus
    Jul 20, 2023 at 8:04
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.