pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: One tuple per transaction
Date: Tue, 15 Mar 2005 09:37:41 +0000
Message-ID: <4236ACE5.1040004@archonet.com> (raw)
In-Reply-To: <A66A11DBF5525341AEF6B8DE39CDE770088052@black.aprote.com>
References: <A66A11DBF5525341AEF6B8DE39CDE770088052@black.aprote.com>

Tambet Matiisen wrote:
>>
>>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.
>>
> 
> 
> But the dead index tuple will still be read from disk next time? Maybe
> really the performance loss will be neglible, but if most of tuples in
> your table/index are dead, then it might be significant.

When a block is read from disk, any dead tuples in that block will be 
read in. Vacuum recovers these.

> Consider the often suggested solution for speeding up "select count(*)
> from table" query: make another table rowcounts and for each of the
> original tables add insert and delete triggers to update row count in
> rowcounts table. Actually this is standard denormalization technique,
> which I use often. For example to ensure that order.total =
> sum(order_line.total).

This does of course completely destroy concurrency. Since you need to 
lock the summary table, other clients have to wait until you are done.

> Now, if typical inserts into your most active table occur in batches of
> 3 rows, in one transaction, then row count for this table is updated 3
> times during transaction. 3 updates generate 3 tuples, while 2 of them
> are dead from the very start. You effectively commit 2 useless tuples.
> After millions of inserts you end up with rowcounts table having 2/3 of
> dead tuples and queries start to slow down.
> 
> Current solution is to vacuum often. My proposal was to create new tuple
> only with first update. The next updates in the same transaction would
> update the existing tuple, not create a new. 

How do you roll back to a savepoint with this model?

--
   Richard Huxton
   Archonet Ltd



view thread (12+ messages)  latest in thread

Message-ID: <4236ACE5.1040004@archonet.com>
Permalink:  ../4236ACE5.1040004@archonet.com/
Also on:    postgresql.org/message-id/4236ACE5.1040004@archonet.com

 · 

reply

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: dev@archonet.com, t.matiisen@aprote.ee
  Subject: Re: One tuple per transaction
  In-Reply-To: <4236ACE5.1040004@archonet.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