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: 200304160848.36431.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Scott,

> 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

Responses

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-2014 The PostgreSQL Global Development Group