In a microservice architecture, each service owns its data and controls access to it. This keeps service boundaries clear, but it makes cross-service queries harder. When an application needs information held by several services, it must either make multiple calls and combine the results or maintain a view shaped for that use case.
This is the role of a data projection: a representation of data prepared for a particular read scenario and stored where that scenario can use it. The design questions are practical ones: who owns the source data, how are changes delivered, and how do we detect a projection that has fallen behind?
Consider account data. Three services need it, each with its own database:
- an account-details service displays information about an account;
- a customer-summary service calculates aggregates across a customer’s accounts;
- a product-recommendation service uses account data to recommend products.
A fourth service is the master service for accounts. It interacts with a fourth system and distributes account data to the three services. The question is how those services should access and combine the data.
Option 1: PostgreSQL FDW
PostgreSQL Foreign Data Wrappers let a database query tables in another database. A query can join local and remote data without first copying the remote tables.
That is not a good fit when queries depend on data owned by several services. A query now depends on the network and on the availability and performance of every database it touches. Workloads are harder to isolate, and a slow remote query can affect the service whose database is being queried.
FDW can be useful for limited integration needs. In this case, however, it puts cross-service dependencies directly in the read path.
Option 2: Call service APIs
An application can call the services that own the required data and combine their responses. Each service retains control of its database and exposes only its own interface.
The challenge is that the use cases require joins. The calling application has to fetch and combine datasets itself. Filtering, sorting, and pagination become more complicated, while each additional call adds latency and another dependency. APIs work well for small lookups by known identifiers; they are less convenient for joining and processing large datasets.
Option 3: Maintain local projections
The master account service can distribute account changes to the databases of the three services. Each service stores a local projection shaped for its own read scenario.
The account-details service reads the data it needs to display an account. The customer-summary service stores the inputs for its calculations. The recommendation service keeps the account attributes needed by its logic. Reads no longer have to join across service databases at request time.
This approach duplicates data and introduces update lag. Writes to three databases do not form one atomic transaction. If delivery fails partway through, one projection may be behind the others.
| Approach | Benefit | Limitation |
|---|---|---|
| FDW | Query remote tables with SQL | Couples query latency and availability across databases |
| Service APIs | Each service controls its own data | Joins and large-scale processing move into application code |
| Local projections | Each database is shaped for its read use case | Requires reliable delivery, retries, and drift detection |
What local projections require
The master service needs to track delivery to each destination. Processing the same change more than once must not create duplicates or overwrite newer data. Failed deliveries need to be retried, and projection lag needs to be measured.
The system also needs a way to compare projections with the source and rebuild a projection when necessary. If updates can arrive out of order, each change needs enough version information to prevent an older account state from replacing a newer one.
The decision
For these read scenarios, local projections are the most suitable option. Each service can read data prepared for its own workload without making cross-service joins on the request path.
The next step is to define the delivery contract: how changes are versioned, how failed writes are retried, and how lagging databases are detected and repaired. Having one master service distribute the data does not, by itself, guarantee that all three projections are updated successfully.
Top comments (0)