Un job nocturne réécrit des statistiques pour toutes les lignes d’un tenant avec une seule instruction UPDATE … FROM (VALUES …). Le service impose un statement_timeout de 10 s, et l’instruction est annulée avec l’erreur 57014.

Cet article décrit le coût d’un VALUES de 150 000 lignes pour le serveur, réécrit la requête avec unnest, puis compare les deux.

Avant : 150 000 lignes de VALUES assemblées dans une instruction.
Après : huit tableaux passés à unnest dans la même instruction

Avantréponse du servicede calcul→bun : 150 000 lignesde VALUES, 27 Mo de texte→un UPDATE de 27 Mo465 455 kB planificationAprèsréponse du servicede calcul→8 tableaux liés10 paramètres→une instruction unnest10 489 kB planification

Avant : une ligne de VALUES par capteur, assemblée dans un texte de 27 Mo.
Après : huit tableaux passés à unnest dans la même instruction.

Le timeout de 10 s couvre toute l’instruction

Le service applique un statement_timeout de 10 s à toutes ses requêtes.
Le délai court de l’arrivée de la commande sur le serveur jusqu’à sa fin.
En protocole étendu, il démarre à la réception de Parse, Bind, Execute ou Describe[1]. Une instruction qui le dépasse échoue avec le SQLSTATE 57014 et le message canceling statement due to statement timeout.

Le job nocturne recalcule des statistiques par capteur pour un tenant.
Un service de calcul externe fournit les valeurs :

  • un nombre de relevés,
  • une moyenne,
  • un nombre d’heures manquantes pour la température,
  • l’humidité et la puissance.

Un capteur sur trois est absent de la réponse et reçoit des NULL ; un autre sur trois a des statistiques à 0.

CREATE TABLE sensors (
    id                        text PRIMARY KEY,
    tenant_code               text NOT NULL,
    -- … colonnes descriptives (label, model, firmware_version, location, metadata, dates)
    reading_count             integer,
    avg_temperature           numeric,
    temperature_missing_hours integer,
    avg_humidity              numeric,
    humidity_missing_hours    integer,
    avg_power                 numeric,
    power_missing_hours       integer,
    stats_refreshed_at        timestamptz
) WITH (autovacuum_enabled = false);
CREATE INDEX sensors_tenant_code_idx ON sensors (tenant_code);

Le jeu de données contient 150 000 capteurs pour le tenant ACME et 20 000 capteurs pour chacun des tenants GLOBEX, INITECH et UMBRELLA, soit 210 000 lignes.
La table complète, avec ses colonnes descriptives, pèse 53 Mo.

L’autovacuum est désactivé sur cette table uniquement pour rendre lisibles les compteurs de tuples morts.

Détails de l’environnement technique

Les mesures tournent dans un conteneur postgres:18 (PostgreSQL 18.6, aarch64) limité à 2 vCPU et 2 Go, sauf mention contraire. Il s’exécute dans une VM Docker Desktop de 4 CPU et 7,75 Gio, sur un hôte Apple M5 Max. shared_buffers (128 Mo) et work_mem (4 Mo) gardent leurs valeurs par défaut. statement_timeout vaut 10 s via PGOPTIONS. Un VACUUM FULL puis un CHECKPOINT précèdent chaque mesure.

Les scripts de ces mesures sont dans le dépôt d’exemple[11].

Le pic de mémoire résidente (VmHWM) vient du noyau Linux, qui l’expose dans /proc/<pid>/status. Il couvre toute la durée de vie du processus serveur : chaque mesure utilise donc une connexion neuve. psql doit tourner dans le conteneur du serveur : sans cela, /proc ne montre pas le processus.

SELECT pg_backend_pid() AS pid \gset
\setenv PID :pid
\! grep VmHWM /proc/$PID/status
\i requete.sql
\! grep VmHWM /proc/$PID/status

La requête initial : une CTE générée par un ORM

L’ORM bun construit la requête avec Bulk(). Elle prend la forme d’une CTE _data qui contient une ligne de VALUES par capteur, chaque valeur étant castée[2]. La jointure sur sensors suit la syntaxe UPDATE … FROM décrite dans “Une requête, plusieurs écritures dans PostgreSQL”.

WITH "_data" ("id", "reading_count", "avg_temperature", "temperature_missing_hours", "avg_humidity",
              "humidity_missing_hours", "avg_power", "power_missing_hours", "stats_refreshed_at") AS (VALUES
  ('0000ae6c-…'::text, 0::integer, 0::numeric, 0::integer, 0::numeric, 0::integer, 0::numeric, 0::integer,
   '2026-10-23 02:00:00+00'::timestamptz),
  ('00039fda-…'::text, NULL::integer, NULL::numeric, NULL::integer, NULL::numeric, NULL::integer, NULL::numeric,
   NULL::integer, '2026-10-23 02:00:00+00'::timestamptz), …)
UPDATE "sensors" AS "s" SET "reading_count" = _data."reading_count", … , "stats_refreshed_at" = _data."stats_refreshed_at"
FROM _data WHERE ("s"."id" = _data."id") AND (s.tenant_code = 'ACME');

Pour 150 000 lignes, le texte envoyé au serveur pèse 27 350 680 octets, soit 27 Mo.

Côté Go, la construction tient en un appel :

// Avant : une ligne de VALUES par capteur, assemblée et formatée côté client.
stmt := db.NewUpdate().
    Model(&stats).
    Column("reading_count", "avg_temperature", /* … 6 autres colonnes */).
    Where("s.id = _data.id").
    Bulk()
_, err := db.ExecContext(ctx, stmt)

Un gros VALUES tue le processus serveur ou dépasse les 10 s

Pour reproduire en local, on a dû élaborer plusieurs banc de test.

🚧 C’est souvent le plus compliqué : reproduire le comportement d’un environement.

C’est souvent celui de production mais pas que. En effet, cela nous est déjà arrivé de détecter un comportement en staging avant d’atteindre la production 😉

Le tableau suivant reprend les mesures de l’instruction VALUES sur le tenant entier, en une seule requête :

CasRessourcesRésultat brut
150 000 lignes2 vCPU, 2 Go2495, 1207, 1209 ms puis 1340, 1312, 1277 ms — UPDATE 150000
600 000 lignes2 vCPU, 2 Goserver closed the connection unexpectedly ; log : client backend (PID 386) was terminated by signal 9: Killed (OOM, reproduit deux fois)
600 000 lignes2 vCPU, 6 Go7761 ms, puis 5539 ms
600 000 lignes0,5 vCPU, 6 GoERROR: 57014: canceling statement due to statement timeout après 10 867,741 ms

À 150 000 lignes, le VALUES passe sous la limite sur la machine de test : environ 1,3 s, pour six exécutions entre 1,2 et 2,5 s.
Le timeout n’a pas été reproduit à ce volume car cela dépend des ressources.
Avec 2 Go de mémoire, le processus serveur est tué (SIGKILL, kill -9) à 600 000 lignes mais sous 6 Go et 0,5 vCPU, la requête dépasse 10 s comme observé sur l’environnement de test.

ℹ️ La limite de 0,5 vCPU simule une instance modeste, dont la taille réelle est inconnue.

Les durées totales de 150 000 à 600 000 lignes sont trop bruitées pour conclure plus.

L’instruction échoue :

  • soit par manque de mémoire (processus tué avec 2 Go)
  • soit par manque de temps (57014 avec 0,5 vCPU).
Ce que l’équipe a observé sur son environnement

L’équipe à l’origine du job a mesuré le même UPDATE … FROM (VALUES …) sur ses propres machines. Ces mesures viennent d’un autre environnement que celles du tableau ci-dessus et ne sont pas comparables.

Sur l’environnement de test, le job échouait chaque nuit avec 57014 pour un tenant d’environ 100 000 lignes. Sur une copie locale de la base de développement (conteneur PostgreSQL 18.1, 150 000 lignes synthétiques, statement_timeout de 10 s), l’instruction a duré 16,4 s avant d’être annulée, puis le processus serveur a été tué par manque de mémoire.

À 150 000 lignes, les mesures de cet article passent en environ 1,3 s sur la machine de test (2 vCPU, 2 Go). La cause de l’écart entre 16,4 s et 1,3 s n’est pas vérifiée. Les deux environnements diffèrent par la machine, la configuration du conteneur PostgreSQL et le jeu de données : lignes synthétiques de l’équipe, schéma anonymisé ici.

Réécrire avec unnest : un tableau par colonne

La réécriture change la forme de la requête, pas les lignes écrites.
Chaque colonne devient un tableau :

  • un paramètre pour tous les identifiants,
  • un autre pour toutes les valeurs d’une colonne.
UPDATE sensors AS s
SET reading_count = v.reading_count, avg_temperature = v.avg_temperature,
    temperature_missing_hours = v.temperature_missing_hours, avg_humidity = v.avg_humidity,
    humidity_missing_hours = v.humidity_missing_hours, avg_power = v.avg_power,
    power_missing_hours = v.power_missing_hours, stats_refreshed_at = $9
FROM unnest($1::text[], $2::int[], $3::numeric[], $4::int[], $5::numeric[], $6::int[], $7::numeric[], $8::int[])
     AS v(id, reading_count, avg_temperature, temperature_missing_hours, avg_humidity,
          humidity_missing_hours, avg_power, power_missing_hours)
WHERE s.id = v.id AND s.tenant_code = $10;

unnest accepte plusieurs tableaux seulement dans la clause FROM.
Il renvoie une colonne par tableau, complète les plus courts avec des NULL[3] et équivaut à ROWS FROM[4].

Les 3 cas limites
  • NULL et zéro : '{12, NULL, 0}'::int[] et '{21.5, NULL, 0}'::numeric[] écrivent 12 | 21.5, deux NULL, puis 0 | 0. NULL reste NULL et 0 reste 0.
  • Longueurs différentes : unnest('{a,b,c}'::text[], '{1,2}'::int[]) renvoie la ligne c avec n à NULL. Un tableau d’identifiants plus long que les tableaux de valeurs écrase donc des colonnes par NULL, sans erreur.
  • Doublon d’identifiant : avec les valeurs 100 et 200, l’instruction répond UPDATE 1 et retient 100, mais la ligne utilisée n’est pas prévisible[5]. Un paquet ne doit contenir chaque identifiant qu’une fois.

Exemple : trois capteurs, mais seulement deux valeurs par colonne.

UPDATE sensors s
SET reading_count = d.reading_count, avg_temperature = d.avg_temperature
FROM unnest('{s1,s2,s3}'::text[], '{11,21}'::int[], '{20.5,21.5}'::numeric[])
     AS d(id, reading_count, avg_temperature)
WHERE s.id = d.id;
Trois identifiants, deux tableaux de valeurs de deux éléments : unnest complète la troisième ligne avec des NULLids3 élémentsreading_count2 élémentsavg_temperature2 élémentss11120.5s22121.5s3NULLNULLunnest complète les tableaux de 2 éléments avec des NULL :s3 reçoit NULL dans les deux colonnes.
Les trois tableaux sont alignés par position. Les tableaux de valeurs ont un élément de moins que celui des identifiants : unnest ajoute NULL en troisième ligne.

L’instruction répond UPDATE 3, sans erreur. Le capteur s3 perd ses valeurs :

idreading_countavg_temperature
avantaprèsavantaprès
s1101120.020.5
s2202121.021.5
s330NULL22.0NULL

Pour 150 000 lignes, la requête et ses valeurs liées pèsent 9 350 693 octets, contre 27 350 680 octets pour le texte du VALUES.

Côté Go, la réécriture change l’appel :

// Après : les tableaux passent en paramètres liés (pgx).
// Dix paramètres, quel que soit le nombre de lignes.
_, err = conn.Exec(ctx, updateSQL,
    pgtype.FlatArray[string](ids),
    pgtype.FlatArray[int32](counts),
    pgtype.FlatArray[pgtype.Numeric](avgTemps),
    pgtype.FlatArray[int32](missingTemps),
    pgtype.FlatArray[pgtype.Numeric](avgHums),
    pgtype.FlatArray[int32](missingHums),
    pgtype.FlatArray[pgtype.Numeric](avgPows),
    pgtype.FlatArray[int32](missingPows),
    statsRefreshedAt, tenant)

VALUES contre unnest : planification et mémoire

Les requêtes VALUES et unnest écrivent les mêmes lignes.
La différence porte sur la planification et la mémoire, pas sur le temps d’exécution du plan.

L’option MEMORY d’EXPLAIN affiche la mémoire consommée par la planification[6]. Pour un paquet de 2 000 lignes, c’est-à-dire un UPDATE qui ne cible que 2 000 des 150 000 capteurs du tenant, elle atteint 6 219 kB avec VALUES contre 186 kB avec unnest.

L’analyse du plan, Hash Join contre Nested Loop, est traitée dans un prochain article.

La mémoire de planification suit la taille de la liste. Premier paquet de chaque taille :

Taille Méthode Jointure Mémoire Planification Exécution
2 000 VALUES Hash Join 6 219 kB 2,355 ms 30,0 ms
2 000 unnest Nested Loop 186 kB 0,279 ms 11,1 ms
20 000 VALUES Hash Join 62 051 kB 17,1 ms 109,3 ms
20 000 unnest Hash Join 1 431 kB 0,490 ms 115,7 ms
150 000 VALUES Hash Join 465 455 kB 139,1 ms 754,2 ms
150 000 unnest Hash Join 10 489 kB 1,369 ms 763,2 ms
Mémoire de planification d’un paquet selon sa taille, avec VALUES et avec unnest, en échelle logarithmiqueMémoire de planification d’un paquet (kB, échelle logarithmique)101001 00010 000100 0001 000 0002 00020 000150 000lignes dans le paquet (échelle logarithmique)6 21962 051465 4551861 43110 489VALUESunnest
Mémoire de planification du premier paquet, selon sa taille, avec les valeurs du tableau ci-dessus. Les deux axes sont logarithmiques. À 150 000 lignes, la mémoire de planification de VALUES vaut environ 44 fois celle d’unnest.

Avec VALUES, le planificateur consomme environ 3,1 kB par ligne, de façon linéaire : 465 455 kB à 150 000 lignes. Avec unnest, la requête ne transmet qu’une constante tableau par colonne, et la mémoire de planification reste à 10 489 kB au même volume. À 150 000 lignes, le temps d’exécution est comparable (754,2 ms contre 763,2 ms). La différence porte sur la planification (139,1 ms contre 1,369 ms) et sur la mémoire.

Temps de planification et d’exécution d’un paquet de 150 000 lignes, avec VALUES et avec unnestPlanification et exécution d’un paquet de 150 000 lignes (ms)VALUESexécution 754,2 msplanification 139,1 msunnestexécution 763,2 msplanification 1,369 ms
Planification et exécution à 150 000 lignes, avec les valeurs du tableau ci-dessus. Les durées d’exécution sont comparables. La planification de VALUES est environ 100 fois plus longue que celle d’unnest.

Le tableau suivant reprend les mesures d’unnest sur le tenant entier, en une seule requête.

Cas Ressources Résultat brut
unnest bind, 150 000 lignes 2 vCPU, 2 Go 782, 781, 781 ms
unnest bind, 600 000 lignes 0,5 vCPU, 6 Go 8391,923 ms (84 % du budget) ; 9 806 et 9 890 ms (98 à 99 %) lors de deux reproductions

Le pic de mémoire résidente du processus serveur (VmHWM) atteint, à 150 000 lignes, 1 239 644 kB avec VALUES et 179 560 kB avec unnest. Avec VALUES, il monte à 1 755 468 kB à 300 000 lignes et à 4 269 264 kB à 600 000 lignes (limite de 6 Go).

Pic de mémoire résidente du processus serveur selon le nombre de lignes, avec VALUES et avec unnestPic de mémoire résidente du processus serveur (VmHWM, kB)limite de 2 Go150 000 lignes · VALUES1 239 644 kB150 000 lignes · unnest179 560 kB300 000 lignes · VALUES1 755 468 kB600 000 lignes · VALUES4 269 264 kB
Pic de mémoire résidente (VmHWM) du processus serveur pour une requête sur le tenant entier. Les mesures à 150 000 et 300 000 lignes tournent sous une limite de 2 Go, celle à 600 000 lignes sous 6 Go : avec 2 Go, le processus est tué.
Pourquoi EXPLAIN affiche moins que le client

EXPLAIN donne une durée plus courte que celle vue par le client.
À 150 000 lignes, le client mesure 1,21 à 1,34 s pour cinq des six exécutions du VALUES, contre 0,89 s dans EXPLAIN.

L’écart d’environ 0,4 s est déduit, non profilé : il correspondrait à l’analyse du texte, que le timeout compte, et à son transport, que le délai ne compte pas (« from the time a command arrives at the server »).
Pour unnest, l’écart est négligeable (0,78 s contre 0,765 s).

La documentation d’EXPLAIN ne précise pas si « Planning Time » inclut l’analyse.

Un billet de Tiger Data attribue aussi l’essentiel du gain d’unnest à la planification[7].

ℹ️ Il porte sur des INSERT et n’indique pas la version de PostgreSQL.

La limite de 65 535 paramètres liés

Le message Bind du protocole PostgreSQL code le nombre de paramètres sur 16 bits, soit 65 535 au maximum[8][9][10]. Avec neuf paramètres par ligne, un VALUES échoue à 7 282 lignes (65 538 paramètres), avec l’erreur number of parameters must be between 0 and 65535 (test sur un SELECT count(*) FROM (VALUES …)). unnest avec N tableaux n’utilise que N paramètres, quel que soit le nombre de lignes.

bun n’est pas concerné

bun n’utilise pas de paramètres liés. Il écrit les valeurs, échappées, dans le texte SQL côté client (db.format, puis ExecContext)[2].
Ses huit tableaux deviendraient donc huit littéraux.

À 150 000 lignes, la variante « bind » (paramètres liés, comme pgx) dure 781,4 ms et la variante « inline » (littéraux, comme bun) 782,5 ms.

Le gain vient de la forme de la requête, pas du protocole.

Conclusion

VALUES et unnest écrivent les mêmes lignes avec un temps d’exécution comparable (754 ms contre 763 ms à 150 000 lignes).

La différence porte sur la planification :

  • 465 455 kB et 139,1 ms pour VALUES,
  • 10 489 kB et 1,369 ms pour unnest.

Selon les ressources, ce coût fait tuer le processus serveur ou dépasser les 10 s.

Ressources

  1. PostgreSQL : Client Connection Defaults
  2. bun v1.3.0 : query_update.go (UpdateQuery.Bulk() construit la CTE _data), query_values.go (génération du VALUES et des casts) et db.go (db.format, ExecContext)
  3. PostgreSQL : Array Functions and Operators
  4. PostgreSQL : Table Expressions
  5. PostgreSQL : UPDATE
  6. PostgreSQL : EXPLAIN
  7. Tiger Data : Boosting Postgres INSERT Performance
  8. PostgreSQL : Message Formats
  9. PostgreSQL, branche REL_18_STABLE : libpq fe-exec.c et libpq-fe.h, serveur postgres.c
  10. pgx : pgconn/pgconn.go
  11. Dépôt d’exemple : postgres-write-in-batches