To: "Pavel Rotek" <pavel.rotek@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Toast space grows 
In-reply-to: <3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com> 
References: <3556c08c0803070035p6d8b4aa8m6318f54728f61022@mail.gmail.com>
	<47D10722.4080508@archonet.com>
	<3556c08c0803070129y6ffabf32i1c8bae445ec460e0@mail.gmail.com>
	<20080307065314.1248665b.wmoran@collaborativefusion.com>
	<3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com>
Comments: In-reply-to "Pavel Rotek" <pavel.rotek@gmail.com>
	message dated "Fri, 07 Mar 2008 13:59:46 +0100"
Date: Fri, 07 Mar 2008 08:33:19 -0500
Message-ID: <11848.1204896799@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Pavel Rotek" <pavel.rotek@gmail.com> writes:
> No i do not mean long running transactions... Update of log entry (update of
> row in dataaction) is performed in series of short transactions, but during
> short transaction there is a lot of change log value, flush, change log
> value ,flush ..... change log value, flush actions (flush means perform
> flush operation via JDBC driver). I'm not sure if this flush affects toast
> space... Maybe this is the reason.

You mean that you build up the 5MB log entry by adding a few lines at a
time?  That's going to consume horrid amounts of toast space, because
each time you add a few lines, an entire new toasted field value is
created.

If you have to do it that way, you'll need very frequent vacuums on this
table (not vacuum full, as noted already) to keep the toast space from
bloating too much.  And make sure you've got max_fsm_pages set high
enough.

If you can restructure your code a bit, it might be better to accumulate
log values in a short-lived table and only store the final form of a log
entry into the main table.

			regards, tom lane