Re: [PATCHES] Big number of "unused" pages as reported by

Hello!

On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote:

> This question should not be posted to -patches, changed accordingly.
>
> What happens if you go 'VACUUM VERBOSE FULL goods;'?

Oh, big thanx!
But 'VACUUM VERBOSE FULL goods;' didn't work, only 'VACUUM FULL VERBOSE
goods;' did.:)

I make a guess I've got this due to parallel running of a program making
bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big
number of unused pages in such a case. LOCK TABLE?

>
> Your on-disk files won't shrink or have unused tuples removed unless you
> VACUUM FULL. The problem with doing VACUUM FULL is that it totally locks
> the whole table while it's running, meaning no-one can use the table. This

This can't scare people whom had dealt with 6.x.;)
Only if "We scare because we care"...=)

> is bad in production environments, so it's not the default. Bear in mind
> that postgres will re-use the unused portion of the table as you add new
> tuples...

Yes, as an ole MUMPSter I did catch this very well some times ago.=)

>
> Chris
>
> > Some time ago I've got troubles with performance of my PG.
> > After investigation I had found that the most probable reason was the big
> > number of "unused" pages. Below follows what VACUUM reported:
> >
> > =======================
> > vacuum verbose goods;
> > NOTICE: --Relation goods--
> > NOTICE: Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep
> > 0, UnUsed 465938.
> > =======================
> > select count(*) from goods;
> > count
> > -------
> > 16157
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Yep! Suggest to add this as well as that typical mistake with
LANGUAGE/HANDLER (plpgsql.so I mean).:-)

--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg(at)center-f1(dot)ru(dot)
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.