Search code examples
postgresqlplpgsqlpljava

Postgres pl/java caveats

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).

Solution

  • 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.