Comprendre le pool de connexions de base de données

Ouvrir une connexion à une base de données coûte cher : une poignée de main TCP, l'authentification, parfois une négociation TLS, généralement des dizaines de millisecondes avant qu'une seule requête ne s'exécute. Ouvrez une connexion par requête et vous payez ce coût à chaque fois, jusqu'à ce que la base refuse de nouvelles connexions parce qu'elle a une limite stricte au nombre de connexions ouvertes qu'elle peut tenir simultanément. Le pool de connexions résout les deux problèmes en gardant un ensemble de connexions déjà ouvertes prêtes à être réutilisées.

Ce que fait réellement un pool

Un pool de connexions est un ensemble fixe de connexions à la base qui vivent pendant toute la durée de vie de votre application, et non d'une seule requête. Quand votre code doit exécuter une requête, il emprunte une connexion au pool, l'utilise, puis la rend ; il n'ouvre ni ne ferme aucun socket.

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'app',
  max: 10, // maximum number of connections in the pool
  idleTimeoutMillis: 30000,
});

async function getUser(id) {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
  } finally {
    client.release(); // return the connection to the pool, don't close it
  }
}

pool.connect() vous remet une connexion inactive existante si elle est disponible, ou en ouvre une nouvelle (jusqu'à max) sinon. client.release() est la partie que l'on oublie : sans lui, cette connexion n'est jamais rendue au pool, et c'est ainsi que l'on finit par l'épuiser sous charge.

Pourquoi un pool non borné est pire que pas de pool

Il est tentant de fixer max haut « par sécurité ». Le problème, c'est que votre base a aussi un plafond strict de connexions (max_connections dans Postgres, souvent 100 par défaut), partagé entre toutes les instances d'application qui lui parlent. Avec 10 instances d'application et max: 50 chacune, vous pouvez demander 500 connexions à une base qui n'en accepte que 100, et une fois ce plafond atteint, tous les autres clients, y compris vos propres outils d'administration, sont rejetés.

Une taille de pool raisonnable se rapproche, comme heuristique de départ pour la base elle-même, de (nombre de cœurs CPU × 2) + nombre effectif de disques, divisé ensuite entre toutes les instances d'application qui s'y connectent, et non de « autant de requêtes concurrentes que je m'attends à avoir ». Plus de connexions que ce que les cœurs CPU de votre base peuvent réellement servir en parallèle ne fait qu'ajouter de la file d'attente, pas du débit.

Le bug de pool le plus courant : ne pas rendre la connexion en cas d'erreur

// Bad: if query() throws, the connection is never released
async function getUserBroken(id) {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  client.release();
  return result.rows[0];
}

Si client.query() lève une exception, client.release() ne s'exécute jamais, et cette connexion reste définitivement empruntée. Répétez cela assez souvent et le pool se réduit silencieusement à zéro connexion utilisable tout en paraissant parfaitement sain de l'extérieur. La version try/finally ci-dessus est la solution : finally s'exécute que la requête réussisse ou lève une exception.

Utiliser pool.query() pour les instructions isolées

La plupart des pilotes offrent un raccourci qui emprunte une connexion, exécute une requête et la rend automatiquement :

const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Utilisez-le pour des requêtes uniques et indépendantes. Il ne convient pas aux transactions, car chaque appel à pool.query() peut s'exécuter sur une connexion différente, alors qu'une transaction n'a de sens que sur une connexion précise pendant toute sa durée.

Les transactions exigent une connexion explicite

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Ici, le même client exécute BEGIN, les deux mises à jour et COMMIT : c'est ce qui les garde dans une seule transaction. Mélanger des appels pool.query() au milieu ferait exécuter silencieusement certaines instructions sur d'autres connexions, hors de la transaction.

Délais d'inactivité et connexions périmées

Les connexions longtemps inactives peuvent être fermées silencieusement par la base, un répartiteur de charge ou un pare-feu intermédiaire, sans que votre application le sache. idleTimeoutMillis demande au pool de fermer et de remplacer de façon proactive les connexions inactives depuis trop longtemps, au lieu d'attendre que vous tentiez d'utiliser une connexion morte et obteniez une erreur déroutante en pleine requête.

const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000, // close idle connections after 30s
  connectionTimeoutMillis: 5000, // fail fast if no connection is available within 5s
});

connectionTimeoutMillis compte tout autant : sans lui, une requête qui ne peut pas obtenir de connexion parce que le pool est épuisé restera suspendue indéfiniment au lieu d'échouer vite avec une erreur claire.

Le serverless change complètement le calcul

Dans un serveur classique de longue durée, un pool vit pendant toute la vie du processus, ce qui est exactement ce pour quoi le pool est conçu. En serverless (Lambda, Cloud Functions), chaque invocation peut démarrer un processus neuf, et créer un pool complet par invocation annule l'intérêt : on paie de nouveau constamment le surcoût de connexion, et une rafale d'invocations concurrentes peut ouvrir chacune son propre pool et dépasser collectivement la limite de connexions de la base. La solution habituelle est un pooler externe (PgBouncer, RDS Proxy) qui se place entre vos fonctions et la base et multiplexe de nombreux clients éphémères sur un petit nombre de vraies connexions à la base.

À retenir

Un pool de connexions échange le coût d'ouverture d'une connexion par requête contre un ensemble fixe de connexions déjà ouvertes et réutilisées, mais il ne vaut que par son dimensionnement et sa rigueur à rendre les connexions. Dimensionnez-le selon ce que la base peut réellement servir, rendez toujours dans un finally, et gardez les transactions attachées à une unique connexion explicite.