Timeouts de requête pour arrêter rapidement les requêtes de base de données hors de contrôle

Pourquoi des requêtes hors de contrôle font planter des applis autrement normales

Une requête hors de contrôle est une requête base de données qui s'exécute beaucoup plus longtemps que prévu. Cela peut venir d'un index manquant, d'un filtre qui force un scan complet de la table, ou d'une jointure qui explose en millions de lignes. Le reste de l'appli peut être sain, mais cette unique requête monopolise une connexion jusqu'à sa fin.

Dans une appli web typique, chaque requête emprunte une connexion à partir d'un pool limité. Si une requête lente occupe une connexion pendant 30 à 120 secondes, quelques utilisateurs touchant le même endpoint peuvent saisir toutes les connexions disponibles. Une fois le pool vide, même les requêtes rapides ne trouvent plus de connexion : elles s'empilent, expirent ou échouent.

Les symptômes ressemblent à un effondrement complet alors qu'une seule requête est en cause : pages qui se bloquent puis échouent, latence générale en hausse, augmentation des 500 lorsque les workers sont bloqués, accumulation de jobs en arrière-plan, et CPU de la base qui s'affole pendant que le débit chute.

On voit souvent ça juste après le déploiement d'un prototype généré par IA. Beaucoup d'outils IA produisent une démo fonctionnelle qui oublie des détails de production comme les index, des patterns de requêtes sûrs, et des limites. Une fonctionnalité qui « marchait » sur 200 lignes peut imploser à 200 000.

Un exemple concret : une page de recherche ajoute un filtre flexible comme "statut contient" ou "nom commence par". En production cela devient une recherche par motif générique sur une grosse table. Une personne exporte les résultats, la requête tourne pendant une minute, et cinq autres personnes font la même chose. Soudain le pool de connexions est épuisé et le reste de l'appli paraît hors service.

Les timeouts d'instruction comptent parce qu'ils mettent un plafond net à la quantité de dégâts qu'une seule requête peut causer.

Timeouts d'instruction : ce qu'ils font et ce qu'ils ne font pas

Un timeout d'instruction est une limite sur la durée pendant laquelle la base passe à exécuter une seule instruction SQL. Si la requête dépasse cette limite, la base l'arrête et renvoie une erreur au lieu de la laisser consommer du CPU, tenir des verrous et occuper une connexion.

C'est différent des limites de temps en dehors de la base. Un timeout côté application ou load balancer peut arrêter l'attente, mais la requête peut continuer à tourner côté base.

Concrètement :

• Les timeouts d'instruction (base de données) arrêtent le travail SQL sur le serveur.
• Les timeouts de requête (app) arrêtent l'attente côté client, mais la base peut rester occupée.
• Les timeouts du load balancer coupent la requête réseau ; la base peut continuer à moins que vous n'annuliez aussi la requête.

Quand une requête longue est tuée, la base libère ce qu'elle peut, mais le comportement dépend du contexte. Si la requête détenait des verrous, ceux-ci sont libérés une fois la déclaration annulée et rollbackée. Si vous étiez à l'intérieur d'une transaction, beaucoup de bases marquent la transaction comme échouée, ce qui implique de la rollback avant de réutiliser la même connexion. C'est important parce qu'une requête expirée peut laisser une connexion effectivement « empoisonnée » jusqu'à nettoyage.

Côté application, vous verrez en général une erreur du type « requête annulée » ou « timeout d'instruction ». Traitez-la comme une défaillance normale et attendue : attrapez-la, faites un rollback si nécessaire, et décidez si un retry est approprié. Ne relancez pas automatiquement la même requête lente sans changer quelque chose.

Bien utilisés, les timeouts d'instruction empêchent qu'une mauvaise requête n'entraîne l'épuisement du pool de connexions.

Où appliquer les timeouts : base, app, ou les deux

Utilisez les deux quand vous le pouvez. Une limite côté base arrête le travail hors de contrôle même si votre app est bloquée, tandis qu'une limite côté app garde vos serveurs réactifs et libère les threads de requête.

Timeouts côté base (le coup d'arrêt)

Définissez des statement timeouts dans la base comme garde-fou. Si une requête tourne plus longtemps que permis, la base l'annule, ce qui protège les ressources partagées comme le CPU, les verrous et les connexions.

Une approche pratique est de définir un défaut global raisonnable, puis d'élargir ce délai seulement pour des rôles ou jobs qui en ont vraiment besoin. Beaucoup d'équipes utilisent :

• Un défaut global pour le trafic applicatif normal
• Une limite plus élevée pour un rôle admin utilisé pour le support et les backfills
• Un rôle séparé pour les jobs de reporting avec un budget plus large

Décidez aussi de la portée. Un timeout peut être appliqué par connexion (couvre tout ce qui passe par cette connexion) ou par transaction (utile si vous voulez des limites plus strictes pour un workflow spécifique).

Timeouts côté app (garde-fou de l'expérience utilisateur)

Votre appli doit conserver son propre délai afin que les requêtes ne restent pas bloquées en attente de la base. Quand l'app expire, elle devrait annuler la requête et renvoyer un message clair à l'utilisateur, du genre « Cette recherche a pris trop de temps. Essayez d'affiner les filtres. »

Pour les cas spéciaux, utilisez des overrides par requête au lieu d'affaiblir vos valeurs par défaut. Gardez ces chemins explicites : migrations longues, fixes de données ponctuels, ou un rapport mensuel.

Voici l'échec que vous voulez éviter : un nouveau filtre « contient » déclenche un scan lent sur une grosse table. Sans limites, 20 utilisateurs cliquent, toutes les connexions se bloquent, et toute l'appli paraît indisponible. Avec des timeouts côté base et annulation côté app, ces requêtes échouent vite, le pool se remet, et vous pouvez corriger la requête sans panique.

Choisir un timeout qui correspond aux vrais usages

Un timeout doit protéger votre application sans casser le trafic normal. La façon la plus simple de se tromper est de choisir un chiffre au doigt mouillé. Choisissez des valeurs à partir de ce que vos utilisateurs font réellement, puis ajoutez de petites marges intentionnelles.

Commencez par les vrais p95 et p99

Récupérez les durées des requêtes depuis les logs ou les stats de la base et regroupez-les par endpoint ou type de job. Si une requête API typique finit en 120 ms au p95 et 400 ms au p99, un plafond de 2 à 3 secondes suffit généralement. Il attrape les rares requêtes hors de contrôle tout en laissant respirer les pics normaux.

Si vous n'avez pas encore de données de timing, commencez conservateur, puis resserrez quand vous avez les distributions réelles.

Utilisez des limites différentes selon le type de travail

La plupart des applis ont au moins trois classes de travail base de données, et elles ne devraient pas partager le même plafond :

• Requêtes orientées utilisateur : limites courtes et strictes
• Jobs en arrière-plan : limites plus longues, mais toujours bornées
• Écrans admin et reporting : limites les plus longues, mais derrière contrôle d'accès et pagination

Gardez les règles simples. Si un rapport nécessite 30 secondes, très bien, mais ne l'exécutez pas sous les mêmes réglages que la connexion ou le paiement.

Autorisez des exceptions, mais avec des garde-fous

Certaines opérations sont connues pour être lourdes : backfills, exports, rapports de fin de mois. Donnez-leur des timeouts supérieurs explicites, mais exigez des garde-fous comme des filtres, des plages de dates, la pagination ou un nombre maximal de lignes.

Exemple : un rapport « Tous les clients » sans filtre de date fonctionne en staging, puis prend des minutes en prod et bloque des connexions. Un timeout plus élevé pour le reporting plus une plage de dates obligatoire évitent ce mode d'échec.

Étape par étape : ajouter des timeouts et l'annulation

Commencez par protéger la base elle-même. Un défaut sûr signifie qu'une seule mauvaise requête ne peut pas rester indéfiniment et bloquer des connexions. Dans Postgres, c'est généralement statement_timeout défini au niveau de la base ou du rôle, de sorte qu'il s'applique même si un développeur oublie d'ajouter un timeout dans le code.

-- Example: Postgres
ALTER ROLE app_user SET statement_timeout = '5s';
-- Or for a whole database
ALTER DATABASE app_db SET statement_timeout = '5s';

Ensuite, ajoutez un timeout plus strict pour les actions utilisateurs dans l'app. Une personne qui clique s'attend à une réponse rapide. Si la requête atteint le timeout, échouez vite avec un message clair et laissez-la réessayer plus tard, plutôt que d'attendre et d'épuiser lentement votre pool de connexions.

Pour le travail en arrière-plan (workers, cron), utilisez un timeout différent. Les jobs touchent souvent plus de lignes, ils peuvent donc bénéficier de plus de temps, mais ils doivent tout de même avoir un plafond net pour qu'une exécution coincée ne bloque pas toute la file.

Une séquence gérable :

• Définir un timeout par défaut au niveau base ou rôle qui soit sûr, pas parfait.
• Appliquer un timeout par requête pour les endpoints web et API.
• Appliquer un timeout par job pour les workers et tâches planifiées.
• Utiliser des overrides par requête uniquement quand vous pouvez expliquer pourquoi (par ex. un rapport mensuel).
• Vérifier en staging avec des volumes de données et une concurrence réalistes.

Les overrides par requête sont là où les équipes se mettent en difficulté. Traitez-les comme des exceptions : loggez leur usage, scopez-les (les définir pour la transaction en cours seulement, puis les réinitialiser), et revisitez-les plus tard.

Enfin, testez le comportement d'annulation, pas seulement le timing. En staging, lancez une requête qui sait qu'elle sera lente (par exemple un filtre sans index) et confirmez trois choses : la requête se termine côté client, la requête s'arrête côté base, et la connexion retourne dans le pool.

Rendre l'annulation fiable côté app

Un timeout n'aide que s'il libère effectivement la connexion base. Définissez un délai au niveau du handler de votre app (handler HTTP, runner de job, worker de file) et transmettez-le jusqu'à l'appel base. Ainsi, quand la requête se termine, la requête base se termine aussi.

Le premier piège est le comportement du driver. Certains drivers arrêtent d'attendre le résultat, mais la base continue d'exécuter la requête. En production, c'est presque aussi mauvais que l'absence de timeout car la connexion reste occupée. Testez votre stack en forçant une requête lente et en vérifiant deux choses : l'app répond vite, et la base montre que la requête a été annulée (et non toujours en cours en arrière-plan).

Quand vous annulez, renvoyez quelque chose que l'utilisateur comprendra et que votre code pourra traiter. « Cette requête a pris trop de temps, veuillez réessayer » suffit généralement. Séparez aussi les erreurs de timeout des vraies erreurs (erreurs de syntaxe, permissions) afin que le monitoring et les retries restent honnêtes.

Les retries ont besoin de règles. Sinon ils doublent la charge pendant un incident :

• Retry des requêtes en lecture seulement quand c'est sûr et si vous n'avez pas commencé à streamer une réponse.
• Ne relancez pas les écritures sans clés d'idempotence ou une stratégie « exactly once ».
• Ajoutez du jitter et une petite limite (par ex. 1 à 2 retries), pas des retries infinis.
• Ne relancez jamais sur des erreurs d'auth ou des requêtes mal formées.

Loggez assez pour déboguer sans divulguer de secrets. Capturez la route ou le nom du job, la valeur du timeout, le temps écoulé, une empreinte de la requête (hash ou template), et un request ID. Évitez de logger le SQL brut avec des données utilisateurs, tokens ou chaînes de connexion.

Erreurs courantes qui font échouer les timeouts

Les timeouts sont censés protéger votre appli, mais quelques mauvais réglages peuvent les transformer en échecs bruyants ou masquer le vrai problème.

S'appuyer uniquement sur les timeouts HTTP est le mode d'échec classique. Si le navigateur ou le load balancer abandonne après 30 secondes, la requête base peut continuer à tourner. Ces requêtes « orphelines » gardent les connexions occupées même après le départ de l'utilisateur.

Une autre erreur fréquente est de mettre des timeouts trop bas. Un timeout global à 200 ms peut sembler sûr, mais il provoque des retries constants, des pages partielles et des tickets support. Vous voulez arrêter les vrais runaways, pas pénaliser les cas lents normaux (cache froid, gros tenants, pics temporaires).

Les transactions sont un autre piège. Une requête expirée dans une transaction peut laisser la transaction en état d'échec. Si vous ne gérez pas correctement et ne faites pas rollback, vous pouvez garder des verrous, bloquer d'autres requêtes et créer un entassement qui ressemble à un gel de la base.

Enfin, évitez une seule limite pour tout. Les pages interactives ont besoin de limites serrées, mais les exports, backfills et rapports admin sont différents. Donnez aux jobs longs leur propre chemin et leur propre timeout plus élevé, afin de protéger les utilisateurs normaux.

Comment repérer les principaux coupables avant qu'ils ne vous abattent

Les statement timeouts sont un filet de sécurité, mais vous devez quand même savoir quelles requêtes tombent souvent dans ce filet.

Commencez par collecter des preuves proches du point de défaillance. Plutôt que de logger chaque requête (trop bruyant), concentrez-vous sur les requêtes lentes et celles « proches du timeout ». Beaucoup de bases peuvent logger les requêtes plus lentes qu'un seuil, et il aide aussi d'échantillonner les requêtes qui tournent plus longtemps que 70 à 90 % de votre timeout. Cette tranche montre souvent les mêmes patterns qui causeront des outages.

Surveillez deux signaux côté app parallèlement aux logs DB : la fréquence des requêtes annulées, et si votre pool de connexions est saturé. Une montée des annulations avec un pool proche du maximum signifie que les timeouts empêchent un crash, mais de justesse.

Suivez régulièrement et alertez quand les valeurs restent élevées plusieurs minutes :

• Requêtes lentes au-delà d'un seuil fixe (et un compteur séparé pour les quasi-timeouts)
• Nombre de requêtes annulées (par endpoint ou type de job)
• Utilisation du pool de connexions et temps d'attente pour obtenir une connexion
• Taux d'erreur et p95 de latence pour les endpoints qui touchent la DB
• Principales empreintes de requête (même forme, paramètres différents)

Quand vous stockez des requêtes pour investiguer, stockez le patron, pas des données personnelles. Gardez des placeholders (WHERE email = ?) et le plan ou l'index utilisé, mais évitez de logger l'email réel, des tokens ou le payload complet.

Exemple : un filtre lent qui met l'appli à genoux

Un fondateur lance une page simple « Clients » avec des filtres comme « Nom de société contient … » et « Inscrit après … ». En test, ça va car la base est petite.

En production, un utilisateur tape un terme courant comme « a » et lance la recherche. L'app envoie une requête qui ne peut pas utiliser d'index pour le filtre « contient ». La base parcourt une énorme table, trie un grand jeu de résultats, et garde une connexion ouverte.

La chaîne de défaillance est prévisible :

• Une requête tourne pendant des minutes parce qu'elle scanne des millions de lignes.
• D'autres personnes font la même recherche et chaque requête prend une autre connexion.
• Le pool se remplit, donc même les endpoints rapides (login, checkout, admin) commencent à expirer.
• L'appli semble en panne, mais le vrai problème ce sont quelques requêtes hors de contrôle.

Avec des timeouts d'instruction et l'annulation côté app, vous pouvez fixer une limite correspondant à votre UX, par exemple 3 à 10 secondes pour une page de recherche. Quand la requête atteint la limite, la base l'arrête. La requête échoue vite avec un message clair, et la connexion retourne au pool.

Le bénéfice clé n'est pas que la requête devienne plus rapide, mais qu'une mauvaise requête ne puisse pas monopoliser les ressources assez longtemps pour affamer tout le reste.

Une fois le feu éteint, corrigez le pattern : ajoutez le bon index, changez le filtre pour quelque chose qui profite d'un index, ou déplacez les recherches « contient » vers une colonne dédiée au search.

Checklist rapide avant de mettre en production

Avant de déployer, faites une dernière vérification pour vous assurer qu'une seule mauvaise requête ne peut pas bloquer votre pool et mettre l'appli KO.

• Définissez un statement timeout par défaut raisonnable pour les chemins de requête courants. Assez bas pour protéger le système, assez haut pour que les pages normales ne tombent pas en échec.
• Utilisez un timeout séparé et plus long pour les jobs de confiance comme les exports et rapports, scoppé à ces endpoints ou workers.
• Vérifiez que le délai côté app et le timeout base marchent ensemble, et que l'annulation est réelle. Quand une requête est annulée, la requête DB doit s'arrêter et la connexion doit revenir vite.
• Gérez proprement les erreurs de timeout (message clair, code d'erreur sûr) et évitez les boucles de retry qui relancent la même requête coûteuse.
• Surveillez les requêtes proches du timeout et les requêtes annulées afin de corriger les pires patterns tôt.

Un test rapide : lancez volontairement une requête lente (par ex. un rapport avec une large plage de dates), puis annulez-la dans le navigateur. Surveillez l'activité DB et les logs app. Si la requête continue après la fin de la requête client, vous avez encore une brèche d'annulation.

Prochaines étapes : stabiliser la base sans tout réécrire

Si vous avez déjà vu une requête hors de contrôle faire tomber une appli, traitez ça comme un projet de sécurité. L'objectif est de garder le système utilisable même quand une requête est lente, un filtre est trop large, ou un job se bloque.

Commencez par auditer les endroits où le temps peut s'échapper : endpoints avec beaucoup de filtres optionnels, rapports qui scannent de larges plages de dates, jobs en arrière-plan qui s'étendent, et tout ce qui tourne sur un cron. Ensuite, durcissez un flux concret de bout en bout, comme le tableau de bord chargé à chaque connexion. Donnez-lui un timeout réaliste, assurez-vous qu'il s'annule proprement, et vérifiez qu'une requête lente ne peut pas bloquer le pool.

Si vous avez hérité du code généré par IA, supposez qu'il y a des pièges cachés tant que ce n'est pas prouvé. Deux pièges courants : les N+1 (une boucle qui exécute silencieusement des centaines de petites requêtes) et les filtres non bornés (une recherche vide qui renvoie toute la table).

Si vous voulez un regard externe sur un prototype qui casse sous vrai trafic, FixMyMess (fixmymess.ai) se concentre sur la transformation d'applis générées par IA en logiciels prêts pour la production, y compris le diagnostic des chemins de requêtes lentes, la correction de la logique et le renforcement de la sécurité.