This weekend I’ve played a bit with PostgreSQL extensions written in C.

A while ago I wrote a Python C extension for Smart to compare Debian package versions. Now I was trying to do something similar inside PostgreSQL, and thus ported the original Python C extension code to a PostgreSQL C extension. It enables queries like the following:

# SELECT 'Matched' WHERE deb_version_match('1.2', '<=', '2.0');
  ?column?
----------
 Matched

The implementation of the PostgreSQL C extension was quite straightforward, but I'm a bit disappointed by the performance of PL/PGSQL.

I've made tests using two environments. One of them is a PL/Python function executed as a trigger, which calls the original Python C extension and executes SQL back in PostgreSQL using the plpy module. The other is a PL/PGSQL function which uses the implemented PostgreSQL C extension directly.

Considering that the function logic consisted of one loop over a SELECT statement, a few tests, and an INSERT statement, I was expecting that the overhead introduced by going back and forth between the PostgreSQL state and the Python interpreter state would be a lot more noticeable when compared with PL/PGSQL executing an internal PostgreSQL function. Tests have shown about 10% of improvement roughly, when doing a similar logic over about 5000 items.

I'm not yet sure if the speed improvement pays off the limited debugging feedback provided by the PL/PGSQL interpreter on errors.