Re: Almost happy ending (from "Data files became huge with no apparent reason" thread)

On Tue, Sep 03, 2002 at 12:39:01PM +0200, Dario Fumagalli wrote:
> Step by step procedure:
> 1) I stopped the postmaster and made sure no process was still on (ps aux)
> 2) As postgres user I started postgres -O -P -D path etc.
> 3) A prompt appeared. Here I typed reindex proj_store force (I'm trying
> to remember it since I'm not in the company the server is in).
> 4) Messages appeared stating indexes were being rebuilt (no errors shown).
> 5) I hit CTRL-D. Postgres exited.
> 6) I restarted the postmaster.

Should work but seems excessive.

> pg_operator_oprname_l_r_k_index | 1092613 | 0 | 0 | i | 7
> pg_proc | 1255 | 17231 | 17243 | r | 28
> pg_proc_oid_index | 17166 | 0 | 0 | i | 5
> pg_proc_proname_narg_type_index | 17169 | 0 | 0 | i | 20

Your list seems truncated?

> As you may see, *_pkey primary keys are BIG. They are the only ones I
> didn't dare to drop and re-create.
>
> Ex.
>
> products_pkey | 258540 | 0 | 0 | i | 1653
>
> where the entire table takes 66 pages, or, worse
>
> det_prod_dep_consumpt_pkey | 258764 | 0 | 0 | i | 3286
>
> where the entire table takes 21 pages.

What happens if you say "REINDEX INDEX products_pkey" or "REINDEX INDEX
det_prod_dep_consumpt_pkey". Do those numbers change?

What is the output of "VACUUM VERBOSE ANALYSE products".

> And "not easily"? I may make backups, go standalone, and do whatever
> evil you may think ;)
> BTW I have the scripts to re-create indexes or constraints and know how
> to hack them, since I'm the programmer in charge for all (sql, programs,
> db administration) for that company.

REINDEX should do it. Please provide the output of the vacuum command.

> It would be acceptable, instead, to have a database that is never
> optimized at 100% (i.e. it has "gaps" because of non full vacuums that
> add for about another almost FIXED 30%), but that NEVER, NEVER grows out
> of control. We may prepare a db maintenance plan that includes a full
> vacuum every 6 months.

Firstly, for 7.2 vacuum doesn't lock any tables, so you can run it as often
as you like. And why only every six months? Just do it daily. If your tables
are so small, it should take seconds to vacuum to whole database. The
default debian setup runs vacuum daily, as is recommended in the docs.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.