Superviser et maintenir PostgreSQL : statistiques, requêtes lentes et VACUUM

Un PostgreSQL en production qui ralentit progressivement : tables qui gonflent, requêtes qui passent de 5 ms à 500 ms, connexions qui s’accumulent sans explication — c’est le scénario classique quand personne ne surveille. PostgreSQL embarque un système de statistiques complet qui permet de diagnostiquer chaque problème, mais encore faut-il savoir quoi regarder et quand agir.

Ce guide suit une démarche observer → identifier → expliquer → corriger. Chaque commande est testée sur un lab réel (PostgreSQL 18.3, Debian 12, base lab_admin avec 90 000 lignes de logs). Vous saurez lire les vues statistiques, interpréter un plan d’exécution, détecter le bloat et utiliser VACUUM, ANALYZE et REINDEX au bon moment.

Ce que vous allez apprendre

Diagnostiquer l’activité en cours avec pg_stat_activity (connexions, états, requêtes bloquées)
Identifier les requêtes coûteuses avec pg_stat_statements (top 10 des plus lentes)
Lire un plan EXPLAIN ANALYZE et comprendre Seq Scan, Index Scan, Hash Join
Détecter le bloat (dead tuples) et surveiller l’autovacuum
Maintenir les performances avec VACUUM, VACUUM FULL, ANALYZE et REINDEX
Observer les I/O par type de backend avec pg_stat_io

Dans quel contexte ?

Vous avez besoin de superviser et maintenir PostgreSQL dans ces situations :

Votre application ralentit progressivement et vous soupçonnez la base de données
Vous voulez mettre en place une supervision proactive avant que les problèmes n’apparaissent
L’autovacuum semble ne pas faire son travail : les tables grossissent, les requêtes ralentissent
Vous devez identifier les requêtes les plus coûteuses pour savoir où optimiser
Vous préparez un plan de maintenance pour une base en production (VACUUM, REINDEX, ANALYZE)
Vous debuggez un problème de verrous ou de connexions qui s’accumulent

Ce guide ne couvre pas…

Configuration Régler shared_buffers, work_mem, max_connections et les paramètres de checkpoint

Sauvegarde et restauration pg_dump, pg_basebackup, archivage WAL et PITR

Sécurisation pg_hba.conf, TLS, séparation de rôles

Prérequis

PostgreSQL installé et le service actif (voir le guide Installation)
Accès au rôle superuser (postgres)
Connaître les bases de psql (voir le guide Prise en main de psql)
Une base avec des données pour observer quelque chose (le lab utilise lab_admin avec 90 000 lignes)

La démarche : observer → identifier → expliquer → corriger

Diagnostiquer un problème de performance PostgreSQL suit toujours le même ordre :

Observer — que se passe-t-il en ce moment ? (pg_stat_activity, connexions, verrous)
Identifier — quelles requêtes consomment le plus ? (pg_stat_statements, top 10)
Expliquer — pourquoi cette requête est-elle lente ? (EXPLAIN ANALYZE, plan d’exécution)
Corriger — ajouter un index, réécrire la requête, ou maintenir (VACUUM, ANALYZE, REINDEX)

Ne sautez pas d’étape. Un VACUUM FULL lancé à l’aveugle sur une table de production verrouille tout en écriture. Un index ajouté sans avoir lu le plan peut aggraver la situation.

Observer l’activité du serveur

pg_stat_activity : qui fait quoi en ce moment

pg_stat_activity est la première vue à consulter. Elle montre toutes les connexions actives, leur état, la requête en cours et depuis combien de temps :

SELECT pid, usename, datname, state,
       LEFT(query, 60) AS query,
       backend_start::timestamp(0),
       state_change::timestamp(0)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

 pid  | usename  | datname  | state  |                            query                             |    backend_start    |    state_change
------+----------+----------+--------+--------------------------------------------------------------+---------------------+---------------------
 7765 | postgres | postgres | active | SELECT pid, usename, datname, state, LEFT(query, 60) AS quer | 2026-04-13 10:43:59 | 2026-04-13 10:43:59

Colonnes clés :

Colonne	Signification
`pid`	Identifiant du processus backend
`state`	État de la connexion (`active`, `idle`, `idle in transaction`)
`query`	Dernière requête exécutée (ou en cours)
`wait_event_type`	Type d’attente (Lock, IO, etc.) — `NULL` si pas en attente
`backend_start`	Quand la connexion a été ouverte
`state_change`	Quand le dernier changement d’état a eu lieu

Les différents types de backend

PostgreSQL exécute plusieurs processus en permanence. pg_stat_activity les montre tous :

SELECT backend_type, count(*)
FROM pg_stat_activity
GROUP BY backend_type
ORDER BY count DESC;

         backend_type         | count
------------------------------+-------
 io worker                    |     3
 walwriter                    |     1
 autovacuum launcher          |     1
 logical replication launcher |     1
 background writer            |     1
 archiver                     |     1
 client backend               |     1
 checkpointer                 |     1

Seuls les client backend correspondent aux connexions utilisateur. Les autres sont des processus système internes à PostgreSQL.

Connexions actives, idle, idle in transaction

Pour un aperçu rapide de la santé des connexions :

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

État	Signification	Action si trop nombreux
`active`	Exécute une requête	Normal, sauf si trop long
`idle`	Connecté, ne fait rien	Pool de connexions mal configuré
`idle in transaction`	Transaction ouverte, rien en cours	Dangereux — bloque l’autovacuum
`idle in transaction (aborted)`	Transaction en erreur, pas rollback	Très dangereux — fermer immédiatement

Ratio de connexions utilisées

Pour surveiller l’approche de la limite max_connections :

SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_conn,
       count(*) * 100 / current_setting('max_connections')::int AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

 total | max_conn | pct_used
-------+----------+----------
     1 |      100 |        1

Le filtre backend_type = 'client backend' est important : sans lui, pg_stat_activity compte aussi les processus internes (autovacuum, checkpointer, walwriter…) qui ne sont pas concernés par max_connections.

Repère de terrain : au-delà de 80 % de connexions clientes, envisagez un pooler de connexions (PgBouncer) ou augmentez max_connections.

Détecter les requêtes bloquées (wait_event)

Pour identifier les connexions en attente d’un verrou ou d’une ressource :

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       LEFT(query, 60) AS query,
       now() - state_change AS duration
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state != 'idle'
ORDER BY duration DESC NULLS LAST;

Si wait_event_type vaut Lock, la connexion attend qu’une autre relâche un verrou. Croisez avec pg_locks pour identifier le bloqueur :

SELECT l.pid, l.locktype, l.mode, l.granted,
       a.usename, LEFT(a.query, 50) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.backend_type = 'client backend'
LIMIT 5;

 pid  |  locktype  |      mode       | granted | usename  |                    query
------+------------+-----------------+---------+----------+----------------------------------------------
 8632 | relation   | AccessShareLock | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,...
 8632 | virtualxid | ExclusiveLock   | t       | postgres | SELECT l.pid, l.locktype, l.mode, l.granted,...

granted = f signifie que le verrou est en attente — c’est la connexion bloquée.

Tuer une requête : pg_cancel_backend, pg_terminate_backend

Deux options pour débloquer une situation :

Fonction	Action	Risque
`pg_cancel_backend(pid)`	Annule la requête en cours	La connexion reste ouverte
`pg_terminate_backend(pid)`	Termine la connexion entière	Risque de perte de transaction non commitée

-- Annuler proprement (essayez d'abord)
SELECT pg_cancel_backend(1234);

-- Si ça ne suffit pas, terminer la connexion
SELECT pg_terminate_backend(1234);

Identifier les requêtes coûteuses

pg_stat_statements : activer et interroger

pg_stat_statements est l’extension la plus importante pour le monitoring. Elle collecte des statistiques agrégées sur toutes les requêtes exécutées : nombre d’appels, temps moyen, temps total, lignes retournées.

Ajouter l’extension à shared_preload_libraries (redémarrage requis) :

Vérifiez d’abord la valeur actuelle pour ne pas écraser les modules déjà chargés :

SHOW shared_preload_libraries;

Puis ajoutez pg_stat_statements à la liste existante :

-- Si la valeur était vide :
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Si d'autres modules étaient déjà chargés (ex: auto_explain) :
-- ALTER SYSTEM SET shared_preload_libraries = 'auto_explain,pg_stat_statements';

sudo systemctl restart postgresql@18-main

Créer l’extension dans la base à surveiller :

CREATE EXTENSION pg_stat_statements;

 extname       | extversion
----------------+------------
 pg_stat_statements | 1.12

Les métriques qui comptent

Colonne	Signification	Usage
`calls`	Nombre total d’exécutions	Identifier les requêtes les plus fréquentes
`mean_exec_time`	Temps moyen par exécution (ms)	Trouver les requêtes unitairement lentes
`total_exec_time`	Temps total cumulé (ms)	Trouver celles qui consomment le plus de ressources
`rows`	Nombre total de lignes retournées	Détecter les requêtes qui retournent trop de données
`shared_blks_hit`	Blocs lus depuis le cache	Mesurer l’efficacité du cache
`shared_blks_read`	Blocs lus depuis le disque	Identifier les requêtes intensives en I/O

Top 10 des requêtes les plus coûteuses

La requête la plus utile au quotidien — copiez-la dans votre boîte à outils :

SELECT calls,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       round(total_exec_time::numeric, 1) AS total_ms,
       rows,
       LEFT(query, 80) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

 calls | mean_ms | total_ms | rows |                                      query
-------+---------+----------+------+----------------------------------------------------------------------------------
     1 |   0.248 |      0.2 |    0 | ANALYZE app.clients
     1 |   0.245 |      0.2 |    0 | ANALYZE app.commandes
     1 |   0.195 |      0.2 |    1 | DELETE FROM app.clients WHERE email = $1
     5 |   0.029 |      0.1 |   25 | SELECT c.nom, co.montant, co.statut FROM app.clients c JOIN app.commandes co ON
     1 |   0.139 |      0.1 |    1 | INSERT INTO app.clients (nom, email, ville) VALUES ($1, $2, $3)
     3 |   0.019 |      0.1 |    3 | SELECT count(*) FROM app.clients
     2 |   0.008 |      0.0 |   10 | SELECT ville, count(*) FROM app.clients GROUP BY ville
     2 |   0.007 |      0.0 |    2 | SELECT avg(montant) FROM app.commandes
     2 |   0.005 |      0.0 |    2 | SELECT * FROM app.clients WHERE ville = $1

Ce qu’il faut regarder en premier :

total_ms élevé : la requête qui consomme le plus de temps serveur au total — c’est votre priorité
mean_ms élevé + calls bas : requête unitairement lente (manque d’index ?)
calls très élevé + mean_ms faible : requête appelée en boucle par l’application (peut-être du N+1)

Notez que pg_stat_statements normalise les requêtes : les valeurs littérales sont remplacées par $1, $2, etc. Cela permet de regrouper les appels identiques.

Réinitialiser les statistiques

Après une optimisation (ajout d’index, réécriture de requête), réinitialisez pour repartir de zéro :

SELECT pg_stat_statements_reset();

Les statistiques recommencent à s’accumuler immédiatement.

Diagnostiquer avec EXPLAIN ANALYZE

EXPLAIN vs EXPLAIN ANALYZE

Commande	Ce qu’elle fait	Exécute la requête ?
`EXPLAIN`	Estime le plan d’exécution	Non
`EXPLAIN ANALYZE`	Exécute et mesure le temps réel	Oui
`EXPLAIN (ANALYZE, BUFFERS)`	Ajoute les statistiques de buffers I/O	Oui

Lire un plan : Seq Scan, Index Scan, Hash Join

Voici une requête sur la table app.logs (90 000 lignes) sans index sur les colonnes filtrées :

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM app.logs WHERE action = 'login' AND client_id = 3;

 Seq Scan on logs  (cost=0.00..2334.00 rows=5023 width=34) (actual time=0.006..3.515 rows=4985 loops=1)
   Filter: ((action = 'login'::text) AND (client_id = 3))
   Rows Removed by Filter: 95015
   Buffers: shared hit=834
 Planning:
   Buffers: shared hit=82
 Planning Time: 0.206 ms
 Execution Time: 3.695 ms

Lecture du plan :

Élément	Valeur	Signification
`Seq Scan`	—	Parcours séquentiel de toute la table
`cost=0.00..2334.00`	—	Coût estimé (en unités arbitraires)
`rows=5023`	estimé	Nombre de lignes que le planificateur attend
`actual time=0.006..3.515`	mesuré	Temps réel (premier résultat → dernier)
`rows=4985`	réel	Lignes effectivement retournées
`Rows Removed by Filter: 95015`	—	95 015 lignes lues pour rien
`Buffers: shared hit=834`	—	834 blocs de 8 Ko lus depuis le cache

Le problème : PostgreSQL lit toute la table (834 blocs) pour ne garder que 5 % des lignes.

Corriger : ajouter un index et comparer

CREATE INDEX idx_logs_action_client ON app.logs (action, client_id);

Relançons la même requête :

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM app.logs WHERE action = 'login' AND client_id = 3;

 Bitmap Heap Scan on logs  (cost=71.78..981.12 rows=5023 width=34) (actual time=0.247..1.364 rows=4985 loops=1)
   Recheck Cond: ((action = 'login'::text) AND (client_id = 3))
   Heap Blocks: exact=831
   Buffers: shared hit=831 read=6
   ->  Bitmap Index Scan on idx_logs_action_client  (cost=0.00..70.52 rows=5023 width=0) (actual time=0.182..0.182 rows=4985 loops=1)
         Index Cond: ((action = 'login'::text) AND (client_id = 3))
         Buffers: shared read=6
 Planning:
   Buffers: shared hit=103 read=1
 Planning Time: 0.338 ms
 Execution Time: 1.546 ms

Résultat : de 3,7 ms (Seq Scan) à 1,5 ms (Bitmap Index Scan) — plus de 2× plus rapide. L’index a permis à PostgreSQL de ne lire que les blocs contenant les lignes ciblées.

Les types de nœuds courants

Nœud	Signification	Quand il apparaît
Seq Scan	Parcours séquentiel, lit tout	Pas d’index, ou table petite
Index Scan	Lecture via l’index, puis la table	Filtre sélectif sur colonne indexée
Bitmap Index Scan	Construit un bitmap des blocs à lire	Filtre moyennement sélectif
Index Only Scan	Lecture de l’index seul (covering)	Toutes les colonnes sont dans l’index
Hash Join	Jointure par table de hachage	Jointures sur des volumes moyens
Nested Loop	Boucle imbriquée	Petits volumes ou index très sélectif
Sort	Tri en mémoire ou sur disque	`ORDER BY`, `DISTINCT`, `GROUP BY`
HashAggregate	Agrégation par hachage	`GROUP BY` avec peu de groupes

Cas pratique : JOIN avec agrégation

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.nom, l.action, count(*)
FROM app.logs l
JOIN app.clients c ON c.id = l.client_id
GROUP BY c.nom, l.action
ORDER BY count(*) DESC;

 Sort  (cost=3070.94..3071.01 rows=25 width=26) (actual time=26.766..26.769 rows=25 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=838
   ->  HashAggregate  (cost=3070.11..3070.36 rows=25 width=26) (actual time=26.747..26.751 rows=25 loops=1)
         Group Key: c.nom, l.action
         Batches: 1  Memory Usage: 32kB
         ->  Hash Join  (cost=1.11..2320.11 rows=100000 width=18) (actual time=0.024..13.845 rows=100000 loops=1)
               Hash Cond: (l.client_id = c.id)
               ->  Seq Scan on logs l  (cost=0.00..1834.00 rows=100000 width=10) (actual time=0.004..3.867 rows=100000 loops=1)
                     Buffers: shared hit=834
               ->  Hash  (cost=1.05..1.05 rows=5 width=16) (actual time=0.012..0.013 rows=5 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
 Planning Time: 0.441 ms
 Execution Time: 26.840 ms

Lecture : PostgreSQL construit une table de hachage sur clients (5 lignes, rapide), puis parcourt logs séquentiellement en rejoignant via le hash. Le HashAggregate regroupe les 25 combinaisons (5 clients × 5 actions). Le Sort final est en mémoire (26 kB) — pas de débordement sur disque.

Surveiller les tables et l’autovacuum

pg_stat_user_tables : l’état de santé des tables

La vue pg_stat_user_tables centralise les statistiques d’activité de chaque table :

SELECT relname,
       seq_scan,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_live_tup,
       n_dead_tup,
       last_vacuum::timestamp(0),
       last_autovacuum::timestamp(0),
       last_analyze::timestamp(0)
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

  relname  | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum |    last_analyze
-----------+----------+----------+-----------+-----------+-----------+------------+------------+-------------+-----------------+---------------------
 logs      |       18 |        4 |    100000 |     80000 |     10000 |      90000 |          0 |  (après)    | 2026-04-13 ...  | 2026-04-13 10:46:02
 clients   |       18 |        4 |         1 |         0 |         2 |          5 |          4 |             |                 | 2026-04-13 10:46:02
 commandes |       12 |        0 |         0 |         0 |         0 |          5 |          0 |             |                 | 2026-04-13 10:46:02

Métriques clés :

Repères de terrain (ces valeurs ne sont pas des seuils officiels PostgreSQL — adaptez-les à votre workload) :

Métrique	Repère	Action
`seq_scan` >> `idx_scan`	Ratio > 10:1 sur une grosse table	Index manquant — vérifier avec `EXPLAIN`
`n_dead_tup` élevé	> 20 % de `n_live_tup` (heuristique)	Autovacuum en retard ou bloqué
`last_autovacuum` NULL	Table jamais nettoyée	Vérifier les paramètres autovacuum

Détecter le bloat (ratio dead tuples)

Le bloat est l’accumulation de dead tuples (lignes supprimées ou anciennes versions de lignes mises à jour) qui occupent de l’espace sans être réutilisables tant que VACUUM ne passe pas.

Pour simuler le bloat sur le lab (50 000 UPDATE + 10 000 DELETE) :

UPDATE app.logs SET detail = 'updated_' || id WHERE id <= 50000;
DELETE FROM app.logs WHERE id > 90000;

Puis observez le ratio :

SELECT relname, n_live_tup, n_dead_tup,
       CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 1)
            ELSE 0 END AS dead_pct,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

  relname  | n_live_tup | n_dead_tup | dead_pct | total_size
-----------+------------+------------+----------+------------
 logs      |      90000 |      60000 |     40.0 | 14 MB
 clients   |          5 |          4 |     44.4 | 64 kB
 commandes |          5 |          0 |      0.0 | 32 kB

40 % de dead tuples sur logs — la table occupe bien plus d’espace qu’elle ne devrait. C’est un cas typique où VACUUM (voire VACUUM FULL) est nécessaire.

pg_stat_user_indexes : index inutilisés

Les index non utilisés consomment de l’espace et ralentissent les écritures (INSERT, UPDATE, DELETE). Identifiez-les :

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY pg_relation_size(indexrelid) DESC;

      indexrelname      | index_size | idx_scan
------------------------+------------+----------
 logs_pkey              | 1984 kB    |        2
 idx_logs_action_client | 640 kB     |        1
 clients_pkey           | 16 kB      |        4
 clients_email_key      | 16 kB      |        2
 commandes_pkey         | 16 kB      |        0

Un index avec idx_scan = 0 (ou très faible sur une longue période) est un candidat à la suppression. Attention : attendez au moins un cycle d’activité complet (fin de mois, reporting trimestriel…) avant de conclure.

Cache hit ratio : pg_statio_user_tables

Le cache hit ratio mesure l’efficacité du shared_buffers. Un ratio faible signifie que PostgreSQL va trop souvent lire sur le disque :

SELECT relname,
       heap_blks_read,
       heap_blks_hit,
       CASE WHEN heap_blks_hit + heap_blks_read > 0
            THEN round(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 1)
            ELSE 0 END AS cache_hit_pct
FROM pg_statio_user_tables
ORDER BY relname;

  relname  | heap_blks_read | heap_blks_hit | cache_hit_pct
-----------+----------------+---------------+---------------
 clients   |              2 |            28 |          93.3
 commandes |              3 |            10 |          76.9

Sur une base OLTP chaude (charge transactionnelle régulière), on cherche généralement un ratio très élevé — mais interprétez-le toujours avec le contexte (disque, mémoire, workload). Ce compteur ne distingue pas ce qui a été lu depuis le disque physique et ce qui était déjà dans le cache noyau (page cache Linux) : un blks_read peut en réalité être servi depuis la mémoire sans accès disque réel.

Au niveau base de données :

SELECT datname,
       blks_read,
       blks_hit,
       round(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname NOT LIKE 'template%' AND blks_read > 0;

  datname  | blks_read | blks_hit | cache_hit_pct
-----------+-----------+----------+---------------
 lab_admin |      1655 |  1524251 |         99.89
 postgres  |       514 |     9593 |         94.91

99,89 % sur lab_admin — le cache fonctionne correctement pour ce lab. En production, un ratio durablement bas (en deçà de 90-95 % sur un workload OLTP) est un signal qui mérite investigation : augmenter shared_buffers, vérifier le dimensionnement mémoire, ou identifier les requêtes qui lisent massivement (voir le guide Configuration).

pg_stat_io : observer les I/O par type de backend

Vue d’ensemble (PostgreSQL 16+)

pg_stat_io est une vue introduite en PostgreSQL 16 qui détaille les opérations d’entrée/sortie par combinaison backend_type / object / context. Ce sont des statistiques cluster-wide agrégées — pas des I/O par connexion individuelle. Elle remplace les vues partielles antérieures par une vision unifiée :

SELECT backend_type, object, context,
       reads, writes, hits
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads + writes DESC
LIMIT 8;

    backend_type     |  object  | context | reads | writes |  hits
---------------------+----------+---------+-------+--------+--------
 client backend      | relation | normal  |  1005 |      0 | 741969
 autovacuum worker   | relation | normal  |    75 |      0 |  10368
 walwriter           | wal      | normal  |       |     24 |
 startup             | wal      | normal  |    23 |      1 |
 checkpointer        | relation | normal  |       |     22 |
 client backend      | wal      | normal  |     0 |     13 |
 checkpointer        | wal      | normal  |       |     11 |
 autovacuum launcher | relation | normal  |     2 |      0 |     65

Lecture : les client backend dominent les lectures (1 005 reads) avec un excellent ratio de cache (741 969 hits). L’autovacuum a ses propres I/O séparées. Le walwriter et le checkpointer gèrent les écritures WAL.

Cette vue est particulièrement utile pour comprendre quel type de processus génère les I/O : est-ce les requêtes utilisateur, l’autovacuum, le checkpointer ?

Maintenir : VACUUM, ANALYZE, REINDEX

VACUUM : libérer l’espace réutilisable

VACUUM marque les dead tuples comme réutilisables. Il ne rend pas l’espace au système d’exploitation — il le rend disponible pour les futures insertions dans la même table.

VACUUM VERBOSE app.logs;

INFO:  vacuuming "lab_admin.app.logs"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO:  finished vacuuming "lab_admin.app.logs": index scans: 1
pages: 0 removed, 1250 remain, 502 scanned (40.16% of total)
tuples: 30000 removed, 84056 remain, 0 are dead but not yet removable
index scan needed: 251 pages from table (20.08% of total) had 29984 dead item identifiers removed
index "logs_pkey": pages: 415 in total, 0 newly deleted, 27 currently deleted
index "idx_logs_action_client": pages: 148 in total, 1 newly deleted, 12 currently deleted

30 000 dead tuples supprimés. Les pages libérées restent dans le fichier de la table, mais sont maintenant réutilisables.

VACUUM FULL : compacter la table

VACUUM FULL réécrit entièrement la table pour rendre l’espace au système d’exploitation. C’est le seul moyen de réduire la taille physique du fichier :

Avant : 14 MB

VACUUM FULL app.logs;

Après  : 8632 kB

De 14 Mo à 8,6 Mo — 40 % d’espace récupéré au niveau du système de fichiers.

ANALYZE : mettre à jour les statistiques du planificateur

Le planificateur de requêtes utilise des statistiques sur la distribution des données pour choisir le meilleur plan. Si ces statistiques sont obsolètes (après un gros INSERT ou DELETE), le planificateur peut faire de mauvais choix.

ANALYZE app.logs;

ANALYZE est léger et ne pose aucun verrou bloquant. L’autovacuum le lance automatiquement, mais après un chargement massif de données, lancez-le manuellement.

REINDEX : reconstruire des index gonflés

Les index B-tree peuvent se fragmenter après de nombreuses mises à jour. REINDEX reconstruit l’index à neuf :

REINDEX (VERBOSE) INDEX app.logs_pkey;

INFO:  index "logs_pkey" was reindexed
DETAIL:  CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s

REINDEX simple
REINDEX CONCURRENTLY

-- Un index
REINDEX INDEX app.logs_pkey;

-- Tous les index d'une table
REINDEX TABLE app.logs;

-- Tous les index d'une base
REINDEX DATABASE lab_admin;

-- Sans verrouiller les écritures (PG 12+)
REINDEX (CONCURRENTLY) INDEX app.logs_pkey;

-- Toute la table
REINDEX (CONCURRENTLY) TABLE app.logs;

REINDEX CONCURRENTLY est recommandé en production : il reconstruit l’index sans verrouiller les écritures. Quelques réserves à connaître :

C’est plus lent et plus gourmand en ressources qu’un REINDEX simple (double construction de l’index)
En cas d’échec (crash, annulation), l’index peut rester dans un état INVALID — il faut alors le supprimer manuellement (DROP INDEX) et relancer le REINDEX
Il nécessite temporairement environ le double de l’espace disque de l’index

L’autovacuum fait-il le travail ?

L’autovacuum est le processus qui exécute automatiquement VACUUM et ANALYZE. Vérifiez qu’il passe régulièrement :

SELECT relname, n_dead_tup,
       last_autovacuum::timestamp(0),
       last_autoanalyze::timestamp(0)
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Si last_autovacuum est NULL ou ancien sur une table avec beaucoup de dead tuples, l’autovacuum est en retard. Causes possibles :

autovacuum_naptime trop élevé (défaut : 60 s — correct pour la plupart des cas)
autovacuum_max_workers insuffisant (défaut : 3)
Une connexion idle in transaction bloque le nettoyage
Le seuil n’est pas atteint (50 + 20 % de n_live_tup par défaut)

Paramètres autovacuum clés

SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

Paramètre	Défaut	Signification
`autovacuum`	`on`	Active/désactive l’autovacuum
`autovacuum_vacuum_threshold`	`50`	Nombre minimum de dead tuples avant VACUUM
`autovacuum_vacuum_scale_factor`	`0.2`	Fraction de la table à ajouter au seuil
`autovacuum_analyze_threshold`	`50`	Nombre minimum de modifications avant ANALYZE
`autovacuum_analyze_scale_factor`	`0.1`	Fraction de la table à ajouter au seuil
`autovacuum_max_workers`	`3`	Nombre maximum de workers simultanés
`autovacuum_naptime`	`60`	Délai entre deux cycles (secondes)
`autovacuum_vacuum_max_threshold`	`100000000`	Seuil maximum (nouveau en PG 18)

Formule du seuil : l’autovacuum se déclenche quand le nombre de dead tuples dépasse threshold + scale_factor × n_live_tup. Pour une table de 100 000 lignes : 50 + 0.2 × 100 000 = 20 050 dead tuples.

Ajuster par table

Pour une table critique avec beaucoup d’écritures, resserrez les seuils :

ALTER TABLE app.logs SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.02
);

Ces paramètres s’appliquent uniquement à cette table — les autres gardent la configuration globale.

Aller plus loin : métriques Prometheus

Pour une supervision continue en production, exportez les métriques PostgreSQL vers Prometheus avec postgres_exporter. Les métriques les plus utiles :

Métrique	Source	Seuil d’alerte
`pg_stat_database_blks_hit_ratio`	`pg_stat_database`	Chute significative par rapport à la baseline
`pg_stat_user_tables_dead_tuples`	`pg_stat_user_tables`	> 10 000 par table (à adapter)
`pg_stat_activity_count` par état	`pg_stat_activity`	> 80 % de `max_connections` (client backend)
`pg_stat_statements_total_time`	`pg_stat_statements`	Top 5 en hausse
`pg_stat_archiver_failed_count`	`pg_stat_archiver`	> 0

L’alerte la plus importante : le nombre de connexions idle in transaction qui dépasse 0 pendant plus de 5 minutes.

Dépannage

Symptôme	Cause probable	Solution
Requêtes de plus en plus lentes	Dead tuples accumulés + stats obsolètes	`VACUUM ANALYZE` sur les tables touchées
`Seq Scan` sur une grosse table	Index manquant ou statistiques obsolètes	Vérifier avec `EXPLAIN`, ajouter l’index, `ANALYZE`
`pg_stat_statements` vide	Extension non créée dans cette base	`CREATE EXTENSION pg_stat_statements;`
Autovacuum ne passe jamais	Connexion `idle in transaction` qui bloque	Identifier avec `pg_stat_activity`, fermer la connexion
Table qui grossit malgré les DELETE	`VACUUM` ne rend pas l’espace au FS	`VACUUM FULL` (verrou exclusif) ou `pg_repack`
`VACUUM FULL` trop long	Table > 10 Go	Utiliser `pg_repack` (réécrit sans verrou exclusif)
Cache hit ratio < 90 %	`shared_buffers` trop petit	Augmenter à 25 % de la RAM (voir guide Configuration)
`REINDEX` verrouille les écritures	`REINDEX` simple pose un verrou	Utiliser `REINDEX CONCURRENTLY`
`pg_stat_io` vide	PostgreSQL < 16	Vue disponible uniquement depuis PG 16

À retenir

pg_stat_activity est le premier réflexe : qui est connecté, dans quel état, depuis combien de temps.
pg_stat_statements identifie les requêtes coûteuses — installez-le sur chaque base de production.
EXPLAIN ANALYZE (avec BUFFERS inclus en PG 18) est le seul moyen fiable de comprendre pourquoi une requête est lente.
Les dead tuples sont la cause n°1 du bloat — surveillez n_dead_tup dans pg_stat_user_tables.
VACUUM rend l’espace réutilisable sans verrouiller. VACUUM FULL compacte mais verrouille tout.
L’autovacuum est activé par défaut — ne le désactivez jamais. Ajustez les seuils par table si nécessaire.
REINDEX CONCURRENTLY est préférable en production, mais un échec peut laisser un index INVALID.
Le cache hit ratio est un indicateur utile mais imparfait — il ne distingue pas le disque physique du cache noyau. Interprétez-le en contexte.
pg_stat_io (PG 16+) montre quel type de processus génère les I/O. PG 18 ajoute pg_stat_get_backend_io() pour le suivi par backend individuel.

Prochaines étapes

Sécuriser PostgreSQL pg_hba.conf restrictif, TLS, séparation de rôles — protéger les accès et les données

Configurer PostgreSQL shared_buffers, work_mem, max_connections — les réglages qui impactent les performances

Sauvegarde et restauration pg_dump, pg_basebackup, PITR — sauvegarder avant de maintenir

Réplication Réplication physique et logique, failover — haute disponibilité et lecture distribuée