Database sharding in MySQL per applicazioni Laravel con milioni di record

A marzo 2025 ho lavorato su una piattaforma SaaS italiana del settore e-commerce B2B - circa 2.800 aziende rivenditori che usano il sistema per gestire cataloghi, ordini, movimenti magazzino - dove la tabella movimenti_stock aveva accumulato 8,3 milioni di righe in quattro anni di operatività. La latenza delle query stava diventando insostenibile: un tempo di risposta P95 di 4.2 secondi per le query di dashboard, punta di 12 secondi durante il reporting di fine mese. Il team aveva già aggiunto ogni indice ragionevole possibile, aveva messo in piedi read replica MySQL, aveva ottimizzato le query di reporting con EXPLAIN e query tuning. I miglioramenti erano stati marginali. Il problema di fondo era che la tabella era diventata così grande da non stare più nel buffer pool InnoDB, e ogni query che non poteva essere risolta da indice puro doveva fare I/O fisico su disco - operazione lenta anche su NVMe veloci. Un database da 120 GB con 8 milioni di righe attive, su un server con 64 GB di RAM, soffriva di cache miss costanti.

In dodici settimane ho implementato lo sharding orizzontale della tabella principale per tenant - ognuno dei 2.800 rivenditori ha i propri movimenti isolati in una partizione logica separata, con 16 shard fisici distribuiti su due server MySQL. Al completamento del progetto: latenza P95 delle query è scesa da 4.2s a 180ms (miglioramento 23x), i report di fine mese che prima richiedevano 45 minuti ora girano in 90 secondi, il buffer pool di ciascun shard è sufficiente per tenere caldo l'intero working set del tenant attivo, non ci sono più contese di lock cross-tenant. Questo articolo descrive la strategia di sharding, la migrazione senza downtime di 8 milioni di righe, e soprattutto le trappole operative che rendono il database sharding una scelta architetturale "di ultima istanza" da valutare con molta attenzione prima di adottarla.

Quando sharding ha senso e quando è over-engineering

La domanda prima di qualsiasi implementazione è: serve davvero?. Lo sharding introduce complessità operativa significativa e non è reversibile con facilità. Le tre condizioni che giustificano sharding, se presenti tutte e tre, sono: dataset che non può stare nel buffer pool anche di server grande, workload fortemente multi-tenant dove i dati di diversi tenant raramente si intersecano in query, crescita prevedibile dei volumi che rende non sostenibile lo scale-up verticale.

Per molti casi d'uso dove la prima reazione è "ho tabella grande, devo fare sharding", in realtà bastano approcci meno invasivi. Table partitioning MySQL nativo è spesso sufficiente per dataset time-series (partizionamento per mese), read replica risolve workload read-heavy, archiviazione in tabelle separate gestisce dataset storici poco acceduti. Lo sharding vero - distribuzione su più database fisici con routing applicativo - ha senso quando queste tecniche sono state già applicate ma non bastano.

Sul cliente e-commerce B2B, le tre condizioni erano tutte presenti: il dataset non entrava nel buffer pool (120 GB di data vs 48 GB buffer pool disponibili dopo assegnazione conservativa), il pattern era strict per-tenant (99.9% delle query filtravano per tenant_id in prima istanza), la crescita prevista era 30-40% annuo per i successivi 3 anni. Senza sharding, nel giro di 18-24 mesi saremmo stati in crisi strutturale. Con sharding, l'architettura scala linearmente aggiungendo shard.

La scelta della chiave di sharding: la decisione che determina tutto il resto

La chiave di sharding è la dimensione su cui i dati vengono distribuiti. La scelta è critica e difficilmente modificabile in futuro - fare rebalance di uno sharding con chiave sbagliata è un progetto di mesi, non di giorni.

Le tre opzioni principali per il cliente erano: sharding per tenant_id (ogni rivenditore sul proprio shard), sharding per data_movimento (ogni mese in uno shard diverso), sharding per prodotto_id (distribuzione per SKU). Ognuna ha trade-off.

Sharding per tenant_id. Pro: isolamento perfetto per tenant - workload noisy di un singolo rivenditore non impatta gli altri. Query per tenant singolo sono super-veloci (coinvolgono un solo shard). Semplicità di comprensione e debugging. Contro: rischio di "tenant gigante" che satura uno shard più degli altri. Query cross-tenant (tipiche del reporting globale dell'amministrazione piattaforma) richiedono scatter-gather.

Sharding per data_movimento. Pro: query time-series sono veloci (toccano pochi shard). Archiviazione di dati vecchi è semplice (drop dello shard più vecchio). Contro: hotspot pesante sul shard corrente - tutte le scritture vanno lì. Bilanciamento complesso.

Sharding per prodotto_id. Pro: distribuzione uniforme se i prodotti sono simili. Contro: rompe le query per tenant (query "dammi tutti i movimenti di Acme SRL" tocca tutti gli shard). Query per prodotto singolo efficienti ma molto meno frequenti delle query per tenant.

La scelta finale è stata tenant_id perché si allineava al 99% delle query dell'applicazione. L'1% di query cross-tenant (reporting amministrativo) è stato gestito con aggregation job asincrono che pre-computa views cross-tenant in tabella separata. Questo è il pattern che descrivo in dettaglio nel mio articolo su CQRS pragmatico in Laravel per separare letture analitiche da scritture transazionali - i due pattern si combinano naturalmente.

L'architettura: 16 shard su 2 server, hash consistente del tenant_id

L'implementazione usa 16 shard logici distribuiti su 2 server fisici (8 shard per server). La scelta di 16 invece di 2 (uno per server) è deliberata: la granularità maggiore permette di aggiungere server nuovi in futuro senza ri-shardare - se voglio 4 server, basta spostare 4 shard logici da ogni server esistente al nuovo.

Il routing di ogni tenant al suo shard avviene tramite hash consistent:

<?php
// app/Services/Sharding/ShardResolver.php
namespace App\Services\Sharding;

class ShardResolver
{
    private const NUM_SHARDS = 16;

    // Mappa shard_id -> connection name (definite in config/database.php)
    private const SHARD_TO_CONNECTION = [
        0 => 'shard_server1_0',
        1 => 'shard_server1_1',
        // ... shard 0-7 su server1
        8 => 'shard_server2_8',
        9 => 'shard_server2_9',
        // ... shard 8-15 su server2
    ];

    public function resolveConnection(int $tenantId): string
    {
        $shardId = $this->resolveShard($tenantId);
        return self::SHARD_TO_CONNECTION[$shardId];
    }

    public function resolveShard(int $tenantId): int
    {
        // Hash consistent: tenant_id sempre sul stesso shard
        return crc32("tenant:{$tenantId}") % self::NUM_SHARDS;
    }
}

La scelta di crc32 come funzione di hashing è calibrata: è veloce (operazione intera senza crypto overhead), distribuisce bene sui valori integer di tenant_id, produce risultati deterministic. Non è sicura in senso crittografico ma questo non è un requisito - serve solo a distribuire uniformemente i tenant sugli shard.

Ogni tenant conosce il proprio shard in un'unica query al database "directory" (un piccolo database separato che mantiene la mappa tenant_id → shard_id). Questa lookup è cacheata in Redis per 24 ore per ogni tenant - il DB directory viene consultato solo al primo accesso.

L'integrazione in Laravel: gestione multi-connection con Eloquent

Laravel supporta nativamente multiple connection. L'integrazione di sharding estende questo pattern: ogni model definisce dinamicamente su quale connection vivere in base al tenant corrente.

<?php
// app/Models/MovimentoStock.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Services\Sharding\ShardResolver;

class MovimentoStock extends Model
{
    protected $table = 'movimenti_stock';

    protected $fillable = [
        'tenant_id', 'prodotto_id', 'tipo', 'quantita',
        'data_movimento', 'note',
    ];

    protected static function booted(): void
    {
        static::creating(function (MovimentoStock $movimento) {
            if (!$movimento->tenant_id) {
                throw new \RuntimeException('tenant_id obbligatorio per MovimentoStock');
            }
            $movimento->setConnection(
                app(ShardResolver::class)->resolveConnection($movimento->tenant_id)
            );
        });

        static::retrieved(function (MovimentoStock $movimento) {
            $movimento->setConnection(
                app(ShardResolver::class)->resolveConnection($movimento->tenant_id)
            );
        });
    }

    public function newQueryForContext(int $tenantId)
    {
        $connection = app(ShardResolver::class)->resolveConnection($tenantId);
        return $this->newQuery()->on($connection);
    }
}

L'utilizzo nel codice applicativo richiede di specificare sempre il tenant:

// creazione: la connection viene settata automaticamente dall'hook
MovimentoStock::create([
    'tenant_id' => $request->user()->tenant_id,
    'prodotto_id' => 42,
    'quantita' => 5,
    // ...
]);

// query: serve un metodo wrapper per indirizzare lo shard giusto
$movimenti = (new MovimentoStock())
    ->newQueryForContext($tenantId)
    ->where('prodotto_id', 42)
    ->where('data_movimento', '>=', now()->subMonth())
    ->get();

Questo pattern richiede disciplina: ogni query deve specificare il tenant. Senza disciplina, un developer può accidentalmente fare MovimentoStock::where(...)->get() su un model che non ha connection settata, e Laravel userebbe la connection default - che non punta a uno shard valido e produce errore. L'utilizzo di PHPStan custom rule aiuta a enforceare questo pattern - il pattern di analisi statica rigorosa su Laravel con PHPStan integrato nel CI/CD si estende a regole custom specifiche di dominio.

Stai cercando un Consulente Informatico esperto per valutare se il database sharding è la scelta architetturale giusta per la tua applicazione Laravel, e per implementarlo con approccio pragmatico che minimizzi complessità operativa? Nel mio profilo professionale trovi l'esperienza concreta su scaling MySQL, architetture multi-tenant, sharding e pattern di distribuzione del carico su database per piattaforme SaaS B2B italiane.

La migrazione: come spostare 8 milioni di righe senza downtime

La parte più delicata del progetto è stata la migrazione dello stato esistente. 8 milioni di righe da un singolo database a 16 shard, mantenendo l'applicazione attiva. Il pattern è stato dual-write in tre fasi.

Fase 1 - dual-write con shard come secondary. L'applicazione scrive sia sul database originale (primary, autoritativo) sia sui shard (secondary, verificato). Le letture avvengono sempre dal primary. Un job asincrono confronta primary e shard periodicamente per rilevare divergenze. Durata di questa fase: 2 settimane.

Fase 2 - backfill storico. In parallelo al dual-write, un job di copia massiva legge i record storici dal primary e li scrive sui shard. Copia in batch da 10.000 righe per volta, commit frequenti per non saturare il buffer pool, throttling per non impattare il carico di produzione. Per 8 milioni di righe, il backfill ha richiesto ~14 ore distribuite su un weekend. Durata totale fase: 2-3 giorni.

Fase 3 - switch delle letture. Una volta che il backfill è completo e le divergenze primary/shard sono zero, si switch il routing delle letture: le query iniziano a leggere dagli shard invece che dal primary. Il primary rimane come fallback per 2 settimane ulteriori, dove si continua con dual-write e monitoring. Al termine, il primary viene messo in sola lettura e poi eliminato.

La procedura completa ha richiesto circa 6 settimane end-to-end, con zero secondi di downtime. Il costo del dual-write durante la transizione è stato un incremento del 15-20% del carico di scrittura (accettabile su hardware dimensionato per la transizione).

Lo script di backfill era strutturato così:

<?php
// app/Console/Commands/BackfillShardMigration.php
public function handle(): int
{
    $batchSize = 10000;
    $lastMigratedId = (int) Cache::get('shard_backfill_last_id', 0);

    $resolver = app(ShardResolver::class);

    while (true) {
        $batch = DB::connection('primary')
            ->table('movimenti_stock')
            ->where('id', '>', $lastMigratedId)
            ->orderBy('id')
            ->limit($batchSize)
            ->get();

        if ($batch->isEmpty()) {
            $this->info("Backfill completato");
            break;
        }

        // Raggruppa per shard per bulk insert efficiente
        $byShard = $batch->groupBy(fn($r) => $resolver->resolveShard($r->tenant_id));

        foreach ($byShard as $shardId => $records) {
            $conn = $resolver->resolveConnection($records->first()->tenant_id);
            DB::connection($conn)->table('movimenti_stock')
                ->insertOrIgnore($records->map(fn($r) => (array) $r)->toArray());
        }

        $lastMigratedId = $batch->max('id');
        Cache::put('shard_backfill_last_id', $lastMigratedId);

        // Throttling per non saturare produzione
        $this->info("Migrato fino a ID {$lastMigratedId}, sleep 200ms");
        usleep(200000);
    }

    return 0;
}

Il dettaglio critico è insertOrIgnore invece di insert: se il job viene killato e riavviato, o se il dual-write ha già scritto alcuni record di quelli che stiamo facendo backfill, l'idempotenza dell'operazione evita errori di duplicate key.

Le query cross-shard: aggregation e reporting

Le query cross-tenant (reporting amministrativo, analytics globali) sono il punto più delicato dello sharding. Non si possono eseguire con una singola query SQL - richiedono scatter-gather: interrogare tutti gli shard in parallelo e aggregare i risultati applicativamente.

Il pattern di implementazione usa parallelizzazione con Laravel Octane Concurrently:

<?php
// app/Services/Sharding/CrossShardQuery.php
namespace App\Services\Sharding;

use Laravel\Octane\Facades\Concurrently;

class CrossShardQuery
{
    public function totalMovimentiByDateRange(string $from, string $to): int
    {
        $shards = range(0, 15);

        $results = Concurrently::run(
            array_map(
                fn($shardId) => fn() => DB::connection("shard_server*_{$shardId}")
                    ->table('movimenti_stock')
                    ->whereBetween('data_movimento', [$from, $to])
                    ->count(),
                $shards
            )
        );

        return array_sum($results);
    }
}

Il pattern Concurrently di Octane esegue in parallelo le 16 query, ognuna sul suo shard, e aggrega il risultato. Il tempo di esecuzione della query aggregata è approssimativamente il tempo della query più lenta tra i 16 shard, non la somma. Su 16 shard ciascuno con 500k righe (distribuzione uniforme dei 8M record), ogni query individuale gira in 20-40 ms, e l'aggregata completa gira in 60-100 ms.

Il pattern è descritto in dettaglio nel mio articolo sul pattern PHP Fibers per concorrenza cooperativa su Laravel ad alto carico - lo sharding sfrutta la stessa primitiva Fiber per parallelizzare operazioni I/O-bound.

I costi operativi: il lato oscuro dello sharding

Lo sharding risolve problemi di performance ma introduce complessità operativa che non va sottovalutata. Cinque aree dove il carico di gestione aumenta significativamente.

Area 1 - backup e disaster recovery. Ogni shard è un database separato con la sua strategia di backup. Orchestrare il backup coordinato di 16 shard richiede automazione specifica - il backup "in-flight" di uno shard mentre un altro sta già restorando produce inconsistenza. Il pattern canonico è backup simultaneo di tutti gli shard con barriere di consistency.

Area 2 - monitoring. Invece di monitorare un database, monitorri 16. Le metriche che raccoglievi in Prometheus per un DB diventano 16 serie temporali, e devi imparare a leggere aggregazioni (media, massimo, P95 cross-shard) invece di valori singoli. La dashboard Grafana per il monitoraggio di VPS Linux e applicazioni PHP con Prometheus che descrivo in un articolo dedicato si complica in contesto shardato.

Area 3 - schema migrations. Ogni cambio di schema deve essere applicato a tutti i 16 shard coordinatamente. Eseguirli in sequenza su 16 database richiede 16x il tempo del singolo; eseguirli in parallelo richiede orchestrazione. Laravel standard migrate non supporta multiple connection nativamente - serve custom command che itera su tutte le connessioni.

Area 4 - debugging produzione. Un ticket utente "non vedo il movimento che ho creato 5 minuti fa" non può essere debuggato con "guardo nel database" - serve prima determinare su quale shard vive quel tenant, poi connetterci al server giusto, poi eseguire la query. Aggiungi 30 secondi a ogni debug cycle.

Area 5 - query ad-hoc del business. Il team commerciale o il CEO che vogliono lanciare una query esplorativa si trovano a fare i conti con lo sharding. La soluzione operativa è una database "materialized view" che aggrega periodicamente i dati cross-shard in un database di sola lettura dedicato a query esplorative - ma è un sistema aggiuntivo da mantenere.

Quando sharding non è la risposta: le alternative migliori per PMI

Dopo aver vissuto l'implementazione, la conclusione onesta è che lo sharding è raramente la scelta giusta per PMI italiane. Il caso del cliente e-commerce B2B era eccezionale - dataset oltre i 100 GB, crescita strutturale prevedibile, workload multi-tenant pure. Per la maggioranza delle PMI, alternative meno invasive producono il 80-90% del beneficio con il 10-20% del costo operativo.

Le alternative da esplorare prima dello sharding sono tre. Prima: MySQL partitioning nativo. Spezza una tabella grande in partizioni per range (tipicamente tempo) che MySQL gestisce trasparentemente. Tutto il SQL normale funziona, nessuna modifica applicativa. Il motore sceglie automaticamente la partizione giusta quando la query filtra sulla colonna di partizionamento. Funziona bene per pattern time-series e per tabelle che possono essere archiviate per partizioni vecchie.

Seconda: read replica con router intelligente. Le query di lettura vanno a repliche, le scritture vanno al master. Se l'applicazione è read-heavy (tipicamente reporting), distribuire il carico su 2-3 replica risolve molti problemi di performance senza toccare l'architettura. Laravel supporta nativamente questo pattern via configurazione del database.

Terza: archiviazione aggressiva dei dati storici. La tabella principale contiene solo i dati degli ultimi N mesi. I dati più vecchi vengono spostati periodicamente in una tabella di archivio separata (o in un DB separato). Le query operational toccano solo la tabella attiva (che rimane piccola), le query storiche toccano l'archivio (più lento ma meno frequente). Questo pattern, più invasivo delle prime due ma meno dello sharding, risolve l'80% dei casi d'uso dove il problema è "troppi dati storici".

Sul cliente e-commerce, prima di arrivare allo sharding, abbiamo tentato tutte e tre le alternative. Il partitioning MySQL non bastava perché il problema non era solo temporale. Le read replica aiutavano ma il master scrittura restava bottleneck. L'archiviazione aggressiva avrebbe richiesto di separare movimenti "recenti" da "storici" in modo che l'applicazione non apprezzava (gli utenti tipicamente vedono movimenti di 6-12 mesi, non solo 1-2). Solo dopo aver esaurito le alternative meno costose siamo passati allo sharding.

I numeri finali: il ROI quantificato

Dodici settimane di lavoro per la migrazione sharding. Costo complessivo per il cliente: circa 60.000 euro fra consulenza mia e ore team interno. Benefici misurabili a 6 mesi dal completamento: latenza P95 scesa da 4.2s a 180ms (23x miglioramento), tasso di abbandono sulla dashboard principale (utenti che chiudevano la pagina prima del completamento load) sceso da 8% a 0.3%, uso CPU server database dal 85% medio al 35% medio (capacità per ulteriore crescita 3x-4x). Ritorno: difficile quantificare esattamente ma il CEO ha riportato un aumento di 12% del tempo medio per sessione sulla piattaforma nei 3 mesi post-rollout, e una riduzione delle lamentele commerciali su "piattaforma lenta" pressoché totale.

Il costo nascosto: incremento permanente del carico operativo di gestione del DB di circa 10-12 ore/mese, distribuito fra DBA interno e supporto esterno mio. Questo costo è strutturale e va considerato nel TCO di lungo periodo. Sul cliente specifico, il trade-off è chiaramente favorevole perché la crescita attesa continuerà per anni a pagare questo investimento. Su altri contesti potrebbe non esserlo.

Se gestisci un database MySQL/PostgreSQL che cresce verso problemi strutturali di performance e hai già applicato le ottimizzazioni ragionevoli (indici, query tuning, read replica), e stai iniziando a valutare database sharding o migrazione a database distribuiti come Vitess/CockroachDB, contattami per una valutazione: in una settimana analizzo il profilo di carico reale del tuo database, identifico le alternative prima dello sharding che potrebbero essere sufficienti, e se sharding è davvero necessario, disegno l'architettura calibrata sulla tua situazione specifica - con la chiara comunicazione dei costi operativi che questa architettura porta, in modo che la decisione del management sia informata e non guidata solo dall'entusiasmo architetturale.