3 ms·
Can someone with knowledge please explain what the use cases are for this vs. just letting autovacuum run?
by oa335 4y ago
Can someone with knowledge please explain what the use cases are for this vs. just letting autovacuum run?
- lfittl 4y agoAutovacuum essentially runs a regular VACUUM, which helps you prevent new table bloat. However, to avoid locking issues, it does not fully rewrite the table, so it does not remove existing table bloat. If you want to remove existing table bloat you either need a VACUUM FULL (takes an exclusive lock, impacting your regular workload thus autovacuum doesn't do it), or use an extension that does an online version of that, i.e. pg_repack or pg_squeeze. Edit: Also, to clarify, with table bloat in this context I mean pages in the table that are not actually needed, but were added to the table previously to store data. Existing pages couldn't be used because rows were still marked as dead, i.e. autovacuum hadn't yet gotten around to clean them up before new data came in.
- dminor 4y agoThere's a use case to replace CLUSTER, which requires downtime and needs to be rerun for new/updated data.
- samokhvalov 4y agoThese tools remove bloat. Autovacuum removes dead tuples. They are often confused, but dead tuple and bloat are different though connected concepts. A lot of accumulated dead tuples (row versions marked dead but still present occupying space) are converted to bloat (empty space, gaps) when VACUUM cleans them up.