We are using postgres v9.4 in our project. We are using MVC Kendo UI and .Net Framework on the Front End. We have 4 database servers for Postgres and each at different physical machine.
GOAL : What I want to do is to prepare some sort of mechanism which will fetch data from all the servers and will accumulate and show in our web page as a single dataset. Currently I am doing this by providing a dropdown box for database selection.
Possible Solutions to achieve the goal :
- Create one postgres function in any of the postgres server, and from there we can use db_link to get data from other 3 postgres servers and Return the combined result set of data. But there are few many issues/blots in using this approach. Since we are using custom paging it may become difficult to bifurcate the data. say my page size is 50 then to bring exact 50 results of dataset I many need to divide 50 among 4 postgres servers.
- Since we are using .Net Framework what I can do is to make 4 Async calls from application and then merge the result set in One and display.
My question is can I use any other approach, or how can I modify/optimize current approaches.