I've been building data sharding architectures for at least a decade, so the problems you raise are ones I am intimately familiar with. Many of the data models I've worked with have unavoidably unpredictable dynamic distributions of both data and load, so it is a problem that can't be ignored. You are correct that this will essentially break naive data sharding architectures.
It is possible to design data sharding architectures where balancing of both data and load across cores is continuous and smooth, with surprisingly minimal overhead and coordination cost. In fact, there are multiple ways of doing it, depending on your workload characteristics. At this point, the design idioms for this style of architecture are refined and robust, so there is no computer science reason the problems you raise need to exist in a real implementation. The reason it seems "difficult" in practice is because so many designs insist on loosely coupling and weakly scheduling storage, execution, network, etc. If your architecture concept is slapping a thin layer on top of RocksDB, it won't be feasible. Every part of the stack needs to understand and be designed to the model. The end result is actually quite elegant in my view, and with unmatched throughput.
People design distributed systems with simple static sharding schemes because they are obvious, easy, and it lets you cut a lot of corners on the rest of your system design. It is not the only way to design a distributed system, continuous adaptive resharding and load shedding is a demonstrably viable option, and it is much easier to implement within a single server than on an actual cluster of networked computers.
Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases
"Since our system has a high tolerance to failures, we can leverage this for maintenance operations that cause segment unavailability. For example, heat management is straightforward. We can mark one of the segments on a hot disk or node as bad, and the quorum will be quickly repaired by migration to some other colder node in the fleet."
It is possible to design data sharding architectures where balancing of both data and load across cores is continuous and smooth, with surprisingly minimal overhead and coordination cost. In fact, there are multiple ways of doing it, depending on your workload characteristics. At this point, the design idioms for this style of architecture are refined and robust, so there is no computer science reason the problems you raise need to exist in a real implementation. The reason it seems "difficult" in practice is because so many designs insist on loosely coupling and weakly scheduling storage, execution, network, etc. If your architecture concept is slapping a thin layer on top of RocksDB, it won't be feasible. Every part of the stack needs to understand and be designed to the model. The end result is actually quite elegant in my view, and with unmatched throughput.
People design distributed systems with simple static sharding schemes because they are obvious, easy, and it lets you cut a lot of corners on the rest of your system design. It is not the only way to design a distributed system, continuous adaptive resharding and load shedding is a demonstrably viable option, and it is much easier to implement within a single server than on an actual cluster of networked computers.