On 2016/01/04 11:26 AM, Simon Slavin wrote: > On 4 Jan 2016, at 9:24am, Rowan Worth <rowanw at dugeo.com> wrote: > >> Of course this also mean that VACUUM takes even longer over NFS, so it's >> absolutely not worth running before every query. But we've seen benefit >> from using VACUUM as a maintenance step while the DBs are not in use. > That is a useful observation. I have seen maintenance routines (some run > monthly, some yearly) which include VACUUM as one of the things they do. > After taking a backup, of course !
Just to add to this whole discussion - improvement of data access after VACUUM is completely dependent on state of the database BEFORE the VACUUM was run. If the state was already optimal, the VACUUM would have very little to zero benefit. If the state was very fragmented** such as when a lot of deletes and inserts happen, then the benefits could be enormous from periodic VACUUMs - but it is never wise to VACUUM before each SELECT. In-process testing is the only thing that can really speak to the truth of assumed improvements. ** - Note that "fragmented" problems are not so much due to the fragmentation itself (which Simon pointed out is handled well by modern systems), but by the fact that a fragmented DB by definition contains a lot of unused space in necessarily more pages than needed, which in turn requires more reading than is needed, which costs time. VACUUM fixes this nicely. PS: Wishing a spectacular 2016 to all!
