News for hackers. Stuff that matters.
Re:For Want of a JOIN by btown on Thursday December 22, 2022 @08:55AM (#34093219)
As someone who’s primarily worked with monoliths, I often wonder how often this exact problem happens, but where A and B are [micro]services owned by two different teams, one is required by company policy to use their APIs not their raw databases, and escalation of each of these issues e.g. query size/rate limiting runs the risk of burning political capital on top of everything else.

How does one JOIN across not just tables but opaque services, in the general case? Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly?

Re:For Want of a JOIN by alexisread on Thursday December 22, 2022 @09:37AM (#34093658)
I think it depends on whether you are talking ad-hoc ie. a user analyzing several datasets, or pipelined ie. preprocessed joins. For pipelined joins, effectively your data forms a DAG (directed acyclic graph, and yes we are ignoring recursion here). Providing your data services speak the same language you can create a pipeline off the first pipeline that joins the data and sticks it in a cache (eg. RDS, Elasticsearch). Changing the underlying data should then trigger a reload of the downstream pipelines. This is basically what Materialize.io, KSQLDB et. al. do - a reactive DAG with a database as the cache.

One issue for larger companies is that you don't control the whole DAG, so discovery, security, protocols etc. need to be coordinated by an overarching architecture for this to work.

Something like Apollo (GraphQL) is a simpler solution (in some ways) as you control the joins on the Apollo server which speak to backend (REST) APIs (other teams).

Re:For Want of a JOIN by btown on Thursday December 22, 2022 @09:49AM (#34093789)
Doesn’t this mean every service must expose a way for downstream consumers (via Apollo or not) to subscribe to updates to allow them to invalidate their caches? I shudder to think how stale the DAG approach would be without this. I suppose this is doable if the company lives on Kafka, but what if it lives on RESTful microservices?
Re:For Want of a JOIN by alexisread on Friday December 23, 2022 @04:11PM (#34110669)
Usually restful services are behind a loadbalancer/reverse proxy. This proxy could also handle things like security, logging, cache invalidation (the service behind the proxy) and service registration (other downstream services).

Marking APIs as immutable/up or down (for rollback) state migrations etc. Is really important from a platform perspective, so it also makes sense for APIs to have a cache hook as well.

The proxy can then manage the platform state wrt change propagation.

Re:For Want of a JOIN by pwg on Thursday December 22, 2022 @09:17AM (#34093458)
> Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly?

What tends to be by far more common is that each team fails to envision that someone, somewhere, sometime in the not so distant future will want or be required to retrieve more than one "element" at a time via their APIs. And so panic ensues when "other entity" begins feeding 20 API retrievals per second at their "one-at-a-time API" and their performance goes off the cliff it was always sitting near.

Re:For Want of a JOIN by NegativeLatency on Thursday December 22, 2022 @10:14AM (#34094082)
Extra points if you manage to do it to your own team’s APIs because of bad design and planning
Re:For Want of a JOIN by tracker1 on Thursday December 22, 2022 @10:56AM (#34094666)
You mean like 8+ joins to get that single record and then having it bottleneck when you get a few hundred requests a minute? (not bitter at all here)
Re:For Want of a JOIN by thatwasunusual on Thursday December 22, 2022 @09:29AM (#34093577)
> How does one JOIN across not just tables but opaque services, in the general case?

You (should) never do that. It's as simple as that. If you create microservices that are atomically depending on each other, you are doing something _extremely_ wrong.

Re:For Want of a JOIN by btown on Thursday December 22, 2022 @10:48AM (#34094541)
Definitely not implying that two services should mutually depend on each other. But a third service C may want to look up in B for every record available from A - say, if B reports reservations, and A reports users who are members of a certain group, and you want the reservations relevant to a specific group. The OP article describes all sorts of pitfalls for the C team if you only had access to B via a "get by IDs" API.
Re:For Want of a JOIN by mcdonje on Thursday December 22, 2022 @10:03AM (#34093958)
>Or does every team doing microservices silently expect that one day a data team will start querying for a massive number of records-by-ID from every service, and the veterans in each team plan for this load pattern accordingly?

I assume here by "data team" you mean reporting. Reporting and operations groups are very different with very different needs.

Microservices are useful in operations settings where the flexibility of taking modules out of a monolith and putting the network between them outweighs the performance hit.

Reporting directly from microservices is a recipe for disaster. To support reporting, the microservices need to contribute data to a data lake, data warehouse, or other repository.

Re:For Want of a JOIN by oftenwrong on Thursday December 22, 2022 @10:37AM (#34094387)
* * *
Re:For Want of a JOIN by tracker1 on Thursday December 22, 2022 @10:54AM (#34094632)
It really depends on how much data you need... I worked in an org where the primary data was in one database, and secondary data was in another. The DBA team wrote the query to call the other (remote) db across in part of the statement, and it was horribly slow... The N+1 pattern combined with memcached on the secondary lookups was so much faster in the end.. since it was limited to a display page worth of secondary lookups. A GraphQL server can relatively effectively do this for you.

In the end it really depends... if you're talking even 10-100k users, a single, well optimized SQL RDBMS is your best bet... getting past that takes deep knowledge and/or more options/skills. In the end, most don't have that next step and the trend to Micro-Service all the things is jumped to too soon in most cases (and not soon enough in others).

Re:For Want of a JOIN by LennyWhiteJr on Thursday December 22, 2022 @01:07PM (#34096399)
A couple ways. If the need is not real-time and analytical, you feed the data from multiple services into a separate BI database which can do slower and more complex joins across data from multiple data sources. Or if the need is real-time, you build a paginated API with a page limit that can always be processed within the API SLA. Then you build workflows on top of the paginated API to operate on that data.

Generally, unbounded operations have to be broken up at some point. It just depends on how big the data set is.

Re:For Want of a JOIN by kgeist on Thursday December 22, 2022 @01:45PM (#34096842)
>How does one JOIN across not just tables but opaque services

One solution we use is to have an event queue from service A to which service B is subscribed. In the event handler, service B fills its own view table with data from service A. And then it can do joins on data from multiple services because everything is in the same DB. We require services to always emit "created" and "updated" events for its objects.

Re:For Want of a JOIN by lmm on Thursday December 22, 2022 @07:20PM (#34100812)
You have a shared datastore, but not an SQL RDBMS. If you're big enough you build it in-house. See "An oral history of Bank Python", posted here a while back.