Re: How to avoid database bloat

Mindaugas Riauba wrote:
>   Hello,
>
>   Our database increases in size 2.5 times during the day.
> What to do to avoid this? Autovacuum running with quite
> aggressive settings, FSM settings are high enough.
>
>   Database size should be more or less constant but it
> has high turnover rate (100+ insert/update/delete per second).
>
>   Below is "du -sk" of database dir during the day. On 4:05
> full vacuum+reindex runs and database size is once again
> reduced.
>
>   Thanks,
>
>   Mindaugas
>
> Tue May 31 11:00:01 EEST 2005
> 533808  /ora/pgsql/base/465436/
> Tue May 31 11:30:01 EEST 2005
> 567344  /ora/pgsql/base/465436/
> Tue May 31 12:00:01 EEST 2005
> 578632  /ora/pgsql/base/465436/
> Tue May 31 12:30:01 EEST 2005
> 586336  /ora/pgsql/base/465436/
> Tue May 31 13:00:01 EEST 2005
> 594716  /ora/pgsql/base/465436/
> Tue May 31 13:30:01 EEST 2005
> 604932  /ora/pgsql/base/465436/
> Tue May 31 14:00:01 EEST 2005
> 613668  /ora/pgsql/base/465436/
> Tue May 31 14:30:01 EEST 2005
> 625752  /ora/pgsql/base/465436/
> Tue May 31 15:00:01 EEST 2005
> 637704  /ora/pgsql/base/465436/
> Tue May 31 15:30:01 EEST 2005
> 649700  /ora/pgsql/base/465436/
> Tue May 31 16:00:01 EEST 2005
> 657392  /ora/pgsql/base/465436/
> Tue May 31 16:30:02 EEST 2005
> 668228  /ora/pgsql/base/465436/
> Tue May 31 17:00:01 EEST 2005
> 676332  /ora/pgsql/base/465436/
> Tue May 31 17:30:01 EEST 2005
> 686376  /ora/pgsql/base/465436/
> Tue May 31 18:00:01 EEST 2005
> 694080  /ora/pgsql/base/465436/
> Tue May 31 18:30:02 EEST 2005
> 705876  /ora/pgsql/base/465436/
> Tue May 31 19:00:01 EEST 2005
> 713916  /ora/pgsql/base/465436/
> Tue May 31 19:30:01 EEST 2005
> 725460  /ora/pgsql/base/465436/
> Tue May 31 20:00:01 EEST 2005
> 733892  /ora/pgsql/base/465436/
> Tue May 31 20:30:01 EEST 2005
> 745344  /ora/pgsql/base/465436/
> Tue May 31 21:00:01 EEST 2005
> 753048  /ora/pgsql/base/465436/
> Tue May 31 21:30:02 EEST 2005
> 768228  /ora/pgsql/base/465436/
> Tue May 31 22:00:01 EEST 2005
> 804796  /ora/pgsql/base/465436/
> Tue May 31 22:30:01 EEST 2005
> 858840  /ora/pgsql/base/465436/
> Tue May 31 23:00:02 EEST 2005
> 902684  /ora/pgsql/base/465436/
> Tue May 31 23:30:01 EEST 2005
> 939796  /ora/pgsql/base/465436/
> Wed Jun  1 00:00:02 EEST 2005
> 990840  /ora/pgsql/base/465436/
> Wed Jun  1 00:30:11 EEST 2005
> 1005316 /ora/pgsql/base/465436/
> Wed Jun  1 01:00:02 EEST 2005
> 1011408 /ora/pgsql/base/465436/
> Wed Jun  1 01:30:01 EEST 2005
> 1010888 /ora/pgsql/base/465436/
> Wed Jun  1 02:00:01 EEST 2005
> 1010872 /ora/pgsql/base/465436/
> Wed Jun  1 02:30:01 EEST 2005
> 1010784 /ora/pgsql/base/465436/
> Wed Jun  1 03:00:02 EEST 2005
> 1003260 /ora/pgsql/base/465436/
> Wed Jun  1 03:30:02 EEST 2005
> 1003372 /ora/pgsql/base/465436/
> Wed Jun  1 04:00:01 EEST 2005
> 1003380 /ora/pgsql/base/465436/
> Wed Jun  1 04:30:01 EEST 2005
> 426508  /ora/pgsql/base/465436/
> Wed Jun  1 05:00:01 EEST 2005
> 429036  /ora/pgsql/base/465436/
> Wed Jun  1 05:30:01 EEST 2005
> 432156  /ora/pgsql/base/465436/
> Wed Jun  1 06:00:01 EEST 2005
> 433332  /ora/pgsql/base/465436/
> Wed Jun  1 06:30:01 EEST 2005
> 435052  /ora/pgsql/base/465436/
> Wed Jun  1 07:00:02 EEST 2005
> 439908  /ora/pgsql/base/465436/
> Wed Jun  1 07:30:01 EEST 2005
> 450144  /ora/pgsql/base/465436/
> Wed Jun  1 08:00:01 EEST 2005
> 471120  /ora/pgsql/base/465436/
> Wed Jun  1 08:30:02 EEST 2005
> 490712  /ora/pgsql/base/465436/
> Wed Jun  1 09:00:01 EEST 2005
> 501652  /ora/pgsql/base/465436/
> Wed Jun  1 09:30:01 EEST 2005
> 530128  /ora/pgsql/base/465436/
> Wed Jun  1 10:00:01 EEST 2005
> 541580  /ora/pgsql/base/465436/
> Wed Jun  1 10:30:01 EEST 2005
> 571204  /ora/pgsql/base/465436/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>


run autovacuum more often.