Re: Performance between triggers/functions written in C and PL/PGSQL

Ludwig,

>    Has anyone done performance comparison between
> triggers/functions in C vs. PL/PGSQL?

On simple ON UPDATE triggers that update an archive table, but are called many
times per minute, about 20:1 in favor of C triggers.  Partly that depends on
whether you load the C function as an external file or compile it into the
database.  The latter is, of course, faster by far less flexible.

Partly this is because C is fast, being a lower-level language.  Partly this
is because the PL/pgSQL parser is in *desperate* need of an overhaul, as it
was written in a hurry and has since suffered incremental development.

>    What are the drawbacks of functions written using
> C?

Writing C is harder.  Gotta manage your own memory.  Plus a badly written C
function can easily crash Postgres, whereas that's much harder to do with
PL/pgSQL.

Usually I just write the original Trigger in PL/pgSQL, test & debug for data
errors, and then farm it out to a crack C programmer to convert to C.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco