PostgreSQL procedural languages overhead (plpython / plsql / pllua...)

Question

I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks.

1. How does they compare to builtin functions?
2. Is there any difference (in overhead) how Postgres call / manage plpython vs plpgsql vs pllua functions (I'm interested in the Postgres integration / context / data transfer side, not the VM itself)?
3. Is the context a big overhead? Can I use it for realtime data mapping (let's say 1000 queries/s))
4. Is there any benefit of writing user defined functions in plpgsql then other pg/language? On the documentation they enumerate advantages, but I think they apply to all postgresql procedural languages.

Related findings:

• A speed of PL languages for atypical usage
• PostgreSQL Function Language Performance: C vs PL/PGSQL

Answer 1

1. UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same.

2. Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge unless you're passing a data type that gets very different handling by one language than another, e.g. one passes a hstore as a string, and another converts it to a dict.

3. Unclear what "the context" is. Can you use it for "real time data mapping" ... well, depends on what the function does and if it's fast enough on the server it's running on, for the clients it's taking to, and for your requirements. How long is a piece of string? Benchmark.

4. PL/PgSQL is simpler to write, and offers faster access to SQL. It's generally better when you need to wrap a little logic around a lot of SQL. It's very slow for mathematical operations and complex algorithms, so purely computational code in PL/PgSQL should be avoided whenever possible in favour of C, or a faster procedural language.

Speedups when re-implementing PL/PgSQL code in C can vary from neglible to over 1000 times. It all depends on what the code is actually doing.

(This kind of multi-question isn't well suited to Stack Exchange as it's harder to have a definitive answer)

Answer 2

Is the context a big overhead? Can I use it for realtime data mapping (let's say 1000 queries/s))

Performance depends on hardware and complexity of your functions. I created an appliance that ran on a small 12-core server and a FusionIO-card (total costs euro 10000) and did about 2500 transactions per second with 20 concurrent users. Each transaction calls 29 stored procedures for processing the data and returning some useful information to the client. Some functions execute just one query, others a couple of queries. In total, it executes about 200000 INSERT, SELECT and UPDATE statements per second.

This is all written in PL/SQL, PL/pgSQL and PL/PerlU. And I'm pretty sure the system can run even faster when (some) functions are rewritten in C.

In this appliance, most performance comes from the SSD card. On a single rotating disk, we would never ever get this performance. Cheap SSD drives also fail, it works for an hour (because of the caching of the raid-card) and then it's game over. The FusionIO-card is expensive, but a very good investment when you're IO bound.

Answer 3

this is pretty hard to tell. it really depends on what you are doing. for example: PL/pgSQL is wonderful if you got large SQL statements in it - it really goes crazy if you got all kinds of branching, substring management and all that.

you really got to test from case to case.