5 ms·
You don't generally ever need to manually trigger them, they happen automatically when needed
by pg-gadfly 6y ago
You don't generally ever need to manually trigger them, they happen automatically when needed
- hu3 6y agoDoes it affect performance? I might not want VACUUM to trigger during high workload.
- pg-gadfly 6y agoSince a vacuum operation uses processor cycles, it's impossible to say no. Their wiki is pretty amazing source for postgres's operation: https://www.postgresql.org/docs/12/routine-vacuuming.html https://www.postgresql.org/docs/12/routine-vacuuming.html
- hans_castorp 6y agoYes it does, it makes things faster ;) A common misconception about (auto) vacuum is, that you need to turn it off, to avoid performance problems. The truth is: you need to make it more aggressive (= run it more frequently) if you have problems with the (additional) I/O load it generates.
- hu3 6y agoObviously I meant to ask if performance is affected during VACUUM. @pg-gadfly's link answered it for me: > VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions.
- deleted 6y ago[deleted]
- brianwawok 6y agoIf you do stuff like drop columns, you totally need to do a vacuum full or you are never getting back disk space...
- takeda 6y agoActually, you should avoid using vacuum full at all. What that operation does is creating a new table with contents of an old table then deleting the old one. This operation is also requesting an exclusive lock, so any write query will cause lockup. Another problem is that vacuum full compacts the database, it reduces database size on disk, but doesn't mean it makes database faster, often it might actually reduce performance. Vacuum full might be useful in scenarios where you want to reclaim space, because autovacuum was not tuned correctly and tables get out of hand, if you need to run vacuum full frequently you should tune autovacuum to run more frequently.
- brianwawok 6y agoGetting back space is real important. Otherwise you have runaway table growth that never ends. Not saying do it all the time, but it is important to use surgically. You can easily calculate wasted table space https://www.citusdata.com/blog/2017/10/20/monitoring-your-bloat-in-postgres/ https://www.citusdata.com/blog/2017/10/20/monitoring-your-bl... When it gets to be too big, it's a good time to do a vacuum full. I normally do it a few times a year on a few giant tables. Yes it locks up a table for a few minutes, but it is a big win and totally worth it.
- takeda 6y agoI'm not denying that VACUUM FULL is useful when things are out of hand. What I'm trying to say is that you probably don't have autovacuum tuned to run often enough (this is also what your link suggests)