A Faster, Lightweight Trigger Function in C for PostgreSQL

We have been writing blog posts about how to write simple extensions in C language and a little more complex one by Ibrar which were well received by PostgreSQL user community. Then we observed that many PostgreSQL users create simple triggers for small auditing requirements, and then feel the pain of trigger on transactions. So we were discussing how simple/lightweight and faster a trigger function is when written in C. Generally, Trigger functions are written in high-level languages like PlpgSQL, but it has a higher overhead during execution and it can impact the transactions – and thereby application performance.

This blog post is an attempt to create a simple trigger function to address one of the common use-cases of triggers, which is to update auditing columns in a table.

In this post, we are going to introduce SPI (Server Programming Interface) functions for novice users. Towards the end of the blog, we share some of the quick benchmark results for understanding the benefits.

Example of Audit timestamp

Let’s proceed with taking up a case and assume that we have a table to hold transaction details. But auditing requirements say that there should be a timestamp on each tuple when the tuple is inserted and when it was last updated.

For demonstration purpose, let’s remove and trim the other columns and create a table with only 3 essential columns.

Developing Trigger Function

The trigger function can also be developed and packaged as an extension, which we discussed in s previous blog post here. So we are not going to repeat those steps here. The difference is that file names are named as “trgr” instead of “addme” in the previous blog. Makefile is also modified to refer “trgr” files. This need not be same as the function name “trig_test” in the C source detailed below.

In the end, the following files are available in the development folder:

The trgr.c is the main source files with the following content:

and trgr--0.0.1.sql  with the following content:

Now it is a matter of building, installing, and creating the extension.

In case you don’t want to develop it as an extension, you may compile it to generate a shared object file (.so) file. Copy the same to the library folder of PostgreSQL binaries, which on my Ubuntu laptop is : /usr/lib/postgresql/11/lib/, and then define the function. You can even specify the full path of the shared object file like this:

Using Trigger Function

Usage of trigger function is not different from regular PLpgSQL functions. You just need to attach the function to the table for all INSERT and UPDATE events.

Benchmarking

For a fair comparison with trigger function written in PLpgSQL, a similar function is created as follows:

The number of lines and the readability of the code is in favor of PLpgSQL. The development and debugging time required is much less.

Regarding the performance benchmarking, three cases are compared.

1. PostgreSQL client/application providing the audit timestamp, so that trigger can be avoided.
2. Trigger function in C language.
3. Trigger function in PLpgSQL.

Here are the performance numbers in milliseconds for 1 million bulk inserts, obviously a smaller number is better.

Caveats

1. The first case where there is no trigger on the database side, it takes less time. But the application and network need to take up the extra load, which is not considered in this test.
2. The C function is bit hardcoded with an attribute number like attnum = 3; and if we want a generic trigger function which looks for specific column name, we can use SPI_fnumber function like attnum = SPI_fnumber(tupdesc,"update_ts"); .  Such a generic trigger function can be used in multiple tables. Obviously, this involves more processing. Those lines are commented out in the source code. On repeated tests, the average time of execution increases to 1826.722 ms. Still, we can see that it is considerably faster than the PLpgSQL trigger function.

Discuss on Hacker News