1

I would very much prefer writing DB side code in Java but would like to know if doing so has any disadvantages compared to pl/pgsql (especially performance & error handling related).

Dojo
  • 5,374
  • 4
  • 49
  • 79

2 Answers2

5

As of my expirience pl/java has some major issues:

  1. It's difficult to install it into a postgresql server. Even if you find binary build for your postgresql version (which is difficult too) - it needs some config and library path juggling.
  2. First call to java stored procedure will result in a new JVM process. JVM processes are connection-scoped and require some amount of memory for java heap, so if you use connection pool you'll end up with 10-20 JVMs started and unused most of time, consuming your server RAM
  3. pl/java can communicate with postgresql database using self-made JDBC driver which emulates common JDBC usage, but has some implementation issues which may surprise you. Especially if you want to use JDBC cusrors or other not-so-common things.
  4. pl/java logging is rather special - it's because of postgresql internal error handling implementation. For example - if you log something with java logging api at ERROR log level - it will terminate your server connection.
  5. pl/java has very good data processing performance compared to application-server based java logic, but it's totally unscalable - pl/java procedures are fully single-threaded - postgresql forbids multi-threaded procedures
  6. If you use internal JDBC driver and your SQL statement contains errors - you'll end up with cryptic error message in postgresql log - totally unrelated to real problem.

As result - you can use pl/java procedures with some success but you need to do it very carefully and probably you need to think about improving your application design.

Konstantin V. Salikhov
  • 4,554
  • 2
  • 35
  • 48
  • 2
    You can actually get away with multi-threaded PL/Java, but you have to be incredibly careful never to call into any PostgreSQL APIs - even logging - from multiple threads. So in practice, yeah, single threaded. – Craig Ringer Jun 25 '14 at 13:16
3

Usually the only major difference is with round-trips. If your Java code has a low-latency connection to the DB, you should find client-side logic to perform quite well.

Update: Ian says you mean PL/Java, i.e in-database execution. If that's what you meant, I would not recommend PL/Java. It is nearly unmaintained; also, the Java model (heavy on threads, big expensive startup) isn't a great match for PostgreSQL's (processes not threads, very lightweight startup).

It'd be great to have people committing work to PL/Java, but right now there's not much happening.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778