Thread

1. One tuple per transaction

   Tambet Matiisen <t.matiisen@aprote.ee> — 2005-03-12T13:08:32Z

   Hi!
   
   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.
   
   If I'm correct, the dead tuples must be scanned also during table and
   index scan, so a lot of dead tuples slows down queries considerably,
   especially when the table doesn't fit into shared buffers any more. And
   as I'm in transaction, I can't VACUUM to get rid of those tuples. In one
   occasion the page count for a table went from 400 to 22000 at the end.
   
   All this made me wonder, why is new tuple created after every update?
   One tuple per transaction should be enough, because you always commit or
   rollback transaction as whole. And my observations seem to indicate,
   that new index tuple is created after column update even if this column
   is not indexed.
   
   One tuple per transaction would save a loads of I/O bandwidth, so I
   believe there must be a reason why it isn't implemented as such. Or were
   my assumptions wrong, that dead tuples must be read from disk?
   
     Tambet
   
   

2. Re: One tuple per transaction

   Josh Berkus <josh@agliodbs.com> — 2005-03-12T22:05:20Z

   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?
   
   -- 
   Josh Berkus
   Aglio Database Solutions
   San Francisco
   
   

3. Re: One tuple per transaction

   Qingqing Zhou <zhouqq@cs.toronto.edu> — 2005-03-14T01:41:30Z

   ""Tambet Matiisen"" <t.matiisen@aprote.ee> writes
   > Hi!
   >
   > 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.
   >
   > If I'm correct, the dead tuples must be scanned also during table and
   > index scan, so a lot of dead tuples slows down queries considerably,
   > especially when the table doesn't fit into shared buffers any more. And
   > as I'm in transaction, I can't VACUUM to get rid of those tuples. In one
   > occasion the page count for a table went from 400 to 22000 at the end.
   
   Not exactly. The dead tuple in the index will be scanned the first time (and
   its pointed heap tuple as well), then we will mark it dead, then next time
   we came here, we will know that the index tuple actually points to a uesless
   tuple, so we will not scan its pointed heap tuple.
   
   >
   > All this made me wonder, why is new tuple created after every update?
   > One tuple per transaction should be enough, because you always commit or
   > rollback transaction as whole. And my observations seem to indicate,
   > that new index tuple is created after column update even if this column
   > is not indexed.
   
   This is one cost of MVCC. A good thing of MVCC is there is no conflict
   between read and write - maybe some applications need this.
   
   A reference could be found here:
   
   http://www.postgresql.org/docs/8.0/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE
   
   >
   > One tuple per transaction would save a loads of I/O bandwidth, so I
   > believe there must be a reason why it isn't implemented as such. Or were
   > my assumptions wrong, that dead tuples must be read from disk?
   >
   >   Tambet
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 8: explain analyze is your friend
   >
   
   
   
   

4. Re: One tuple per transaction

   Hannu Krosing <hannu@tm.ee> — 2005-03-17T21:27:25Z

   On L, 2005-03-12 at 14:05 -0800, Josh Berkus wrote:
   > 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?
   
   Also, you could at least use a temp table for intermediate steps. This
   will at least save WAL traffic.
   
   -- 
   Hannu Krosing <hannu@tm.ee>