Re: How to avoid database bloat

> > 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.
>
> First thing I'd suggest is to get a more detailed idea of exactly
> what is bloating --- which tables/indexes are the problem?

I think the most problematic table is this one. After vacuum full/reindex
it was 20MB in size now (after 6 hours) it is already 70MB and counting.

vacuum verbose output below. msg_id is integer, next_retry - timestamp,
recipient - varchar(20). max_fsm_pages = 200000. Another table has foregn
key which referenced msg_id in this one.

Thanks,

Mindaugas

$ vacuumdb -v -z -U postgres -t queue database
INFO: vacuuming "queue"
INFO: index "queue_msg_id_pk" now contains 110531 row versions in 5304
pages
DETAIL: 31454 index row versions were removed.
95 index pages have been deleted, 63 are currently reusable.
CPU 0.03s/0.07u sec elapsed 2.50 sec.
INFO: index "queue_next_retry" now contains 110743 row versions in 3551
pages
DETAIL: 31454 index row versions were removed.
1163 index pages have been deleted, 560 are currently reusable.
CPU 0.04s/0.06u sec elapsed 4.93 sec.
INFO: index "queue_recipient_idx" now contains 111596 row versions in 1802
pages
DETAIL: 31454 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.05u sec elapsed 0.16 sec.
INFO: "queue": removed 31454 row versions in 1832 pages
DETAIL: CPU 0.00s/0.01u sec elapsed 0.27 sec.
INFO: "queue": found 31454 removable, 110096 nonremovable row versions in
9133 pages
DETAIL: 119 dead row versions cannot be removed yet.
There were 258407 unused item pointers.
0 pages are entirely empty.
CPU 0.12s/0.25u sec elapsed 8.20 sec.
INFO: analyzing "queue"
INFO: "queue": scanned 3000 of 9133 pages, containing 34585 live rows and
1808 dead rows; 3000 rows in sample, 105288 estimated total rows
VACUUM