0

We have some PL/pgSQL stored procedures in our DB (PostgreSQL 9.x).

These are strictly sequential, and under some circumstances, can be very slow.

We are thinking on porting these to PL/Java, PL/Python or something like these, and exploit the multithreading abilities of these languages.

The main question is: how "effectively" these language supports are implemented? For example, I'm thinking on the Virtual Machines that run Java code: when calling my PL/Java code, each time it summons a new VM for it, or does PL/Java keeps some kind of pool of VMs, and associate one of them for the actual call?

gyorgyabraham
  • 2,550
  • 1
  • 28
  • 46
  • 5
    What is slow in the procedure? That's what you want to fix, porting a problem to a different language won't fix the problem. – Frank Heikens Jan 12 '12 at 16:22

1 Answers1

3

Pl/Java runs in a jvm embedded in the postgres backend process. See: PL/Java wiki

I'd suggest that you don't just translate everything to java without first looking at pure SQL solutions. In many cases SQL's ability to work on a large number of records in bulk will outweigh any benefit of dealing with records individually but leading to more SQL calls.

The multi-threading capability of Java doesn't give benefits if the task is inherently sequential.

Gary - Stand with Ukraine
  • 7,081
  • 1
  • 30
  • 39
  • Thanks guys, we are putting together the information these weeks before making any decisions. Your comments are really helpful! – gyorgyabraham Jan 24 '12 at 16:15