Database Sharding Strategies for Laravel Applications

When your single database can't handle the load — billions of rows, thousands of writes per second — you need to split data across multiple databases. This is sharding.

When Do You Need Sharding?

You probably don't. Before sharding, try:

1. Query optimization (indexes, explain analyze)
2. Read replicas
3. Caching (Redis)
4. Table partitioning
5. Vertical scaling (bigger server)

Shard when:

• Single table has 100M+ rows and growing fast
• Write throughput exceeds single server capacity
• Data locality requirements (EU data in EU, US data in US)
• Multi-tenant SaaS with strict data isolation

Sharding Strategies

Setting Up Multiple Database Connections

// config/database.php

'connections' => [
    'shard_0' => [
        'driver' => 'mysql',
        'host' => env('DB_SHARD_0_HOST', '127.0.0.1'),
        'database' => env('DB_SHARD_0_DATABASE', 'app_shard_0'),
        'username' => env('DB_SHARD_0_USERNAME', 'root'),
        'password' => env('DB_SHARD_0_PASSWORD', ''),
    ],
    'shard_1' => [
        'driver' => 'mysql',
        'host' => env('DB_SHARD_1_HOST', '127.0.0.1'),
        'database' => env('DB_SHARD_1_DATABASE', 'app_shard_1'),
        'username' => env('DB_SHARD_1_USERNAME', 'root'),
        'password' => env('DB_SHARD_1_PASSWORD', ''),
    ],
    'shard_2' => [
        'driver' => 'mysql',
        'host' => env('DB_SHARD_2_HOST', '127.0.0.1'),
        'database' => env('DB_SHARD_2_DATABASE', 'app_shard_2'),
        'username' => env('DB_SHARD_2_USERNAME', 'root'),
        'password' => env('DB_SHARD_2_PASSWORD', ''),
    ],

    // Central database for shard routing metadata
    'central' => [
        'driver' => 'mysql',
        'host' => env('DB_CENTRAL_HOST', '127.0.0.1'),
        'database' => env('DB_CENTRAL_DATABASE', 'app_central'),
        'username' => env('DB_CENTRAL_USERNAME', 'root'),
        'password' => env('DB_CENTRAL_PASSWORD', ''),
    ],
],

Shard Manager

// app/Services/ShardManager.php

namespace App\Services;

class ShardManager
{
    private const SHARD_COUNT = 3;

    private static array $connections = [
        0 => 'shard_0',
        1 => 'shard_1',
        2 => 'shard_2',
    ];

    /**
     * Hash-based sharding: deterministically map an ID to a shard.
     */
    public static function getConnectionForId(int|string $id): string
    {
        $shardIndex = crc32((string) $id) % self::SHARD_COUNT;

        return self::$connections[$shardIndex];
    }

    /**
     * Tenant-based sharding: map tenant to their shard.
     */
    public static function getConnectionForTenant(int $tenantId): string
    {
        // Look up in central database
        $mapping = \DB::connection('central')
            ->table('tenant_shards')
            ->where('tenant_id', $tenantId)
            ->first();

        if (!$mapping) {
            // Assign to least-loaded shard
            return self::assignTenantToShard($tenantId);
        }

        return self::$connections[$mapping->shard_id];
    }

    /**
     * Get all shard connections for cross-shard queries.
     */
    public static function allConnections(): array
    {
        return self::$connections;
    }

    private static function assignTenantToShard(int $tenantId): string
    {
        // Simple round-robin assignment
        $shardId = $tenantId % self::SHARD_COUNT;

        \DB::connection('central')->table('tenant_shards')->insert([
            'tenant_id' => $tenantId,
            'shard_id' => $shardId,
            'created_at' => now(),
        ]);

        return self::$connections[$shardId];
    }
}

Sharded Model Trait

// app/Traits/Shardable.php

namespace App\Traits;

use App\Services\ShardManager;
use Illuminate\Database\Eloquent\Builder;

trait Shardable
{
    /**
     * Get the shard key value for this model.
     */
    abstract public function getShardKey(): int|string;

    /**
     * Get the attribute name used for sharding.
     */
    public function getShardKeyName(): string
    {
        return 'user_id';
    }

    /**
     * Set the correct connection based on the shard key.
     */
    public function resolveConnection(): static
    {
        $connection = ShardManager::getConnectionForId($this->getShardKey());
        $this->setConnection($connection);

        return $this;
    }

    /**
     * Override save to route to correct shard.
     */
    public function save(array $options = []): bool
    {
        $this->resolveConnection();
        return parent::save($options);
    }

    /**
     * Query scope for shard-aware queries.
     */
    public static function onShard(int|string $shardKey): Builder
    {
        $connection = ShardManager::getConnectionForId($shardKey);

        return static::on($connection);
    }

    /**
     * Query across all shards (expensive — use sparingly).
     */
    public static function onAllShards(): \Illuminate\Support\Collection
    {
        $results = collect();

        foreach (ShardManager::allConnections() as $connection) {
            $shardResults = static::on($connection)->get();
            $results = $results->merge($shardResults);
        }

        return $results;
    }
}

Using Sharded Models

// app/Models/Order.php

namespace App\Models;

use App\Traits\Shardable;
use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    use Shardable;

    protected $fillable = ['user_id', 'total', 'status'];

    public function getShardKey(): int|string
    {
        return $this->user_id;
    }
}

// Creating an order (routes to correct shard)
$order = new Order([
    'user_id' => 42,
    'total' => 9999,
    'status' => 'pending',
]);
$order->save(); // Automatically saves to the shard for user 42

// Querying on a specific shard
$orders = Order::onShard(42)
    ->where('user_id', 42)
    ->where('status', 'completed')
    ->get();

// Cross-shard query (expensive!)
$allPendingOrders = Order::onAllShards()
    ->where('status', 'pending');

Running Migrations on All Shards

// app/Console/Commands/MigrateShards.php

class MigrateShards extends Command
{
    protected $signature = 'shards:migrate {--seed} {--fresh}';
    protected $description = 'Run migrations on all shard databases';

    public function handle(): int
    {
        $connections = ShardManager::allConnections();

        foreach ($connections as $index => $connection) {
            $this->info("Migrating shard {$index} ({$connection})...");

            $command = 'migrate';
            $options = ['--database' => $connection, '--force' => true];

            if ($this->option('fresh')) {
                $command = 'migrate:fresh';
            }

            $this->call($command, $options);

            if ($this->option('seed')) {
                $this->call('db:seed', ['--database' => $connection]);
            }
        }

        $this->info('All shards migrated.');
        return Command::SUCCESS;
    }
}

Cross-Shard Queries

Sometimes you need data from multiple shards:

class OrderAnalytics
{
    public function getTotalRevenue(): int
    {
        $total = 0;

        foreach (ShardManager::allConnections() as $connection) {
            $total += Order::on($connection)
                ->where('status', 'completed')
                ->sum('total');
        }

        return $total;
    }

    public function getTopCustomers(int $limit = 10): Collection
    {
        $customers = collect();

        foreach (ShardManager::allConnections() as $connection) {
            $shardCustomers = Order::on($connection)
                ->select('user_id', DB::raw('SUM(total) as total_spent'))
                ->where('status', 'completed')
                ->groupBy('user_id')
                ->orderByDesc('total_spent')
                ->limit($limit)
                ->get();

            $customers = $customers->merge($shardCustomers);
        }

        // Re-sort and limit across all shards
        return $customers->sortByDesc('total_spent')->take($limit);
    }
}

Tenant-Based Sharding

For SaaS apps, each tenant gets their own logical database:

// app/Http/Middleware/SetTenantShard.php

class SetTenantShard
{
    public function handle(Request $request, Closure $next): mixed
    {
        $tenant = $request->user()?->tenant;

        if ($tenant) {
            $connection = ShardManager::getConnectionForTenant($tenant->id);
            config(['database.default' => $connection]);
        }

        return $next($request);
    }
}

// Once middleware sets the connection, all queries route correctly:
$orders = Order::where('status', 'active')->get(); // Queries tenant's shard automatically

Challenges & Solutions

1. Auto-Increment IDs

IDs will conflict across shards. Use UUIDs or a distributed ID generator:

// app/Traits/UsesUuid.php
trait UsesUuid
{
    protected static function bootUsesUuid(): void
    {
        static::creating(function ($model) {
            if (!$model->id) {
                $model->id = (string) Str::uuid();
            }
        });
    }

    public function getIncrementing(): bool
    {
        return false;
    }

    public function getKeyType(): string
    {
        return 'string';
    }
}

2. Cross-Shard Joins

You can't join across databases. Solutions:

• Denormalize data (store copies)
• Application-level joins
• Use a central database for shared lookup data

3. Transactions

Distributed transactions are hard. Prefer saga pattern:

// Instead of a cross-shard transaction, use compensating actions
try {
    // Step 1: Create order on user's shard
    $order = Order::onShard($userId)->create([...]);

    // Step 2: Update inventory on product's shard
    $product = Product::onShard($productId)->find($productId);
    $product->decrement('stock', $quantity);
} catch (\Exception $e) {
    // Compensate: delete the order
    $order?->delete();
    throw $e;
}

Monitoring & Health Check

class ShardHealthCheck extends Command
{
    protected $signature = 'shards:health';

    public function handle(): int
    {
        $rows = [];

        foreach (ShardManager::allConnections() as $index => $connection) {
            try {
                $start = microtime(true);
                DB::connection($connection)->select('SELECT 1');
                $latency = round((microtime(true) - $start) * 1000, 1);

                $count = DB::connection($connection)->table('orders')->count();

                $rows[] = [$index, $connection, '✓ OK', "{$latency}ms", number_format($count)];
            } catch (\Exception $e) {
                $rows[] = [$index, $connection, '✗ DOWN', '-', '-'];
            }
        }

        $this->table(['Shard', 'Connection', 'Status', 'Latency', 'Rows'], $rows);

        return Command::SUCCESS;
    }
}

Output:

+-------+------------+--------+---------+------------+
| Shard | Connection | Status | Latency | Rows       |
+-------+------------+--------+---------+------------+
| 0     | shard_0    | ✓ OK   | 1.2ms   | 33,451,200 |
| 1     | shard_1    | ✓ OK   | 1.1ms   | 33,129,800 |
| 2     | shard_2    | ✓ OK   | 1.3ms   | 33,580,100 |
+-------+------------+--------+---------+------------+

If row counts are skewed too much (>20%), shard key distribution has a problem — review the hash function.

Testing Sharded Code

class ShardedOrderTest extends TestCase
{
    public function test_order_routes_to_correct_shard(): void
    {
        $order = new Order(['user_id' => 42]);
        $order->resolveConnection();

        $expected = ShardManager::getConnectionForId(42);
        $this->assertEquals($expected, $order->getConnectionName());
    }

    public function test_cross_shard_query_returns_all_results(): void
    {
        // Seed data on each shard
        foreach (ShardManager::allConnections() as $connection) {
            Order::on($connection)->create([
                'user_id' => rand(1, 1000),
                'total' => 100,
                'status' => 'completed',
            ]);
        }

        $all = Order::onAllShards();
        $this->assertCount(3, $all); // One per shard
    }
}

Conclusion

Sharding is a last resort for scaling — but when you need it, you need to implement it correctly. Checklist:

1. Exhaust alternatives first — optimize, cache, replicas, partitioning
2. Choose the shard key carefully — usually user_id or tenant_id, must be immutable
3. Use UUIDs for primary keys — avoid ID conflicts
4. Hash-based for even distribution — safe default
5. Denormalize — avoid cross-shard joins, accept data duplication
6. Global DB for shared data — users, config, permissions
7. Saga pattern for cross-shard transactions — compensating actions
8. Monitor distribution — alert when skew >20%
9. Test shard routing — deterministic, evenly distributed
10. Automate migrations — migrate/seed all shards with one command