Revoir les réglages mémoire (shared_buffers, work_mem, maintenance_work_mem)

Commande

SELECT name,
       current_setting(name) AS value,
       source,
       context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'autovacuum_work_mem', 'max_connections',
               'max_parallel_workers_per_gather', 'huge_pages', 'temp_buffers', 'wal_buffers')
ORDER BY name;

-- Rough ceilings: work_mem x max_connections and the autovacuum memory ceiling
SELECT pg_size_pretty(pg_size_bytes(current_setting('work_mem'))
                      * current_setting('max_connections')::int) AS work_mem_x_max_connections,
       pg_size_pretty(pg_size_bytes(current_setting('maintenance_work_mem'))
                      * current_setting('autovacuum_max_workers')::int) AS autovacuum_mem_ceiling;

-- Trace of operations that did not fit in work_mem: temp files per database
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

Ce que fait la commande

Affiche les paramètres liés à la mémoire avec leur valeur, leur source (défaut/fichier de configuration) et leur contexte de modification (postmaster = redémarrage) ; des plafonds mémoire approximatifs pour work_mem et autovacuum ; et le volume de fichiers temporaires par base.

Quand l'utiliser

Lors de la mise en place d'un nouveau serveur, après un ajout de RAM, après un événement OOM killer, ou lorsqu'une forte utilisation de fichiers temporaires est constatée.

Attention

work_mem n'est pas alloué par connexion mais par NŒUD de tri/hachage (multiplié par hash_mem_multiplier pour les hachages) et séparément pour chaque worker parallèle ; une requête complexe peut utiliser plusieurs multiples de work_mem, le produit calculé est donc un avertissement approximatif, pas une borne supérieure. Points de départ typiques : shared_buffers ~25 % de la RAM (redémarrage requis), effective_cache_size ~50-75 % (simple indication pour le planificateur, n'alloue rien), maintenance_work_mem 512MB-2GB. Avec autovacuum_work_mem = -1, chaque worker autovacuum peut utiliser maintenance_work_mem. Si temp_bytes est élevé, relevez work_mem par rôle ou par requête, pas globalement. Avec un shared_buffers important sous Linux, envisagez huge_pages.