Re: Vacuum Full

On Tue, Mar 31, 2009 at 1:20 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Tue, Mar 31, 2009 at 10:04 AM, Rafael Domiciano
> <rafael(dot)domiciano(at)gmail(dot)com> wrote:
> > Hello People,
> >
> > I have some doubts about Vacuum Full. There We go:
> > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> > "dead space" on the disc, and reorganize the relation at the physical
> level?
> > If it's true, so doing this may speed up select's, while the Postgres
> will
> > going to do less hit in the disc. I'm right?
>
> Generally speaking, vacuum full should not normally be needed.
> Regular vacuuming should free up enough free space that the table
> reaches an equilibrium where it has some small percentage of available
> space (5 to 15% or so) and stay there.

So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough
for Maintenance Base?
One more question, If I understood Vacuum Full it's similar to Defrag Tool
like Defrag Windows. So, like defraging Windows, it's speed up a little (the
disc is going to read the blocks faster) , Vacuuming Full Postgres should
have the same behavior?!

>
>
> > 2) Doing only Vacuum Analyze I have a enlargement of the parameter
> > max_fsm_pages. Does it have any impact in the operation?
> > Should I increment the value in the conf to be so large than the Vacuum
> > Verbose shows me?
>
> If the needed fsm settings need to keep increasing then something is wrong.

The fsm_pages values incresead from 120000 to 320000, and now is around that
every day.
On more question, the postgres.conf max_fsm_pages is set to 150000, and
every vacuum hint me to increase this parameter. Does it have any impact in
the normal operation of Postgres?

>
>
> > 3) There are differences in performace of Vacuum Full between versions
> 8.1.4
> > and 8.3.7?
> > As soon as possible we are going to migrate the Postgres to 8.3.7, just
> > waiting the finish of the tests of the software in the new version.
>
> Yeah, 8.3 is faster.
>
> > 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or
> Reindex
> > and Vacuum?
> > Running Vacuum I have a Index Bloat, right?! So I have to run Reindex
> afet
> > Vacuum?!
> > If true I'll change the script to make first Vacuum and then Reindex.
>
> yep, vacuum full, then reindex.
>
> > 5) Does have any way to see how much is left to finish vacuum?
>
> Not that I know of.
>
> I notice you don't mention autovacuum. Are you running it? And if
> not, why not?

No, I'm not running autovacuum in the 8.1, but in the 8.3 I'm going to
autovacuum by default.

Thnks for Response