Re: Postgres performance slowly gets worse over a month

My apologies, to all that told me that the reindex was the solution to
prevent the grouth of the database.

I had a script that i thought that would reindex my database, but wasn't
true.

I figure out ,that the grouth of my database was due to the indexes with
the following sql command:

dbname=# select relname, relpages,reltuples from pg_class order by
relpages desc limit 50;
relname | relpages | reltuples
---------------------------------+----------+-----------
sms_recv_unique_idclimsgidclien | 55800 | 39259
sms_recv_pkey | 39810 | 38948
sms_send_pkey | 22828 | 52048

The relpages * 8 Kbytes, gives =~ the disk space used by the
index/table (correct me if i'm wrong). As we can see from the query
result the disk space used by the indexes is very high.

After doing the reindex of the tables, that values droped down, as well
as the space used by the database.

Query result after reindex:

dbname=# select relname, relpages,reltuples from pg_class order by
relpages desc limit 50;

relname | relpages | reltuples
---------------------------------+----------+-----------
sms_recv_unique_idclimsgidclien | 222 | 40581
sms_recv_pkey | 174 | 40581
sms_send_pkey | 430 | 54004

As a conclusion, in databases with high turn-around the use of reindex
is imperative, combined with vacuums.

Thanks to all,

M.P.Garcia

---------------------------------------------------------------------

Do not forget to reindex the db after the delete, index's do not
manage them selves(if I remember correctly). The index will continue
to grow until it eats your file system, as it did with me.

By Marc Spitzer
---------------------------------------------------------------------

On Thu, 2002-07-25 at 01:17, Marcos Garcia wrote:
> Since this conversation had started, the presented solutions for the
> disk space (disk space of tables and indexes) problem were:
>
> - reindex -> no space recovered
>
> - frequent vacuums -> some space recovered, meanwhile the database
> keeps growing
>
> - vacuumm full -> some space recovered, meanwhile the database keeps
> growing, quite similar to simple vacuum. we have also to keep in mind,
> that the option "full", makes locks to the tables. Therefore, the
> services around the database locks too, and with the growing of the
> database the time spent for "vacumm full" increases, as well as the
> downtime of the services around the database.
>
>
> So, my doubts are:
>
> - There's nothing we can do to avoid the growth of the database, only
> slow down that growth.
>
> - We, application developers that use postgresql databases have to
> count with this problem.
>
> Sorry if i'm being a little rude, but i'm in a real trouble.
>
> Thanks in advance,
>
>
> M.P.Garcia
> `
> end
--
M.P.Garcia
PT Inovação, SA
Serviços e Redes Móveis
Rua José Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253 - Fax: 234 424 160
E-mail: marcos-p-garcia(at)ptinovacao(dot)pt