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