At some point, the agency I worked at had a need for syndication logic on several builds at the same time. In this context, syndication meant a hub and spoke model – some content created at the hub needed to be available at the spokes as well.
There are ways to solve this in the WP space. I think many devs immediately think WP REST API and occasional syncing.
I proposed a very different solution that I liked. It probably needed a few more iterations to go anywhere. That solution was MySQL views of the hub posts table (and meta…) and the spoke table. This required filtering every mysql query.
Views are cool, they are basically a “virtual table” in MySQL that is built by another query. There is overhead, but most users are hitting full page cache on things like that.
One caveat is that database portability becomes worse. View creation is exported with the name of the creating user which is likely not the same as the creating user where the data is being imported.