A couple years ago, I learned the hard way that having my Java application sitting on one server, with the database sitting on another, and having complex transactions that issue lots of queries each, results in very poor performance due to network latency that keeps adding up.

In my specific case, a single DB transaction can possibly generate hundreds of subqueries/commands or even more, with data going back-and-forth between the application server running Java and the database server running PostgreSQL until the transaction is committed. The network millisecond delays add up big time.

Since then, I have been working with a database that allows me to send my entire transaction's logic, programmed in JavaScript, to the database server so that it is executed remotely there, and only the final result is returned, ensuring that there is only one network back-and-forth between the two servers. I like that approach.

I am considering working with PostgreSQL again as my main database, yet I still want to be able to follow the same approach where the entire transaction's logic is executed on the database server for one network back-and-forth only, no networking delays in-between. And I want to be able to program my transaction logic in either Java or JavaScript -- SQL won't do.

So when it comes to PostgreSQL, it seems like I have two major options.

  • A) I can either install a stored procedure extension such as PL/Java or PL/v8 and program my transaction logic as functions within the database, or

  • B) I could implement a Java/JS-based intermediary program that would sit on the database server, receive remote requests through some messaging layer, perform the transactions and their logic against the database on localhost directly, and return the final result.

I would largely prefer to go with B), but I'd like to ask more experienced professionals here if I can expect to eliminate network latency just as much with that option as I would with A).

So, the question is: if my intermediary Java/JS software, sitting directly on the database server, is connecting to PostgreSQL on localhost strictly (not even another server on LAN), are there any networking or others delays imposed that I would otherwise not get if my logic was written within PostgreSQL functions instead?

Is there any overhead I am adding if I go with B) instead of A)? (excluding overhead caused by my messaging/RPC layer that I'd build)

  • 1
    Why don't you put your Java application server (e.g. Tomcat, Glassfish, ...) on the same server as the database? To me PL/pgSQL would be the first choice to implement "transaction logic" on the server side. PL/Java has way too much overhead as it will start up a JVM for each database connection. – a_horse_with_no_name Jan 31 '18 at 6:57
  • Thanks for your input. Indeed option B) is the equivalent of what you suggested, putting Java code on the same server. I wasn't aware that PL/Java would spawn a JVM for each connection, this is very useful information which further favors option B). I appreciate your information! Feel free to republish it as an answer if you wish. – hbCyber Jan 31 '18 at 9:59
  • 1
    By the way, a hardware solution for server-to-server performance problems is to create a dedicated network. I’ve used second Ethernet ports (adding Ethernet cards if need be) to make a dedicated network through a switch or even direct connection via crossover cable (tip: some modern Ethernet ports auto-crossover with regular cable). This is where gigabit Ethernet pays off, but even 10/100 will see huge improvement when dedicated. – Basil Bourque Jan 19 '19 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.