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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

run autovacuum more often.