Обсуждение: Does VACUUM ever free up any disk space?

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?

24/7 production databases cannot be locked for long periods
of time to run VACUUM FULL, but I do not want data files
growing indefinitely (any more than they need to) so I hope
the routine VACCUM ANALYSE will take care of this.

My pg is version 7.2.x but the question can apply to newer
versions as well if the functionality has changed.

Regards,
Chris.

--
Chris Miles
http://chrismiles.info/

On Thu, Sep 11, 2003 at 18:42:25 +0100,
  Chris Miles <chris_pg002@psychofx.com> wrote:
> 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?
>
> 24/7 production databases cannot be locked for long periods
> of time to run VACUUM FULL, but I do not want data files
> growing indefinitely (any more than they need to) so I hope
> the routine VACCUM ANALYSE will take care of this.

Vacuum full actually shrinks the file sizes. A normal vacuum just marks
free areas in the files so that they can be reused. If you regularly
do a normal vacuum your database should have a steady state size with
some of the disk space taken up by free space. If something unusual
happens and the database grows much bigger than it needs to be, you
can then use vacuum full to reclaim space. If your FSM setting is too
low, your normal vacuums won't mark all of the free space and this
can result in continual growth of the database files.

You also have to worry about index growth. In versions prior to 7.4
btree indexes would not reuse space when the index values were montonicly
increasing. This isn't a problem for everyone, but is for some. In 7.4
things work much better.

On Thu, 11 Sep 2003, Chris Miles wrote:

> 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?
>
> 24/7 production databases cannot be locked for long periods
> of time to run VACUUM FULL, but I do not want data files
> growing indefinitely (any more than they need to) so I hope
> the routine VACCUM ANALYSE will take care of this.
>
> My pg is version 7.2.x but the question can apply to newer
> versions as well if the functionality has changed.

A regular vacuum since 7.2 never actually frees up space, it simply marks
the space in the file as available for reuse.  Note that in many
circumstances this is actually better than freeing up the space, as it
allows the database to store date without having to extend and possibly
fragment the table.

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Thu, 11 Sep 2003, Chris Miles wrote:
>> I've read a lot where people recommend using VACUUM FULL
>> to free up disk space, especially after many updates/inserts.

> A regular vacuum since 7.2 never actually frees up space, it simply marks
> the space in the file as available for reuse.  Note that in many
> circumstances this is actually better than freeing up the space, as it
> allows the database to store date without having to extend and possibly
> fragment the table.

Regular vacuum *will* shorten the table's file if (a) there are some
completely-empty pages at the end, and (b) it can get an exclusive lock
on the table without blocking.  This might be a relatively rare
condition in a heavily-used table.  But "never actually frees up space"
is incorrect.

You're correct that regular vacuum is designed around the idea of
maintaining a steady-state file size rather than trying very hard to
give space back to the OS.

            regards, tom lane

chris_pg002@psychofx.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)