Questa domanda può essere riformulata più in generale come "quando denormalizzo i dati per migliorare le prestazioni". E la risposta è "quando il costo di eseguire una query normalizzata supera la soglia delle prestazioni e il costo di mantenere i dati duplicati è giustificato".
Iniziamo con un progetto relazionale tradizionale per una banca. Come calcoli il saldo del conto corrente? In uno schema completamente normalizzato, si esegue una query sulla tabella delle transazioni. Tuttavia, i tuoi utenti controlleranno continuamente i loro saldi e non vorrai pagare il costo di tale query per ogni richiesta di saldo. Quindi prendi la decisione di aggiungere un campo alla tua tabella ACCOUNTS, in cui memorizzi il saldo corrente e lo aggiorni come parte di ogni transazione.
D'altra parte, dato il requisito "mostrare ai clienti l'importo totale che hanno speso, per beneficiario", probabilmente eseguirai la query rispetto alle transazioni, piuttosto che tentare di denormalizzare. Mentre ci possono essere alcuni utenti che controllano la loro suddivisione dei pagamenti su base giornaliera, la maggior parte degli utenti non lo farà mai. Quindi non vuoi sprecare spazio su disco e tempo di codifica su una tabella denormalizzata.
Penso che la stessa cosa valga per "mostrami tutti i miei commenti". Sì, ci sono alcuni utenti che lo faranno sempre (e io potrei essere uno di questi, controllo spesso il tag "Attività" su SO per prestare attenzione alle domande a cui ho risposto o commentato). Ma probabilmente non è la maggioranza dei tuoi utenti, quindi probabilmente non è così costoso nel grande schema delle cose eseguire una query con tutti i frammenti per recuperare i dati.
O forse lo è. In tal caso, devi rispondere alla domanda "chi deve essere padrone" (o, senza il riferimento di Alice nel Paese delle Meraviglie, la "fonte autorevole"). Nel caso del conto bancario, la tabella delle transazioni è sempre quella principale. Se, per qualche motivo, il saldo memorizzato nell'account è diverso da quello calcolato dalle transazioni, deve aggiornare il primo da quest'ultimo.
Nel caso di commenti sul blog, credo che il blog sia la fonte autorevole. Ciò significa che, al termine della richiesta, desidero che la tabella venga aggiornata, indipendentemente da ciò che accade alla tabella commenti per utente. E voglio anche che la tabella commenti per utente abbia un riferimento alla tabella commenti per entrata, in modo che io possa ricostruirla se i due non vengono sincronizzati.
Il modo in cui lo realizzi è un compromesso tra complessità, tempo di risposta e quanto sia importante mantenere sincronizzate le due tabelle.
Come fai notare, avere trigger tra i vari frammenti è sciocco; l'intero motivo per cui il sharding è operazioni di database indipendenti. Quindi puoi buttarlo fuori subito.
L'aggiornamento di entrambe le tabelle contemporaneamente è l'approccio con il minor numero di parti mobili. A lungo termine, sarà il più gestibile. E sarà il più facile eseguire il debug se qualcosa va storto.
Ma se il tempo di risposta è importante, si potrebbe pensare ad una sorta di approccio di messaggistica: aggiornare la tabella commenti per entrata e accodare un messaggio per aggiornare la tabella commenti per utente. Se ci vuole un'ora per elaborare quel messaggio, o se si perde in un crash del sistema, non è un grosso problema, puoi sempre recuperare. In nessun caso dovresti utilizzare un approccio di messaggistica per aggiornare entrambe le tabelle.
Quindi, la linea di fondo è che non ci sono risposte chiare; si tratta di compromessi. Ed è per questo che ci pagano.