Eliminating PostgreSQL/database network latency: PL/Java stored procedure VS. Java code running on localhost

Question

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)

Answer 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. - user1822

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