Réduire la charge de Prisma et PostgreSQL avant d’acheter une base de données plus grande

Dans une application utilisant Prisma et PostgreSQL, la base de données devient souvent le premier véritable goulot d’étranglement. Une requête qui s’exécute en 5 ms sur quelques centaines de lignes peut prendre plusieurs secondes lorsque la table contient des millions de lignes, et on a tendance à acheter une instance plus puissante ou un forfait plus cher. Prenons l’exemple d’une application de marché dont le trafic sur la base de données atteint 5 000 à 6 000 opérations par jour, ce qui suffit pour épuiser le plan gratuit basé sur l’utilisation avant même la fin du mois. Un travail d’analyse et de optimisation des requêtes pendant le week-end peut réduire ces opérations de plus de 60 %, accélérer les réponses API et diminuer la facture, sans avoir à modifier l’équipement.

Les techniques présentées ci-dessous, classées de la diagnostic à la conception du schéma en passant par l’entretien, sont celles qui permettent généralement de faire cette différence.

Mesurez avant de changer quoi que ce soit

Ne supposez pas qu’une requête est lente ; mesurez-la. Deux outils couvrent la plupart des besoins :

• EXPLAIN ANALYZE affiche le plan d’exécution de PostgreSQL ainsi que le nombre de lignes scannées, l’utilisation éventuelle d’index et le temps réel de traitement.
• Le journalisation des requêtes de Prisma révèle le SQL exact généré par le client.

L’exécution de EXPLAIN ANALYZE sur un filtre par vendeur se fait de cette manière. Notez que ANALYZE exécute réellement l’instruction, il est donc conseillé d’encadrer les requêtes modifiant des données dans une transaction que l’on peut annuler.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE seller_id = 123;

Même une appel Prisma bien structuré peut entraîner un balayage séquentiel de toute la table ; seul le plan d’exécution vous permet de le savoir. pg_stat_statements (qui classe les requêtes en fonction du temps total de traitement), les analyses et tableaux de bord de Prisma, ainsi que ceux fournis par Vercel, vous aident à déterminer ce qu’il convient de corriger en premier. Pour une approche structurée, consultez comment identifier le véritable goulot d’étranglement dans un endpoint Node.js lent.

Indexez les requêtes que vous exécutez réellement

Les index sont généralement le changement ayant le plus grand impact. Sans eux, une requête comme celle-ci peut forcer PostgreSQL à examiner chaque ligne dans orders:

SELECT *
FROM orders
WHERE seller_id = 123;

Un index B-tree simple sur la colonne filtrée permet au planificateur de se rendre directement aux lignes correspondantes :

CREATE INDEX idx_orders_seller
ON orders(seller_id);

Lorsque l’application filtre systématiquement plusieurs colonnes en même temps, un index composite sert directement cette combinaison :

CREATE INDEX idx_orders_seller_status
ON orders(seller_id, status);

Avec lui, une condition concernant à la fois le vendeur et l’état devient une recherche dans un index ciblé :

WHERE seller_id = 123
AND status = 'PAID'

L’ordre des colonnes est important dans un index composite. (seller_id, status) aide également les requêtes qui filtrent uniquement sur seller_id, mais pas celles qui ne filtrent que sur status. La règle d’or est d’indexer les colonnes que l’on filtre réellement, sur lesquelles on effectue un tri ou une jointure, et non tout ; chaque index consomme de l’espace disque et ralentit toute écriture dans la table.

Éliminer les requêtes N+1

Le schéma N+1 est un problème classique des ORM. Vous chargez une liste de 100 commandes :

const orders = await prisma.order.findMany();

puis vous récupérez l’utilisateur de chaque commande une par une à l’intérieur d’une boucle :

for (const order of orders) {
  await prisma.user.findUnique({
    where: {
      id: order.userId
    }
  });
}

Cela représente 101 requêtes pour une seule page de données. Demander à Prisma de charger la relation en même temps que les commandes élimine cette boucle :

const orders = await prisma.order.findMany({
  include: {
    user: true
  }
});

Une seule appel remplace la boucle, avec une latence et une charge bien moindres. Selon la version et la configuration, Prisma exécute include comme une jointure ou comme une requête groupée supplémentaire ; dans les deux cas, les allers-retours par ligne disparaissent.

Sélectionnez uniquement les colonnes dont vous avez besoin

Réduire les données transférées est l’un des gains les plus simples. Un findMany basique renvoie toutes les colonnes de chaque ligne :

await prisma.user.findMany();

Un select limite les résultats à ce que l’appelant utilise réellement :

await prisma.user.findMany({
  select: {
    id: true,
    name: true
  }
});

Avec des blocs JSON, du Markdown, des images ou de longs textes dans la table, cela seul peut réduire considérablement les temps de réponse, et il permet d’éviter que des colonnes sensibles ne figurent dans les réponses API.

Remplacez les agrégations répétées par des compteurs stockés

Les tableaux de bord analytiques recomptent souvent les agrégations à chaque chargement de page :

COUNT(*)
SUM(amount)
AVG(...)

Cela fonctionne bien avec 100 lignes, mais devient problématique avec des millions de lignes. Par exemple, calculer le total des ventes à chaque demande signifie additionner toute la table :

SELECT SUM(amount)
FROM orders;

L’alternative consiste à mettre à jour les agrégats au moment de l’écriture, en stockant directement des valeurs telles que les totaux dans le enregistrement de l’utilisateur :

User
-------------
totalSales
totalOrders
totalRevenue

Mettez à jour ces champs chaque fois qu’une vente est finalisée, ce qui permet aux lectures de devenir des recherches en O(1) au lieu de balayages sur des millions de lignes ; dans le scénario ci-dessus, c’était l’une des plus grandes réductions possibles. Mettez à jour les compteurs de manière atomique (par exemple avec increment de Prisma dans la même transaction que l’insertion de la commande), et réconciliez-les occasionnellement, car les totaux dénormalisés peuvent s’éloigner de la réalité.

Paginer chaque liste

Les tables ne restent jamais petites, il ne faut donc jamais retourner une liste illimitée comme celle-ci :

await prisma.product.findMany();

Utilisez take et skip pour récupérer une page délimitée ; cet exemple renvoie la deuxième des 20 pages :

await prisma.product.findMany({
  take: 20,
  skip: 20
});

Pour de grands ensembles de données ou le défilement infini, privilégiez la pagination par curseur : un offset fait en sorte que PostgreSQL passe outre chaque ligne sautée, tandis qu’un curseur sur une colonne indexée reprend exactement là où s’est terminée la dernière page.

Lectures en cache qui changent rarement

La requête la plus rapide est celle qui ne s’exécute jamais. Les données qui changent peu sont de bons candidats pour le cache : les pages de produits, les vitrines publiques, les paramètres d’application et les listes de catégories. Parmi les options courantes figurent Upstash Redis, le cache de Next.js et un cache LRU en mémoire. En sortant les lectures fréquentes de PostgreSQL, on allège considérablement la charge ; le prix à payer est l’invalidation des données, il faut donc déterminer jusqu’à quel point les données peuvent être obsolètes et les révalider lorsque les lignes changent.

Écritures en lot plutôt que boucles

Chaque appel en attente représente un aller-retour réseau, et leur nombre augmente rapidement. Créer des enregistrements un par un dans une boucle en est un exemple classique :

for (const item of items) {
  await prisma.item.create({
    data: item
  });
}

createMany insère tout le tableau en une seule instruction :

await prisma.item.createMany({
  data: items
});

Un aller-retour vaut mieux que des centaines. Notez que createMany ne crée pas de relations imbriquées et renvoie un comptage plutôt que les lignes ; consultez la documentation de votre version de Prisma si vous avez besoin des enregistrements.

Grouper les écritures liées dans une transaction

Créer une commande implique souvent d’autres écritures, telles que l’ajustement des stocks, la mise à jour des soldes et la génération d’une facture. Ces modifications doivent être traitées ensemble, et $transaction garantit qu’elles réussissent toutes ou sont annulées entièrement :

await prisma.$transaction([
  prisma.order.create(...),
  prisma.user.update(...),
  prisma.balance.update(...)
]);

Le véritable avantage réside dans la cohérence plutôt que dans la vitesse : aucune opération ne peut se dérouler sans mise à jour de l’équilibre en cas d’échec au milieu du processus. Gardez les transactions courtes, car elles maintiennent des verrous ainsi qu’une connexion.

Réutiliser un seul Prisma Client

Créer un nouveau PrismaClient à chaque requête est coûteux et peut épuiser les connexions ; de plus, le chargement dynamique en développement peut générer un nouveau client à chaque modification de fichier. Un singleton permet d’éviter ces problèmes. Importez le client :

import { PrismaClient } from "@prisma/client";

Ensuite, cachez l’instance dans globalThis en dehors du mode production afin que les modules rechargés utilisent le client existant plutôt que d’en créer un nouveau :

const globalForPrisma =
  globalThis as unknown as {
    prisma: PrismaClient | undefined;
  };export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient();if (process.env.NODE_ENV !== "production")
  globalForPrisma.prisma = prisma;

Le cast as unknown as confère à globalThis une propriété prisma de type défini ; en mode production, le module s’charge une seule fois, il n’est donc pas nécessaire de disposer d’un cache global.

Gérer un pool de connexions, surtout sur des environnements serverless

Les plateformes serverless peuvent lancer des milliers d’instances de fonctions simultanées, chacune ayant besoin de sa propre connexion à la base de données. Sans mécanisme de mise en pool, PostgreSQL peut épuiser ses connexions bien avant que le CPU ne devienne un problème. Prisma Accelerate et PgBouncer se situent tous deux entre l’application et la base de données, permettant à de nombreuses requêtes d’utiliser un ensemble bien plus restreint de connexions réelles à PostgreSQL.

Indexez les champs JSONB que vous filtrez

Le type JSONB de PostgreSQL est très puissant, mais filtrer sur des champs imbriqués sans index signifie généralement un balayage complet de la table. Si vous effectuez régulièrement des requêtes à l’intérieur de documents JSON, ajoutez un index GIN sur la colonne (ou un index d’expression sur une clé que vous filtrez fréquemment), ce qui transforme des recherches lentes dans les documents en recherches indexées.

Préférez findUnique pour les recherches uniques

Lorsque vous effectuez des requêtes par clé primaire ou par un autre champ unique, utilisez cette méthode :

findUnique()

Cela indique à Prisma que la recherche vise une seule ligne, ce qui permet à Prisma de regrouper plusieurs appels findUnique provenant du même instant en une seule requête, ce qui est pratique dans les résolveurs GraphQL. Par exemple :

await prisma.user.findUnique({
  where: {
    id
  }
});

Utilisez cette fonction plutôt que celle suivante lorsque le champ est unique :

findFirst()

findFirst reste le choix approprié pour les filtres non uniques, lorsque c’est « la première ligne correspondante » que vous souhaitez réellement obtenir.

Archiver les anciennes lignes plutôt que de les supprimer temporairement

Les suppressions temporaires sont pratiques ; il suffit de marquer la ligne comme supprimée :

isDeleted = true

Cependant, avec le temps, chaque requête doit filtrer les lignes supprimées, ce qui entraîne un encombrement des tables et des index. Dans les systèmes à fort volume, déplacez périodiquement les enregistrements supprimés vers des tables d’archivage : des tables plus petites signifient des index plus petits et des requêtes plus rapides. Un index partiel sur les lignes non supprimées constitue une solution intermédiaire.

Préserver la santé d’autovacuum

PostgreSQL utilise MVCC (contrôle de concurrence à plusieurs versions), de sorte que les lignes mises à jour ou supprimées ne sont pas immédiatement retirées ; elles restent sous forme de tuples morts. Autovacuum est le processus en arrière-plan qui s’occupe de les nettoyer. Un autovacuum bien ajusté :

• libère l’espace occupé par les tuples morts ;
• met à jour les statistiques du planificateur ;
• garantit l’efficacité des index ;
• assure la précision des plans de requête.

L’ignorer entraîne un encombrement excessif et des plans de mauvaise qualité, surtout sur des tables fortement mises à jour comme les compteurs mentionnés ci-dessus.

Points clés

La plupart des problèmes de performance des bases de données ne sont pas résolus par des serveurs plus puissants ; ils le sont grâce à de meilleurs schémas d’accès aux données. Dans le scénario décrit, le fournisseur d’hébergement et la taille de la base de données sont restés identiques. Les améliorations proviennent de :

• l’analyse des requêtes coûteuses plutôt que de deviner ;

ajout d’index correspondant à des filtres réels ;

élimination des boucles N+1 et des colonnes inutiles ;

mémorisation des lectures répétées ;

remplacement des agrégations coûteuses par des compteurs maintenus ;

imposition de limites et de pagination pour chaque liste.

Tous ces éléments ensemble ont permis de réduire les opérations de plus de 60 %, d’accélérer les API et d’accepter un volume de trafic bien plus important. Avant d’augmenter les capacités, découvrez comment votre base de données est réellement utilisée ; les requêtes existantes cachent souvent la majeure partie des performances nécessaires.