Queries that name their key stay fast
A query that names its shard key stays fast forever. One person's orders live on one shard, and an index finds them there.
Every other query just got expensive. Sharding did not really make those queries slow. It revealed that they now need a distributed system where an index used to be enough.
Scatter-gather, and why it scales badly
Expect to reach for scatter-gather, which asks every shard at once and merges the answers in your application. It works, and it scales badly in two separate ways.
Load, because one search makes work on every machine you own, so your total cost multiplies by the number of shards. And latency, because you wait for the slowest one to answer. At 16 shards, every such query is a fresh sample of your worst machine on its worst day. Acceptable for an admin screen nobody opens. Poison on a page everybody opens.
Joins across shards mostly stop existing. You replace them two ways. Copy the seller's name onto the order row as you write it, so the read never joins anything. And keep a second copy of the data organised by a different key, updated as changes stream through.
You are paying storage and a more complicated write path to keep your reads on one shard, and that is nearly always the right trade.
Transactions across shards are the sharpest edge in the chapter. A protocol for committing on two machines at once exists, and it is slow and fragile enough that most teams refuse it. They undo the first step when the second one fails, or accept a brief disagreement and reconcile later.
Solve it upstream instead, by picking a shard key that keeps things which must change together on the same machine. That is why keying by tenant is so popular: a tenant's entire world commits in one place.
Analytics belong off your shards entirely. Stream your changes into a warehouse, and let the queries that want all of the data run where all of the data lives.
Worked example
Ingrid's marketplace shards by seller_id, which makes seller dashboards instant. Then product asks for a buyer order history page, and a buyer's orders are scattered across however many sellers they bought from. Version one scatter-gathers all 16 shards; p99 lands at 600 ms because some shard is always having a bad moment, and every page view multiplies query load 16x during peak. Version two builds a second table, orders_by_buyer, sharded on buyer_id and written through the outbox that already feeds their event bus. Writes now touch two places, the backfill takes a weekend, and order storage doubles, about 40 dollars a month at their size. The page becomes a single-shard read at 9 ms. When product later asks to sort sellers by rating across all orders, that query goes to the warehouse.