Pool de connexions PostgreSQL sur un petit VPS

Pourquoi un petit VPS manque de RAM avant d’atteindre max_connections

Le connection pooling PostgreSQL sur un VPS n’est pas une astuce pour accélérer les requêtes. Il permet au serveur de 4 GB de rester opérationnel, car chaque connexion PostgreSQL correspond à un processus distinct du système d’exploitation qui conserve sa propre mémoire privée. Un pooler place un petit nombre fixe de processus backend réels derrière un grand nombre de connexions client, peu coûteuses.

La valeur par défaut de max_connections est 100. Il s’agit d’une limite, pas d’un budget. PostgreSQL ne vérifie jamais si votre machine peut réellement faire fonctionner 100 backends qui exécutent des requêtes. Le serveur tombe donc en panne avant. Le OOM killer du kernel sélectionne un processus. S’il sélectionne un backend, PostgreSQL redémarre l’ensemble du cluster pour préserver l’intégrité de la mémoire partagée. Le journal affiche server process (PID 1234) was terminated by signal 9: Killed, puis terminating any other active server processes. Toutes les connexions ouvertes sont interrompues, y compris celles qui fonctionnaient correctement.

Le serveur manque de mémoire parce que chaque connexion est un processus et parce que work_mem est alloué pour chaque opération de tri ou de hachage, et non pour chaque connexion. Ces deux facteurs se multiplient.

Chaque connexion est un processus, et chaque processus consomme de la mémoire

PostgreSQL utilise un processus par connexion. Le postmaster crée un backend lorsqu’un client se connecte, et ce backend reste actif jusqu’à la déconnexion du client. Ce n’est pas un thread. Il possède ses propres tables de pages, ses propres caches de catalogues et ses propres plans de requêtes en cache. Ces caches grossissent à mesure que la connexion accède à davantage de tables et exécute davantage de requêtes distinctes. Une connexion longue durée dans une application ORM active consomme donc plus de mémoire qu’une connexion nouvellement créée.

La mémoire partagée est réellement partagée. shared_buffers correspond à une allocation pour l’ensemble du cluster, mappée dans chaque backend. La mémoire privée n’est pas partagée. C’est pourquoi top peut vous induire en erreur ici : la resident set size (RSS) d’un backend inclut les pages partagées auxquelles ce backend a accédé. Additionner la RSS de 50 backends compte donc shared_buffers 50 fois.

Mesurez plutôt la partie privée. La PSS (proportional set size) répartit chaque page partagée entre les processus qui la mappent. L’USS (unique set size) compte uniquement les pages appartenant exclusivement au processus concerné.

sudo apt update
sudo apt install -y smem
sudo smem -k -P '^postgres'

La colonne USS indique la mémoire qui serait libérée si ce backend s’arrêtait. Il s’agit du coût réel de chaque connexion. Les chiffres publiés évaluent généralement la consommation d’un backend inactif à quelques mégaoctets, et celle d’un backend ayant exécuté de grandes requêtes ORM à plusieurs fois cette valeur. Considérez ces chiffres comme des ordres de grandeur publiés, et non comme vos propres mesures. La seule valeur utile pour le dimensionnement est celle mesurée sur votre serveur avec votre charge de travail.

Une allocation par session est facile à oublier. temp_buffers est défini par défaut à 8MB et est alloué par session lorsque celle-ci accède pour la première fois à une table temporaire. Cette mémoire n’est pas rendue avant la fin de la session.

work_mem est allouée par opération, pas par connexion

C’est à ce stade que les calculs deviennent trompeurs. work_mem vaut 4MB par défaut, et la documentation PostgreSQL explique clairement ce que cela implique : « une requête complexe peut effectuer plusieurs opérations de tri et de hachage simultanément ; chaque opération peut généralement utiliser autant de mémoire que cette valeur avant de commencer à écrire des données dans des fichiers temporaires ». Un plan comportant trois nœuds de tri peut utiliser trois fois work_mem dans un même backend, au même moment.

Les opérations de hachage peuvent utiliser davantage. hash_mem_multiplier vaut 2.0 par défaut. Une jointure par hachage ou une agrégation par hachage peut donc utiliser work_mem fois cette valeur, soit 8MB avec la configuration par défaut. Les requêtes parallèles multiplient encore cette consommation, car chaque worker parallèle est un processus supplémentaire disposant de sa propre allocation.

Faites le calcul pour un VPS de 4 GB. Définissez shared_buffers à 1 GB, laissez work_mem à 4MB et autorisez 100 connexions à exécuter chacune une requête comportant deux nœuds de hachage. Cela représente 100 fois 16MB, soit 1.6 GB de mémoire privée en plus du 1 GB de shared buffers, avant de compter le page cache et le reste du système. Augmentez maintenant work_mem à 64MB parce que le serveur dispose de mémoire disponible. Les mêmes 100 connexions représentent alors 100 fois 256MB. Aucun avertissement ne s’affiche. Vous le découvrez lorsque l’OOM killer intervient.

Vous pouvez vérifier si work_mem est trop faible au lieu de procéder au hasard. Définissez log_temp_files = 0 dans postgresql.conf, puis rechargez la configuration. Chaque écriture temporaire sur disque ajoutera alors une ligne indiquant le nom et la taille du fichier, comme temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 20971520. Des écritures fréquentes indiquent qu’une valeur plus élevée de work_mem serait utile. En l’absence d’écritures temporaires, l’augmenter ne sert à rien et consomme de la mémoire dont vous ne disposez pas.

Le calcul du pool qui finit réellement par poser problème

Personne ne prévoit d’ouvrir 240 connexions. On configure un pool de 20 connexions, puis on exécute l’application à plusieurs endroits.

[
  {
    "config": "1 worker",
    "backends": 20
  },
  {
    "config": "4 web workers",
    "backends": 80
  },
  {
    "config": "4 web + 2 background",
    "backends": 120
  },
  {
    "config": "2 hosts x 4 workers",
    "backends": 160
  },
  {
    "config": "3 hosts x 4 workers",
    "backends": 240
  }
]

Quatre workers Gunicorn, chacun avec un pool de 20 connexions, demandent 80 backends. Ajoutez deux workers de tâches en arrière-plan et le total passe à 120. Avec 3 hosts x 4 workers, l’application demande 240 backends pour un max_connections de 100. Aucun de ces 5 déploiements n’est mal configuré à un seul endroit. Le pool est propre à chaque processus, et aucune partie de l’application ne voit le total.

Les valeurs par défaut des bibliothèques vont dans le même sens. Le paramètre QueuePool de SQLAlchemy vaut par défaut pool_size=5 avec max_overflow=10, soit 15 connexions par processus. HikariCP utilise 10 par défaut. Django ne disposait d’aucun pool intégré avant la version 5.1 et utilisait une connexion par processus worker. C’est pourquoi les applications Django rencontrent ce problème plus tard, puis toutes en même temps lorsque quelqu’un définit CONN_MAX_AGE ou active la nouvelle option "pool": True. Si vous exécutez une application Django derrière Gunicorn et nginx, le nombre à multiplier est celui des workers Gunicorn, pas celui des serveurs.

Ce que le pooling des connexions PostgreSQL change réellement sur un VPS

Un pooler est un processus qui parle le protocole wire PostgreSQL à votre application d’un côté et maintient un petit ensemble de connexions réelles au serveur de l’autre. Il n’accélère aucune requête. Il change la partie qui prend en charge le coût d’une connexion et le nombre de backends réels qui existent.

Deux éléments s’améliorent. L’établissement d’une connexion ne coûte plus un fork et les recherches dans le catalogue qui remplissent le cache vide d’un backend, car le pooler répond lui-même à la connexion du client. Surtout, le nombre de backends réels ne suit plus le nombre de connexions de l’application : 500 clients peuvent partager 20 backends.

L’attente est la fonctionnalité, et c’est ce qui suscite le plus de réticences. Sans pooler, 500 requêtes concurrentes obtiennent chacune un backend et s’exécutent toutes en même temps sur deux cœurs CPU. Elles sont donc toutes lentes et toute la mémoire est utilisée au même moment. Avec un pooler, 20 requêtes s’exécutent et les autres attendent quelques millisecondes. Chaque requête en cours d’exécution reçoit ainsi une part réelle du CPU et se termine plus rapidement. Une file d’attente devant un petit pool est préférable à l’absence de file devant un grand pool.

Un pooler ne limite rien d’autre sur la machine. Si Postgres partage le VPS avec un serveur applicatif ou avec une base de données vectorielle sur le même VPS, le pooler protège Postgres contre votre application, et rien de plus. Fixez aussi une limite stricte aux services voisins : vous pouvez limiter la mémoire et le CPU qu’un service peut utiliser avec systemd afin qu’un processus incontrôlé ne puisse pas entraîner la base de données dans sa chute. L’emplacement de la base de données détermine la manière de définir ces limites. C’est la différence pratique entre exécuter Postgres dans Docker ou directement sur l’hôte.

Mise en pool par session ou par transaction

Un seul paramètre détermine tout le reste : pool_mode.

Avec la mise en pool par session, une connexion serveur est attribuée à un client pendant toute la durée de sa connexion, puis libérée lorsque le client se déconnecte. Tout fonctionne, car le pooler agit comme un simple proxy. Vous économisez le coût de l’établissement des connexions, rien de plus. Si l’application ouvre 200 connexions, vous avez toujours besoin de 200 backends.

Avec la mise en pool par transaction, une connexion serveur est attribuée à un client uniquement pendant une transaction. À COMMIT ou ROLLBACK, elle retourne dans le pool et le client suivant en attente la récupère. C’est ce qui permet de faire gérer 500 clients par 20 backends. C’est aussi ce qui casse certaines fonctionnalités, par conception : l’instruction suivante peut s’exécuter sur un backend différent de celui utilisé par l’instruction précédente.

Le mode par défaut de PgBouncer est pool_mode = session. Installez-le sans rien modifier : vous obtenez la moitié économique, sans aucun gain réel. Un troisième mode, statement, retourne la connexion après chaque instruction et refuse les transactions comportant plusieurs instructions. Ne l’utilisez que si vous savez exactement pourquoi vous en avez besoin.

Quel mode de transaction pose problème, et pourquoi

Tout ce qui suit échoue pour une seule raison. Il s’agit d’un état conservé dans un backend donné, alors que le transaction pooling ne garantit pas l’utilisation deux fois du même backend.

• SET et RESET au niveau de la session. SET search_path, SET statement_timeout, SET TIME ZONE et SET ROLE s’exécutent sur le backend qui a traité l’instruction concernée, puis sont perdus à la transaction suivante. Utilisez SET LOCAL dans une transaction explicite. Il est limité à cette transaction et peut donc être utilisé sans risque.
• LISTEN. La remise des notifications dépend du backend qui a exécuté LISTEN. Ce backend est attribué à un autre client dès que la transaction se termine. NOTIFY fonctionne toujours en mode transaction, ce qui rend l’échec difficile à diagnostiquer : l’envoi réussit, mais la réception n’a jamais lieu. Si vous avez besoin de LISTEN, ouvrez une connexion supplémentaire directement sur le port 5432, sans passer par le pooler.
• Les verrous advisory au niveau de la session. pg_advisory_lock() est conservé par la session et libéré lorsque celle-ci se termine. Avec le transaction pooling, l’appel de déverrouillage s’exécute sur un autre backend. Le verrou reste donc détenu jusqu’à ce que PgBouncer retire cette connexion serveur. Par défaut, cela se produit après server_lifetime, soit une heure. Utilisez pg_advisory_xact_lock(). Il est libéré à la fin de la transaction par le même backend que celui qui l’a pris.
• PREPARE et DEALLOCATE, les instructions SQL. Elles ne sont jamais disponibles en mode transaction.
• Les curseurs WITH HOLD et tout curseur côté serveur censé rester disponible après la fin de sa transaction.
• Les tables temporaires censées rester disponibles après un commit. CREATE TEMP TABLE ... ON COMMIT PRESERVE ROWS place la table dans le schéma temporaire d’un backend, mais votre transaction suivante peut utiliser un autre backend.
• LOAD.

Les prepared statements au niveau du protocole sont le seul élément qui a évolué. PgBouncer 1.21.0 les prend en charge en mode transaction, et la version 1.24.0 a activé cette fonctionnalité par défaut en définissant max_prepared_statements à 200. Les versions plus anciennes laissent cette valeur à 0, ce qui désactive la fonctionnalité. Ubuntu 24.04 fournit PgBouncer 1.22.0. La fonctionnalité est donc présente, mais vous devez définir max_prepared_statements vous-même. Si vous ne savez pas exactement ce que fait votre build, le réglage sûr se trouve côté client : psycopg 3 cesse d’utiliser les prepared statements côté serveur lorsque vous définissez prepare_threshold sur None.

Django utilise sa propre terminologie pour ce problème. La documentation précise que « l’utilisation d’un pooler de connexions en mode transaction (par exemple PgBouncer) nécessite de désactiver les curseurs côté serveur pour cette connexion », car « les curseurs côté serveur ne sont accessibles que depuis la connexion sur laquelle ils ont été créés ». Définissez DISABLE_SERVER_SIDE_CURSORS sur True dans l’entrée de cette base de données. Sinon, chaque appel à .iterator() échouera de manière intermittente, uniquement sous charge.

Le mode transaction vaut la peine d’être utilisé, mais il implique un contrat. Lisez la liste, vérifiez que votre ORM et votre bibliothèque de tâches en arrière-plan la respectent, puis activez-le.

Installer PgBouncer et faire pointer l’application vers lui

La configuration ci-dessous doit être exécutée sur votre propre serveur : Ubuntu 24.04, avec PostgreSQL qui écoute déjà sur 127.0.0.1, port 5432.

sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version

Ubuntu 24.04 fournit PgBouncer 1.22.0. La version amont est 1.25.2 en août 2026. Vérifiez la version installée, car le comportement des requêtes préparées décrit plus haut en dépend.

Créez un rôle dont le seul objectif est de se connecter à la console d’administration de PgBouncer, puis créez le fichier de mots de passe. PgBouncer a besoin des secrets SCRAM (mécanisme d’authentification par réponse à un challenge salé) de pg_authid, et seul un superuser peut lire cette table.

sudo -u postgres psql -c "CREATE ROLE pgb_admin LOGIN PASSWORD 'change-this'"
sudo -u postgres psql -At -c \
  'SELECT format($$"%s" "%s"$$, rolname, rolpassword) FROM pg_authid WHERE rolpassword IS NOT NULL' \
  > /tmp/userlist.txt
sudo install -o postgres -g postgres -m 640 /tmp/userlist.txt /etc/pgbouncer/userlist.txt
rm /tmp/userlist.txt

C’est la copie des secrets, et non la ressaisie des mots de passe, qui permet cette configuration. PgBouncer ne peut réutiliser un secret SCRAM pour se connecter à PostgreSQL que si le client s’est également authentifié avec SCRAM, si le secret du fichier est identique octet par octet à celui de pg_authid (même sel et même nombre d’itérations, pas seulement le même mot de passe), et si la ligne [databases] ne fixe pas de user=. Ajoutez user=appuser à cette ligne et PgBouncer aura besoin d’un mot de passe en clair. Vérifiez que le propriétaire du fichier correspond au compte utilisé par le service avec systemctl show pgbouncer -p User. Modifier un mot de passe dans PostgreSQL implique de régénérer ce fichier, faute de quoi la prochaine connexion renverra password authentication failed.

Écrivez maintenant /etc/pgbouncer/pgbouncer.ini.

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgb_admin
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
max_db_connections = 80
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits

listen_addr = 127.0.0.1 maintient le pooler hors de l’Internet public, ce qui est important, car un pooler accessible depuis l’extérieur constitue un endpoint d’authentification que vous ne vouliez pas publier. max_client_conn indique le nombre de connexions applicatives que PgBouncer acceptera, et ces connexions coûtent peu de ressources ; cette valeur peut donc être élevée. default_pool_size indique le nombre de backends réels qu’une paire base de données et utilisateur peut utiliser ; c’est la valeur coûteuse. max_db_connections limite l’ensemble de la base de données à 80 connexions, afin de laisser de la marge sous max_connections pour psql, les sauvegardes et la supervision. ignore_startup_parameters = extra_float_digits empêche PgBouncer de rejeter les drivers, notamment le driver JDBC, qui envoient ce paramètre lors de la connexion.

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager
sudo journalctl -u pgbouncer -n 20 --no-pager

Un démarrage correct écrit une ligne indiquant que PgBouncer écoute sur 127.0.0.1:6432. Si le démarrage échoue à cause du fichier de mots de passe, le journal indique le chemin qu’il n’a pas pu lire. Le problème vient presque toujours des permissions ou du propriétaire, et non de la syntaxe. Modifiez ensuite la chaîne de connexion de l’application pour remplacer le port 5432 par le port 6432, puis redémarrez-la. Rien d’autre ne change dans l’application.

Comment vérifier que le pool joue bien son rôle

PgBouncer dispose d’une console d’administration accessible via une base de données virtuelle appelée pgbouncer.

psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

SHOW POOLS est l’indicateur à surveiller. cl_active correspond aux clients actuellement attachés à une connexion serveur, cl_waiting aux clients en attente d’une connexion, sv_active et sv_idle aux backends réellement utilisés et libres, et maxwait au temps d’attente, en secondes, du client en tête de file. Sous une charge normale, un pool sain affiche cl_waiting à 0 et maxwait à 0. Un maxwait qui dépasse une ou deux secondes indique que le pool est trop petit ou que les requêtes sont trop lentes. Ces deux problèmes ne se corrigent pas de la même manière.

Vérifiez lequel se produit avant d’augmenter default_pool_size.

SELECT state, count(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' GROUP BY state;

Si la plupart des backends restent en idle in transaction, la taille du pool n’est pas en cause. L’application ouvre une transaction, puis effectue une opération lente à l’intérieur, par exemple un appel HTTP. Chaque backend reste ainsi occupé sans exécuter de requête. idle_in_transaction_session_timeout interrompra ces transactions, mais la véritable correction doit être apportée dans le code de l’application. Si, au contraire, tous les backends sont en active, le pool est réellement saturé. Les requêtes doivent alors faire l’objet de EXPLAIN (ANALYZE, BUFFERS) avant d’augmenter le nombre de connexions disponibles.

Pour dimensionner le pool, le point de départ le plus souvent cité est l’heuristique de HikariCP : environ deux fois le nombre de cœurs, plus un. Sur un VPS à 2 cœurs, cela donne 5. Considérez cette valeur comme un point de départ documenté, définissez default_pool_size à une valeur proche, puis ajustez-la en fonction de maxwait. Les petits pools semblent souvent insuffisants, mais donnent généralement de meilleurs résultats aux mesures. Un backend en attente ne consomme rien, tandis qu’un backend en cours d’exécution consomme du CPU et de la mémoire et augmente la contention sur les verrous.

Choisir entre PgBouncer, PgDog et Pgpool-II

PgBouncer convient au cas courant : un serveur PostgreSQL, un VPS et une application qui ouvre plus de connexions que la machine ne peut en gérer. Il fait une seule chose, sa configuration tient dans un seul fichier ini et il est fourni dans les paquets Debian et Ubuntu. Il gère les connexions dans un seul thread. C’est suffisant pour la charge d’un VPS. Cette architecture ne devient une limite que sur des machines beaucoup plus puissantes.

PgDog mérite votre attention lorsque la décision de routage doit être prise sur le même saut réseau que le pooling. Il se présente comme un proxy destiné à faire évoluer PostgreSQL. Il est écrit en Rust. Il prend en charge le pooling des transactions et des sessions, ainsi que la séparation des lectures et des écritures par analyse des requêtes. Il prend aussi en charge le sharding avec routage multi-shard et validation en deux phases. Utilisez-le lorsque vous avez un serveur primary et une ou plusieurs réplicas, et que vous voulez envoyer les lectures aux réplicas sans apprendre à l’application qu’elles existent. Deux points appellent votre attention. PgDog est sous licence AGPLv3. La clause relative à l’utilisation sur le réseau est donc un point de licence à régler avec la personne qui prend cette décision dans votre entreprise avant le passage en production. Le projet considère que l’utilisation interne et les modifications privées n’imposent pas de fournir le code source. Le projet est aussi récent, avec des releases hebdomadaires et des numéros de version 0.x. Épinglez donc un tag de release au lieu de suivre main.

git clone https://github.com/pgdogdev/pgdog
cd pgdog
cargo build --release
./target/release/pgdog --config pgdog.toml --users users.toml

La compilation depuis les sources nécessite une toolchain Rust stable récente, CMake et un compilateur C/C++. Des binaires Linux précompilés et des paquets Debian sont également disponibles sur la page des releases. Une image de conteneur est disponible à l’adresse ghcr.io/pgdogdev/pgdog. La configuration est répartie entre deux fichiers. Le premier contient les paramètres généraux et une entrée par base de données. Ces entrées sont écrites ici sous la forme d’un tableau TOML de tables inline, afin de distinguer facilement les deux formes.

databases = [
  { name = "appdb", host = "127.0.0.1" },
]

[general]
port = 6432
default_pool_size = 10

Le second contient une entrée par utilisateur, sous la même forme de tableau.

users = [
  { name = "appuser", database = "appdb", password = "change-this" },
]

PgDog écoute par défaut sur le port 6432, comme PgBouncer. Les deux ne peuvent donc pas utiliser la valeur par défaut sur le même hôte.

Pgpool-II, en version 4.7.2 en juin 2026, fournit du pooling et de la répartition de charge, avec un watchdog pour le failover automatique. Ces fonctionnalités supplémentaires ajoutent des modes de défaillance supplémentaires. Il faut surtout comprendre son modèle de pooling avant de le choisir. Pgpool-II précrée num_init_children processus enfants. Chaque enfant met en cache jusqu’à max_pool connexions aux serveurs. Le nombre maximal de backends est donc num_init_children multiplié par max_pool. Chaque enfant ne sert qu’un client à la fois. Le nombre de clients que vous pouvez accepter est donc égal à num_init_children et il est fixé au démarrage. Même un client inactif occupe toujours un processus enfant. Définissez num_init_children à 100 et max_pool à 4 : vous autorisez 400 backends. C’est précisément le problème que vous avez installé un pooler pour résoudre. Choisissez Pgpool-II si vous avez besoin de son failover et de son routage des requêtes, puis effectuez soigneusement cette multiplication. Si votre seul objectif est de réduire le nombre de backends, cet outil fournit plus de mécanismes que nécessaire.

La question du proxy managé et son équivalent auto-hébergé

Les plateformes managées proposent cela comme un produit distinct. AWS place RDS Proxy devant RDS, et Supabase place son propre pooler, Supavisor, devant Supabase Postgres. Les deux remplissent le rôle décrit ici : conserver les connexions client à faible coût et distribuer un nombre réduit de connexions vers les vrais backends. Supavisor est open source et peut être auto-hébergé. Le choix ne se limite donc pas à une solution propriétaire ou à une solution gratuite.

L’équivalent auto-hébergé d’un proxy managé n’est pas une idée différente. C’est la même idée, avec le fichier de configuration sous votre contrôle : PgBouncer en mode transaction, sur le même VPS que la base de données, en écoute sur 127.0.0.1. Deux différences sont réelles. Un proxy managé se trouve à un saut réseau de la base de données. Il ajoute donc de la latence et continue à conserver les connexions client pendant que la base de données redémarre. PgBouncer sur l’hôte de la base de données ajoute un saut loopback, dont le coût est presque nul, et s’arrête lorsque cet hôte s’arrête. Pour conserver les connexions après un redémarrage, vous avez également besoin d’un mécanisme de failover. C’est à ce moment que le watchdog de Pgpool-II ou les health checks de PgDog commencent à justifier leur complexité.

Une autre option doit être prise en compte. Si le nombre de connexions est le principal facteur de complexité de votre déploiement, une base de données embarquée n’a pas de modèle de connexions à mettre en pool. Il s’agit d’une bibliothèque intégrée à votre processus, et non d’un serveur en écoute sur un port. Pour un serveur applicatif unique avec un volume d’écritures modéré, exécuter SQLite en production sur un VPS supprime entièrement ce problème au lieu de vous obliger à le gérer. Lorsque vous avez besoin d’un vrai serveur, dimensionnez le pool avant de dimensionner la machine.

FAQ

Ai-je encore besoin de PgBouncer si mon application utilise déjà un pool de connexions ?

Généralement oui, car le pool de l’application est propre à chaque processus et ne voit pas les autres. Quatre workers Gunicorn qui détiennent chacun un pool de 20 backends de requêtes 80, puis l’ajout de deux workers en arrière-plan, portent le total à 120. PgBouncer est le seul composant qui voit le total et peut le plafonner. La bonne configuration combine les deux : un petit pool dans chaque worker, afin que les requêtes n’aient pas à établir une connexion TCP, et PgBouncer en mode transaction pour limiter le nombre réel de backends utilisés.

Que se passe-t-il exactement lorsque je passe PgBouncer en mode transaction ?

Tout ce qui conserve un état dans un même backend entre deux transactions cesse de fonctionner correctement. Cela concerne SET et RESET au niveau de la session, les curseurs LISTEN et WITH HOLD, les instructions SQL PREPARE et DEALLOCATE, les verrous consultatifs au niveau de la session, les tables temporaires qui doivent survivre à un commit et LOAD. NOTIFY continue de fonctionner, ce qui fait paraître les problèmes liés à LISTEN comme des erreurs de livraison plutôt que comme des problèmes de pooling. Avec Django, définissez DISABLE_SERVER_SIDE_CURSORS sur True. Avec psycopg 3, définissez prepare_threshold sur None ou utilisez PgBouncer 1.22 ou une version ultérieure avec max_prepared_statements supérieur à 0. Remplacez pg_advisory_lock() par pg_advisory_xact_lock().

Quelle valeur donner à default_pool_size sur un VPS à 2 cœurs ?

Une valeur plus faible que ce qui semble intuitif. L’heuristique HikariCP largement publiée consiste à prendre environ deux fois le nombre de cœurs, plus un, soit environ 5 sur deux cœurs. Il s’agit d’un point de départ, pas d’une réponse définitive. Définissez cette valeur, puis consultez maxwait et cl_waiting dans SHOW POOLS sous une charge réelle. Si les deux valeurs sont à 0, le pool est suffisamment grand. Une valeur maxwait en hausse indique que les clients sont mis en file d’attente. Avant d’augmenter la taille du pool, vérifiez pg_stat_activity : des backends bloqués dans idle in transaction signalent un problème dans l’application que l’ajout de connexions ne fera que masquer.

PgBouncer ou PgDog ?

PgBouncer pour un serveur PostgreSQL sur un seul VPS, ce qui correspond à la plupart des déploiements. Il est fourni dans Ubuntu, son comportement est bien documenté et toute sa configuration tient dans un seul fichier ini. Choisissez PgDog lorsque la répartition des lectures et des écritures entre des réplicas ou le sharding doit être géré au même niveau que le pooling, afin que l’application n’ait pas à connaître la topologie. Avant de retenir PgDog, clarifiez la question de l’AGPLv3 avec la personne responsable des licences dans votre organisation et verrouillez une version précise, car le projet utilise encore des numéros de version 0.x et publie chaque semaine.