One of the great features of PostgreSQL is its extendability. My colleague and senior PostgreSQL developer Ibar has blogged about developing an extension with much broader capabilities including callback functionality. But in this blog post, I am trying to address a complete novice user who has never tried but wants to develop a simple function with business logic. Towards the end of the blog post, I want to show how lightweight the function is by doing simple benchmarking which is repeatable and should act as a strong justification for why end-users should do such development.
Generally, PostgreSQL and extension developers work on a PostgreSQL source build. For a novice user, that may not be required, instead, dev/devel packages provided for the Linux distro would be sufficient. Assuming that you have installed PostgreSQL already, the following steps can get you the additional development libraries required.
On Ubuntu/Debian
1 | $ sudo apt install postgresql-server-dev-11 |
On RHEL/CentOS
1 | sudo yum install postgresql11-devel |
The next step is to add a PostgreSQL binary path to your environment, to ensure that pg_config is there in the path. In my Ubuntu laptop, this is how:
1 | export PATH=/usr/lib/postgresql/11/bin:$PATH |
Above mentioned paths may vary according to the environment.
Please make sure that the pg_config is executing without specifying the path:
1 | $ pg_config |
PostgreSQL installation provides a build infrastructure for extensions, called PGXS, so that simple extension modules can be built simply against an already-installed server. It automates common build rules for simple server extension modules.
1 2 | $ pg_config --pgxs /usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk |
Now let’s create a directory for development. I am going to develop a simple extension addme with a function addme to add 2 numbers.
1 | $ mkdir addme |
Now we need to create a Makefile which builds the extension. Luckily, we can use all PGXS macros.
1 2 3 4 5 6 | MODULES = addme EXTENSION = addme DATA = addme--0.0.1.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) |
MODULE specifies the shared object without file extension and EXTENSION specifies the name of the extension name. DATA defines the installation script. The reason for –0.0.1 specifying in the name is that I should match the version we specify in the control file.
Now we need a control file addme.control with the following content:
1 2 3 4 | comment = 'Simple number add function' default_version = '0.0.1' relocatable = true module_pathname = '$libdir/addme' |
And we can prepare our function in C which will add 2 integers:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | #include "postgres.h" #include "fmgr.h" PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(addme); Datum addme(PG_FUNCTION_ARGS) { int32 arg1 = PG_GETARG_INT32(0); int32 arg2 = PG_GETARG_INT32(1); PG_RETURN_INT32(arg1 + arg2); } |
At this stage, we have only 3 files in the directory.
1 2 | $ ls addme.c addme.control Makefile |
Now we can make the file:
1 | $ make |
For installing the extension, we need a SQL file with create function. This SQL file name should be the same as the one we specified in DATA parameter in the Makefile, which is addme–0.0.1.sql
Add the following content into this file:
1 2 3 | CREATE OR REPLACE FUNCTION addme(int,int) RETURNS int AS 'MODULE_PATHNAME','addme' LANGUAGE C STRICT; |
And install the extension:
1 | $ sudo make install |
Now we can proceed to create the extension and test it:
1 2 3 4 5 6 7 | postgres=# create extension addme; CREATE EXTENSION postgres=# select addme(2,3); addme ------- 5 (1 row) |
Just like any function, we can use it in queries against multiple tuples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# select 7||'+'||g||'='||addme(7,g) from generate_series(1,10) as g; ?column? ---------- 7+1=8 7+2=9 7+3=10 7+4=11 7+5=12 7+6=13 7+7=14 7+8=15 7+9=16 7+10=17 (10 rows) |
Performance Benchmarking
Now it is important to understand the performance characteristics calling a C function in extension. For comparison, we have two options like:
1. ‘+’ operator provided by SQL like select 1+2;
2. PLpgSQL function as below
1 2 3 4 5 6 7 | CREATE FUNCTION addmepl(a integer, b integer) RETURNS integer as $$ BEGIN return a+b; END; $$ LANGUAGE plpgsql; |
For this test/benchmark, I am going to call the function for 1 million times!
SQL + operator
1 | time psql -c "select floor(random() * (100-1+1) + 1)::int+g from generate_series(1,1000000) as g" > out.txt |
C function call
1 | $ time psql -c "select addme(floor(random() * (100-1+1) + 1)::int,g) from generate_series(1,1000000) as g" > out.txt |
PL function call
1 | $ time psql -c "select addmepl(floor(random() * (100-1+1) + 1)::int,g) from generate_series(1,1000000) as g" > out.txt |
I have performed the tests 6 times for each case and tabulated below.
Test Run
As we can see, the performance of Built in ‘+’ operator and the custom C function in the extension takes the least time with almost the same performance. But the PLpgSQL function call is slow and it shows considerable overhead. Hope this justifies why those functions, which are heavily used, need to be written as a native C extension.
PLpgSQL is designed to be glue for SQL queries – when it is used, then it is pretty fast. Your example is typical worst case and good example when don’t use plpgsql. SQL function is fast due inlining – and when inlining is possible, then it is good case for it.
Your C example can be enhanced to use buildin function for sum of two integers instead C operator. For example, the overflow is not tested and solved in your C function.
I agree with you. PLpgSQL as a gluing language has much more scope. Just execution speed many not be a good comparison aswell. Other factors like speed of development and debugging, protection from memory leaks etc are more important from organizations.
In this post, I was trying to address completely novice user who never tried anything on C extension side.
Thank you for great feedback and I shall try to enhance that in upcoming posts.
cool.
sometime we need C extension for special function/logic that use in plpgsql.
Yes Agree. Thank you for your feedback
Some questions: Do you use an IDE for build and debug? Which one? Also, how do you debug a function that is getting called from Postgres?
your comment mention create folder but does mention in what folder.