Laravel Sharding Laravel Package

Getting Started

This package shards keyed queries in Laravel: a query that filters on the shard key (e.g. where('user_id', 5)) can be routed to a single shard, while an unkeyed query fans out and reads every shard.

1. Install and publish the config, then define your shard map / connection list. The two settings that matter most day-to-day are the shard key columns and pin_by_key.
2. Find the first use case in your app that is already partitioned by an owner column — user_id, tenant_id, account_id — and route that model through the package. Keep the rest of your models on the default connection until the sharded path is proven.
3. Verify routing before writing business logic: run a keyed query and confirm it hits one connection, then run the same query unkeyed and confirm it fans out. readsOneShard() is the internal predicate behind both behaviours; understanding it early explains most of the package's rules.

The canonical first query is a keyed count or fetch:

User::on('shard')->where('user_id', 5)->count();

If that returns from a single shard, your setup is correct.

Implementation Patterns

Keyed queries as the default. Treat every sharded query as keyed until you have a reason otherwise. Passing the shard key into the where() clause is what allows the package to pin the query to one shard, and it is the only shape that reliably supports grouped, distinct, and having aggregates.

Aggregates on keyed queries. As of v0.6.4, a distinct, grouped, or having aggregate on a keyed query is answered from its single shard:

// Now works: key names one shard, nothing to combine
User::where('user_id', 5)->distinct()->count('letter_id');

// Also taken from the shard whole, not as sum/count pairs
User::where('user_id', 5)->groupBy('team_id')->avg('score');

Previously both of these threw «cannot be combined across shards» because the refusal looked only at query shape. Now it consults readsOneShard() — the same check the raw-aggregate refusal uses — so the refusal only fires when the query genuinely spans shards.

Fan-out is a deliberate choice, not an accident. An unkeyed aggregate is still refused. If you need a cross-shard total, compute it explicitly — a queued job that iterates shards and sums the results, or a materialised rollup table — rather than expecting the package to add things up for you. Aggregates that are not additive across shards cannot be reconstructed from per-shard answers anyway.

Plan around pin_by_key. When pin_by_key is on, keyed queries stay pinned. When it is off, a keyed query fans out again and the multi-shard refusal comes back. Likewise, during a rebalance a keyed query may touch more than one shard, and the refusal correctly reapplies. Code that assumes "keyed means single shard" must tolerate these windows.

Grouped aggregates should be fetched whole. A grouped avg() taken as a sum and a count from two separate queries can mix groups — the two result sets need not contain the same groups in the same order. v0.6.4 answers it from the shard as a single operation; prefer that path and avoid hand-rolling sum/count pairs against grouped queries.

Integration tips. Route report queries through a service class that explicitly declares its shard scope. Where a query must span shards, isolate it in one place with a comment explaining why, so the next developer does not assume it is keyed.

Gotchas and Tips

• Shape is not scope. The pre-0.6.4 bug is a good mental model for the whole package: a query that looks like a cross-shard aggregate may still be a single-shard read. Check readsOneShard() semantics rather than reading the SQL.
• pin_by_key off changes aggregate behaviour. Tests written with pinning enabled can pass while production, with pinning off, throws the multi-shard refusal. Assert against the real config.
• Rebalances are transient multi-shard states. Keyed aggregates that work in steady state can refuse mid-rebalance. Do not treat that refusal as a permanent bug; retry or defer the job.
• Refusals are guardrails, not limitations to work around. If you hit «cannot be combined across shards», the fix is almost always to key the query or move the aggregation out of the request path — not to bypass the check.
• Debugging routing. Log the resolved connection for a query when behaviour surprises you; the difference between a pinned read and a fan-out is usually visible there before it is visible in the error.
• Upgrade note for v0.6.4. If you had workarounds for keyed distinct/grouped/having aggregates — manual per-shard loops, DB::connection() escapes — they can likely be removed. Re-run your aggregate tests with pinning both on and off, and again under a simulated rebalance, since the new behaviour is conditional on all three.
• Extension point. Because the refusal keys off a single predicate, any custom aggregate or raw expression you add should consult the same scope check rather than reimplementing a shape-based test.