Re: FW: index bloat

"David Esposito" <pgsql-general@esposito.newnetco.com> writes:
>> BTW, the tail of the VACUUM VERBOSE output ought to have
>> something about
>> overall usage of the FSM --- what does that look like?

> INFO:  free space map: 528 relations, 172357 pages stored; 170096 total
> pages needed
> DETAIL:  Allocated FSM size: 10000 relations + 1000000 pages = 6511 kB
> shared memory.

OK, so you are definitely not running out of FSM slots...

I spent some time this morning trying to reproduce the bloating
behavior, without any success.  I made a table with a plain "serial
primary key" column, and ran a test program that did

    insert 10000 rows
    update about 10% of the rows at random
    if more than 500000 rows, delete the oldest 10000
    vacuum
    repeat

which is intended to emulate your daily cycle with about one-tenth
as much data (just to keep the runtime reasonable).  I didn't see
any bloat at all: the index growth looked like

INFO:  index "t_pkey" now contains 450000 row versions in 1374 pages
INFO:  index "t_pkey" now contains 460000 row versions in 1404 pages
INFO:  index "t_pkey" now contains 470000 row versions in 1435 pages
INFO:  index "t_pkey" now contains 480000 row versions in 1465 pages
INFO:  index "t_pkey" now contains 490000 row versions in 1496 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1527 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1557 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1588 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1588 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1589 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages
INFO:  index "t_pkey" now contains 500000 row versions in 1590 pages

and it never grew any larger than that even in several hundred "days".

This test was against CVS tip, but I'm pretty certain the relevant
algorithms were all the same in 7.4.  So there is some important
aspect in which this test does not replicate the conditions your
index is seeing.  Can you think of any way that I've missed capturing
your usage pattern?

            regards, tom lane