IIRC CockroachDB had (has?) interleaved tables for this reason. It seemed to allow collocating shards with the same foreign key in the same machine, leveraging composite keys for that.

For example, a multi-tenant application could have a tenants table with PK id, and an orders table with PK (tenant_id, id), and a FK from orders(tenant_id) to tenants(id). Then CockroachDB would keep the orders with tenant_id = 123 in the same machine where the tenant with id = 123 was.

I have the impression they deprecated interleaved tables, but I can't find more info about it.