Any difference in performance/compatibility of different languages in PostgreSQL?

Question

In nowadays the PostgreSQL offers plenty of procedural languages: pl/pgsql, pl/perl, etc

Are there any difference in the speed/memory consumption in procedures written in different languages?

Does anybody have done any test?

Is it true that to use the native pl/pgsql is the most correct choice?

How the procedure written in C++ and compiled into loadable module differs in all parameter w.r.t. the user function written with pl/* languages?

Answer 1

The correct choice depends on what exactly you're going to do.

In my experience, pl/pgsql has less overhead than say pl/perl, pl/python or pl/tcl, if what you want to do is easily expressde in pl/pgsql. For example, if you're doing database operations. However, the overhead you pay for example for pl/perl quickly comes back if you do something that perl is good at - such as for example string processing.

The one thing you have to think of in those cases are that the "bigger" languages like perl and python have a significantly higher startup cost than pl/pgsql - but that can easily be mitigated by using persistent connections.

Functions written in C will almost certainly be faster - and they'll use less memory if you can write code that is good at using little memory. However, the overheard of writing and maintaining them is usually a lot worse than the PLs - in particularly constructing and deconstructing tuples will take a lot more code than you think in any kind of complex functions.

The only PL style language I've found significantly slower than the others is pl/java, due to the JVM overhead. But I'm sure you can find cases where it's faster for some problems.

If you want to be sure of what's absolutely most efficient in your scenario, you need to develop a couple of your functions in each language and then benchmark those. But chances are it doesn't really matter and you can pick whichever language suits the task best from a development perspective rather than a performance one.