Colonnes générées PostgreSQL pour accélérer les filtres d'administration

Pourquoi les écrans d'administration deviennent vite lents et brouillons

Les écrans d'administration commencent souvent simple : une table, quelques filtres, peut‑être un tri par « plus récent d'abord ». Puis la réalité arrive. Le support veut une recherche qui trouve les clients par nom, e‑mail et téléphone. Les commerciaux veulent trier par « dernière activité ». La finance veut « solde impayé ». Chaque demande ajoute des conditions, des jointures et des calculs supplémentaires.

La plupart des listes d'administration ralentissent pour la même raison : chaque clic modifie la requête. Le filtrage et le tri peuvent pousser la base à scanner beaucoup de lignes, surtout quand la requête doit calculer une valeur pour chaque ligne avant de savoir si elle correspond.

Un point de bascule fréquent arrive quand WHERE et ORDER BY se remplissent d'expressions. Au lieu de filtrer sur une colonne simple, on filtre sur lower(email), date_trunc('day', last_seen_at) ou un CASE qui regroupe plusieurs statuts en un seul « bucket ». Ces expressions sont non seulement plus lentes, elles rendent le SQL plus difficile à lire, plus difficile à indexer et plus facile à casser.

Le SQL d'administration en désordre naît généralement de quelques motifs récurrents :

• Un champ de « recherche » qui vérifie plusieurs colonnes avec des règles différentes
• Le tri par une valeur dérivée (nom complet, score de priorité, « dernière activité significative »)
• Des règles métier copiées entre écrans (actif vs inactif, payé vs en retard)
• De petits ajustements « utilitaires » (trim, lower, coalesce) dispersés partout
• La même valeur calculée utilisée dans la liste, les filtres et le tri

Les équipes essaient souvent de masquer ça côté application : générateurs de requêtes dynamiques, jointures conditionnelles, ou précalculs en code. Ça peut marcher, mais ça répartit la logique entre l'UI et la base, ce qui rend le débogage des requêtes lentes pénible.

L'objectif est simple : des requêtes rapides et lisibles. Quand une valeur calculée réapparait sur plusieurs écrans d'administration, les colonnes générées PostgreSQL permettent de centraliser la règle tout en laissant la base l'optimiser.

Les colonnes générées en clair

Une colonne générée est une colonne classique dont la valeur est calculée à partir d'autres colonnes. Vous n'écrivez pas la valeur. PostgreSQL la remplit avec l'expression que vous définissez.

Dans PostgreSQL, les colonnes générées sont stockées. PostgreSQL calcule la valeur à l'insertion ou à la mise à jour, puis la sauvegarde sur disque comme n'importe quelle colonne. C'est généralement ce qu'on veut pour les écrans d'administration : lectures rapides, et possibilité d'indexer la valeur calculée.

C'est différent de refaire le même calcul dans chaque requête. Si vous continuez à écrire WHERE lower(email) = lower($1) ou à trier par last_name || ', ' || first_name, vous payez le coût à chaque fois et votre SQL devient verbeux. Une colonne générée déplace ce calcul répété dans la définition du tableau. Vos requêtes deviennent plus simples, et le résultat est cohérent partout.

Quand les données source changent, PostgreSQL met automatiquement à jour la valeur générée pour cette ligne. Votre application n'a pas à s'en souvenir.

Un modèle mental utile :

• Définissez la formule une fois.
• PostgreSQL la calcule aux écritures.
• Les requêtes la lisent comme une colonne normale.
• Comme elle est stockée, vous pouvez l'indexer.

Si vous changez la formule plus tard, il faudra une modification du schéma. Traitez‑la comme une migration : les lignes existantes seront actualisées pour correspondre à la nouvelle expression.

Bons usages des champs calculés pour filtres et tris

Les colonnes générées brillent quand la valeur est toujours dérivée d'autres colonnes et que vous la filtrez ou la triez souvent. Elles sont moins utiles pour des rapports ponctuels.

Champs de recherche utiles et réellement utilisés

La recherche admin est rarement « pure ». Les utilisateurs attendent que le champ gère du texte mal formé, des différences de casse et des espaces en trop. Si vous stockez une « clé de recherche » générée déjà normalisée, votre WHERE reste lisible et se comporte de la même manière partout.

Bonnes candidates : un nom complet combiné, du texte en minuscules et trimé pour une recherche insensible à la casse, une version nettoyée qui réduit les espaces, ou une étiquette de statut dérivée de plusieurs champs.

Exemple : au lieu de répéter lower(trim(first_name || ' ' || last_name)) dans chaque requête, générez full_name_key une fois et filtrez dessus.

Clés de tri qui correspondent à la façon dont les humains trient

Le tri est souvent le domaine où les champs calculés rapportent le plus vite, car trier peut forcer PostgreSQL à évaluer des expressions sur de nombreuses lignes.

Clés de tri courantes : un rang numérique (niveau de plan mappé en 1, 2, 3), un seul timestamp « dernière activité » (la max de deux timestamps), ou un code paddé qui trie correctement comme texte.

Quand la clé de tri est une colonne indexée simple, ORDER BY devient beaucoup moins coûteux.

Flags dérivés pour des filtres rapides

Les utilisateurs aiment les cases à cocher comme « En retard » ou « Valeur élevée ». Ce type de filtre fonctionne bien comme colonne générée quand la logique est stable et basée seulement sur les données de la ligne.

Par exemple, pour une liste client qui a besoin de « A des messages non lus » et « Est en retard », une colonne générée has_unread booléenne (depuis unread_count > 0) et is_overdue (depuis due_date < now() et paid_at is null) permet aux filtres UI de devenir de simples conditions.

Choisir entre colonnes générées, indexes et autres options

Les écrans d'administration ont trois besoins : filtrage rapide, tri rapide et SQL lisible à relire dans quelques mois. La vraie décision est de savoir où placer le calcul : dans la table, dans un index, dans une vue ou dans le code applicatif.

Les colonnes générées conviennent quand vous voulez que la valeur se comporte comme une vraie colonne : facile à référencer, visible dans les SELECT, et difficile à oublier quand on ajoute de nouveaux filtres. Elles se marient naturellement avec des index classiques.

Les indexes d'expression sont souvent plus rapides à ajouter parce qu'ils n'altèrent pas la définition du tableau. Si vous ne vous souciez que de la vitesse et supportez un SQL plus « sale », un index d'expression suffit souvent. L'inconvénient : la lisibilité, et vous dépendez du planner qui reconnaisse exactement votre expression.

Les vues aident quand vous voulez une forme de données partagée, surtout si votre liste admin joint beaucoup de tables. Mais une vue complexe peut masquer du travail coûteux et ajoute un deuxième endroit à déboguer.

Les triggers peuvent maintenir une colonne normale, mais ce sont des pièces mobiles. Ils peuvent ralentir les mises à jour en masse et être faciles à oublier lors du dépannage.

Parfois la meilleure option est une colonne normale remplie par l'application. Si les utilisateurs peuvent la modifier, ou si la formule change souvent pour des raisons métier (et pas seulement à partir des données de la ligne), la garder explicite est plus clair.

Un moyen rapide de choisir :

• Vous voulez des requêtes lisibles et une formule stable basée seulement sur les données de la ligne ? Utilisez une colonne générée.
• Vous voulez de la vitesse pour un filtre précis et ne craignez pas un SQL verbeux ? Utilisez un index d'expression.
• Vous avez besoin d'une forme jointe réutilisée partout ? Envisagez une vue.
• Vous avez une logique inter‑tables ou des effets de bord ? Privilégiez la logique applicative d'abord, les triggers en dernier recours.

Étape par étape : ajouter une colonne générée et l'utiliser dans une requête

Commencez par une requête lente d'une liste admin que vous ressentez dans l'UI. Notez les filtres et le tri utilisés le plus souvent. Améliorez d'abord cette requête.

Choisissez un champ calculé qui élimine le travail répété, et nommez‑le clairement en snake_case pour que les autres comprennent ce qu'il contient sans relire l'expression.

1) Ajouter la colonne générée (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Validez sur des lignes réelles avant d'ajouter des indexes :

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Si le résultat est incorrect, corrigez l'expression maintenant. STORED signifie que PostgreSQL la mettra à jour aux insertions et mises à jour.

2) Ajouter l'index qui correspond à votre écran admin

Si votre écran filtre par status et trie par nom, indexez ce motif :

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Mettre à jour la requête admin pour utiliser la nouvelle colonne

Avant, vous aviez peut‑être un ORDER BY compliqué. Après, c'est clair :

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Utilisez les colonnes générées pour les éléments que les gens filtrent et trient tous les jours, pas pour des écrans rares.

Schémas d'indexation qui correspondent aux écrans réels

Les écrans admin répètent quelques comportements : filtrer par un petit nombre de colonnes, trier par une colonne, et paginer. La meilleure configuration n'est rarement « indexez tout ». C'est plutôt « indexez la forme exacte des requêtes les plus courantes ».

Une règle pratique : mettez les colonnes de filtrage les plus fréquentes en premier, et la colonne de tri principale en dernier. Si vous êtes multi‑tenant, workspace_id (ou équivalent) vient souvent en premier : (workspace_id, status, created_at).

La recherche textuelle est un problème à part. Beaucoup de champs finissent en ILIKE '%term%', ce qui est difficile à accélérer avec des index btree basiques. Un motif utile est de rechercher une colonne utilitaire normalisée plutôt que le texte brut (minuscules, trimé, peut‑être concaténé). Si l'UI peut accepter une recherche par préfixe (term%), un index btree sur cette colonne normalisée peut aider. Si elle doit faire une recherche « contient » (%term%), pensez à restreindre l'UI pour les grandes tables (par exemple « email commence par »), ou limitez la recherche à un sous‑ensemble plus petit.

Vérifiez aussi la sélectivité avant d'ajouter des indexes. Si 95 % des lignes partagent la même valeur (comme status = 'active'), indexer cette colonne seule n'aidera pas beaucoup. Associez‑la à une colonne plus sélective, ou utilisez un index partiel pour le cas minoritaire.

Exemple réaliste : une liste client qui reste rapide

Imaginez une page admin clients typique : une boîte de recherche, quelques filtres (inactif, fourchette de solde) et une colonne « Dernière visite » triable. Avec le temps, le SQL devient illisible : LOWER(), TRIM(), COALESCE(), calculs de date et CASE répétés.

Une façon de garder rapidité et lisibilité est de pousser ces expressions répétées dans des colonnes générées.

Table et colonnes générées

Supposons une table customers avec name, email, last_seen et balance. Ajoutez trois champs calculés :

• search_key : un texte normalisé pour les recherches simples
• is_inactive : un booléen filtrable sans répéter la logique de date
• balance_bucket : une étiquette pour une segmentation rapide

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Désormais la requête admin ressemble à l'UI.

Filtre lisible + tri

« Clients inactifs, par date de dernière activité » devient :

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Et une recherche basique devient :

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Le vrai gain, c'est la cohérence. Les mêmes champs alimentent plusieurs écrans sans réécrire la logique :

• La recherche de la liste client utilise search_key
• L'onglet « Clients inactifs » utilise is_inactive
• Les filtres de solde utilisent balance_bucket

Erreurs fréquentes et pièges

Les colonnes générées peuvent sembler une solution simple : mettez les calculs dans la table et nettoyez vos requêtes. Elles n'aident que si elles correspondent aux filtres et tris de l'écran, et si vous ajoutez le bon index.

Erreurs courantes :

• Penser que ça accélère sans indexer. Une valeur calculée doit toujours être indexée pour filtrer ou trier rapidement à grande échelle.
• Mettre trop de logique dans un seul champ. Si une colonne générée devient un mini‑programme, les gens perdent confiance. Gardez‑la courte et nommez‑la clairement.
• Utiliser des fonctions non immuables. PostgreSQL exige que l'expression d'une colonne STORED soit immuable. now() ou random() cassent l'attente et sont souvent interdits.
• Ignorer le coût en écritures. Les inserts et updates doivent maintenir la valeur calculée. Des lectures plus rapides ne valent pas la peine si les imports et intégrations ralentissent trop.
• Créer des quasi‑doublons. Standardisez un ou deux motifs (par exemple une clé normalisée) au lieu d'accumuler cinq colonnes similaires.

Si votre liste admin dépend de recherches « contient » (ILIKE '%ann%'), une colonne générée seule ne suffira pas. Il faudra une autre approche de recherche. Mais pour les requêtes de tous les jours « filtrer et trier », colonnes générées + bon index rendent souvent les performances beaucoup plus prévisibles.

Checklist rapide avant déploiement

Avant de mettre en production, vérifiez que la valeur calculée, la requête et l'index correspondent :

• La formule est stable et expliquable en une phrase.
• Votre requête utilise bien la colonne générée dans WHERE et/ou ORDER BY.
• L'index correspond à l'usage réel, pas à un test ponctuel.
• Vous avez comparé les résultats avec l'ancienne logique sur les cas limites (NULL, chaînes vides, espaces, casse mixte).
• Vous avez testé les performances d'écriture si la table est active (imports, mises à jour en arrière‑plan, intégrations).

Étapes suivantes : appliquez‑le à vos écrans admin

Choisissez un petit point de départ à fort impact : les 2‑3 écrans admin que l'on ouvre toute la journée (commandes, clients, tickets). Notez ce qui semble lent (un filtre de date, le tri par « dernière activité », la recherche par nom combiné, un filtre par libellé de statut). Standardisez ensuite un petit ensemble de champs calculés réutilisables.

Un plan de déploiement facile à mesurer et à annuler :

• Ajoutez la/les colonne(s) générée(s) avec des noms clairs.
• Faites tourner l'ancienne et la nouvelle approche en parallèle brièvement si vous remplacez une logique existante.
• Ajoutez l'index qui correspond au filtre/tri principal.
• Basculez la requête de l'écran pour utiliser la nouvelle colonne.
• Mesurez avant/après (temps de requête et lignes scannées), puis retirez le contournement ancien.

Si vous construisez des outils d'administration internes dans AppMaster (appmaster.io), ces champs calculés s'intègrent bien dans un modèle de données partagé : la base porte la règle, et vos filtres UI peuvent pointer vers un nom de champ simple au lieu de répéter des expressions sur tous les écrans.