Combine/Union data from multiple microservices

Question

I have a three microservices, Let says Facebook, Google, LinkedIn. All have some common functionalities but implementation is different as per their APIs. Also DB tables are specific for that microservice says tbl_google, tbl_facebook, tbl_linkedin. Now all services have simple CRUD apis for particular functionality like says User.

While addition, editing, deletion is not an issue, As specific API request says POST /api/google/user will hit the google micro-service and request google user creation. Same for PUT, DELETE. Also their is listing GET API which provide list of users of that service which pagination, filteration and sorting enabled.

But here is the issue, I need to create an API which will fetch all users data default sort by latest added and some optional filters also can be applied. Now I see no optimal easier solution to achieve this. This approaches I thought of but not convinced completely.

a) Getting all filtered data from each API and combine and sort / paginate them after that may not work as filtered data still be huge ( thousands, hundred thousands rows ).

b) Getting paginated/filtered/sorted data from each api and combine them may not work as it may possible that in reality 28 of latest 30 records is from single service, but logic will fetch 10 user per service.

c) Use Common Table. Their is some technical blocker to achieve this. But in addition to this, I also not comfortable with idea of maintain common table, while most of columns may be specific to service, So I may even need to break table into multiple table to achieve common table solution like tbl_common, tbl_facebook, tbl_google which require syncing/saving data into two different tables .

d) DB Views, This seems to be better idea above all. as Views are recommended for read solutions. But in context of microservices, not sure completely

I do not want to involve third party high architecture solution and only want to achieve this through code/DB logic itself.

Example Data

Google Service

[{id: 1, name: "Google User 1", role: "Supervisor", added_at: "2024-01-20"}, {id: 2, name: "Google User 2", role: "Manager", added_at: "2024-01-18"}, {id: 3, name: "Google User 3", role: "Support", added_at: "2024-01-03"}, {id: 4, name: "Google User 4", role: "Manager", added_at: "2024-01-22"}]

Facebook Service

[{id: 1, name: "Facebook User 1", role: "Manager", added_at: "2024-01-23"}, {id: 2, name: "Facebook User 2", role: "Tech", added_at: "2024-01-12"}, {id: 3, name: "Facebook User 3", role: "Sales", added_at: "2024-01-24"}]

LinkedIn Service

[{id: 1, name: "LinkedIn User 1", role: "Manager", added_at: "2024-01-11"}, {id: 2, name: "LinkedIn User 2", role: "Tech", added_at: "2024-01-10"}, {id: 3, name: "LinkedIn User 3", role: "Manager", added_at: "2024-01-09"}]


Required Results : 
[
    {id: 1, name: "Facebook User 1", role: "Manager", added_at: "2024-01-23", type: "Facebook"},
    {id: 4, name: "Google User 4", role: "Manager", added_at: "2024-01-22", type: "Google"},
    {id: 2, name: "Google User 2", role: "Manager", added_at: "2024-01-18", type: "Google"}.
    {id: 1, name: "LinkedIn User 1", role: "Manager", added_at: "2024-01-11", type: "LinkedIn"},
    {id: 3, name: "LinkedIn User 3", role: "Manager", added_at: "2024-01-09", type: "LinkedIn"}
    
]

Answer 1

Based on the provided description it seems that you are actually share a database between services. While shared database is a pattern which is seen in microservices architecture not that rare but in general I would argue that it actually is an anti-pattern which should be addressed as soon as possible since it results in coupling between services which should be avoided.

Another point which could be made that there should not be 3 different services here but only one (Users). But without having a full overview of the reasons which has lead to current design it's a bit hard to tell.

Options I would consider:

1. The most "microservicy" approach can be introducing a new service - Users which will combine the shared data from those 3 services and maintain some logic to distinguish between them. It will maintain a copy of data ideally using some async way to deal with changes (i.e. will be eventually consistent as quite often is done in microservice architecture)

2. If you are not ready to introduce a new service and manage separate database then the "Use Common Table" approach is actually should be feasible - there are multiple approaches to handle inheritance in database like table-per-hierarchy (TPH), table-per-type (TPT) and table-per-concrete-type (TPC) (some ORMs even support it out of the box - .NET's EF Core for example)

3. Views. It should be the easiest option to implement since the common table approach is not available. While your concerns about it are justified and this approach is not a good fit for microservices architecture but in general shared databases are. If you should go this way heavily depends on what are the next plans for the application/project and what are the available resources.

4. Another option worth considering would to load data in some kind of search engine (Solr, Elastic) but it would be better option if you will need full text search capabilities. Also it can be combined with solution from point one (i.e. new microservice will "own" the search engine, will update data in it and proxy search requests).