Trigger Overhead (Part 2)

I found a bit more time dig into timing of triggers and their overhead so I wanted to see how much overhead the choice of procedural language affected performance. I followed the same testing methodology from my original trigger test. For this test I created an empty trigger in the following languages:


CREATE FUNCTION empty_trigger() RETURNS trigger AS $$
$$ LANGUAGE plpgsql;


#include "postgres.h"
#include "commands/trigger.h"
Datum empty_c_trigger(PG_FUNCTION_ARGS);
 TriggerData *tg;
 HeapTuple ret;
tg = (TriggerData *) (fcinfo->context);
 if (TRIGGER_FIRED_BY_UPDATE(tg->tg_event))
 ret = tg->tg_newtuple;
 ret = tg->tg_trigtuple;
return PointerGetDatum(ret);


CREATE FUNCTION empty_python_trigger() RETURNS trigger AS $$
$$ LANGUAGE plpythonu;


CREATE FUNCTION empty_perl_trigger() RETURNS trigger AS $$
$$ LANGUAGE plperl;


CREATE FUNCTION empty_tcl_trigger() RETURNS trigger AS $$
 return [array get NEW]
$$ LANGUAGE pltcl;


package org.postgresql.pljava;
import java.sql.SQLException;
import java.sql.ResultSet;
import org.postgresql.pljava.TriggerData;
import org.postgresql.pljava.TriggerException;
public class TriggerTest {
 static void test(TriggerData td) throws SQLException {
 ResultSet _new = td.getNew();


CREATE FUNCTION empty_v8_trigger() RETURNS trigger AS $$
 return NEW;


CREATE FUNCTION empty_r_trigger() RETURNS trigger AS $$
$$ LANGUAGE plr;

All of the triggers essentially return NEW so we’re basically measuring the overhead starting up the trigger function. I then timed inserting 100,000 rows with the triggers in place and compared them to inserting into a table without a trigger. Some of the timings that I found were obvious such as C being the fastest, but others were pretty surprising.

Some of the bigger things that I noticed that out of the 3 built-in higher level languages, Python has much less overhead than Perl and TCL.

The other notable point was how little overhead PL/Java had compared to the other languages. PL/Java only had more overhead than C, PL/pgSQL and PL/Python.

The moral of the story is that when writing triggers some choices matter a lot. If you’re writing a simple trigger that just ensures a column equals the current timestamp, don’t write in PL/v8 just because it cool. Use PL/pgSQL for the simple things and save the other languages for your more complex logic where the overhead of starting them up won’t be noticed.

  1. FrankFrank05-22-2014

    What is the configuration setting for shared_preload_libraries? I have no idea if it would make a difference, but it worth to take look at it.

    • Jim MlodgenskiJim Mlodgenski05-23-2014

      I tried setting shared_prelaod_libraries for PL/R and it made no difference. Since the test opened a single connection and then inserted 100,000 the overhead of loading the library on the first insert quickly becomes insignificant in relation to the time of the entire test.

Leave a Reply

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

/* ]]> */