Quelques petits trucs & astuces pour PostgreSQL
Des petites commandes bien utiles à lancer dans le terminal psql pour monitorer et nettoyer ses bases de données.
Les raccourcis psql indispensables
Ceux qu'on oublie tout le temps une fois connecté en ligne de commande :
\l: Liste toutes les bases de données.\c nom_de_la_base: Se connecter à une base spécifique.\dt: Liste toutes les tables de la base actuelle.\d nom_de_la_table: Affiche la structure exacte d'une table (colonnes, types, index).\x: Active/désactive l'affichage étendu (indispensable si une ligne de résultat est trop longue et casse ton terminal, ça l'affichera à la verticale).\q: Quitter proprement psql.
Taille réelle des tables (Données vs Index)
Permet de repérer les tables les plus volumineuses en séparant le poids des données pures de celui des index. Idéal pour trouver les tables d'historique ou de cache à purger.
SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 5;
Exemple de sortie :
table_name | total_size | data_size | external_size --------------+------------+-----------+--------------- history | 530 MB | 336 MB | 195 MB trends | 476 MB | 309 MB | 167 MB
Comment le lire : total_size est la place réelle prise sur le disque. data_size est le poids de tes données, et external_size est le poids des Index. Si un index est plus lourd que la donnée elle-même, c'est parfois suspect (sauf sur de très petites tables).
Lignes Mortes (Dead Tuples) et Autovacuum
Indispensable après de grosses suppressions. PostgreSQL ne supprime pas physiquement la donnée, il la marque comme "morte". Cette requête montre si le nettoyeur (autovacuum) passe bien balayer derrière.
SELECT
relname AS table_name,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;
Exemple de sortie :
table_name | live_rows | dead_rows | last_autovacuum --------------+-----------+-----------+------------------------------- trends | 3394149 | 659084 | 2026-07-26 11:47:32.418125+02 history | 4179878 | 625848 | 2026-07-26 18:35:04.626506+02
Comment le lire : Si dead_rows devient presque aussi grand que live_rows, ta base est fragmentée et lente. Vérifie que la date last_autovacuum est récente. Si elle est vide ou vieille, le nettoyage automatique est cassé ou n'arrive pas à suivre le rythme.
Voir les requêtes en cours (Temps réel)
Pour prendre un processus la main dans le sac quand le serveur rame (CPU ou I/O à 100%).
SELECT
pid,
state,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state != 'idle'
AND pid <> pg_backend_pid()
ORDER BY duration DESC;
Exemple de sortie :
pid | state | duration | query --------+--------+------------------+-------------------------------------------------------- 354869 | active | 00:00:04.491543 | delete from trends_uint where itemid=33649 ...
Comment le lire : Regarde la duration. Une requête qui dure 0.05 seconde, c'est normal. Une requête qui est active depuis plusieurs minutes, c'est elle qui bloque la base. Tu as même le code SQL exact (query) pour débugger ton application.
4. Hit Ratio du Cache (RAM vs Disque)
Vérifie si PostgreSQL arrive à garder tes données en mémoire vive (RAM) ou s'il passe son temps à lire sur ton disque.
Importants : Ces compteurs sont cumulatifs depuis la nuit des temps.
Plus précisément, ils tournent et s'additionnent depuis :
- La création de la base de données.
- OU la dernière remise à zéro manuelle par un administrateur.
(Ils survivent aux redémarrages normaux du serveur, PostgreSQL les sauvegarde dans un fichier avant de s'éteindre, valable aussi pour la commande suivante).
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
ROUND(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 0.0001) * 100, 2) as ratio
FROM pg_statio_user_tables;
Exemple de sortie :
heap_read | heap_hit | ratio -----------+------------+------- 53885346 | 1229659376 | 95.80
Comment le lire : Le but est d'avoir un ratio proche de 99%. Ici, à 95.80%, ça va encore, mais ça veut dire qu'environ 4% des requêtes forcent PostgreSQL à aller gratter physiquement le disque dur. Si le ratio tombe sous les 90%, il faut rajouter de la RAM au serveur (ou vérifier la config pour que pgsql utilise la RAM correctement).
Volume d'écriture et d'activité (I/O)
Affiche les tables qui subissent le plus d'ajouts (INSERT), de modifications (UPDATE) ou de suppressions (DELETE).
SELECT
relname AS table_name,
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_del AS deletes
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC
LIMIT 5;
Exemple de sortie :
table_name | inserts | updates | deletes
-------------------+----------+---------+----------
history | 20234890 | 0 | 20715554
history_uint | 15718402 | 0 | 15878391
Comment le lire : Si ton disque gratte sans arrêt, cette requête te dira "qui" écrit. Ici, on voit très clairement des millions de DELETE, signe typique d'un script de nettoyage/purge applicatif (ex: Zabbix Housekeeper).
Remettre les compteurs de statistiques à zéro
Attention aux compteurs : Les chiffres renvoyés par les requêtes de Cache (Hit Ratio) et d'I/O (Inserts/Updates/Deletes) sont cumulatifs depuis la création de la base de données. Ce ne sont pas des compteurs en "temps réel".
Si tu es en train de débugger un problème en cours et que tu veux des statistiques fraîches (pour voir l'activité depuis cet instant T), tu peux remettre les compteurs de la base de données actuelle à zéro. C'est 100% sans danger pour tes vraies données :
SELECT pg_stat_reset();
Une fois la commande passée, relance tes requêtes de Hit Ratio ou d'I/O quelques minutes plus tard pour observer l'activité en cours !
La bombe nucléaire "VACUUM FULL"
Comme on l'a vu plus haut, l'autovacuum classique marque les vieilles données comme "mortes" pour réutiliser l'espace, mais il ne réduit jamais la taille du fichier sur le disque dur. Si tu as supprimé 10 Go de données et que tu veux vraiment récupérer ces 10 Go d'espace libre sur ton serveur, il faut utiliser un VACUUM FULL.
VACUUM FULL VERBOSE nom_de_la_table;
(Si tu ne précises pas de table, il le fera sur toute la base entière)
Exemple de sortie :
INFO: vacuuming "public.history" INFO: "history": found 625848 removable, 4179878 nonremovable row versions...
ATTENTION !! => Le VACUUM FULL va créer un nouveau fichier propre et y recopier les données saines, puis écraser l'ancien.
1. Tu as besoin d'espace disque libre temporaire (au moins la taille de la table).
2. Il verrouille totalement la table (LOCK) pendant toute l'opération. L'application (Nextcloud, Zabbix...) sera figée/inaccessible tant que ce n'est pas terminé. À ne lancer qu'en pleine nuit ou lors d'une maintenance !
Trouver les index qui ne servent à rien
Un index permet d'accélérer les recherches (les SELECT). Mais attention : à chaque fois que tu insères ou modifies une donnée, PostgreSQL doit mettre à jour l'index. Un index qui n'est jamais utilisé pour lire, c'est juste un poids mort qui ralentit tes écritures (INSERT/UPDATE) et bouffe de l'espace disque. Voici comment débusquer les parasites :
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND idx_indexprs IS NULL
AND indisunique IS FALSE
ORDER BY pg_relation_size(indexrelid) DESC;
Comment le lire : La colonne idx_scan indique combien de fois l'index a été utilisé. Si elle est à 0 (et que l'application tourne depuis des mois), cet index prend de la place (index_size) strictement pour rien. Tu peux envisager de le supprimer (après avoir vérifié avec les développeurs !).
Qui bloque qui ? (Les verrous / Locks)
Parfois, ta base ne consomme ni CPU ni disque, mais l'application est figée. C'est typique d'un "Lock" (Verrou) : la requête A est en train de modifier une ligne, et la requête B veut modifier la même ligne. B va attendre indéfiniment que A termine. Cette requête magique te montre qui est le coupable et qui est la victime :
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted;
Comment le lire : Le blocked_pid est la victime qui poireaute, le blocking_pid est le coupable qui retient le verrou. Tu vois exactement quelle requête bloque l'autre. Si le processus coupable est planté dans les choux, tu peux le tuer sans pitié avec un petit SELECT pg_terminate_backend(blocking_pid);.
Monitorer la réplication (Maître / Esclave)
Si tu as un cluster avec une base principale (Primary) qui se réplique en temps réel sur un serveur de secours (Standby), voici comment vérifier que l'esclave n'est pas à la traîne. Ces requêtes sont vitales pour éviter le "Split Brain" ou la perte de données.
Sur le serveur Maître (Primary) : Quel est le retard ?
Cette requête te montre l'état de tous les serveurs esclaves connectés et surtout, combien d'octets ils ont de retard sur l'écriture.
SELECT
client_addr AS replica_ip,
state,
sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS pending_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
Comment le lire : Le state doit être sur "streaming". Le replay_lag_bytes est le plus important : c'est le volume de données que l'esclave n'a pas encore appliqué. S'il grimpe en flèche (plusieurs dizaines de Mo), ton esclave est en train de ramer (souvent à cause d'un I/O trop lent sur le serveur de secours).
Sur le serveur Esclave (Standby) : Suis-je bien connecté ?
À lancer sur la machine de secours pour vérifier qu'elle reçoit bien le flux du maître.
SELECT
status,
now() - last_msg_receipt_time AS time_since_last_msg
FROM pg_stat_wal_receiver;
Comment le lire : status doit être sur "streaming". time_since_last_msg t'indique depuis combien de temps l'esclave n'a pas eu de nouvelles du maître. Si ça dépasse quelques secondes, c'est que ton réseau est coupé ou que le maître est tombé.