Selecting Top/Max/Avg (Any aggregation Function) for N values from 2 different servers Efficiently
<p>I've 2 different web-services which in turn talk to 2 different POSTGRES DB and both these DB's have the exact same schema (but different data).</p>

<p>The responsibility of these services are to fire a set of query based on the conditions passed and retrieve the result.</p>

<p><strong>Now the problem</strong> - 
I'm developing a new service, which is suppose to <strong>accumulate/aggregate</strong> the results from these services and send this as the final result.
The Query fired could contain group by, having clause, order by, aggregation functions.</p>

<p>The only solution I could think of for this problem, is to get <strong>all the data</strong> for the given conditions from both the DB's (<strong>Event if limit is present</strong>) and then perform aggregation operations at my end as requested by user.</p>

<p>Say For Example, the query sent to my service is - </p>

<pre><code>select sum(salary), dept_id 
from employee 
group by dept_id 
order by sum(salary) desc
limit 2;
</code></pre>

<p>Which means I just need to send the sum of top 2 salaries from various departments. Now comes the trickiest part</p>

<p>Assume this is the output, </p>

<pre><code>From DB1:
sum(salary) | dept_id 
10 | 2
8  | 1
5  | 3


From DB1:
sum(salary) | dept_id 
30 | 3
8  | 1
1  | 2
</code></pre>

<p>So, If I pass the limit to both the services and get only top 2 then the output would be wrong.</p>

<pre><code>DB1: Sends (10,2) and (8,1)
DB2: Sends (30,3) and (8,1)
</code></pre>

<p>And now if I sum up in my service based on the dept_id (1/2/3)
<strong>Aggregate Service: (30,3) and (16,1)</strong></p>

<p><strong>While the actual output should've been, the max. of both the values from both DB's put together</strong>:</p>

<pre><code>Max(DB1+DB2) - 
dept_id1 - (8 + 8) = 16
dept_id2 - (10 + 1) = 11
dept_id3 - (30 + 5) = 35
</code></pre>

<p>So My Actual Output Should have been : <strong>(35,3) and (16,1).</strong></p>

<p>With this example, you can see that I need all the values from both the DB's. Perform the required aggregation/filter conditions at my service and then output the result.</p>

<p>The problem with this is the <strong>Performance</strong>.</p>

<ol>
<li>If I fire the query directly in the DB, it takes around <strong>2 seconds</strong> to apply all the filters and give the result back.</li>
<li>When I service talks to these DB's it retrieves all their data (After minor computations from their end like summing up), perform the calculation at my service. This takes <strong>about 20 seconds</strong>.

<ul>
<li>Out of these, It takes 15 seconds for these DB's to get all the aggregated Data. 5 Seconds is taken by my code to sort/filter/aggregate data at my service end.</li>
</ul></li>
</ol>

<p><strong>Question:
How to Improve the performance here? Is there a better way to handle this scenario? Any algorithm or tweak which can be partially applied to improve this performance by a little?
Kindly let me know if you need any other info about how I'm handling it.</strong></p>

<p>Note : I've parallel threads which read the data from each of these services independently. So no performance issue there.
I've parallel streams which process these data from both these services, so even this shouldn't be an issue.</p>