Unleashing PostgreSQL's Potential: Easy C Extension Development for Beginners! Part-2

In the previous part we learned how to develop a simple C extension for our postgreSQL database. But now the question arises, why even bother making extensions especially when postgres has certain built-in functions or operators that can do the same task as we did.

Well, one reason why you might think of doing this would be for performance reasons. Allow me to elaborate.

In part 1 we created a subtraction function that took in two arguments are subtracted the second arg from the first arg. Simple enough!

But we could have done this in two other ways. One simply using the basic SQL - operator and the other making a PLpgSQL function inside our database that does the work for us. Let us look at the two approaches.

PLpgSQL function

Inside our postgreSQL database we can run this code

CREATE FUNCTION submepl(a integer, b integer)
 RETURNS integer
as $$ 
BEGIN
  return a-b;
END;
$$ LANGUAGE plpgsql;

This will create a function that does exactly the same thing that our C function did i.e subtract two numbers.

SQL operator

This is self-explanatory we simply subtract numbers using the - operator. For example
SELECT 10 - 5;

Benchmark

Now let us consider our function vs the SQL operator and the PL function call way. In order to track the time we will use the time function in linux and run each methodology 1 million times!

Needless to say, the results are as follows:

The operator and the C function took almost the same time in execution. The PL function on the other hand, took way more time:

And this was just for a simple basic function that subtract. This would only increase as more complex functions are formed.

In short, if performance is your demand then extensions are your friend!