Bases de données Linux : PostgreSQL sur petit VPS — Le réglage qui empêche les OOM

Comment survient un OOM sur un petit VPS (et pourquoi Postgres est blâmé)

Sur un petit VPS, la « mémoire » n’est pas un unique réservoir. C’est un traité fragile entre le cache de pages du noyau, la
mémoire anonyme (tas/pile), les segments de mémoire partagée, les allocations par processus et ce que l’hyperviseur a décidé que
votre voisin ne mérite pas aujourd’hui. PostgreSQL ajoute ses propres catégories : shared buffers, mémoire de travail par backend,
workers en arrière-plan, mémoire de maintenance, et des extensions qui n’ont aucun intérêt pour votre budget.

Quand un OOM arrive, PostgreSQL devient souvent la vedette parce que c’est un grand processus de longue vie avec beaucoup d’enfants.
Mais la décision de tuer appartient au noyau, basé sur un score de mauvais comportement, les cgroups mémoire (si utilisés),
et ce qui est le plus simple à abattre sans faire tomber tout l’OS. Parfois il tue le backend le plus volumineux. Parfois il tue le
postmaster et vous avez une panne totale. Parfois il tue votre agent de monitoring, ce qui revient à tirer sur l’alarme incendie parce que le bip est gênant.

Le schéma le plus courant sur des petites machines n’est pas « shared_buffers trop grand ». C’est « concurrence non bornée plus
paramètres mémoire par requête qui se multiplient ». Une seule instruction peut allouer work_mem plusieurs fois (une par nœud de tri/hash),
et de nombreux backends peuvent le faire simultanément. Ce n’est pas un bug. C’est des maths.

Le second schéma fréquent est la combustion lente : autovacuum n’arrive pas à suivre, les tables gonflent, les index gonflent, les requêtes
ralentissent, et des requêtes plus lentes gardent plus longtemps les connexions, ce qui augmente la concurrence et donc la pression mémoire.
Félicitations, vous avez inventé une boucle de rétroaction.

Blague n°1 : Si vous mettez max_connections à 2000 sur un VPS 1 Go, vous n’avez pas un serveur de base de données — vous avez une loterie sur le thème mémoire.

Faits intéressants et contexte historique (ce qui mord encore)

• Fait 1 : L’architecture multi-processus de PostgreSQL (un processus OS par connexion) remonte à des décisions prises à une époque où les threads étaient moins portables et moins prévisibles sur les variantes Unix.
• Fait 2 : shared_buffers était autrefois recommandé assez élevé par défaut, mais le cache de pages Linux et le comportement IO du noyau ont déplacé la bonne pratique vers « buffers modérés, laissez le cache OS travailler ».
• Fait 3 : work_mem est mal compris depuis des décennies car il est par opération, pas par requête. Une requête peut l’utiliser plusieurs fois par backend.
• Fait 4 : PostgreSQL a log_temp_files depuis longtemps, mais beaucoup d’équipes ne l’activent pas — elles ne découvrent donc jamais que leur « mystère » OOM était un tri qui aurait dû déverser sur disque.
• Fait 5 : L’autovacuum a été introduit pour réduire la pénibilité du vacuum manuel, mais son agressivité par défaut est volontairement conservatrice pour éviter des surprises — sur un petit VPS il nécessite souvent de l’aide.
• Fait 6 : Le killer OOM du noyau existe pour garder le système en vie, pas votre service. Ce n’est pas un algorithme d’équité ; c’est une opération de triage.
• Fait 7 : Le comptage mémoire de PostgreSQL n’est pas centralisé parce qu’il est réparti entre processus et contextes ; c’est pourquoi « l’utilisation mémoire de Postgres » est toujours une estimation, jamais un compteur unique.
• Fait 8 : Le pool de connexions (transaction pooling en particulier) est devenu courant pour Postgres en partie parce que « un processus par connexion » est coûteux opérationnellement sur de petites machines.

Le modèle mental : où PostgreSQL dépense réellement la mémoire

1) Mémoire partagée : shared_buffers (et alliés)

shared_buffers est le cache géré par Postgres des pages de tables et d’index. Il vit en mémoire partagée et apparaît dans l’empreinte principale du postmaster, mais il est utilisé par tous les backends. Sur un petit VPS, vous voulez qu’il soit assez grand pour éviter un churn constant et assez petit pour laisser de la place au reste (y compris le cache de pages de l’OS).

Règle pratique : sur un VPS 1–4 Go, un shared_buffers entre 128 MB et 1 GB est souvent raisonnable. Si vous augmentez, ayez une raison et des mesures. Une petite machine a toujours besoin de mémoire pour :

• les frais généraux par connexion (stacks, contextes mémoire, appels système)
• sorts/hashes (work_mem)
• maintenance (maintenance_work_mem)
• WAL buffers, processus en arrière-plan et mémoire d’extensions
• le cache de pages du noyau (votre accélérateur IO bon marché)

2) Mémoire par backend : le multiplicateur silencieux

Chaque connexion client est un processus backend. Chaque backend a une utilisation mémoire de base (quelques Mo à des dizaines de Mo,
selon les paramètres, extensions, locale et forme des requêtes). Ensuite les requêtes ajoutent de la mémoire par blocs, souvent dans des contextes mémoire liés aux opérateurs.

Les paramètres dangereux sont ceux qui paraissent inoffensifs isolément :

• work_mem (tris, hashes)
• temp_buffers (tables temporaires par session)
• max_parallel_workers_per_gather (les requêtes parallèles multiplient la consommation)

Un petit VPS n’a pas besoin d’ingéniosité. Il a besoin de prévisibilité. Votre objectif est de limiter le pire cas mémoire, pas de gagner un benchmark.

3) Mémoire de maintenance : vacuum, create index, et la « reconstruction surprise du week-end »

maintenance_work_mem est utilisé par VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY, et consorts.
Sur les petites machines, vous pouvez le garder modeste (64–256 MB) et ça suffit. Le piège est de lancer plusieurs tâches de maintenance
en parallèle : chacune peut consommer jusqu’à cette limite. Les workers d’autovacuum peuvent le faire simultanément.

4) Le cache de pages de l’OS : pas optionnel, pas « gaspillé »

Linux utilisera la mémoire libre comme cache. C’est une bonne chose. Postgres lit des données, le noyau les met en cache, les lectures suivantes sont plus rapides.
Si vous affamez le cache de pages en gonflant les paramètres de Postgres, vous forcerez des lectures disque et augmenterez la latence,
ce qui rallonge les requêtes, ce qui augmente la concurrence, ce qui augmente la pression mémoire. Voilà la combustion lente menant à l’OOM.

5) Swap : sortie d’urgence, pas seconde pièce à vivre

Sur un petit VPS, un peu de swap est souvent mieux que rien. Il donne au noyau un endroit pour placer des pages froides au lieu de
tuer immédiatement votre base de données lors d’un pic transitoire. Mais si vous laissez le système thrash sur le swap sous charge,
vous ne « survivez » pas, vous « expirez lentement ».

Mode d’emploi diagnostic rapide : premières vérifications

C’est l’ordre qui gagne les incidents. Pas toujours. Mais souvent.

First: confirm it’s actually OOM (and who died)

• Vérifiez les logs du noyau pour des messages du killer OOM.
• Vérifiez si le postmaster a redémarré (logs de crash recovery de Postgres).
• Vérifiez si systemd/cgroup a provoqué un kill (ressemble souvent à un OOM mais ne l’est pas).

Second: find the multiplier (connections, parallelism, work_mem)

• Combien de connexions actives au pic ?
• Y a-t-il eu un changement soudain de max_connections, du pooler de connexions ou un déploiement applicatif ?
• Des requêtes effectuent-elles d’énormes tris/hashes ou écrivent des fichiers temporaires ?
• Requête parallèle activée sur une petite machine ?

Third: check for the slow-burn loop (bloat, autovacuum lag, IO wait)

• L’autovacuum tourne-t-il ? Prend-il du retard ?
• Signes de bloat table/index (taille qui augmente, scans lents) ?
• Latence disque élevée ? WAL fsync lent ? Ça transforme des pics en pression soutenue.

Fourth: validate OS-level basics (swap, overcommit, vm settings)

• Le swap existe et est dimensionné convenablement.
• vm.swappiness n’est pas réglé sur une valeur dramatique sans raison.
• L’overcommit mémoire ne vous trompe pas en faisant croire que les allocations sont « gratuites ».

Idée paraphrasée (attribuée) : L’espoir n’est pas une stratégie. — Général H. Norman Schwarzkopf, souvent cité en contexte opérationnel (idée paraphrasée)

Tâches pratiques : commandes, sorties et décisions (12+)

Voici les vérifications que je lance réellement sur un petit VPS quand Postgres est accusé de meurtre. Chaque tâche inclut :
commande → sortie d’exemple → ce que ça signifie → décision à prendre.

Task 1: Confirm OOM killer activity

cr0x@server:~$ journalctl -k -g -i 'out of memory|oom-killer|Killed process' -n 50
Jan 12 03:17:21 vps kernel: Out of memory: Killed process 24113 (postgres) total-vm:612844kB, anon-rss:348920kB, file-rss:1200kB, shmem-rss:0kB, UID:113 pgtables:1256kB oom_score_adj:0
Jan 12 03:17:21 vps kernel: oom_reaper: reaped process 24113 (postgres), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

Signification : Le noyau a tué un backend postgres spécifique. C’est un vrai OOM, pas « Postgres a planté ».

Décision : Passez immédiatement aux vérifications de concurrence et de mémoire par backend. Un backend est devenu gros.

Task 2: Check if the postmaster restarted (crash recovery)

cr0x@server:~$ sudo journalctl -u postgresql -n 80
Jan 12 03:17:22 vps postgresql[1023]: LOG:  database system was interrupted; last known up at 2026-01-12 03:12:05 UTC
Jan 12 03:17:22 vps postgresql[1023]: LOG:  database system was not properly shut down; automatic recovery in progress
Jan 12 03:17:23 vps postgresql[1023]: LOG:  redo starts at 0/5A1C2B0
Jan 12 03:17:24 vps postgresql[1023]: LOG:  database system is ready to accept connections

Signification : Postgres lui‑même est tombé brutalement (postmaster mort ou SIGKILL). La récupération s’est lancée.

Décision : Traitez cela comme une panne : vérifiez les taux d’erreur client, le temps de récupération WAL, et considérez le comportement OOM de systemd.

Task 3: See memory and swap status right now

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           1.9Gi       1.6Gi        74Mi        28Mi       264Mi       152Mi
Swap:          1.0Gi       612Mi       412Mi

Signification : La RAM est tendue ; le swap est utilisé. « Available » est ce qui compte pour de nouvelles allocations.

Décision : Si le swap augmente sous charge avec des pics de latence, vous êtes en thrash ; limitez la concurrence et les multiplicateurs mémoire.

Task 4: Identify top memory consumers (RSS) quickly

cr0x@server:~$ ps -eo pid,ppid,user,comm,rss,etime --sort=-rss | head -n 15
  PID  PPID USER     COMMAND   RSS     ELAPSED
24113  1023 postgres postgres  348920  00:03:12
24102  1023 postgres postgres  112540  00:03:11
1023      1 postgres postgres   87420  05:42:19
24098  1023 postgres postgres   80112  00:03:10
1780      1 root     node       62310  02:11:03

Signification : Un backend est énorme. C’est généralement un gros tri/hash, un plan parallèle, ou une extension.

Décision : Mappez ce PID à la requête et à l’utilisateur ; optimisez la requête ou réduisez work_mem/max_parallel_workers_per_gather.

Task 5: Map a backend PID to its query

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS age, left(query,120) AS q FROM pg_stat_activity WHERE pid=24113;"
  pid  | usename | state  | wait_event_type | wait_event |   age    | q
-------+---------+--------+-----------------+------------+----------+------------------------------------------------------------
 24113 | app     | active |                 |            | 00:03:09 | SELECT ... ORDER BY created_at DESC LIMIT 200000;
(1 row)

Signification : Il exécute un order-by volumineux avec une très grande limite. Invitation à la mémoire et aux fichiers temporaires.

Décision : Corrigez la requête (index, limites plus petites, pagination par clé) et définissez statement_timeout comme ceinture de sécurité.

Task 6: See connection counts and states

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;"
   state   | count
-----------+-------
 idle      | 82
 active    | 14
 idle in transaction | 7
(3 rows)

Signification : Trop de connexions idle sur un petit VPS, c’est la mort par mille petites coupures.

Décision : Ajoutez un pooler (pgBouncer), réduisez max_connections, et tuez les sessions « idle in transaction ».

Task 7: Check max_connections and current settings

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SHOW max_connections; SHOW shared_buffers; SHOW work_mem; SHOW maintenance_work_mem; SHOW effective_cache_size;"
 max_connections
-----------------
 300
(1 row)

 shared_buffers
---------------
 512MB
(1 row)

 work_mem
---------
 64MB
(1 row)

 maintenance_work_mem
----------------------
 512MB
(1 row)

 effective_cache_size
----------------------
 1536MB
(1 row)

Signification : Sur une machine 2 GB, max_connections=300 et work_mem=64MB est une recette OOM si la concurrence augmente.

Décision : Réduisez max_connections (pooler), réduisez work_mem (souvent 4–16 MB), et redimensionnez la mémoire de maintenance.

Task 8: Find whether sorts/hash operations are spilling to disk (temp files)

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SHOW log_temp_files;"
 log_temp_files
----------------
 -1
(1 row)

Signification : Vous êtes aveugle à l’usage des fichiers temporaires dans les logs.

Décision : Mettez log_temp_files = 0 (tout consigner) temporairement pendant le diagnostic, ou un seuil comme 16MB en steady-state.

Task 9: Check current temp file usage per database

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC;"
  datname  | temp_files | temp_bytes
----------+------------+------------
 appdb    |      18412 | 37 GB
 postgres |          3 | 12 MB
(2 rows)

Signification : Les requêtes déversent beaucoup sur temporaire. C’est parfois acceptable, mais sur petits disques ça devient pénible IO et prolonge la durée des requêtes.

Décision : Corrigez les pires requêtes et index ; n’augmentez pas work_mem juste pour éviter les fichiers temporaires. C’est comme acheter un OOM.

Task 10: Check autovacuum status and dead tuples

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"
    relname     | n_live_tup | n_dead_tup |        last_autovacuum        |       last_autoanalyze
---------------+------------+------------+-------------------------------+------------------------------
 events        |   48210321 |   9123401  | 2026-01-10 02:11:44+00        | 2026-01-10 02:30:02+00
 sessions      |    1192031 |    412120  |                               | 2026-01-08 11:02:19+00
(2 rows)

Signification : Pression de bloat. Une table accumule des tuples morts ; une autre n’a pas autovacuumé récemment.

Décision : Ajustez les seuils autovacuum pour les tables chaudes ; assurez-vous que le vacuum n’est pas bloqué ; envisagez un VACUUM manuel en période creuse.

Task 11: Check if vacuum is blocked by long transactions

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT pid, usename, state, now()-xact_start AS xact_age, left(query,120) AS q FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start ASC LIMIT 5;"
  pid  | usename | state |  xact_age  | q
-------+---------+-------+------------+------------------------------------------------------------
 23301 | app     | idle in transaction | 02:14:09 | UPDATE sessions SET ...;
(1 row)

Signification : « Idle in transaction » pendant des heures empêche le nettoyage du vacuum et crée du bloat.

Décision : Tuez la session, corrigez la gestion des transactions de l’app, ajoutez idle_in_transaction_session_timeout.

Task 12: See IO pressure and swap activity under load

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0 621120  74200  18324 232140    0   18   120   340  450  780 22  8 55 15  0
 3  1 623080  61840  18324 230820    0  120   200  1100  520  910 28 11 39 22  0
 4  1 624904  51220  18324 229110    0  240   180  2500  600 1020 34 12 26 28  0

Signification : Swap-out (so) en hausse plus augmentation de IO wait (wa) suggèrent du thrash et de la contention disque.

Décision : Réduisez la concurrence, baissez la mémoire par requête, et assurez-vous que le stockage n’est pas surchargé (WAL sur disque lent aggrave tout).

Task 13: Check per-cgroup memory limits (systemd) if applicable

cr0x@server:~$ systemctl show postgresql -p MemoryMax -p MemoryHigh -p OOMPolicy -p ManagedOOMMemoryPressure
MemoryMax=infinity
MemoryHigh=infinity
OOMPolicy=stop
ManagedOOMMemoryPressure=auto

Signification : Pas de MemoryMax explicite ici ; systemd peut quand même réagir via ManagedOOM selon les valeurs par défaut de la distro.

Décision : Si vous définissez MemoryMax, faites-le en connaissance de cause et laissez une marge pour la mémoire partagée et les besoins du noyau ; sinon vous obtiendrez des « kills mystérieux ».

Task 14: Inspect huge pages / shared memory constraints

cr0x@server:~$ grep -E 'HugePages|Shmem' /proc/meminfo | head
Shmem:             28672 kB
ShmemHugePages:        0 kB
ShmemPmdMapped:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0

Signification : Pas de huge pages configurées ; la mémoire partagée est faible pour le moment. C’est typique pour un petit VPS.

Décision : Ne poursuivez pas les huge pages comme premier correctif sur de petites machines. Corrigez d’abord la concurrence et les multiplicateurs mémoire.

Task 15: Identify worst query shapes (quick-and-dirty top time)

cr0x@server:~$ sudo -u postgres psql -d appdb -c "SELECT queryid, calls, mean_exec_time, rows, left(query,100) AS q FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
 queryid  | calls | mean_exec_time |  rows  | q
----------+-------+----------------+--------+----------------------------------------------------
 91344122 |   112 |       8421.113 | 200000 | SELECT ... ORDER BY created_at DESC LIMIT $1
 11822001 |  9011 |        312.882 |     20 | SELECT ... FROM sessions WHERE user_id = $1
(2 rows)

Signification : Une requête est lente et renvoie beaucoup de lignes ; elle produit probablement de gros tris ou des hashes larges.

Décision : EXPLAINez-la, indexez-la et limitez-la. Sur un petit VPS, une mauvaise requête peut dicter toute l’histoire mémoire.

Task 16: Verify current kernel swappiness and overcommit policy

cr0x@server:~$ sysctl vm.swappiness vm.overcommit_memory vm.overcommit_ratio
vm.swappiness = 10
vm.overcommit_memory = 0
vm.overcommit_ratio = 50

Signification : Swappiness conservateur, heuristique d’overcommit par défaut. Pas mauvais en soi.

Décision : Évitez les valeurs extrêmes sauf si vous comprenez le mode de défaillance. Sur petit VPS, la prévisibilité bat le folklore.

Le réglage qui empêche les OOM (quoi régler, quoi éviter)

Commencez par une vérité dure : votre pire cas doit tenir

Sur de petites machines, le tuning n’est pas « le rendre rapide ». C’est « le rendre incapable de mourir ». La vitesse vient d’éviter les comportements pathologiques : trop de backends, trop de mémoire par backend, et des transactions longues qui convertissent des pics en charge soutenue.

Pensez en budgets :

• Budget A : mémoire partagée (buffers et overhead)
• Budget B : baseline mémoire par connexion × connexions actives max
• Budget C : mémoire de travail par requête × opérateurs lourds concurrents
• Budget D : workers de maintenance × mémoire de maintenance
• Budget E : besoins OS + cache de pages + « inconnues » (extensions, libc, TLS, monitoring)

1) Réglez le véritable coupable : le nombre de connexions

Si vous êtes sur un VPS 1–2 GB et que vous ouvrez des connexions directes à Postgres depuis une application web avec trafic en rafales,
vous jouez. Chaque connexion « idle » est toujours un processus avec un coût mémoire, et chaque rafale crée un choc mémoire.

Que faire :

• Utilisez pgBouncer (transaction pooling sauf si vous avez besoin de fonctions de session).
• Réglez max_connections sur ce que votre RAM peut supporter, pas sur ce que votre app peut ouvrir.
• Privilégiez moins de connexions avec des requêtes rapides plutôt que beaucoup de connexions avec des requêtes lentes.

2) Réglez work_mem comme si vous le payiez (vous le payez)

Sur petit VPS, un work_mem global de 4–16 MB est un bon point de départ. Si cela vous semble petit, tant mieux : cela force les opérations volumineuses à déverser sur disque plutôt que d’assassiner la RAM. Le déversement est plus lent, mais mourir est plus lent encore.

Le meilleur schéma est par défaut bas, plus élevé par rôle ou par session pour les jobs contrôlés :
le rôle analytics obtient plus, le rôle OLTP obtient moins.

Aussi : les requêtes parallèles augmentent la consommation mémoire. Chaque worker peut allouer work_mem. Sur un petit VPS, limitez fortement le parallélisme ou désactivez-le s’il est imprévisible pour votre charge.

3) Gardez shared_buffers modéré

Ne mettez pas shared_buffers à 70 % de la RAM parce qu’un blog a dit « le cache est roi ». Linux met déjà en cache. Vous devez laisser de la place pour l’OS et pour les pics par backend.

Points de départ pratiques :

• 1 GB RAM : 128–256 MB shared_buffers
• 2 GB RAM : 256–512 MB shared_buffers
• 4 GB RAM : 512 MB–1 GB shared_buffers

Puis mesurez : ratio de cache, IO wait, et latence sous charge. Si vous augmentez les buffers et que les performances n’améliorent pas, vous avez juste volé de la mémoire là où elle était nécessaire.

4) Autovacuum : réglez-le pour que le bloat ne devienne pas pression mémoire

L’autovacuum sur un petit VPS doit être réglé pour suivre, pas pour être poli. Les valeurs par défaut visent à être sûres sur des machines très différentes. Votre petit VPS n’est pas très différent — il est spécifiquement contraint.

Ce qui aide :

• Abaissez autovacuum_vacuum_scale_factor sur les tables chaudes ; comptez moins sur les seuils en pourcentage et plus sur des seuils fixes.
• Augmentez autovacuum_max_workers seulement si l’IO peut le supporter ; sinon vous ajoutez de la contention.
• Augmentez modestement autovacuum_work_mem (ou utilisez maintenance_work_mem) mais ne laissez pas plusieurs workers partir en vrille.
• Surveillez les « idle in transaction » et les transactions longues qui bloquent le nettoyage.

5) Mettez des garde-fous sur le comportement des transactions et des instructions

La fonctionnalité de fiabilité la moins chère dans Postgres est un timeout. Les timeouts convertissent « manger lentement votre serveur » en « échec rapide avec logs ». Vous pouvez déboguer un échec. Vous ne pouvez pas déboguer un OOM après que le processus a disparu.

• statement_timeout : stoppe les requêtes incontrôlées.
• idle_in_transaction_session_timeout : stoppe les apps qui ouvrent une transaction et disparaissent.
• lock_timeout : stoppe les piles de requêtes causées par des attentes de lock.

6) Swap : ayez-en, mais ne comptez pas dessus

Un petit fichier swap (équivalent à la RAM ou la moitié de la RAM, selon le disque et la charge) peut réduire la « mort instantanée » durant un pic.
Ce n’est pas une licence pour ignorer le tuning mémoire. Surveillez les taux swap-in/out ; si le swap churn sous charge, vous êtes déjà en zone dangereuse.

7) Logging qui empêche la superstition

Pour éviter les jeux de blâme futurs, consignez suffisamment pour relier symptômes et causes :

• Activez le log des requêtes lentes (avec log_min_duration_statement).
• Activez la journalisation des fichiers temporaires (log_temp_files seuil).
• Utilisez pg_stat_statements pour identifier les récidivistes.

Blague n°2 : Activer les logs ne réparera pas votre incident, mais améliorera radicalement votre capacité à avoir raison lors du prochain incident.

Trois mini-histoires du monde corporate (comment ça foire en vrai)

Mini-histoire 1 : L’incident causé par une mauvaise hypothèse (« work_mem c’est par requête, non ? »)

Une équipe SaaS de taille moyenne faisait tourner Postgres sur un VPS 2 GB pour un dashboard analytics « non critique ». Cela a commencé comme un projet annexe et est devenu « tout le monde l’utilise chaque matin ». Ça se passe toujours comme ça.

Un développeur a augmenté work_mem à 128 MB pour accélérer un ensemble de requêtes de dashboard. L’hypothèse était qu’une requête n’obtient qu’une allocation de work_mem. Ils ont aussi laissé max_connections généreux parce que « les connexions sont bon marché ». Le dashboard était plus rapide en test. En production, il est resté plus rapide jusqu’à la réunion générale du lundi.

Au pic, des dizaines de requêtes concurrentes de dashboard ont tourné avec plusieurs nœuds de tri/hash par requête. Certaines sont devenues parallèles. La mémoire a monté vite. Le swap a été touché. La latence a explosé. Les clients ont réessayé. Les comptes de connexion ont augmenté. Le killer OOM du noyau a commencé à éliminer les backends les plus gras, puis a pris le postmaster pour de bon.

La correction n’a pas été héroïque : réduire le work_mem par défaut, limiter les workers parallèles, ajouter pgBouncer, et définir un work_mem plus élevé seulement pour un rôle « analytics » avec concurrence contrôlée. Ils ont aussi ajouté un statement_timeout, transformant « fondre la machine » en « le dashboard expire », un mode d’échec bien plus sain.

Mini-histoire 2 : L’optimisation qui s’est retournée contre eux (« Mettons shared_buffers au max et désactivons le swap »)

Une autre société a déplacé un outil support client sur un VPS 1 GB. L’outil avait un trafic modeste mais beaucoup de recherche full-text et quelques extensions. Un ingénieur, voulant bien faire, a appliqué un snippet de tuning :
shared_buffers=768MB, effective_cache_size=1GB, et « le swap c’est mal, désactivons-le ».

La première semaine s’est bien passée. Puis ils ont installé un agent de sécurité qui a ajouté un peu de consommation mémoire. Ensuite ils ont activé un job de fond qui exécutait un rapport nocturne avec quelques tris lourds. Avec le swap désactivé, les pics transitoires n’avaient nulle part où aller. Le killer OOM du noyau a déclenché plus tôt et plus violemment.

Le rapport d’incident avait une odeur familière : les graphiques montraient la mémoire monter jusqu’à chuter en falaise. Le tuning de l’ingénieur avait réduit les lectures disque, mais il avait aussi éliminé la marge du cache de pages OS et supprimé le clapet de pression qu’était le swap. Le système est devenu fragile.

Ils ont restauré des buffers modérés, réactivé un petit fichier swap, et réduit le work_mem par défaut. Les performances se sont améliorées parce que le système a arrêté de thrasher et de redémarrer. Il s’avère que « être en ligne » est un état très rapide.

Mini-histoire 3 : La pratique ennuyeuse qui a sauvé la mise (« timeouts, pooler et un seul dashboard »)

Un service financier tournait sur un VPS 4 GB avec Postgres. Rien d’extraordinaire. Ils avaient deux habitudes qui paraissaient douloureusement conservatrices : timeouts stricts et un pooler avec une taille de pool serveur volontairement basse.

Un chef produit a demandé une nouvelle fonctionnalité « exporter tout ». L’équipe a implémenté ça comme un job de fond et un endpoint utilisateur. L’endpoint avait un statement_timeout strict. Le job utilisait un rôle dédié avec un work_mem plus élevé et une file à faible concurrence.

Le jour du déploiement, un client a tenté d’exporter un jeu de données massif pendant le pic. L’endpoint a expiré et a renvoyé une erreur agaçante mais honnête. Le job de fond s’est mis en file et a tourné plus tard avec des ressources contrôlées. La base est restée stable.

Le postmortem a été bref parce qu’il n’y avait pas grand-chose à dire. La pratique ennuyeuse — compartimentation des ressources et timeouts — a empêché la requête d’un client de devenir la panne de tout le monde. Personne n’a été promu pour cela, mais personne n’a non plus été réveillé.

Erreurs courantes : symptômes → cause racine → correctif

1) Symptom: random Postgres disconnects, “server closed the connection unexpectedly”

Cause racine : Le killer OOM du noyau tue des backends ou le postmaster ; parfois systemd tue en raison de pression mémoire.

Correctif : Confirmez via les logs noyau ; diminuez la concurrence, réduisez work_mem, ajoutez pgBouncer, et assurez-vous que le swap existe.

2) Symptom: swap usage grows, latency spikes, then everything times out

Cause racine : Pression mémoire provoquant du thrash swap ; typiquement trop de connexions actives ou des requêtes lourdes concurrentes.

Correctif : Limitez les connexions actives, appliquez des timeouts, optimisez les pires requêtes, envisagez de réduire shared_buffers pour redonner du souffle au cache OS.

3) Symptom: “idle in transaction” connections pile up

Cause racine : Fuite de transactions dans l’application (oublie de commit/rollback), maintient des verrous, bloque le vacuum.

Correctif : Définissez idle_in_transaction_session_timeout ; corrigez la gestion des connexions/transactions de l’app ; surveillez et tuez les coupables.

4) Symptom: autovacuum always running, queries slowly get worse over days

Cause racine : Vacuum ne suit pas ; le bloat augmente l’IO et le temps des requêtes ; la concurrence augmente ; la pression mémoire se maintient.

Correctif : Ajustez l’autovacuum par table chaude ; éliminez les bloqueurs (transactions longues) ; planifiez des VACUUM manuels en fenêtres calmes.

5) Symptom: temp disk fills, then Postgres errors about no space

Cause racine : Gros tris/hashes déversant sur des fichiers temporaires ; disque insuffisant ; parfois une seule requête de rapport.

Correctif : Corrigez les requêtes/indexes ; limitez la taille des résultats ; activez la journalisation des fichiers temporaires ; assurez un disque adéquat ou déplacez le répertoire temp sur un volume plus rapide/plus grand si possible.

6) Symptom: performance got worse after “tuning” shared_buffers upward

Cause racine : Cache de pages OS et mémoire par backend affamés ; IO wait augmenté et requêtes plus longues.

Correctif : Réduisez shared_buffers à une valeur modérée ; mesurez ; concentrez-vous sur les plans de requête et la gestion des connexions.

7) Symptom: a single query sometimes triggers OOM

Cause racine : Requête parallèle plus work_mem élevé ; plan de requête inclut plusieurs nœuds gourmands ; parfois lignes larges.

Correctif : Baissez work_mem ; limitez les workers parallèles ; réécrivez la requête ; ajoutez des index ; définissez un statement timeout.

Checklists / plan pas à pas (baseline sûre pour petit VPS)

Step-by-step: stabilize first, then optimize

1. Prouver l’OOM : vérifiez les logs du noyau pour les kills ; enregistrez horodatages et PIDs.
2. Comptez les connexions : mesurez le pic actif et total ; identifiez « idle » vs « idle in transaction ».
3. Installez ou configurez pgBouncer : visez un petit pool serveur stable.
4. Réduisez max_connections : forcez le pooler à faire son travail.
5. Définissez des défauts conservateurs : work_mem bas, maintenance_work_mem modeste, shared_buffers modéré.
6. Limitez le parallélisme : restreignez max_parallel_workers_per_gather (ou désactivez si besoin).
7. Ajoutez des timeouts : statement + idle-in-transaction + lock timeout pour la sanity.
8. Activez les voyants d’observabilité : slow query log, temp file logging, pg_stat_statements.
9. Corrigez les 3 pires requêtes : indexez, réécrivez ou limitez. Sur petites machines, pas besoin d’une top 30.
10. Tunez l’autovacuum pour les tables chaudes : réduisez les scale factors ; assurez-vous que vacuum n’est pas bloqué.
11. Validez le swap : assurez-vous qu’il existe ; évitez de le désactiver ; surveillez swap-in/out.
12. Testez la charge avec concurrence : vérifiez que le pire cas tient en mémoire.

Baseline config ideas (not universal, but safe-ish)

Ce sont des points de départ volontairement conservateurs pour petit VPS. Ajustez selon les mesures, pas les impressions.

• 1 GB RAM : shared_buffers 128–256MB, work_mem 4–8MB, maintenance_work_mem 64–128MB, max_connections 50–100 (préférez un pooler).
• 2 GB RAM : shared_buffers 256–512MB, work_mem 4–16MB, maintenance_work_mem 128–256MB, max_connections 100–150 (préférez un pooler).
• 4 GB RAM : shared_buffers 512MB–1GB, work_mem 8–16MB, maintenance_work_mem 256MB, max_connections 150–200 (préférez un pooler).

Operational checklist: after any change

• Revérifiez le comportement des connexions pendant le pic.
• Revérifiez la génération de fichiers temporaires et les requêtes lentes.
• Revérifiez l’activité du swap et le IO wait.
• Revérifiez la progression de l’autovacuum et les tuples morts.
• Gardez un plan de rollback : les changements de config sont peu coûteux ; le temps de récupération ne l’est pas.

FAQ

1) Is shared_buffers the main cause of OOM on small VPS?

Parfois, mais généralement non. Le classique OOM sur petit VPS est la mémoire par backend multipliée par la concurrence : trop de connexions plus un work_mem généreux, souvent avec requêtes parallèles. Des buffers modérés tuent rarement un système à eux seuls ; ce sont les pics non bornés qui le font.

2) What’s a safe work_mem on a 1–2 GB box?

Commencez à 4–8MB pour des charges OLTP générales. Augmentez par rôle pour des jobs contrôlés (comme un worker de reporting unique), pas globalement. Rappelez-vous : une requête peut allouer work_mem plusieurs fois.

3) Should I disable swap for databases?

Sur un petit VPS, généralement non. Un petit swap peut prévenir une OOM brutale lors de pics transitoires. L’objectif réel est d’éviter le swap permanent ; surveillez swap-in/out et IO wait. Désactiver le swap transforme souvent « lent » en « mort ».

4) Why do I see lots of “idle” connections? Aren’t idle connections harmless?

Les connexions idle coûtent toujours de la mémoire et des ressources de processus. Sur de petites machines, ce coût compte. Les connexions idle aggravent aussi les rafales car vous avez déjà dépensé votre budget de base. Utilisez un pooler et gardez les connexions serveur petites et stables.

5) How do I know if OOM is caused by a specific query?

Corrélez : PID du kill noyau → pg_stat_activity (si encore présent) → logs applicatifs → slow query logs.
Ajoutez pg_stat_statements et la journalisation des fichiers temporaires. Si une forme de requête domine temp_bytes ou mean_exec_time, c’est un fort suspect.

6) Should I increase effective_cache_size to fix performance?

effective_cache_size n’alloue pas de mémoire ; c’est un indice pour l’optimiseur. Réglez-le sur une estimation raisonnable de la mémoire disponible pour le caching (cache OS + shared buffers). Ne le traitez pas comme un levier de performance pour éviter l’OOM.

7) Does autovacuum tuning help with OOM?

Indirectement, oui — souvent beaucoup. Quand l’autovacuum prend du retard, le bloat augmente, les requêtes ralentissent, la concurrence monte, et la pression mémoire se maintient. Garder le vacuum sain prévient la voie « combustion lente » vers l’OOM.

8) Can I solve this just by upgrading the VPS?

Plus de RAM achète de la marge, pas de la correction. Si la charge a une concurrence non bornée ou des requêtes runaway, elle remplira toute mémoire que vous lui donnez — simplement plus tard et plus cher. Tondez d’abord, scalez ensuite avec des preuves.

9) Are parallel queries bad on small VPS?

Elles ne sont pas mauvaises en soi. Elles sont opérationnellement imprévisibles. Le parallélisme peut multiplier la consommation mémoire et la contention CPU. Sur petit VPS, limitez-le fortement. Si vous avez besoin d’analytique parallèle lourde, vous avez probablement besoin d’une autre machine ou d’une autre architecture.

10) What’s the fastest win if I’m already paging weekly?

Ajoutez un pooler et baissez max_connections. Puis réduisez globalement work_mem à quelque chose de conservateur. Ces deux changements seuls éliminent souvent les événements OOM car ils contrôlent le multiplicateur.

Prochaines étapes à faire aujourd’hui

1. Exécutez le playbook diagnostic rapide : confirmez l’OOM, identifiez le multiplicateur, et vérifiez la boucle lente bloat/IO.
2. Posez un cap dur sur la concurrence : pgBouncer + baisse de max_connections.
3. Réinitialisez work_mem à une valeur conservatrice : puis augmentez-le sélectivement par rôle pour les jobs contrôlés.
4. Ajoutez des timeouts : statement + idle-in-transaction + lock timeout. Rendez vos échecs rapides et débogables.
5. Activez la visibilité sur les fichiers temporaires et les requêtes lentes : afin que le prochain incident ait des preuves, pas du folklore.
6. Tunez l’autovacuum pour les tables chaudes : la prévention du bloat est une protection mémoire qui porte un chapeau IO.

PostgreSQL sur petit VPS n’est pas fragile par nature. Il devient fragile quand vous laissez l’utilisation mémoire être non bornée. Bornez-la, mesurez-la, et vous dormirez comme quelqu’un qui ne considère pas le killer OOM comme un répartiteur de charge.