Re: Does VACUUM ever free up any disk space?

chris_pg002(at)psychofx(dot)com (Chris Miles) writes:
> I've read a lot where people recommend using VACUUM FULL
> to free up disk space, especially after many updates/inserts.
>
> But does a regular VACUUM (or VACUUM ANALYSE) ever free up
> any space?

Yes, VACUUM surely can and does.

[extract from recent vacuum on a fairly busy system running 7.2.4]

NOTICE: --Relation pg_class--
NOTICE: Index pg_class_oid_index: Pages 11369; Tuples 173: Deleted 944.
CPU 2.60s/0.45u sec elapsed 3.36 sec.
NOTICE: Index pg_attribute_relid_attnam_index: Pages 12928; Tuples 4248: Deleted 1400.
CPU 3.85s/0.79u sec elapsed 5.37 sec.
NOTICE: Index pg_attribute_relid_attnum_index: Pages 4220; Tuples 4268: Deleted 1400.
CPU 1.27s/0.27u sec elapsed 2.14 sec.
NOTICE: Removed 1400 tuples in 24 pages.
CPU 0.00s/0.03u sec elapsed 0.37 sec.
NOTICE: Pages 295: Changed 0, Empty 0; Tup 4168: Vac 1400, Keep 0, UnUsed 1.
Total CPU 5.17s/1.13u sec elapsed 7.99 sec.
NOTICE: Truncated 295 --> 57 pages.
CPU 0.17s/0.02u sec elapsed 0.34 sec.
NOTICE: --Relation pg_class--

Apparently pg_class will need to get reindexed at some point, as there
are more pages than there are live tuples, but notice that this Plain
Old Vacuum truncated the table itself down from 295 pages to 57 pages.

ANALYZE has nothing to do with this; ANALYZE modifies statistics in
pg_statistic, which will usually have the effect of adding some
"garbage" to that table.
--
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)