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