Re: Slow SELECT -> Growing Database

> The problem isn't in the select.
>
> I realize that my database is growing and growing.
>
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
>
> Track the problem:
>
> # df -h /var/lib/pgsql -> 7.8 GB (I create this database 4 month's ago)
>
> # pg_dump dbnane > dbname.dump
>
> # dropdb dbname
>
> # createdb dbname
>
> # psql dbaname < dbname.dump
>
> # df -h /var/lib/pgsql -> 140 M
>
> I don't understand why the database is growing????
> And is still growing.
> I make a vacuum -z -d dbname everyday (I checked if it really runs).

You have never mentioned PostgreSQL verison you are using, so...

(1) if that's 7.2.x, you need to slightly increase FSM parameters in
postgresql.conf. As you have ~8GB database, probably you need to
set max_fsm_pages to: 8*1024*1024*1024/8192 = 1048576. It will
need about 7MB more shmem, but it would not be too much for modern
PC.

(2) if that's 7.1.x, or earlier, you need to vacuum and reindex more
frequently.
--
Tatsuo Ishii