> 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,
probablyyou need to set max_fsm_pages to: 8*1024*1024*1024/8192 = 1048576. It will need about 7MB more shmem, but
itwould 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