Obsolescence de la Colonne de Priorité Dénormalisée

Problème

Lors du tri des enregistrements par colonnes d'agrégat dénormalisées (comme total_loops, vine_count, order_count), la requête retourne les éléments dans le mauvais ordre de priorité car les valeurs dénormalisées sont obsolètes, non remplies ou incorrectes.

Contexte / Conditions Déclenchantes

• Un job batch traite les éléments dans un ordre inattendu
• Les éléments prioritaires (selon une certaine métrique d'agrégat) sont traités en dernier ou ignorés
• ORDER BY aggregate_column DESC ne retourne pas les résultats attendus
• Les colonnes d'agrégat affichent 0 ou NULL pour les enregistrements qui devraient avoir des valeurs élevées
• Seul un sous-ensemble d'enregistrements a la colonne d'agrégat remplie
• Les scripts de backfill ont peut-être été exécutés pour certains enregistrements mais pas d'autres

Cause Racine

Les colonnes dénormalisées (copies de données agrégées stockées pour les performances des requêtes) peuvent devenir obsolètes quand :

1. La migration initiale des données ne les a pas remplies
2. Les scripts de backfill n'ont été exécutés que pour certains enregistrements
3. De nouveaux enregistrements sources ont été ajoutés sans mettre à jour la colonne dénormalisée
4. La logique d'agrégation a changé mais la colonne n'a pas été recalculée

Solution

Option 1 : Calculer au Moment de la Requête (Correctif Immédiat)

Joindre avec la table source pour calculer les vrais agrégats :

-- AVANT (cassé) : Utilise une colonne dénormalisée potentiellement obsolète
SELECT user_id, username
FROM users
WHERE status = 'pending'
ORDER BY total_loops DESC NULLS LAST;

-- APRÈS (corrigé) : Calcule l'agrégat réel à partir de la source
SELECT u.user_id, u.username,
       COALESCE(SUM(vm.loops), 0) as actual_total_loops
FROM users u
LEFT JOIN vine_metadata vm ON u.user_id = vm.user_id
WHERE u.status = 'pending'
GROUP BY u.user_id, u.username
ORDER BY actual_total_loops DESC;

Option 2 : Backfiller la Colonne Dénormalisée (Correctif Permanent)

Mettre à jour la colonne dénormalisée à partir des données source :

UPDATE users u
SET total_loops = subq.actual_loops
FROM (
    SELECT user_id, COALESCE(SUM(loops), 0) as actual_loops
    FROM vine_metadata
    GROUP BY user_id
) subq
WHERE u.user_id = subq.user_id;

Option 3 : Utiliser des Vues Matérialisées (Le Meilleur des Deux)

Créer une vue matérialisée pour les agrégats :

CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id,
       COUNT(*) as item_count,
       SUM(loops) as total_loops
FROM vine_metadata
GROUP BY user_id;

-- Rafraîchir périodiquement
REFRESH MATERIALIZED VIEW user_stats;

Vérification

Après avoir appliqué le correctif, vérifiez que la requête retourne les résultats attendus :

-- Vérifier que les éléments prioritaires sont réellement prioritaires
SELECT user_id, username, actual_total_loops
FROM (your_fixed_query)
LIMIT 10;

-- Comparer avec l'agrégat direct
SELECT user_id, SUM(loops) as loops
FROM source_table
GROUP BY user_id
ORDER BY loops DESC
LIMIT 10;

Exemple

Scénario : Le récupérateur d'avatars devrait traiter d'abord les meilleurs Viners (par total de loops), mais traite à la place les utilisateurs avec le préfixe d'ID "10" (ordre effectivement aléatoire).

Investigation :

-- Vérifier si la colonne dénormalisée est remplie
SELECT
    COUNT(*) as total,
    SUM(CASE WHEN loop_count > 0 THEN 1 ELSE 0 END) as has_loop_count
FROM users;
-- Résultat : Seuls 29 878 sur 119 785 utilisateurs ont loop_count rempli

-- Vérifier les meilleurs utilisateurs par dénormalisé vs réel
SELECT u.user_id, u.username, u.loop_count as denormalized,
       SUM(vm.loops) as actual
FROM users u
JOIN vine_metadata vm ON u.user_id = vm.user_id
GROUP BY u.user_id, u.username, u.loop_count
ORDER BY SUM(vm.loops) DESC
LIMIT 5;
-- Résultat : Les meilleurs créateurs affichent loop_count=0 mais actual=1 281 730 353

Correctif : Modification de la requête pour faire une JOINTURE avec vine_metadata et trier par la somme calculée.

Notes

• C'est un compromis classique de dénormalisation : lectures plus rapides vs données obsolètes
• Lors de la dénormalisation, implémentez toujours des triggers ou des mises à jour au niveau applicatif pour rester synchronisé
• Envisagez si la requête d'agrégat est assez rapide pour être calculée à l'exécution
• LEFT JOIN garantit que les enregistrements sans données source apparaissent quand même (avec des valeurs 0)
• Utilisez COALESCE(SUM(...), 0) pour gérer correctement les agrégats NULL
• NULLS LAST dans ORDER BY empêche les valeurs NULL de se trier en premier en ordre DESC

Motifs Connexes

• Event sourcing : Conserver les événements source, calculer les agrégats au besoin
• CQRS : Modèles de lecture séparés qui sont explicitement mis à jour
• Triggers : Mettre à jour automatiquement les colonnes dénormalisées lors de changements source