PostgreSQL work_mem : tris et réglages locaux

Dans PostgreSQL, work_mem contrôle l’allocation mémoire de base d’opérations comme les tris et les tables de hachage avant le recours au stockage temporaire sur disque. Il s’applique aux opérations du plan : une requête peut donc utiliser plusieurs allocations. Les requêtes simultanées et les workers parallèles augmentent le total. Trouver une valeur adaptée à une requête ne suffit pas pour la généraliser au serveur.

Ce guide utilise une base PostgreSQL 18.6 jetable pour montrer un tri qui déborde sur disque à 64 kB et reste en mémoire à 32 MB. Les deux exécutions renvoient les mêmes 40 000 lignes dans le même ordre. L’objectif est de rendre les observations lisibles et de limiter la durée du changement. Aucune de ces valeurs n’est recommandée comme réglage de production.

Examiner les réglages de la connexion utilisée

Commencez par les valeurs effectives. Cette requête indique aussi l’unité et le contexte de configuration, afin de ne pas interpréter un nombre nu comme une valeur en mégaoctets :

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'max_parallel_workers_per_gather')
ORDER BY name;

hash_mem_multiplier|2|<NULL>|user
maintenance_work_mem|65536|kB|user
max_parallel_workers_per_gather|2|<NULL>|user
work_mem|4096|kB|user

Dans notre environnement neuf, work_mem vaut 4096 kB, soit 4 MB. Le multiplicateur sans unité vaut 2. Le réglage de parallélisme autorise initialement deux workers par Gather ; les expériences suivantes le fixent explicitement à zéro dans leurs transactions.

La documentation des ressources distingue ces paramètres. Les opérations de hachage utilisent work_mem multiplié par hash_mem_multiplier. Les opérations de maintenance relèvent de limites distinctes, notamment maintenance_work_mem. Cet article mesure un tri, pas un débordement de hachage ni une opération de maintenance.

Notez la valeur avant le test. Une connexion applicative peut avoir des réglages différents d’une nouvelle connexion d’administration : l’examen de l’une n’explique donc pas nécessairement le comportement de l’autre.

Créer un jeu de données limité

Exécutez ce SQL dans une base jetable où workmem_lab n’existe pas encore. Il crée 40 000 lignes avec une clé de tri et une charge utile de 128 caractères. Aucun index ne fournit l’ordre demandé :

CREATE SCHEMA workmem_lab;
CREATE TABLE workmem_lab.events AS
SELECT g AS id,
       md5((g * 37)::text) AS sort_key,
       repeat(md5(g::text), 4) AS payload
FROM generate_series(1, 40000) AS g;
ANALYZE workmem_lab.events;
SELECT count(*), sum(id) FROM workmem_lab.events;

40000|800020000

La sortie confirme le nombre de lignes et la somme des identifiants. Le code de vérification contrôle aussi les identifiants de 1 à 40 000, leur unicité, la longueur constante de la charge utile et l’absence d’index. ANALYZE fournit les statistiques au planificateur.

La charge utile est volontairement incluse dans la requête triée. Trier un résultat plus large peut demander un espace de travail différent de celui nécessaire aux seuls identifiants. Gardez les mêmes colonnes et le même ordre lorsque vous comparez deux réglages.

Notre programme de test crée une nouvelle instance locale, l’écoute uniquement sur localhost avec un port disponible, utilise une authentification par mot de passe et l’arrête ensuite. Le SQL de l’article peut aussi s’exécuter dans une base jetable existante. Ne le copiez pas dans une base de production comme exercice de réglage.

Observer un tri qui déborde sur disque

La première transaction limite work_mem à 64 kB et exclut les workers parallèles. EXPLAIN ANALYZE exécute le SELECT ; BUFFERS enregistre l’activité des blocs. TIMING OFF et SUMMARY OFF concentrent la démonstration sur le plan plutôt que sur une comparaison de durées :

BEGIN;
SET LOCAL work_mem = '64kB';
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF, FORMAT JSON)
SELECT id, sort_key, payload
FROM workmem_lab.events
ORDER BY sort_key, id;
COMMIT;

{
  "Node Type": "Sort",
  "Sort Key": [
    "sort_key",
    "id"
  ],
  "Actual Rows": 40000.0,
  "Actual Loops": 1,
  "Sort Method": "external merge",
  "Sort Space Used": 7176,
  "Sort Space Type": "Disk",
  "Temp Read Blocks": 2678,
  "Temp Written Blocks": 2864
}

Le JSON affiché après le SQL est un extrait de champs du nœud Sort capturé, pas le document EXPLAIN complet. Le plan intégral figure dans l’archive de revue.

Dans cette exécution, la méthode est external merge et Sort Space Type vaut Disk. Le nœud indique 7 176 kB d’espace de tri, 2 678 blocs temporaires lus et 2 864 écrits. Ces champs montrent directement le recours au disque pour ce jeu de données et ce réglage.

L’espace de tri et les compteurs de blocs temporaires décrivent des choses différentes. Ne les additionnez pas et ne les interprétez pas comme la valeur exacte de work_mem qui éviterait le débordement. Le nombre réel de lignes est 40 000 et le nombre de boucles vaut un : l’exemple porte sur une seule exécution du tri.

La documentation EXPLAIN de PostgreSQL décrit les informations de tri et de buffers. Notre guide EXPLAIN, en anglais, explique la lecture du plan complet lorsqu’une requête réelle comporte plusieurs opérations importantes.

Comparer une allocation locale plus grande

Exécutez le même SELECT dans une autre transaction avec 32 MB. Conservez les données, les colonnes sélectionnées, l’ordre et le réglage du parallélisme :

BEGIN;
SET LOCAL work_mem = '32MB';
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF, FORMAT JSON)
SELECT id, sort_key, payload
FROM workmem_lab.events
ORDER BY sort_key, id;
COMMIT;

{
  "Node Type": "Sort",
  "Sort Key": [
    "sort_key",
    "id"
  ],
  "Actual Rows": 40000.0,
  "Actual Loops": 1,
  "Sort Method": "quicksort",
  "Sort Space Used": 9037,
  "Sort Space Type": "Memory",
  "Temp Read Blocks": 0,
  "Temp Written Blocks": 0
}

Le nœud Sort indique cette fois quicksort, Memory et 9 037 kB d’espace de tri. Les lectures et écritures temporaires valent zéro. Les deux plans comportent un tri principal sur tout le jeu de données, sans Gather ni Gather Merge.

L’espace indiqué pour le tri en mémoire dépasse celui du tri sur disque précédent. Cette différence observée illustre pourquoi l’espace disque ne donne pas directement la quantité de mémoire à configurer. Nous avons choisi 32 MB pour rendre le changement visible, sans rechercher la plus petite valeur suffisante.

Notre vérification exécute la requête complète avec les deux valeurs et compare chaque ligne renvoyée, charge utile comprise. Les 40 000 lignes coïncident dans le même ordre. Un calcul Python indépendant reconstitue également les clés de tri, l’ordre et les charges utiles.

Ce résultat montre que le réglage a changé l’emplacement de travail du tri sans modifier son résultat. Il ne constitue pas un benchmark de vitesse. Un seul plan a été enregistré pour chaque valeur, l’état du cache n’a pas été contrôlé pour comparer les performances et aucune charge concurrente ne s’exécutait.

Vérifier la portée de SET LOCAL

Utilisez une transaction explicite pour un changement temporaire. Cet exemple établit d’abord une valeur de session de 2 MB, puis modifie la valeur locale dans deux transactions distinctes :

SET work_mem = '2MB';
SHOW work_mem;
BEGIN;
SET LOCAL work_mem = '8MB';
SHOW work_mem;
COMMIT;
SHOW work_mem;
BEGIN;
SET LOCAL work_mem = '16MB';
SHOW work_mem;
ROLLBACK;
SHOW work_mem;
RESET work_mem;

2MB
8MB
2MB
16MB
2MB

La séquence est 2 MB, 8 MB, 2 MB, 16 MB puis 2 MB. COMMIT et ROLLBACK rétablissent tous deux la valeur de session qui précédait leur changement local. Le RESET final retire la surcharge de session de cet exemple. Dans notre environnement, la valeur par défaut après RESET est 4 MB.

La référence SET documente cette portée. Hors transaction, SET LOCAL émet un avertissement et n’a pas d’effet durable. Un simple SET peut persister dans la session après une transaction réussie, ce qui compte lorsqu’une connexion de pool est réutilisée.

Exécutez BEGIN, SET LOCAL, la requête et la fin de transaction sur la même connexion. Un pool ne réunit pas automatiquement des demandes distinctes dans une transaction. Nos tests vérifient aussi la restauration après retour à un point de sauvegarde, l’interprétation de 2048 sans unité comme 2 MB et le rejet de valeurs mal formées ou inférieures au minimum.

Déterminer si davantage de mémoire traite le problème

Avant de transformer l’essai local en changement opérationnel, identifiez le tri ou le hachage concerné, la fréquence de la requête et les autres traitements simultanés. Examinez si un résultat moins large, un index adapté ou un filtre appliqué plus tôt peut supprimer du travail inutile. Une allocation supérieure ne corrige pas tous les mauvais plans.

Un calcul simple illustre le risque lié à la concurrence sans prétendre prévoir toute la mémoire du serveur. Si dix requêtes simultanées comportent chacune deux opérations utilisant 32 MB, ces opérations seules peuvent représenter environ 640 MB. Ce calcul exclut les multiplicateurs de hachage, les workers, la mémoire partagée, le coût des connexions et d’autres allocations. Il s’agit d’un exemple de planification, pas d’un plafond ni d’une charge mesurée.

Validez sous une charge représentative avant d’élargir le réglage. Examinez ensemble la pression mémoire et les entrées-sorties temporaires, avec un moyen clair de rétablir la configuration précédente. Notre guide de l’hébergement PostgreSQL, en anglais, présente les responsabilités opérationnelles des services gérés et des serveurs autogérés.

Supprimer les données de l’exercice

À la fin, supprimez uniquement le schéma créé pour cet exercice. CASCADE retire aussi ses objets ; vérifiez donc son nom avant d’exécuter la commande :

DROP SCHEMA workmem_lab CASCADE;
SELECT to_regnamespace('workmem_lab') IS NULL AS removed;

t

Le t final confirme que le schéma n’existe plus. Notre test a réussi 31 vérifications, supprimé les données de l’exercice, arrêté le serveur et effacé le fichier de mot de passe temporaire. Il fournit une méthode reproductible pour relier un réglage temporaire à des observations réelles du plan, tout en conservant les limites de l’expérience.