Skip site navigation (1) Skip section navigation (2)

Re: for help!

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>,linweidong <wdlin(at)sis(dot)sh(dot)cn>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: for help!
Date: 2003-04-16 15:48:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> Well, you can always use the trick of putting an on insert / delete
> trigger on the table that maintains a single row table with the current
> count.  That way, whenever a row is added or removed, the count is
> updated.  this will slow down inserts and deletes a little, but TANSTAAFL.

BTW, I tested this for a client.   I found the performance penalty on inserts 
and updates to be:

-- For a single stream of intermittent updates from a single connection
   on an adequately powered server with moderate disk support (IDE Linux RAID)
   (100 inserts/updates per minute, with VACUUM every 5 minutes)
   PL/pgSQL Trigger:  20% penalty      C Trigger: 9-11% penalty

-- For 5 streams of inserts and updates at high volume on an overloaded
   server with moderate disk support (dual fast SCSI disks)
   (1000 inserts/updates per minute, vacuum every 5 minutes)
   PL/pgSQL Trigger: 65% penalty    C Trigger: 40% penalty

Please note that the effective performance penalty on inserts and updates was 
dramatically higher for large batches of updates than for small ones.

Josh Berkus
Aglio Database Solutions
San Francisco

In response to


pgsql-performance by date

Next:From: Andrew SullivanDate: 2003-04-16 15:53:12
Subject: Re: for help!
Previous:From: Andreas PflugDate: 2003-04-16 08:45:20
Subject: Re: Do Views offer any performance advantage?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group