From: Pavel Rotek <pavel.rotek@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Toast space grows
Date: Fri, 7 Mar 2008 15:22:17 +0100
Message-ID: <3556c08c0803070622vb11c402q473f902df678ffa3@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0803071351200.20402@aragorn.flymine.org>
References: <3556c08c0803070035p6d8b4aa8m6318f54728f61022@mail.gmail.com>
<47D10722.4080508@archonet.com>
<3556c08c0803070129y6ffabf32i1c8bae445ec460e0@mail.gmail.com>
<20080307065314.1248665b.wmoran@collaborativefusion.com>
<3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com>
<11848.1204896799@sss.pgh.pa.us>
<Pine.LNX.4.64.0803071351200.20402@aragorn.flymine.org>
2008/3/7, Matthew <matthew@flymine.org>:
>
> > "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.
>
>
> On Fri, 7 Mar 2008, Tom Lane wrote:
> > 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.
>
>
> Moreover, what is the point of flushing data to Postgres without
> committing the transaction, if you're only going to overwrite the data
> later. You don't get any level of protection for your data just by
> flushing it to Postgres - you have to commit the transaction for that to
> happen. In my opinion, you should just be generating the log entry in
> memory entirely, and then flushing it in a transaction commit when it's
> finished, since you're obviously holding it all in memory all the time
> anyway.
Because i use kind of hibrid access to work with data in database (both
hibernate and plain JDBC queries shares the same connection) and when i want
to see data saved via hibernate in JDBC queries, i have to do flush of
hibernate session... :-(
> 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.
>
>
> Agreed, this is kind of the worst-case-scenario for table bloat.
>
> Matthew
>
> --
> Now the reason people powdered their faces back then was to change the
> values
> "s" and "n" in this equation here. - Computer science
> lecturer
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavel.rotek@gmail.com
Subject: Re: Toast space grows
In-Reply-To: <3556c08c0803070622vb11c402q473f902df678ffa3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox