👋 Suivre l’activité de votre base minute par minute ? C’est possible (et simple)

Vous gérez une base de données avec des millions d’enregistrements, et vous cherchez à visualiser son activité en temps réel : évolution ou modification des données. Une simple requête SQL ne suffit plus.

Pas de panique, vous pouvez maintenant générer des données en temps réel avec PostgreSQL et crosstab(). Cela permet de transformer vos données brutes en un tableau de bord dynamique et agrégé, avec répartition des données par minute et par table. Et tout ça, sans quitter votre base !

Oui oui, même pas besoin d’un outil externe 🧙‍♂️

Vous l’avez compris, dans cet article, nous allons voir comment construire une vue dynamique qui affiche le nombre d’enregistrements insérés ou modifiés par minute sur chaque table pertinente. ✅

🧠Comprendre les concepts clés : crosstab(), tables dynamiques et pivot

🤖 Marius pose le décor :

« Ta base, c’est un aéroport. Chaque table est une piste, chaque insertion ou modification un avion qui atterrit. Tu veux voir à quelle minute chaque piste a accueilli du trafic. Bref, tu veux une tour de contrôle de tes données. »

Objectif du jour : construire une vue agrégée et dynamique qui affiche le nombre d’événements par minute (insertions ou modifications), répartis par table.

🔧 Les outils de notre boîte à outils PostgreSQL

📦 Extension tablefunc et la fonction crosstab()

crosstab() est une fonction fournie par l’extension tablefunc de PostgreSQL. Elle permet de pivoter les lignes en colonnes, comme dans un tableau Excel, pour afficher des données croisées.

C’est l’outil parfait pour visualiser combien de lignes ont été créées ou modifiées par minute dans chaque table, ce qui permet de créer des rapports et des tableaux de bord dynamiques aux petits oignons.

📚 Principe de fonctionnement

1. On extrait les données (à partir de x_datecreated ou x_datechanged).
2. On regroupe les valeurs par minute et par table.
3. On utilise crosstab() pour transformer ces lignes en colonnes dynamiques.

🤖 Marius :

« C’est comme un tableau Excel où tu veux voir chaque minute en ligne et chaque table en colonne, avec le nombre d’enregistrements comme valeurs. PostgreSQL le fait pour toi, en temps réel ! »

🛠️ Tutoriel : générer un tableau dynamique avec PostgreSQL

Voici la requête SQL pour obtenir notre tableau pivot. Elle permet de choisir dynamiquement entre x_datecreated et x_datechanged.

Voici comment générer un tableau dynamique en SQL (pas besoin d’outil tiers ni de frontend compliqué).

1️⃣ Activer l’extension tablefunc :

sql
CopierModifier
CREATE EXTENSION IF NOT EXISTS tablefunc;

2️⃣ Lister les tables pertinentes

On crée une table temporaire z_pivot pour lister toutes les tables contenant la colonne choisie (x_datecreated ou x_datechanged).

sql
CopierModifier
DROP TABLE IF EXISTS z_pivot;
CREATE TABLE z_pivot (
d_table text NOT NULL PRIMARY KEY
);

<?sql
CopierModifier
DO $$
DECLARE
    p_date_column text := 'x_datecreated';  -- À modifier selon votre besoin
BEGIN
    IF p_date_column NOT IN ('x_datecreated', 'x_datechanged') THEN
        RAISE EXCEPTION 'Paramètre invalide : utilisez "x_datecreated" ou "x_datechanged"';
    END IF;

    INSERT INTO z_pivot (d_table)
    SELECT tablename
    FROM pg_tables
    WHERE schemaname = 'public'
      AND tablename NOT IN ('z_pivot', 'z_pivot_results')
      AND EXISTS (
          SELECT 1
          FROM information_schema.columns
          WHERE table_schema = 'public'
            AND table_name = tablename
            AND column_name = p_date_column
      );
END $$;

3️⃣ Générer le tableau dynamique avec crosstab()

Ce bloc va construire dynamiquement la requête SQL à exécuter pour créer un tableau croisé avec les données agrégées par minute.

sql
sql
CopierModifier
DO $$
DECLARE
    l_tables text[];
    l_sql text;
    i int;
    table_name text;
    p_date_column text := 'x_datecreated';
BEGIN
    SELECT array_agg(d_table ORDER BY d_table) INTO l_tables FROM z_pivot;

    IF l_tables IS NULL OR cardinality(l_tables) = 0 THEN
        RAISE NOTICE 'Aucune table avec % trouvée. Abandon.', p_date_column;
        RETURN;
    END IF;

    l_sql := 'DROP TABLE IF EXISTS z_pivot_results; '
          || 'CREATE TABLE z_pivot_results AS '
          || 'SELECT * '
          || 'FROM crosstab($CT$ '
          || '    SELECT date_trunc(''minute'', ' || p_date_column || ')::timestamp, d_table, COUNT(*)::int AS nb_events '
          || '    FROM (';

    FOR i IN 1 .. cardinality(l_tables) LOOP
        table_name := quote_ident(l_tables[i]);
        l_sql := l_sql || 'SELECT ' || p_date_column || ', ' || quote_literal(l_tables[i]) || ' AS d_table FROM ' || table_name;
        IF i < cardinality(l_tables) THEN
            l_sql := l_sql || ' UNION ALL ';
        END IF;
    END LOOP;

    l_sql := l_sql || ') AS all_tables WHERE ' || p_date_column || ' >= date_trunc(''day'', now()) '
          || 'GROUP BY 1,2 ORDER BY 1,2 $CT$, '
          || ' $CT$ SELECT d_table FROM z_pivot ORDER BY d_table $CT$) AS pivot_table(minute_utc timestamp';

    FOR i IN 1 .. cardinality(l_tables) LOOP
        table_name := quote_ident(l_tables[i]);
        l_sql := l_sql || ', "' || table_name || '" int';
    END LOOP;

    l_sql := l_sql || ' );';
    EXECUTE l_sql;
END $$;

4️⃣ Visualiser le tableau final

sql
CopierModifier
SELECT * FROM public.z_pivot_results ORDER BY minute_utc DESC;

⚡️ Bonnes pratiques et pièges à éviter

💡 Bonnes pratiques

✅ Spécifiez clairement la colonne de date utilisée (x_datecreated ou x_datechanged)

✅ Vérifier que toutes les tables pertinentes contiennent bien cette colonne

✅ Utiliser date_trunc('minute', x_datecreated) pour regrouper précisément par minute.

🔴 Pièges à éviter

❌ Ne pas activer l’extension tablefunc

❌ Ne pas oublier d’activer tablefunc.

❌ Si une table ne contient pas x_datecreated ou x_datechanged, elle sera ignorée.

💪 Conclusion : PostgreSQL, un vrai tableau de bord temps réel !

Créer des données en temps réel avec PostreSQL et Crosstab est maintenant une affaire qui roule !
En combinant les fonctions avancées de PostgreSQL avec une pincée de logique métier, vous obtenez un véritable tableau de bord temps réel, directement dans votre base de données.

🤖 Marius conclut :

« C’est comme brancher un électrocardiogramme à ta base : tu vois chaque battement, chaque minute. Et le tout sans outil externe, sans surcouche. »

Il ne vous reste plus qu’à tester cette requête sur votre base ! 🚀