Re: One tuple per transaction

> -----Original Message-----
> From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
> Sent: Sunday, March 13, 2005 12:05 AM
> To: Tambet Matiisen
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] One tuple per transaction
>
>
> Tambet,
>
> > In one of our applications we have a database function, which
> > recalculates COGS (cost of good sold) for certain period. This
> > involves deleting bunch of rows from one table, inserting
> them again
> > in correct order and updating them one-by-one (sometimes one row
> > twice) to reflect current state. The problem is, that this
> generates
> > an enormous amount of tuples in that table.
>
> Sounds like you have an application design problem ... how
> about re-writing
> your function so it's a little more sensible?
>

I agree, that I have violated the no 1 rule of transactions - don't make
the transaction last too long. But imagine a situation, where a table is
updated twice in transaction. Why? Perhaps programmer felt, that the
code is more modular in this way. Now if you have tons of those
transactions, the I/O throughput is twice as big as it could be, because
every transaction creates two tuples instead of one. One tuple per
transaction could allow the programmer to keep his modular code and
benefit from the increased performance.

Tambet