CODEWITHSUNDEEP
postgresql
user2793872
user2793872

Reputation: 319

What difference does it make to backup PostgreSQL before or after vacuum?

What is the difference between performing a PostgreSQL backup before vacuuming the DB compared to after vacuuming the DB?

Upvotes: 1

Views: 1585

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324501

If your backup is done with pg_dump then there'll no difference. It might be slightly faster if you had lots of dead space leading to a better visibility and freespace map after vacuum, but generally not much.

If your backup is done with pg_basebackup or at the filesystem level then a vacuum full will reduce backup size. A regular vacuum generally won't make any difference.

If you plan to regularly vacuum full, consider setting a fillfactor of lower than 100% on your tables. Otherwise you'll take a serious performance hit on all regularly updated tables after each vacuum full run as PostgreSQL has to expand the tables it just shrunk.

Upvotes: 5

Related Questions