Re: Unbounded (Possibly) Database Size Increase - Toasting

On Sat, 2002-05-11 at 11:24, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Was it not the case that lazy vacuum had problems freeing tuples that
> > have toasted fields ?
>
> News to me if so.
>
> regards, tom lane

It looks like this may in fact be the case.

I performed a number of tests using the previous setup, but shortening the row length and
using pg_attribute.attstorage to untoast the text field for some of the tests.

The difference is striking.

The behaviour of the untoasted case is pretty much as expected :
the database grows a bit and then stabilizes at some size.

However I could not get any size stabilization in the toasted case.

Here are (some) of my test results :

Fsm Siz |Threads|Toast |Init(M)|End (M)|Stable |Stable Time(h) |Run Time(h)
20000 | 2 |Y | 166 | 380 | N | - |17
60000 | 2 |Y | 166 | 430 | N | - |20
10000 | 2 |N | 162 | 235 | Y | 0.5 |1
20000 | 2 |N | 166 | 235 | Y | 0.5 |13
60000 | 2 |N | 166 | 235 | Y | 0.5 |13

legend :

Fsm Siz = max_fsm_pages
Threads = no. update threads
Toast = whether body field was toasted
Init = initial database size
End = final database size
Stable = whether database growth had stopped
Stable Time = when stable size was achieved
Run Time = length of test run (excluding initial database population)

Average vacuum time = 300s
Typical (1 thread) entire table update time = 2000s
Row length = 7.5K

The scripts I used are here :

http://homepages.slingshot.co.nz/~markir/tar/test/spin.tar.gz

At this point I am wondering about sending this in as a bug report - what do you think ?

regards,

Mark