Use of VACUUM / REINDEX - Mailing list pgsql-general

From Patrick Fiche
Subject Use of VACUUM / REINDEX
Date
Msg-id 85058ADF852DD5118FD50002A528A5B6079A88@SERVEUR
Whole thread Raw
List pgsql-general
I think I don't understand how VACCUM is working...
I have an empty table ( many inserts and deletes were done but now it's
empty ).

This table is called D_FLUX and has 3 indexes : pk_d_flux, sk1_d_flux,
sk2_d_flux

When I look in the pg_class table here is what I get

relname        relpages    reltuples
pk_d_flux    5892        0
sk1_d_flux    5883        0
sk3_d_flux    4418        0
d_flux        0        0

As you can see, indexes are using space even with no data.

So I run
VACUUM FULL VERBOSE D_FLUX

NOTICE:  --Relation d_flux--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Index pk_d_flux: Pages 5892; Tuples 0.
    CPU 0.18s/0.03u sec elapsed 7.81 sec.
NOTICE:  Index sk1_d_flux: Pages 5883; Tuples 0.
    CPU 0.19s/0.01u sec elapsed 11.95 sec.
NOTICE:  Index sk3_d_flux: Pages 4418; Tuples 0.
    CPU 0.17s/0.03u sec elapsed 9.86 sec.

In pg_class table, there is no modification and the files ( indicated by
relfilenodes ) are some Mb large...

The only way to have my indexes emptied is to use REINDEX D_FLUX

Is it the correct way of working ?

And if it's the case, I create and drop many temporary tables and it's just
like the indexes pg_attribute_relid_attnam_index and
pg_attribute_relid_attnum_index are growing too...
The REINDEX of these indexes need FORCE flag because they are system indexes
and to do so, I have to launch postgres with -O -P options....
So I have to stop the exploitation regularly and it's not acceptable...
Is there any other solution ?

My version is : 7.2.1

Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18



pgsql-general by date:

Previous
From: "Shridhar Daithankar"
Date:
Subject: Re: [HACKERS] Postgres-based system to run .org registry?
Next
From: Neil Fraser
Date:
Subject: Corrupt database