Querying / Pagination Across Microservices

Question

Our shop has recently started taking on an SOA approach to application development. We are seeing some great benefits with the separation of concerns, reusability, and other benefits of SOA/microservices.

However, one big item we're stuck on is aggregating, filtering, and paginating results across services. Let me describe the issue with a scenario.

Say we have 3 services:

1. PersonService - Stores information on people (names, addresses, etc)
2. ItemService - Stores information on items that are purchasable.
3. PaymentService - Stores information regarding payments that people have made for different items.

Now, say we want to build a reporting/admin tool that can display / report on multiple services in aggregate. For instance, we want to display a paginated list of Payments, along with the Person and Item that each payment was for. This is pretty straightforward: Grab the list of payments, then query PersonService and ItemService for the respective Person and Item records.

However, the issue comes into play when we want to then filter down that data: For instance, displaying a paginated list of payments made by people with the first name 'Bob', who have purchased the item 'Car'. This makes things much more complicated, because we need to filter results from 3 different services without knowing how many results each service is going to return.

From a performance perspective, querying all of the services over and over again to narrow down the results would be costly, so I've been researching better solutions. However, I cannot find concrete solutions to this problem (or at least a "best practice"). In a monolithic application, we'd simply use SQL joins across the different tables. I'm having a ton of trouble figuring out how/if something similar is possible across services.

My question to the community is: What would your approach be? Things I've considered:

1. Using some sort of search index (Elasticsearch, Solr) that contains all data for all services (updated via events pushed out by services), and then querying the search index for results.
2. Attempting to understand how projects like GraphQL and Neo4j may assist us with these issues.

Answer 1

I stick with Sam Newman who says in Chapter 4 "The shared Database" of his book something like:

Remember when we talked about the core principles behind good microservices? Strong cohesion and loose coupling --with database integration, we lose both things. Database integration makes it very easy for services to share data, but does nothing about sharing behaviour. Our internal representation is exposed over the wire to our consumers, and it can be very difficult to avoid making breaking changes, wich inevitably leads to fear of any changes at all. Avoid at (nearly) all costs.

This is the point I make when I curse at Content-Management-Systems.

In my view a microservice is autonomous, what it cannot be if it shares things or consumes shared things. The only exception I make here are Domain-Objects, those represent the shared understanding of the business model and must be used in communication between microservices solely.

It depends on the microservice itself if an ER or AggregationOriented database (divided into document based or graph based) better suits the needs. The funny thing is, by being loosley coupled and by being autonomus you are able to do just that!

If an PaymentService shares the behaviour of "how many payments for Person A" He needs to know Person A in order to fullfill this. But Everything he knows about Person A must origin from the PersonService, maybe at runtime (the PaymentService maybe just stores an id) or event based (the PaymentService stores the data it needs up to the Domain-Object user, what gets updated triggered and supplied by the PersonService). The PaymentService itself does not share users itself.

Answer 2

Storing this data in elasticsearch/solr/cognitivesearch type service in addition to SQL could help solve some of these problems.

In your given example,

In the search index(elasticsearch/solr/cognitivesearch) person object will have a property called "items" that will contain a list of items that are paid for by that person.

That way, you can filter across objects, get a paginated list that is sorted by any property of the person. You can add similar information on other documents to better suit your business needs.

Using a GraphDatabase would seem to solve your problem from a 10000ft, but you will run into pagination problems when you operate at scale. GraphDatabases do not do pagination well(they will have to visit all the nodes anyway, even when you need a paginated list) and will cause timeouts/performance issues.