Vous trouverez en ligne les différentes versions complètes de ce document. Les solutions de TP ne figurent pas forcément dans la version imprimée, mais sont dans les versions numériques (PDF ou HTML).

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Le présent module vise à donner un premier aperçu global du fonctionnement interne de PostgreSQL.

Après quelques rappels sur l’installation, nous verrons essentiellement les processus et les fichiers utilisés.

Nous recommandons très fortement l’utilisation des paquets Linux précompilés. Dans certains cas, il ne sera pas possible de faire autrement que de passer par des outils externes, comme l’installeur d’EntrepriseDB sous Windows.

Debian et Red Hat fournissent des paquets précompilés adaptés à leur distribution. Dalibo recommande d’installer les paquets de la communauté, ces derniers étant bien plus à jour que ceux des distributions.

L’installation d’un paquet provoque la création d’un utilisateur système nommé postgres et l’installation des binaires. Suivant les distributions, l’emplacement des binaires change. Habituellement, tout est placé dans /usr/pgsql-<version majeure> pour les distributions Red Hat et dans /usr/lib/postgresql/<version majeure> pour les distributions Debian.

Dans le cas d’une distribution Debian, une instance est immédiatement créée dans /var/lib/postgresql/<version majeure>/main. Elle est ensuite démarrée.

Dans le cas d’une distribution Red Hat, aucune instance n’est créée automatiquement. Il faudra utiliser un script (dont le nom dépend de la version de la distribution) pour créer l’instance, puis nous pourrons utiliser le script de démarrage pour lancer le serveur.

L’annexe ci-dessous décrit l’installation de PostgreSQL sans configuration particulière pour suivre le reste de la formation.

L’architecture PostgreSQL est une architecture multiprocessus et non multithread.

Cela signifie que chaque processus de PostgreSQL s’exécute dans un contexte mémoire isolé, et que la communication entre ces processus repose sur des mécanismes systèmes inter-processus : sémaphores, zones de mémoire partagée, sockets. Ceci s’oppose à l’architecture multithread, où l’ensemble du moteur s’exécute dans un seul processus, avec plusieurs threads (contextes) d’exécution, où tout est partagé par défaut.

Le principal avantage de cette architecture multiprocessus est la stabilité : un processus, en cas de problème, ne corrompt que sa mémoire (ou la mémoire partagée), le plantage d’un processus n’affecte pas directement les autres. Son principal défaut est une allocation statique des ressources de mémoire partagée : elles ne sont pas redimensionnables à chaud.

Tous les processus de PostgreSQL accèdent à une zone de « mémoire partagée ». Cette zone contient les informations devant être partagées entre les clients, comme un cache de données, ou des informations sur l’état de chaque session par exemple.

PostgreSQL utilise une architecture client-serveur. Nous ne nous connectons à PostgreSQL qu’à travers d’un protocole bien défini, nous n’accédons jamais aux fichiers de données.

Nous constatons que plusieurs processus sont présents dès le démarrage de PostgreSQL. Nous allons les détailler.

NB : sur Debian, le postmaster est nommé postgres comme ses processus fils ; sous Windows les noms des processus sont par défaut moins verbeux.

Le postmaster est responsable de la supervision des autres processus, ainsi que de la prise en compte des connexions entrantes.

Le background writer et le checkpointer s’occupent d’effectuer les écritures en arrière plan, évitant ainsi aux sessions des utilisateurs de le faire.

Le walwriter écrit le journal de transactions de façon anticipée, afin de limiter le travail de l’opération COMMIT.

L’autovacuum launcher pilote les opérations d’« autovacuum ».

Avant la version 15, le stats collector collecte les statistiques d’activité du serveur. À partir de la version 15, ces informations sont conservées en mémoire jusqu’à l’arrêt du serveur où elles sont stockées sur disque jusqu’au prochain démarrage.

Le logical replication launcher est un processus dédié à la réplication logique.

Des processus supplémentaires peuvent apparaître, comme un walsender dans le cas où la base est le serveur primaire du cluster de réplication, un logger si PostgreSQL doit gérer lui-même les fichiers de traces (par défaut sous Red Hat, mais pas sous Debian), ou un archiver si l’instance est paramétrée pour générer des archives de ses journaux de transactions.

Ces différents processus seront étudiées en détail dans d’autres modules de formation.

Aucun de ces processus ne traite de requête pour le compte des utilisateurs. Ce sont des processus d’arrière-plan effectuant des tâches de maintenance.

Il existe aussi les background workers (processus d’arrière-plan), lancés par PostgreSQL, mais aussi par des extensions tierces. Par exemple, la parallélisation des requêtes se base sur la création temporaire de background workers épaulant le processus principal de la requête. La réplication logique utilise des background workers à plus longue durée de vie. De nombreuses extensions en utilisent pour des raisons très diverses. Le paramètre max_worker_processes régule le nombre de ces workers. Ne descendez pas en-dessous du défaut (8). Il faudra même parfois monter plus haut.

Pour chaque nouvelle session à l’instance, le processus postmaster crée un processus fils qui s’occupe de gérer cette session. Il y a donc un processus dédié à chaque connexion cliente, et ce processus est détruit à fin de cette connexion.

Ce processus dit backend reçoit les ordres SQL, les interprète, exécute les requêtes, trie les données, et enfin retourne les résultats. Dans certains cas, il peut demander le lancement d’autres processus pour l’aider dans l’exécution d’une requête en lecture seule (parallélisme).

Le dialogue entre le client et ce processus respecte un protocole réseau bien défini. Le client n’a jamais accès aux données par un autre moyen que par ce protocole.

Le nombre maximum de connexions à l’instance simultanées, actives ou non, est limité par le paramètre max_connections. Le défaut est 100.

Certaines connexions sont réservées. Les administrateurs doivent pouvoir se connecter à l’instance même si la limite est atteinte. Quelques connexions sont donc réservées aux superutilisateurs (paramètre superuser_reserved_connections, à 3 par défaut). On peut aussi octroyer le rôle pg_use_reserved_connections à certains utilisateurs pour leur garantir l’accès à un nombre de connexions réservées, à définir avec le paramètre reserved_connections (vide par défaut), cela à partir de PostreSQL 16.

Attention : modifier un de ces paramètres impose un redémarrage complet de l’instance, puisqu’ils ont un impact sur la taille de la mémoire partagée entre les processus PostgreSQL. Il faut donc réfléchir au bon dimensionnement avant la mise en production.

La valeur 100 pour max_connections est généralement suffisante. Il peut être intéressant de la diminuer pour se permettre de monter work_mem et autoriser plus de mémoire de tri. Il est possible de monter max_connections pour qu’un plus grand nombre de clients puisse se connecter en même temps.

Il s’agit aussi d’arbitrer entre le nombre de requêtes à exécuter à un instant t, le nombre de CPU disponibles, la complexité des requêtes, et le nombre de processus que peut gérer l’OS. Ce dimensionnement est encore compliqué par le parallélisme et la limitation de la bande passante des disques.

Intercaler un « pooler » entre les clients et l’instance peut se justifier dans certains cas :

• connexions/déconnexions très fréquentes (la connexion a un coût) ;
• centaines, voire milliers, de connexions généralement inactives.

Le plus réputé est PgBouncer, mais il est aussi souvent inclus dans des serveurs d’application (par exemple Tomcat).

La gestion de la mémoire dans PostgreSQL mérite un module de formation à lui tout seul.

Pour le moment, bornons-nous à la séparer en deux parties : la mémoire partagée et celle attribuée à chacun des nombreux processus.

La mémoire partagée stocke principalement le cache des données de PostgreSQL (shared buffers, paramètre shared_buffers), et d’autres zones plus petites : cache des journaux de transactions, données de sessions, les verrous, etc.

La mémoire propre à chaque processus sert notamment aux tris en mémoire (définie en premier lieu par le paramètre work_mem), au cache de tables temporaires, etc.

Une instance est composée des éléments suivants :

Le répertoire de données :

Il contient les fichiers obligatoires au bon fonctionnement de l’instance : fichiers de données, journaux de transaction….

Les fichiers de configuration :

Selon la distribution, ils sont stockés dans le répertoire de données (Red Hat et dérivés comme CentOS ou Rocky Linux), ou dans /etc/postgresql (Debian et dérivés).

Un fichier PID :

Il permet de savoir si une instance est démarrée ou non, et donc à empêcher un second jeu de processus d’y accéder.

Le paramètre external_pid_file permet d’indiquer un emplacement où PostgreSQL créera un second fichier de PID, généralement à l’extérieur de son répertoire de données.

Des tablespaces :

Ils sont totalement optionnels. Ce sont des espaces de stockage supplémentaires, stockés habituellement dans d’autres systèmes de fichiers.

Le fichier de statistiques d’exécution :

Généralement dans pg_stat_tmp/.

Les fichiers de trace :

Typiquement, des fichiers avec une variante du nom postgresql.log, souvent datés. Ils sont par défaut dans le répertoire de l’instance, sous log/. Sur Debian, ils sont redirigés vers la sortie d’erreur du système. Ils peuvent être redirigés vers un autre mécanisme du système d’exploitation (syslog sous Unix, journal des événements sous Windows),

Le répertoire de données est souvent appelé PGDATA, du nom de la variable d’environnement que l’on peut faire pointer vers lui pour simplifier l’utilisation de nombreux utilitaires PostgreSQL. Il est possible aussi de le connaître, une fois connecté à une base de l’instance, en interrogeant le paramètre data_directory.

SHOW data_directory;

      data_directory
---------------------------
 /var/lib/pgsql/15/data

Il est recommandé de ne jamais créer ce répertoire PGDATA à la racine d’un point de montage, quel que soit le système d’exploitation et le système de fichiers utilisé. Si un point de montage est dédié à l’utilisation de PostgreSQL, positionnez-le toujours dans un sous-répertoire, voire deux niveaux en dessous du point de montage. (par exemple <point de montage>/<version majeure>/<nom instance>).

Voir à ce propos le chapitre Use of Secondary File Systems dans la documentation officielle : https://www.postgresql.org/docs/current/creating-cluster.html.

Vous pouvez trouver une description de tous les fichiers et répertoires dans la documentation officielle.

Les fichiers de configuration sont de simples fichiers textes. Habituellement, ce sont les suivants.

postgresql.conf contient une liste de paramètres, sous la forme paramètre=valeur. Tous les paramètres sont modifiables (et présents) dans ce fichier. Selon la configuration, il peut inclure d’autres fichiers, mais ce n’est pas le cas par défaut. Sous Debian, il est sous /etc.

postgresql.auto.conf stocke les paramètres de configuration fixés en utilisant la commande ALTER SYSTEM. Il surcharge donc postgresql.conf. Comme pour postgresql.conf, sa modification impose de recharger la configuration ou redémarrer l’instance. Il est techniquement possible, mais déconseillé, de le modifier à la main. postgresql.auto.conf est toujours dans le répertoire des données, même si postgresql.conf est ailleurs.

pg_hba.conf contient les règles d’authentification à la base selon leur identité, la base, la provenance, etc.

pg_ident.conf est plus rarement utilisé. Il complète pg_hba.conf, par exemple pour rapprocher des utilisateurs système ou propres à PostgreSQL.

Ces deux derniers fichiers peuvent eux-mêmes inclure d’autres fichiers (depuis PostgreSQL 16). Leur utilisation est décrite dans notre première formation.

PG_VERSION est un fichier. Il contient en texte lisible la version majeure devant être utilisée pour accéder au répertoire (par exemple 15). On trouve ces fichiers PG_VERSION à de nombreux endroits de l’arborescence de PostgreSQL, par exemple dans chaque répertoire de base du répertoire PGDATA/base/ ou à la racine de chaque tablespace.

Le fichier postmaster.pid est créé au démarrage de PostgreSQL. PostgreSQL y indique le PID du processus père sur la première ligne, l’emplacement du répertoire des données sur la deuxième ligne et la date et l’heure du lancement de postmaster sur la troisième ligne ainsi que beaucoup d’autres informations. Par exemple :

~$ cat /var/lib/postgresql/15/data/postmaster.pid
7771
/var/lib/postgresql/15/data
1503584802
5432
/tmp
localhost
  5432001  54919263
ready

$ ps -HFC postgres
UID  PID    SZ     RSS PSR STIME TIME   CMD
pos 7771 0 42486 16536   3 16:26 00:00  /usr/local/pgsql/bin/postgres
                                          -D /var/lib/postgresql/15/data
pos 7773 0 42486  4656   0 16:26 00:00  postgres: checkpointer
pos 7774 0 42486  5044   1 16:26 00:00  postgres: background writer
pos 7775 0 42486  8224   1 16:26 00:00  postgres: walwriter
pos 7776 0 42850  5640   1 16:26 00:00  postgres: autovacuum launcher
pos 7777 0 42559  3684   0 16:26 00:00  postgres: logical replication launcher

$ ipcs -p |grep  7771
54919263   postgres   7771       10640

$ ipcs  | grep 54919263
0x0052e2c1 54919263   postgres   600        56         6

Le processus père de cette instance PostgreSQL a comme PID le 7771. Ce processus a bien réclamé une sémaphore d’identifiant 54919263. Cette sémaphore correspond à des segments de mémoire partagée pour un total de 56 octets. Le répertoire de données se trouve bien dans /var/lib/postgresql/15/data.

Le fichier postmaster.pid est supprimé lors de l’arrêt de PostgreSQL. Cependant, ce n’est pas le cas après un arrêt brutal. Dans ce genre de cas, PostgreSQL détecte le fichier et indique qu’il va malgré tout essayer de se lancer s’il ne trouve pas de processus en cours d’exécution avec ce PID. Un fichier supplémentaire peut être créé ailleurs grâce au paramètre external_pid_file, c’est notamment le défaut sous Debian :

external_pid_file = '/var/run/postgresql/15-main.pid'

Par contre, ce fichier ne contient que le PID du processus père.

Quant au fichier postmaster.opts, il contient les arguments en ligne de commande correspondant au dernier lancement de PostgreSQL. Il n’est jamais supprimé. Par exemple :

$ cat $PGDATA/postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/var/lib/postgresql/15/data"

base/ contient les fichiers de données (tables, index, vues matérialisées, séquences). Il contient un sous-répertoire par base, le nom du répertoire étant l’OID de la base dans pg_database. Dans ces répertoires, nous trouvons un ou plusieurs fichiers par objet à stocker. Ils sont nommés ainsi :

• Le nom de base du fichier correspond à l’attribut relfilenode de l’objet stocké, dans la table pg_class (une table, un index…). Il peut changer dans la vie de l’objet (par exemple lors d’un VACUUM FULL, un TRUNCATE…)
• Si le nom est suffixé par un « . » suivi d’un chiffre, il s’agit d’un fichier d’extension de l’objet : un objet est découpé en fichiers de 1 Go maximum.
• Si le nom est suffixé par _fsm, il s’agit du fichier stockant la Free Space Map (liste des blocs réutilisables).
• Si le nom est suffixé par _vm, il s’agit du fichier stockant la Visibility Map (liste des blocs intégralement visibles, et donc ne nécessitant pas de traitement par VACUUM).

Un fichier base/1247/14356.1 est donc le second segment de l’objet ayant comme relfilenode 14356 dans le catalogue pg_class, dans la base d’OID 1247 dans la table pg_database.

Savoir identifier cette correspondance ne sert que dans des cas de récupération de base très endommagée. Vous n’aurez jamais, durant une exploitation normale, besoin d’obtenir cette correspondance. Si, par exemple, vous avez besoin de connaître la taille de la table test dans une base, il vous suffit d’exécuter la fonction pg_table_size(). En voici un exemple complet :

CREATE TABLE test (id integer);
INSERT INTO test SELECT generate_series(1, 5000000);
SELECT pg_table_size('test');

 pg_table_size
---------------
     181305344

Depuis la ligne de commande, il existe un utilitaire nommé oid2name, dont le but est de faire la liaison entre le nom de fichier et le nom de l’objet PostgreSQL. Il a besoin de se connecter à la base :

$ pwd
/var/lib/pgsql/15/data/base/16388

$ /usr/pgsql-15/bin/oid2name -f 16477 -d employes
From database "employes":
  Filenode         Table Name
-----------------------------
     16477  employes_big_pkey

Le répertoire base peut aussi contenir un répertoire pgsql_tmp. Ce répertoire contient des fichiers temporaires utilisés pour stocker les résultats d’un tri ou d’un hachage. À partir de la version 12, il est possible de connaître facilement le contenu de ce répertoire en utilisant la fonction pg_ls_tmpdir(), ce qui peut permettre de suivre leur consommation.

Si nous demandons au sein d’une première session :

SELECT * FROM generate_series(1,1e9) ORDER BY random() LIMIT 1 ;

alors nous pourrons suivre les fichiers temporaires depuis une autre session :

SELECT * FROM pg_ls_tmpdir() ;

       name        |    size    |      modification
-------------------+------------+------------------------
 pgsql_tmp12851.16 | 1073741824 | 2020-09-02 15:43:27+02
 pgsql_tmp12851.11 | 1073741824 | 2020-09-02 15:42:32+02
 pgsql_tmp12851.7  | 1073741824 | 2020-09-02 15:41:49+02
 pgsql_tmp12851.5  | 1073741824 | 2020-09-02 15:41:29+02
 pgsql_tmp12851.9  | 1073741824 | 2020-09-02 15:42:11+02
 pgsql_tmp12851.0  | 1073741824 | 2020-09-02 15:40:36+02
 pgsql_tmp12851.14 | 1073741824 | 2020-09-02 15:43:06+02
 pgsql_tmp12851.4  | 1073741824 | 2020-09-02 15:41:19+02
 pgsql_tmp12851.13 | 1073741824 | 2020-09-02 15:42:54+02
 pgsql_tmp12851.3  | 1073741824 | 2020-09-02 15:41:09+02
 pgsql_tmp12851.1  | 1073741824 | 2020-09-02 15:40:47+02
 pgsql_tmp12851.15 | 1073741824 | 2020-09-02 15:43:17+02
 pgsql_tmp12851.2  | 1073741824 | 2020-09-02 15:40:58+02
 pgsql_tmp12851.8  | 1073741824 | 2020-09-02 15:42:00+02
 pgsql_tmp12851.12 | 1073741824 | 2020-09-02 15:42:43+02
 pgsql_tmp12851.10 | 1073741824 | 2020-09-02 15:42:21+02
 pgsql_tmp12851.6  | 1073741824 | 2020-09-02 15:41:39+02
 pgsql_tmp12851.17 |  546168976 | 2020-09-02 15:43:32+02

Le répertoire global/ contient notamment les objets globaux à toute une instance, comme la table des bases de données, celle des rôles ou celle des tablespaces ainsi que leurs index.

Le répertoire pg_wal contient les journaux de transactions. Ces journaux garantissent la durabilité des données dans la base, en traçant toute modification devant être effectuée AVANT de l’effectuer réellement en base.

Par défaut, les fichiers des journaux font tous 16 Mo. Ils ont des noms sur 24 caractères, comme par exemple :

$ ls -l
total 2359320
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007C
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007D
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007E
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007F
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000000
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000001
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000002
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000003
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001D
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001E
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001F
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000020
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000021
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000022
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000023
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000024
drwx------ 2 postgres postgres    16384 Mar 26 16:28 archive_status

La première partie d’un nom de fichier (ici 00000002) correspond à la timeline (« ligne de temps »), qui ne s’incrémente que lors d’une restauration de sauvegarde ou une bascule entre serveurs primaire et secondaire. La deuxième partie (ici 00000142 puis 00000143) correspond au numéro de journal à proprement parler, soit un ensemble de fichiers représentant 4 Go. La dernière partie correspond au numéro du segment au sein de ce journal. Selon la taille du segment fixée à l’initialisation, il peut aller de 00000000 à 000000FF (256 segments de 16 Mo, configuration par défaut, soit 4 Go), à 00000FFF (4096 segments de 1 Mo), ou à 0000007F (128 segments de 32 Mo, exemple ci-dessus), etc. Une fois ce maximum atteint, le numéro de journal au centre est incrémenté et les numéros de segments reprennent à zéro.

L’ordre d’écriture des journaux est numérique (en hexadécimal), et leur archivage doit suivre cet ordre. Il ne faut pas se fier à la date des fichiers pour le tri : pour des raisons de performances, PostgreSQL recycle généralement les fichiers en les renommant. Dans l’exemple ci-dessus, le dernier journal écrit est 000000020000014300000020 et non 000000020000014300000024. Ce mécanisme peut toutefois être désactivé en passant wal_recycle à off (ce qui a un intérêt sur certains systèmes de fichiers comme ZFS).

Dans le cadre d’un archivage PITR et/ou d’une réplication par log shipping, le sous-répertoire pg_wal/archive_status indique l’état des journaux dans le contexte de l’archivage. Les fichiers .ready indiquent les journaux restant à archiver (normalement peu nombreux), les .done ceux déjà archivés.

À partir de la version 12, il est possible de connaître facilement le contenu de ce répertoire en utilisant la fonction pg_ls_archive_statusdir() :

# SELECT * FROM pg_ls_archive_statusdir() ORDER BY 1 ;

              name              | size |      modification
--------------------------------+------+------------------------
 000000010000000000000067.done  |    0 | 2020-09-02 15:52:57+02
 000000010000000000000068.done  |    0 | 2020-09-02 15:52:57+02
 000000010000000000000069.done  |    0 | 2020-09-02 15:52:58+02
 00000001000000000000006A.ready |    0 | 2020-09-02 15:53:53+02
 00000001000000000000006B.ready |    0 | 2020-09-02 15:53:53+02
 00000001000000000000006C.ready |    0 | 2020-09-02 15:53:54+02
 00000001000000000000006D.ready |    0 | 2020-09-02 15:53:54+02
 00000001000000000000006E.ready |    0 | 2020-09-02 15:53:54+02
 00000001000000000000006F.ready |    0 | 2020-09-02 15:53:54+02
 000000010000000000000070.ready |    0 | 2020-09-02 15:53:55+02
 000000010000000000000071.ready |    0 | 2020-09-02 15:53:55+02

Le répertoire pg_xact contient l’état de toutes les transactions passées ou présentes sur la base (validées, annulées, en sous-transaction ou en cours), comme nous le détaillerons dans le module « Mécanique du moteur transactionnel ».

D’autres répertoires contiennent des fichiers essentiels à la gestion des transactions :

• pg_commit_ts contient l’horodatage de la validation de chaque transaction ;
• pg_multixact est utilisé dans l’implémentation des verrous partagés (SELECT ... FOR SHARE) ;
• pg_serial est utilisé dans l’implémentation de SSI (Serializable Snapshot Isolation) ;
• pg_snapshots est utilisé pour stocker les snapshots exportés de transactions ;
• pg_subtrans stocke l’imbrication des transactions lors de sous-transactions (les SAVEPOINTS) ;
• pg_twophase est utilisé pour l’implémentation du Two-Phase Commit, aussi appelé transaction préparée, 2PC, ou transaction XA dans le monde Java par exemple.

pg_logical contient des informations sur la réplication logique.

pg_replslot contient des informations sur les slots de réplications, qui sont un moyen de fiabiliser la réplication physique ou logique.

Sans réplication en place, ces répertoires sont quasi-vides. Là encore, il ne faut pas toucher à leur contenu.

Dans PGDATA, le sous-répertoire pg_tblspc contient les tablespaces, c’est-à-dire des espaces de stockage.

Sous Linux

Sous Linux, ce sont des liens symboliques vers un simple répertoire extérieur à PGDATA. Chaque lien symbolique a comme nom l’OID du tablespace (table système pg_tablespace). PostgreSQL y crée un répertoire lié aux versions de PostgreSQL et du catalogue, et y place les fichiers de données.

postgres=# \db+
                          Liste des tablespaces
    Nom     | Propriétaire |      Emplacement      | … | Taille  |…
------------+--------------+-----------------------+---+---------+-
 froid      | postgres     | /mnt/hdd/pg           |   | 3576 kB | 
 pg_default | postgres     |                       |   | 6536 MB | 
 pg_global  | postgres     |                       |   | 587 kB  | 

sudo ls -R /mnt/hdd/pg

/mnt/hdd/pg:
PG_15_202209061

/mnt/hdd/pg/PG_15_202209061:
5

/mnt/hdd/pg/PG_15_202209061/5:
142532  142532_fsm  142532_vm

Sous Windows

Sous Windows, les liens sont à proprement parler des Reparse Points (ou Junction Points) :

postgres=# \db
          Liste des tablespaces
    Nom     | Propriétaire | Emplacement 
------------+--------------+-------------
 pg_default | postgres     | 
 pg_global  | postgres     | 
 tbl1       | postgres     | T:\TBL1

PS P:\PGDATA13> dir 'pg_tblspc/*' | ?{$_.LinkType} | select FullName,LinkType,Target

FullName                     LinkType Target
--------                     -------- ------
P:\PGDATA13\pg_tblspc\105921 Junction {T:\TBL1}

Par défaut, pg_tblspc/ est vide. N’existent alors que les tablespaces pg_global (sous-répertoire global/ des objets globaux à l’instance) et pg_default (soit base/).

Leur utilité et leur gestion seront abordés plus loin.

pg_stat_tmp est, jusqu’en version 15, le répertoire par défaut de stockage des statistiques d’activité de PostgreSQL, comme les entrées-sorties ou les opérations de modifications sur les tables. Ces fichiers pouvant générer une grande quantité d’entrées-sorties, l’emplacement du répertoire peut être modifié avec le paramètre stats_temp_directory. Par exemple, Debian place ce paramètre par défaut en tmpfs :

-- jusque v14
SHOW stats_temp_directory;

           stats_temp_directory
-----------------------------------------
 /var/run/postgresql/14-main.pg_stat_tmp

À l’arrêt, les fichiers sont copiés dans le répertoire pg_stat/.

PostgreSQL gérant ces statistiques en mémoire partagée à partir de la version 15, le collecteur n’existe plus. Mais les deux répertoires sont encore utilisés par des extensions comme pg_stat_statements ou pg_stat_kcache.

pg_dynshmem est utilisé par les extensions utilisant de la mémoire partagée dynamique.

pg_notify est utilisé par le mécanisme de gestion de notification de PostgreSQL (LISTEN et NOTIFY) qui permet de passer des messages de notification entre sessions.

Le paramétrage des journaux est très fin. Leur configuration est le sujet est évoquée dans notre première formation.

Si logging_collector est activé, c’est-à-dire que PostgreSQL collecte lui-même ses traces, l’emplacement de ces journaux se paramètre grâce aux paramètres log_directory, le répertoire où les stocker, et log_filename, le nom de fichier à utiliser, ce nom pouvant utiliser des échappements comme %d pour le jour de la date, par exemple. Les droits attribués au fichier sont précisés par le paramètre log_file_mode.

Un exemple pour log_filename avec date et heure serait :

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

La liste des échappements pour le paramètre log_filename est disponible dans la page de manuel de la fonction strftime sur la plupart des plateformes de type UNIX.

Ce schéma ne soit à présent plus avoir aucun secret pour vous.

L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comem Oracle Linux et Fedora), et Debian/Ubuntu.

Elle ne dure que quelques minutes.

Sur Rocky Linux 8 ou 9

Installation du dépôt communautaire  :

Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/, en précisant :

• la version majeure de PostgreSQL (ici la 17) ;
• la distribution (ici Rocky Linux 8) ;
• l’architecture (ici x86_64, la plus courante).

Les commandes sont à lancer sous root :

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf -qy module disable postgresql

Installation de PostgreSQL 17 (client, serveur, librairies, extensions) : 

# dnf install -y postgresql17-server postgresql17-contrib

Les outils clients et les librairies nécessaires seront automatiquement installés.

Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé.

# dnf install postgresql17-llvmjit

Création d’une première instance :

Il est conseillé de déclarer PG_SETUP_INITDB_OPTIONS, notamment pour mettre en place les sommes de contrôle et forcer les traces en anglais :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb
# cat /var/lib/pgsql/17/initdb.log

Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par :

Success. You can now start the database server using:

    /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile start

Chemins :

Configuration :

Modifier postgresql.conf est facultatif pour un premier lancement.

Commandes d’administration habituelles :

Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage :

# systemctl start postgresql-17
# systemctl stop postgresql-17
# systemctl status postgresql-17
# systemctl reload postgresql-17
# systemctl restart postgresql-17

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Démarrage de l’instance au lancement du système d’exploitation :

# systemctl enable postgresql-17

Ouverture du firewall pour le port 5432 :

Voir si le firewall est actif :

# systemctl status firewalld

Si c’est le cas, autoriser un accès extérieur :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

(Rappelons que listen_addresses doit être également modifié dans postgresql.conf.)

Création d’autres instances :

Si des instances de versions majeures différentes doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans dnf install …) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf pour que les instances puissent tourner simultanément.

Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des PGDATA différents.

• Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd).
• Respecter les normes et conventions de l’OS : placer les instances dans un nouveau sous-répertoire de /var/lib/pgsqsl/17/ (ou l’équivalent pour d’autres versions majeures).

Pour créer une seconde instance, nommée par exemple infocentre :

• Création du fichier service de la deuxième instance :

# cp /lib/systemd/system/postgresql-17.service \
        /etc/systemd/system/postgresql-17-infocentre.service

• Modification de ce dernier fichier avec le nouveau chemin :

Environment=PGDATA=/var/lib/pgsql/17/infocentre

• Option 1 : création d’une nouvelle instance vierge :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb postgresql-17-infocentre

• Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.

• Adaptation de /var/lib/pgsql/17/infocentre/postgresql.conf (port surtout).

• Commandes de maintenance de cette instance :

# systemctl [start|stop|reload|status] postgresql-17-infocentre
# systemctl [enable|disable] postgresql-17-infocentre

• Ouvrir le nouveau port dans le firewall au besoin.

Sur Debian / Ubuntu

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Référence : https://apt.postgresql.org/

Installation du dépôt communautaire :

L’installation des dépôts du PGDG est prévue dans le paquet Debian :

# apt update
# apt install -y  gnupg2  postgresql-common 
# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Ce dernier ordre créera le fichier du dépôt /etc/apt/sources.list.d/pgdg.list adapté à la distribution en place.

Installation de PostgreSQL 17 :

La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation :

Dans /etc/postgresql-common/createcluster.conf, paramétrer au moins les sommes de contrôle et les traces en anglais :

initdb_options = '--data-checksums --lc-messages=C'

Puis installer les paquets serveur et clients et leurs dépendances :

# apt install postgresql-17 postgresql-client-17

La première instance est automatiquement créée, démarrée et déclarée comme service à lancer au démarrage du système. Elle porte un nom (par défaut main).

Elle est immédiatement accessible par l’utilisateur système postgres.

Chemins :

Configuration

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance :

# pg_ctlcluster 17 main [start|stop|reload|status|restart]

Démarrage de l’instance avec le serveur :

C’est en place par défaut, et modifiable dans /etc/postgresql/17/main/start.conf.

Ouverture du firewall :

Debian et Ubuntu n’installent pas de firewall par défaut.

Statut des instances du serveur :

# pg_lsclusters

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Destruction d’une instance :

# pg_dropcluster 17 main

Création d’autres instances :

Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :

• optionnellement, /etc/postgresql-common/createcluster.conf permet de mettre en place tout d’entrée les checksums, les messages en anglais, le format des traces ou un emplacement séparé pour les journaux :

initdb_options = '--data-checksums --lc-messages=C'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'

• créer une instance :

# pg_createcluster 17 infocentre

Il est également possible de préciser certains paramètres du fichier postgresql.conf, voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut) :

# pg_createcluster 17 infocentre \
  --port=12345 \
  --datadir=/PGDATA/17/infocentre \
  --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \
  --  --data-checksums --waldir=/ssd/postgresql/17/infocentre/journaux

• adapter au besoin /etc/postgresql/17/infocentre/postgresql.conf ;

• démarrage :

# pg_ctlcluster 17 infocentre start

Accès à l’instance depuis le serveur même (toutes distributions)

Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire :

$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=#

Ce qui suit permet la connexion directement depuis un utilisateur du système :

Pour des tests (pas en production !), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf :

local   all             postgres                               trust

La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée :

dalibo:~$ psql -U postgres
psql (17.0)
Type "help" for help.
postgres=#

Une authentification par mot de passe est plus sécurisée :

• dans pg_hba.conf, paramétrer une authentification par mot de passe pour les accès depuis localhost (déjà en place sous Debian) :

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

(Ne pas oublier de recharger la configuration en cas de modification.)

• ajouter un mot de passe à l’utilisateur postgres de l’instance :

dalibo:~$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit

dalibo:~$ psql -h localhost -U postgres
Password for user postgres:
psql (17.0)
Type "help" for help.
postgres=#

• Pour se connecter sans taper le mot de passe à une instance, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion :

localhost:5432:*:postgres:motdepassetrèslong

Ce fichier doit être protégé des autres utilisateurs :

$ chmod 600 ~/.pgpass

• Pour n’avoir à taper que psql, on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc :

export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost

Rappels :

• en cas de problème, consulter les traces (dans /var/lib/pgsql/17/data/log ou /var/log/postgresql/) ;
• toute modification de pg_hba.conf ou postgresql.conf impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système :

root:~# systemctl reload postgresql-17

root:~# pg_ctlcluster 17 main reload

postgres:~$ psql -c 'SELECT pg_reload_conf()'

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m1_solutions.

Si ce n’est pas déjà fait, démarrer l’instance PostgreSQL.

Lister les processus du serveur PostgreSQL. Qu’observe-t-on ?

Se connecter à l’instance PostgreSQL.

Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on ?

Créer une nouvelle base de données nommée b0.

Se connecter à la base de données b0 et créer une table t1 avec une colonne id de type integer.

Insérer 10 millions de lignes dans la table t1 avec :

INSERT INTO t1 SELECT generate_series(1, 10000000) ;

Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on ?

Configurer la valeur du paramètre max_connections à 15.

Redémarrer l’instance PostgreSQL.

Vérifier que la modification de la valeur du paramètre max_connections a été prise en compte.

Se connecter 15 fois à l’instance PostgreSQL sans fermer les sessions, par exemple en lançant plusieurs fois :

psql -c 'SELECT pg_sleep(1000)' &

Se connecter une seizième fois à l’instance PostgreSQL. Qu’observe-t-on ?

Configurer la valeur du paramètre max_connections à sa valeur initiale.

Aller dans le répertoire des données de l’instance PostgreSQL. Lister les fichiers.

Aller dans le répertoire base. Lister les fichiers.

À quelle base est lié chaque répertoire présent dans le répertoire base ? (Voir l’oid de la base dans pg_database ou l’utilitaire en ligne de commande oid2name)

Créer une nouvelle base de données nommée b1. Qu’observe-t-on dans le répertoire base ?

Se connecter à la base de données b1. Créer une table t1 avec une colonne id de type integer.

Récupérer le chemin vers le fichier correspondant à la table t1 (il existe une fonction pg_relation_filepath).

Regarder la taille du fichier correspondant à la table t1. Pourquoi est-il vide ?

Insérer une ligne dans la table t1. Quelle taille fait le fichier de la table t1 ?

Insérer 500 lignes dans la table t1 avec generate_series. Quelle taille fait le fichier de la table t1 ?

Pourquoi cette taille pour simplement 501 entiers de 4 octets chacun ?

Processus

Si ce n’est pas déjà fait, démarrer l’instance PostgreSQL.

Sous Rocky Linux, CentOS ou Red Hat en tant qu’utilisateur root :

# systemctl start postgresql-15

Lister les processus du serveur PostgreSQL. Qu’observe-t-on ?

En tant qu’utilisateur postgres :

$ ps -o pid,cmd fx
    PID CMD
  27886 -bash
  28666  \_ ps -o pid,cmd fx
  27814 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
  27815  \_ postgres: logger
  27816  \_ postgres: checkpointer
  27817  \_ postgres: background writer
  27819  \_ postgres: walwriter
  27820  \_ postgres: autovacuum launcher
  27821  \_ postgres: logical replication launcher

Se connecter à l’instance PostgreSQL.

$ psql postgres
psql (15.0)
Type "help" for help.

postgres=#

Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on ?

$ ps -o pid,cmd fx
    PID CMD
  28746 -bash
  28779  \_ psql
  27886 -bash
  28781  \_ ps -o pid,cmd fx
  27814 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
  27815  \_ postgres: logger
  27816  \_ postgres: checkpointer
  27817  \_ postgres: background writer
  27819  \_ postgres: walwriter
  27820  \_ postgres: autovacuum launcher
  27821  \_ postgres: logical replication launcher
  28780  \_ postgres: postgres postgres [local] idle

Il y a un nouveau processus (ici PID 28780) qui va gérer l’exécution des requêtes du client psql.

Créer une nouvelle base de données nommée b0.

Depuis le shell, en tant que postgres :

$ createdb b0

Alternativement, depuis la session déjà ouverte dans psql :

CREATE DATABASE b0;

Se connecter à la base de données b0 et créer une table t1 avec une colonne id de type integer.

Pour se connecter depuis le shell :

psql b0

ou depuis la session psql :

\c b0

Création de la table :

CREATE TABLE t1 (id integer);

Insérer 10 millions de lignes dans la table t1 avec :

INSERT INTO t1 SELECT generate_series(1, 10000000) ;

INSERT INTO t1 SELECT generate_series(1, 10000000);

INSERT 0 10000000

Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on ?

$ ps -o pid,cmd fx
    PID CMD
  28746 -bash
  28779  \_ psql
  27886 -bash
  28781  \_ ps -o pid,cmd fx
  27814 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
  27815  \_ postgres: logger
  27816  \_ postgres: checkpointer
  27817  \_ postgres: background writer
  27819  \_ postgres: walwriter
  27820  \_ postgres: autovacuum launcher
  27821  \_ postgres: logical replication launcher
  28780  \_ postgres: postgres postgres [local] INSERT

Le processus serveur exécute l’INSERT, ce qui se voit au niveau du nom du processus. Seul est affiché le dernier ordre SQL (ie le mot INSERT et non pas la requête complète).

Configurer la valeur du paramètre max_connections à 15.

La première méthode est d’ouvrir le fichier de configuration postgresql.conf et de modifier la valeur du paramètre max_connections :

max_connections = 15

La seconde méthode se fait directement depuis psql en tant que superutilisateur :

ALTER SYSTEM SET max_connections TO 15 ;

Ce dernier ordre fera apparaître une ligne dans le fichier /var/lib/pgsql/15/data/postgresql.auto.conf.

Dans les deux cas, la prise en compte n’est pas automatique. Pour ce paramètre, il faut redémarrer l’instance PostgreSQL.

Redémarrer l’instance PostgreSQL.

En tant qu’utilisateur root :

# systemctl restart postgresql-15

Vérifier que la modification de la valeur du paramètre max_connections a été prise en compte.

postgres=# SHOW max_connections ;

 max_connections
-----------------
 15

Se connecter 15 fois à l’instance PostgreSQL sans fermer les sessions, par exemple en lançant plusieurs fois :

psql -c 'SELECT pg_sleep(1000)' &

Il est possible de le faire manuellement ou de l’automatiser avec ce petit script shell :

$ for i in $(seq 1 15); do psql -c "SELECT pg_sleep(1000);" postgres & done
[1] 998
[2] 999
...
[15] 1012

Se connecter une seizième fois à l’instance PostgreSQL. Qu’observe-t-on ?

$ psql postgres
psql: FATAL:  sorry, too many clients already

Il est impossible de se connecter une fois que le nombre de connexions a atteint sa limite configurée avec max_connections. Il faut donc attendre que les utilisateurs se déconnectent pour accéder de nouveau au serveur.

Configurer la valeur du paramètre max_connections à sa valeur initiale.

Si le fichier de configuration postgresql.conf avait été modifié, restaurer la valeur du paramètre max_connections à 100.

Si ALTER SYSTEM a été utilisée, il faudrait pouvoir entrer dans psql :

ALTER SYSTEM RESET max_connections ;

Mais cela ne fonctionne pas si toutes les connexions réservées à l’utilisateur ont été consommées (paramètre superuser_reserved_connections, par défaut à 3). Dans ce cas, il n’y a plus qu’à aller dans le fichier de configuration /var/lib/pgsql/15/data/postgresql.auto.conf, pour supprimer la ligne avec le paramètre max_connections avant de redémarrer l’instance PostgreSQL.

Toutefois si l’instance est démarrée et qu’il est encore possible de s’y connecter, le plus propre est ceci :

ALTER SYSTEM RESET max_connections ;

Puis redémarrer PostgreSQL : toutes les connexions en cours vont être coupées.

# systemctl restart postgresql-15
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
[...]
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
connection to server was lost

Il est à présent possible de se reconnecter. Vérifier que cela a été pris en compte :

postgres=# SHOW max_connections ;

 max_connections
-----------------
 100

Fichiers

Aller dans le répertoire des données de l’instance PostgreSQL. Lister les fichiers.

En tant qu’utilisateur système postgres :

 echo $PGDATA
/var/lib/pgsql/15/data

$ cd $PGDATA

$ ls -al
total 68
drwx------. 7 postgres postgres    59 Nov  4 09:55 base
-rw-------. 1 postgres postgres    30 Nov  4 10:38 current_logfiles
drwx------. 2 postgres postgres  4096 Nov  4 10:38 global
drwx------. 2 postgres postgres    58 Nov  4 07:58 log
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_commit_ts
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_dynshmem
-rw-------. 1 postgres postgres  4658 Nov  4 09:50 pg_hba.conf
-rw-------. 1 postgres postgres  1685 Nov  3 14:16 pg_ident.conf
drwx------. 4 postgres postgres    68 Nov  4 10:38 pg_logical
drwx------. 4 postgres postgres    36 Nov  3 14:11 pg_multixact
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_notify
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_replslot
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_serial
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_snapshots
drwx------. 2 postgres postgres     6 Nov  4 10:38 pg_stat
drwx------. 2 postgres postgres    35 Nov  4 10:38 pg_stat_tmp
drwx------. 2 postgres postgres    18 Nov  3 14:11 pg_subtrans
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_tblspc
drwx------. 2 postgres postgres     6 Nov  3 14:11 pg_twophase
-rw-------. 1 postgres postgres     3 Nov  3 14:11 PG_VERSION
drwx------. 3 postgres postgres    92 Nov  4 09:55 pg_wal
drwx------. 2 postgres postgres    18 Nov  3 14:11 pg_xact
-rw-------. 1 postgres postgres    88 Nov  3 14:11 postgresql.auto.conf
-rw-------. 1 postgres postgres 29475 Nov  4 09:36 postgresql.conf
-rw-------. 1 postgres postgres    58 Nov  4 10:38 postmaster.opts
-rw-------. 1 postgres postgres   104 Nov  4 10:38 postmaster.pid

Aller dans le répertoire base. Lister les fichiers.

$ cd base

$ ls -al
total 60
drwx------  8 postgres postgres   78 Nov  4 16:21 .
drwx------ 20 postgres postgres 4096 Nov  4 15:33 ..
drwx------. 2 postgres postgres 8192 Nov  4 10:38 1
drwx------. 2 postgres postgres 8192 Nov  4 09:50 16404
drwx------. 2 postgres postgres 8192 Nov  3 14:11 4
drwx------. 2 postgres postgres 8192 Nov  4 10:39 5
drwx------  2 postgres postgres    6 Nov  3 15:58 pgsql_tmp

À quelle base est lié chaque répertoire présent dans le répertoire base ? (Voir l’oid de la base dans pg_database ou l’utilitaire en ligne de commande oid2name)

Chaque répertoire correspond à une base de données. Le numéro indiqué est un identifiant système (OID). Il existe deux moyens pour récupérer cette information :

• directement dans le catalogue système pg_database :

$ psql postgres

psql (15.0)
Type "help" for help.

postgres=# SELECT oid, datname FROM pg_database ORDER BY oid::text;

  oid  |  datname
-------+-----------
     1 | template1
 16404 | b0
     4 | template0
     5 | postgres

• avec l’outil oid2name (à installer au besoin via le paquet postgresql15-contrib) :

$ /usr/pgsql-15/bin/oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16404             b0  pg_default
      5       postgres  pg_default
      4      template0  pg_default
      1      template1  pg_default

Donc ici, le répertoire 1 correspond à la base template1, et le répertoire 5 à la base postgres (ces nombres peuvent changer suivant l’installation).

Créer une nouvelle base de données nommée b1. Qu’observe-t-on dans le répertoire base ?

$ createdb b1

$ ls -al
total 60
drwx------  8 postgres postgres   78 Nov  4 16:21 .
drwx------ 20 postgres postgres 4096 Nov  4 15:33 ..
drwx------. 2 postgres postgres 8192 Nov  4 10:38 1
drwx------. 2 postgres postgres 8192 Nov  4 09:50 16404
drwx------. 2 postgres postgres 8192 Nov  4 09:55 16405
drwx------. 2 postgres postgres 8192 Nov  3 14:11 4
drwx------. 2 postgres postgres 8192 Nov  4 10:39 5
drwx------  2 postgres postgres    6 Nov  3 15:58 pgsql_tmp

Un nouveau sous-répertoire est apparu, nommé 16405. Il correspond bien à la base b1 d’après oid2name.

Se connecter à la base de données b1. Créer une table t1 avec une colonne id de type integer.

$ psql b1
psql (15.0)
Type "help" for help.

b1=# CREATE TABLE t1(id integer);

CREATE TABLE

Récupérer le chemin vers le fichier correspondant à la table t1 (il existe une fonction pg_relation_filepath).

La fonction a pour définition :

b1=# \df pg_relation_filepath
                                 List of functions
   Schema   |         Name         | Result data type | Argument data types | Type
------------+----------------------+------------------+---------------------+------
 pg_catalog | pg_relation_filepath | text             | regclass            | func

L’argument regclass peut être l’OID de la table, ou son nom.

L’emplacement du fichier sur le disque est donc :

b1=# SELECT current_setting('data_directory') || '/' || pg_relation_filepath('t1')
     AS chemin;

                 chemin
-----------------------------------------
 /var/lib/pgsql/15/data/base/16405/16406

Regarder la taille du fichier correspondant à la table t1. Pourquoi est-il vide ?

$ ls -l /var/lib/pgsql/15/data/base/16393/16398
-rw-------. 1 postgres postgres 0 Nov  4 10:42 /var/lib/pgsql/15/data/base/16405/16406

La table vient d’être créée. Aucune donnée n’a encore été ajoutée. Les métadonnées se trouvent dans d’autres tables (des catalogues systèmes). Donc il est logique que le fichier soit vide.

Insérer une ligne dans la table t1. Quelle taille fait le fichier de la table t1 ?

b1=# INSERT INTO t1 VALUES (1);
INSERT 0 1

$ ls -l /var/lib/pgsql/15/data/base/16393/16398
-rw-------. 1 postgres postgres 8192 Nov  4 12:40 /var/lib/pgsql/15/data/base/16405/16406

Il fait à présent 8 ko. En fait, PostgreSQL travaille par blocs de 8 ko. Si une ligne ne peut être placée dans un espace libre d’un bloc existant, un bloc entier est ajouté à la table.

Vous pouvez consulter le fichier avec la commande hexdump -x <nom du fichier> (faites un CHECKPOINT avant pour être sûr qu’il soit écrit sur le disque).

Insérer 500 lignes dans la table t1 avec generate_series. Quelle taille fait le fichier de la table t1 ?

b1=# INSERT INTO t1 SELECT generate_series(1, 500);
INSERT 0 500

$ ls -l /var/lib/pgsql/15/data/base/16393/16398
-rw-------. 1 postgres postgres 24576 Nov  4 12:41 /var/lib/pgsql/15/data/base/16405/16406

Le fichier fait maintenant 24 ko, soit 3 blocs de 8 ko.

Pourquoi cette taille pour simplement 501 entiers de 4 octets chacun ?

Nous avons enregistré 501 entiers dans la table. Un entier de type int4 prend 4 octets. Donc nous avons 2004 octets de données utilisateurs. Et pourtant, nous arrivons à un fichier de 24 ko.

En fait, PostgreSQL enregistre aussi dans chaque bloc des informations systèmes en plus des données utilisateurs. Chaque bloc contient un en-tête, des pointeurs, et l’ensemble des lignes du bloc. Chaque ligne contient les colonnes utilisateurs mais aussi des colonnes système. La requête suivante permet d’en savoir plus sur les colonnes présentes dans la table :

b1=# SELECT CASE WHEN attnum<0 THEN 'systeme' ELSE 'utilisateur' END AS type,
     attname, attnum, typname, typlen,
     sum(typlen) OVER (PARTITION BY attnum<0) AS longueur_tot
     FROM pg_attribute a
     JOIN pg_type t ON t.oid=a.atttypid
     WHERE attrelid ='t1'::regclass
     ORDER BY attnum;

    type     | attname  | attnum | typname | typlen | longueur_tot
-------------+----------+--------+---------+--------+--------------
 systeme     | tableoid |     -6 | oid     |      4 |           26
 systeme     | cmax     |     -5 | cid     |      4 |           26
 systeme     | xmax     |     -4 | xid     |      4 |           26
 systeme     | cmin     |     -3 | cid     |      4 |           26
 systeme     | xmin     |     -2 | xid     |      4 |           26
 systeme     | ctid     |     -1 | tid     |      6 |           26
 utilisateur | id       |      1 | int4    |      4 |            4

Vous pouvez voir ces colonnes système en les appelant explicitement :

SELECT cmin, cmax, xmin, xmax, ctid, *
FROM t1 ;

L’en-tête de chaque ligne pèse 26 octets dans le cas général. Dans notre cas très particulier avec une seule petite colonne, c’est très défavorable mais ce n’est généralement pas le cas.

Avec 501 lignes de 26+4 octets, nous obtenons 15 ko. Chaque bloc possède quelques informations de maintenance : nous dépassons alors 16 ko, ce qui explique pourquoi nous en sommes à 24 ko (3 blocs).

Ces paramètres sont en lecture seule, mais peuvent être consultés par la commande SHOW, ou en interrogeant la vue pg_settings. Il est possible aussi d’obtenir l’information via la commande pg_controldata.

• block_size est la taille d’un bloc de données de la base, par défaut 8192 octets ;
• wal_block_size est la taille d’un bloc de journal, par défaut 8192 octets ;
• segment_size est la taille maximum d’un fichier de données, par défaut 1 Go ;
• wal_segment_size est la taille d’un fichier de journal de transactions (WAL), par défaut 16 Mo.

Ces paramètres sont tous fixés à la compilation, sauf wal_segment_size à partir de la version 11 : initdb accepte alors l’option --wal-segsize et l’on peut monter la taille des journaux de transactions à 1 Go. Cela n’a d’intérêt que pour des instances générant énormément de journaux.

Recompiler avec une taille de bloc de 32 ko s’est déjà vu sur de très grosses installations (comme le rapporte par exemple Christophe Pettus (San Francisco, 2023)) avec un shared_buffers énorme, mais cette configuration est très peu testée, nous la déconseillons dans le cas général.

Les fichiers de configuration sont habituellement les 4 suivants :

• postgresql.conf : il contient une liste de paramètres, sous la forme paramètre=valeur ;
• pg_hba.conf : il contient les règles d’authentification à la base.
• pg_ident.conf : il complète pg_hba.conf, quand nous déciderons de nous reposer sur un mécanisme d’authentification extérieur à la base (identification par le système ou par un annuaire par exemple) ;
• postgresql.auto.conf : il stocke les paramètres de configuration fixés en utilisant la commande ALTER SYSTEM et surcharge donc postgresql.conf.

C’est le fichier le plus important. Il contient le paramétrage de l’instance. PostgreSQL le cherche au démarrage dans le PGDATA. Par défaut, dans les versions compilées, ou depuis les paquets sur Red Hat, CentOS ou Rocky Linux, il sera dans le répertoire principal avec les données (/var/lib/pgsql/15/data/postgresql.conf par exemple). Debian le place dans /etc (/etc/postgresql/15/main/postgresql.conf pour l’instance par défaut).

Dans le doute, il est possible de consulter la valeur du paramètre config_file, ici dans la configuration par défaut sur Rocky Linux :

# SHOW config_file;

               config_file
---------------------------------------------
 /var/lib/postgresql/15/data/postgresql.conf

Ce fichier contient un paramètre par ligne, sous le format :

clé = valeur

Les commentaires commencent par « # » (croisillon) et les chaînes de caractères doivent être encadrées de « ’ » (single quote). Par exemple :

data_directory = '/var/lib/postgresql/15/main'
listen_addresses = 'localhost'
port = 5432
shared_buffers = 128MB

Nous allons en effet voir que l’on peut les surcharger.

Le paramétrage ne dépend pas seulement du contenu de postgresql.conf.

Nous pouvons inclure d’autres fichiers depuis postgresql.conf grâce à l’une de ces directives :

include = 'nom_fichier'
include_if_exists = 'nom_fichier'
include_dir = 'répertoire'       # contient des fichiers .conf

Le ou les fichiers indiqués sont alors inclus à l’endroit où la directive est positionnée. Avec include, si le fichier n’existe pas, une erreur FATAL est levée ; au contraire la directive include_if_exists permet de ne pas s’arrêter si le fichier n’existe pas. Ces directives permettent notamment des ajustements de configuration propres à plusieurs machines d’un ensemble primaire/secondaires dont le postgresql.conf de base est identique, ou de gérer la configuration hors de postgresql.conf.

Si des paramètres sont répétés dans postgresql.conf, éventuellement suite à des inclusions, la dernière occurrence écrase les précédentes. Si un paramètre est absent, la valeur par défaut s’applique.

Ces paramètres peuvent être surchargés par le fichier postgresql.auto.conf, qui contient le résultat des commandes de ce type :

ALTER SYSTEM SET paramètre = valeur ;

Ce fichier est principalement utilisé par les administrateurs et les outils qui n’ont pas accès au système de fichiers du serveur. (À partir de PostgreSQL 17, l’utilisation de ALTER SYSTEM peut être interdite, en passant le paramètre allow_alter_system à off. Le but est d’éviter des erreurs de manipulation quand la configuration est gérée par un outil extérieur, comme Ansible ou Patroni. À noter qu’un superutilisateur malveillant saura tout de même contourner cette limite.)

Si des options sont passées directement en arguments à pg_ctl (situation rare), elles seront prises en compte en priorité par rapport à celles de ces fichiers de configuration.

Il est possible de surcharger les options modifiables à chaud par utilisateur, par base, et par combinaison « utilisateur+base », avec par exemple :

ALTER ROLE nagios SET log_min_duration_statement TO '1min';
ALTER DATABASE dwh SET work_mem TO '1GB';
ALTER ROLE patron IN DATABASE dwh SET work_mem TO '2GB';

Ces surcharges sont visibles dans la table pg_db_role_setting ou via la commande \drds de psql.

Ensuite, un utilisateur peut changer à volonté les valeurs de beaucoup de paramètres au sein d’une session :

SET parametre = valeur ;

ou même juste au sein d’une transaction :

SET LOCAL parametre = valeur ;

Au final, l’ordre des surcharges est le suivant :

paramètre par défaut
  -> postgresql.conf
    -> ALTER SYSTEM SET (postgresql.auto.conf)
      -> option de pg_ctl / postmaster
        -> paramètre par base
          -> paramètre par rôle
            -> paramètre base+rôle
              -> paramètre dans la chaîne de connexion
                -> paramètre de session (SET)
                  -> paramètre de transaction (SET LOCAL)

À titre d’exemple, voici ce qu’il se passe en appliquant la requête suivante :

SET client_min_messages = debug2;

La meilleure source d’information sur les valeurs actives est la vue pg_settings :

SELECT name,source,context,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('client_min_messages', 'log_checkpoints', 'wal_segment_size');

        name         |  source  | context  | setting  | boot_val | reset_val
---------------------+----------+----------+----------+----------+-----------
 client_min_messages | default  | user     | debug2   | notice   | notice
 log_checkpoints     | default  | sighup   | off      | off      | off
 wal_segment_size    | override | internal | 16777216 | 16777216 | 16777216

Nous constatons par exemple que, dans la session ayant effectué la requête, la valeur du paramètre client_min_messages a été modifiée à la valeur debug2. Nous pouvons aussi voir le contexte dans lequel le paramètre est modifiable : le client_min_messages est modifiable par l’utilisateur dans sa session. Le log_checkpoints seulement par sighup, c’est-à-dire par un pg_ctl reload, et le wal_segment_size n’est pas modifiable après l’initialisation de l’instance.

De nombreuses autres colonnes sont disponibles dans pg_settings, comme une description détaillée du paramètre, l’unité de la valeur, ou le fichier et la ligne d’où provient le paramètre. Le champ pending_restart indique si un paramètre a été modifié mais attend encore un redémarrage pour être appliqué.

Il existe aussi une vue pg_file_settings, qui indique la configuration présente dans les fichiers de configuration (mais pas forcément active !). Elle peut être utile lorsque la configuration est répartie dans plusieurs fichiers. Par exemple, suite à un ALTER SYSTEM, les paramètres sont ajoutés dans postgresql.auto.conf mais un rechargement de la configuration n’est pas forcément suffisant pour qu’ils soient pris en compte :

ALTER SYSTEM SET work_mem TO '16MB' ;
ALTER SYSTEM SET max_connections TO 200 ;

SELECT pg_reload_conf() ;

 pg_reload_conf
----------------
 t

SELECT * FROM pg_file_settings
WHERE name IN ('work_mem','max_connections')
ORDER BY name ;

-[ RECORD 1 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.conf
sourceline | 64
seqno      | 2
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.auto.conf
sourceline | 4
seqno      | 17
name       | max_connections
setting    | 200
applied    | f
error      | setting could not be applied
-[ RECORD 3 ]-------------------------------------------------
sourcefile | /var/lib/postgresql/15/data/postgresql.auto.conf
sourceline | 3
seqno      | 16
name       | work_mem
setting    | 16MB
applied    | t
error      |

PostgreSQL offre une certaine granularité dans sa configuration, ainsi certains paramètres peuvent être surchargés par rapport au fichier postgresql.conf. Il est utile de connaître l’ordre de précédence. Par exemple, un utilisateur peut spécifier un paramètre dans sa session avec l’ordre SET, celui-ci sera prioritaire par rapport à la configuration présente dans le fichier postgresql.conf.

postgresql.conf contient environ 300 paramètres. Il est séparé en plusieurs sections, dont les plus importantes figurent ci-dessous. Il n’est pas question de les détailler toutes.

La plupart des paramètres ne sont jamais modifiés. Les défauts sont souvent satisfaisants pour une petite installation. Les plus importants sont supposés acquis (au besoin, voir la formation DBA1).

Les principales sections sont :

Connections and authentication

S’y trouveront les classiques listen_addresses, port, max_connections, password_encryption, ainsi que les paramétrages TCP (keepalive) et SSL.

Resource usage (except WAL)

Cette partie fixe des limites à certaines consommations de ressources.

Sont normalement déjà bien connus shared_buffers, work_mem et maintenance_work_mem (qui seront couverts extensivement plus loin).

On rencontre ici aussi le paramétrage du VACUUM (pas directement de l’autovacuum !), du background writer, du parallélisme dans les requêtes.

Write-Ahead Log

Les journaux de transaction sont gérés ici. Cette partie sera également détaillée dans un autre module.

Tout est prévu pour faciliter la mise en place d’une réplication sans avoir besoin de modifier cette partie sur le primaire.

Dans la partie Archiving, l’archivage des journaux peut être activé pour une sauvegarde PITR ou une réplication par log shipping.

Depuis la version 12, tous les paramètres de restauration (qui peuvent servir à la réplication) figurent aussi dans les sections Archive Recovery et Recovery Target. Auparavant, ils figuraient dans un fichier recovery.conf séparé.

Replication

Cette partie fournit le nécessaire pour alimenter un secondaire en réplication par streaming, physique ou logique.

Ici encore, depuis la version 12, l’essentiel du paramétrage nécessaire à un secondaire physique ou logique est intégré dans ce fichier.

Query tuning

Les paramètres qui peuvent influencer l’optimiseur sont à définir dans cette partie, notamment seq_page_cost et random_page_cost en fonction des disques, et éventuellement le parallélisme, le niveau de finesse des statistiques, le JIT…

Reporting and logging

Si le paramétrage par défaut des traces ne convient pas, le modifier ici. Il faudra généralement augmenter leur verbosité. Quelques paramètres log_* figurent dans d’autres sections.

Autovacuum

L’autovacuum fonctionne généralement convenablement, et des ajustements se font généralement table par table. Il arrive cependant que certains paramètres doivent être modifiés globalement.

Client connection defaults

Cette partie un peu fourre-tout définit le paramétrage au niveau d’un client : langue, fuseau horaire, extensions à précharger, tablespaces par défaut…

Lock management

Les paramètres de cette section sont rarement modifiés.

L’authentification est paramétrée au moyen du fichier pg_hba.conf. Dans ce fichier, pour une tentative de connexion à une base donnée, pour un utilisateur donné, pour un transport (IP, IPV6, Socket Unix, SSL ou non), et pour une source donnée, ce fichier permet de spécifier le mécanisme d’authentification attendu.

Si le mécanisme d’authentification s’appuie sur un système externe (LDAP, Kerberos, Radius…), des tables de correspondances entre utilisateur de la base et utilisateur demandant la connexion peuvent être spécifiées dans pg_ident.conf.

Ces noms de fichiers ne sont que les noms par défaut. Ils peuvent tout à fait être remplacés en spécifiant de nouvelles valeurs de hba_file et ident_file dans postgresql.conf (les installations Red Hat et Debian utilisent là aussi des emplacements différents, comme pour postgresql.conf).

Leur utilisation est décrite dans notre première formation.

Par défaut, PostgreSQL se charge du placement des objets sur le disque, dans son répertoire des données, mais il est possible de créer des répertoires de stockage supplémentaires, nommés tablespaces.

Utilité des tablespaces

Un tablespace, vu de PostgreSQL, est un espace de stockage des objets (tables et index principalement). Son rôle est purement physique, il n’a pas à être utilisé pour une séparation logique des tables (c’est le rôle des bases et des schémas), encore moins pour gérer des droits.

Pour le système d’exploitation, il s’agit juste d’un répertoire, déclaré ainsi :

CREATE TABLESPACE ssd LOCATION '/mnt/ssd/pg';

L’idée est de séparer physiquement les objets suivant leur utilisation. Les cas d’utilisation des tablespaces dans PostgreSQL sont :

• l’ajout d’un disque après saturation de la partition du PGDATA sans possibilité de l’étendre au niveau du système (par LVM ou dans la baie de stockage, par exemple) ;
• la répartition des entrées-sorties… si le SAN ou la virtualisation permet encore d’agir à ce niveau ;
• et notamment la séparation des index et des tables, pour répartir les écritures ;
• le déport des fichiers temporaires vers un tablespace dédié, pour la performance ou éviter qu’ils saturent le PGDATA ;
• la séparation entre données froides et chaudes sur des disques de performances différentes, ou encore des index et des tables ;
• les quotas : PostgreSQL ne disposant pas d’un système de quotas, dédier une partition entière d’une taille précise à un tablespace est un contournement ; une transaction voulant étendre un fichier sera alors annulée avec l’erreur cannot extend file.

Un tablespace n’est pas adapté à une séparation logique des objets. Si vous tenez à distinguer les fichiers de chaque base sans besoin physique, rappelez-vous que PostgreSQL crée déjà un sous-répertoire par base de données dans PGDATA/base/.

PostgreSQL ne connaît pas de notion de tablespace en lecture seule, ni de tablespace transportable entre deux bases ou deux instances.

Emplacement des tablespaces

Il y a quelques pièges à éviter à la définition d’un tablespace :

Positionnez toujours les données dans un sous-répertoire, par exemple dans /mnt/ssd/pg plutôt que directement dans le point de montage /mnt/ssd. (Voir Utilisation de systèmes de fichiers secondaires dans la documentation officielle, ou le bug à l’origine de ce conseil.)

Si ce conseil n’est pas suivi, PostgreSQL crée le tablespace mais renvoie un avertissement :

WARNING:  tablespace location should not be inside the data directory
CREATE TABLESPACE

Il est aussi déconseillé de mettre le numéro de version de PostgreSQL dans le chemin du tablespace. PostgreSQL le gère déjà à l’intérieur du tablespace. Cela évite des incohérences dans les noms des chemins si vous migrez plus tard avec pg_upgrade.

Le répertoire du tablespace doit exister et les accès ouverts et restreints à l’utilisateur système sous lequel tourne l’instance (en général postgres sous Linux, Network Service sous Windows) :

# /mnt/ssd/ doit exister (point de montage d'un SSD par exemple)
 # chown postgres:postgres /mnt/ssd/pg
 # chmod 700 /mnt/ssd/pg

Les ordres SQL plus haut permettent de :

• créer un tablespace simplement en indiquant son emplacement dans le système de fichiers du serveur ;
• créer une base de données dont le tablespace par défaut sera celui indiqué ;
• modifier le tablespace par défaut d’une base ;
• donner le droit de créer des tables dans un tablespace à un utilisateur (c’est nécessaire avant de l’utiliser) ;
• créer une table dans un tablespace ;
• déplacer une table dans un tablespace ;
• déplacer un index dans un tablespace.

Quelques choses à savoir :

Les tablespaces des tables sont visibles dans la vue système pg_tables, dans \d+ sous psql, et dans pg_indexes pour les index :

SELECT schemaname, indexname, tablespace
FROM   pg_indexes
WHERE  tablename = 'ma_table';

 schemaname |  indexname   | tablespace
------------+--------------+------------
 public     | matable_idx  | chaud
 public     | matable_pkey |

Tablespaces de données

Le paramètre default_tablespace permet d’utiliser un autre tablespace que celui par défaut dans PGDATA. En plus du postgresql.conf, il peut être défini au niveau rôle, base, ou le temps d’une session :

ALTER DATABASE erp_prod     SET default_tablespace TO ssd ;   -- base
ALTER DATABASE erp_archives SET default_tablespace TO froid ; -- base
ALTER ROLE etl              SET default_tablespace TO ssd ; -- niveau rôle
ALTER ROLE audit IN DATABASE erp_prod SET default_tablespace TO froid ; -- niveau rôle dans une base
SET default_tablespace TO ssd ; -- session

Tablespaces de tri

Les opérations de tri et les tables temporaires peuvent être déplacées vers un ou plusieurs tablespaces dédiés grâce au paramètre temp_tablespaces. Le premier intérêt est de dédier aux tris une partition rapide (SSD, disque local…). Un autre est de ne plus risquer de saturer la partition du PGDATA en cas de fichiers temporaires énormes dans base/pgsql_tmp/.

Il faudra ouvrir les droits aux utilisateurs ainsi :

GRANT CREATE ON TABLESPACE  ssd_tri1  TO  dupont ;

Plusieurs tablespaces temporaires peuvent être paramétrés. Noter que la déclaration se fait sans guillemet. Chaque transaction en choisira un de façon aléatoire à la création d’un objet temporaire, puis utilisera alternativement les autres pour chaque nouveau fichier. C’est un bon moyen de lisser la charge :

• lors d’une requête, un gros fichier de tri peut s’étaler sur plusieurs tablespaces de tri, car il se découpe au besoin en fichiers de 1 Go, voire moins ;
• les différentes tables temporaires d’une même session se répartiront dans les différents tablespaces temporaires ; par contre les différents fichiers d’une grosse table temporaire resteront ensemble dans le même tablespace (une table n’a qu’un tablespace), et ses index avec elle par défaut.

Dans le cas de disques de performances différentes, il faut adapter les paramètres concernés aux caractéristiques du tablespace si la valeur par défaut ne convient pas. Ce sont des paramètres classiques qui ne seront pas décrits en détail ici :

• seq_page_cost (coût d’accès à un bloc pendant un parcours, défaut 1) ;
• random_page_cost (coût d’accès à un bloc isolé, défaut 4) ;
• effective_io_concurrency (nombre d’I/O simultanées, défaut 1) et maintenance_io_concurrency (idem, pour une opération de maintenance, défaut 10).

Notamment :

effective_io_concurrency a pour but d’indiquer le nombre d’opérations disques possibles en même temps pour un client (prefetch). Seuls les parcours Bitmap Scan sont impactés par ce paramètre. Selon la documentation, pour un système disque utilisant un RAID matériel, il faut le configurer en fonction du nombre de disques utiles dans le RAID (n s’il s’agit d’un RAID 1, n-1 s’il s’agit d’un RAID 5 ou 6, n/2 s’il s’agit d’un RAID 10). Avec du SSD, il est possible de monter à plusieurs centaines, étant donné la rapidité de ce type de disque. Ces valeurs doivent être réduites sur un système très chargé. Une valeur excessive mène au gaspillage de CPU. Le défaut deffective_io_concurrency est seulement de 1, et la valeur maximale est 1000.

(Avant la version 13, le principe était le même, mais la valeur exacte de ce paramètre devait être 2 à 5 fois plus basse comme le précise la formule des notes de version de la version 13.)

maintenance_io_concurrency est similaire à effective_io_concurrency, mais pour les opérations de maintenance. Celles‑ci peuvent ainsi se voir accorder plus de ressources qu’une simple requête. Il faut penser à le monter aussi si on adapte effective_io_concurrency.

Par exemple, un système paramétré pour des disques classiques aura comme paramètres par défaut :

random_page_cost = 4
effective_io_concurrency = 1
maintenance_io_concurrency = 10

et un tablespace sur un SSD les surchargera ainsi :

ALTER TABLESPACE ssd SET ( random_page_cost = 1 );
ALTER TABLESPACE ssd SET ( effective_io_concurrency   = 500,
                           maintenance_io_concurrency = 500 ) ;

Le processus postmaster est en écoute sur les différentes sockets déclarées dans la configuration. Cette déclaration se fait au moyen des paramètres suivants :

• port : le port TCP. Il sera aussi utilisé dans le nom du fichier socket Unix (par exemple : /tmp/.s.PGSQL.5432 ou /var/run/postgresql/.s.PGSQL.5432 selon les distributions) ;
• listen_adresses : la liste des adresses IP du serveur auxquelles s’attacher ;
• unix_socket_directories : le répertoire où sera stocké la socket Unix ;
• unix_socket_group : le groupe (système) autorisé à accéder à la socket Unix ;
• unix_socket_permissions : les droits d’accès à la socket Unix.

Les connexions par socket Unix ne sont possibles sous Windows qu’à partir de la version 13.

Il faut bien faire la distinction entre session TCP et session de PostgreSQL. Si une session TCP sert de support à une requête particulièrement longue, laquelle ne renvoie pas de données pendant plusieurs minutes, alors le firewall peut considérer la session inactive, même si le statut du backend dans pg_stat_activity est active.

Il est possible de préciser les propriétés keepalive des sockets TCP, pour peu que le système d’exploitation les gère. Le keepalive est un mécanisme de maintien et de vérification des sessions TCP, par l’envoi régulier de messages de vérification sur une session TCP inactive. tcp_keepalives_idle est le temps en secondes d’inactivité d’une session TCP avant l’envoi d’un message de keepalive. tcp_keepalives_interval est le temps entre un keepalive et le suivant, en cas de non-réponse. tcp_keepalives_count est le nombre maximum de paquets sans réponse accepté avant que la session ne soit déclarée comme morte.

Les valeurs par défaut (0) reviennent à utiliser les valeurs par défaut du système d’exploitation.

Le mécanisme de keepalive a deux intérêts :

• il permet de détecter les clients déconnectés même si ceux-ci ne notifient pas la déconnexion (plantage du système d’exploitation, fermeture de la session par un firewall…) ;
• il permet de maintenir une session active au travers de firewalls, qui seraient fermées sinon : la plupart des firewalls ferment une session inactive après 5 minutes, alors que la norme TCP prévoit plusieurs jours.

Un autre cas peut survenir. Parfois, un client lance une requête. Cette requête met du temps à s’exécuter et le client quitte la session avant de récupérer les résultats. Dans ce cas, le serveur continue à exécuter la requête et ne se rendra compte de l’absence du client qu’au moment de renvoyer les premiers résultats. Depuis la version 14, il est possible d’autoriser la vérification de la connexion pendant l’exécution d’une requête. Il faut pour cela définir la durée d’intervalle entre deux vérifications avec le paramètre client_connection_check_interval. Par défaut, cette option est désactivée et sa valeur est de 0.

Il existe des options pour activer SSL et le paramétrer. ssl vaut on ou off, ssl_ciphers est la liste des algorithmes de chiffrement autorisés, et ssl_renegotiation_limit le volume maximum de données échangées sur une session avant renégociation entre le client et le serveur. Le paramétrage SSL impose aussi la présence d’un certificat. Pour plus de détails, consultez la documentation officielle.

Les différents processus de PostgreSQL collectent des statistiques d’activité qui ont pour but de mesurer l’activité de la base. Notamment :

• combien de fois cette table a-t-elle été parcourue séquentiellement ?
• combien de blocs ont été trouvés dans le cache pour ce parcours d’index, et combien ont dû être demandés au système d’exploitation ?
• Quelles sont les requêtes en cours d’exécution ?
• Combien de buffers ont été écrits par le processus background writer ? Par les processus backend eux-mêmes ? durant un checkpoint ?

Il ne faut pas confondre les statistiques d’activité avec celles sur les données (taille des tables, des enregistrements, fréquences des valeurs…), qui sont à destination de l’optimiseur de requête !

Pour des raisons de performance, ces stastistiques restent en mémoire (ou dans des fichiers temporaires jusque PostgreSQL 14) et ne sont stockées durablement qu’en cas d’arrêt propre. Un arrêt brutal implique donc leur réinitialisation !

Voici les paramètres concernés par cette collecte d’informations.

track_activities (on par défaut) précise si les processus doivent mettre à jour leur activité dans pg_stat_activity.

track_counts (on par défaut) indique que les processus doivent collecter des informations sur leur activité. Il est vital pour le déclenchement de l’autovacuum.

track_activity_query_size est la taille maximale du texte de requête pouvant être stocké dans pg_stat_activity. 1024 caractères est un défaut souvent insuffisant, à monter vers 10 000 si les requêtes sont longues, voire plus ; cette modification nécessite un redémarrage vu qu’elle touche au dimensionnement de la mémoire partagée.

Disponible depuis la version 14, compute_query_id permet d’activer le calcul de l’identifiant de la requête. Ce dernier sera visible dans le champ query_id de la vue pg_stat_activity, ainsi que dans les traces.

track_io_timing (off par défaut) précise si les processus doivent collecter des informations de chronométrage sur les lectures et écritures, pour compléter les champs blk_read_time et blk_write_time des vues pg_stat_database et pg_stat_statements, ainsi que les plans d’exécutions appelés avec EXPLAIN (ANALYZE,BUFFERS) et les traces de l’autovacuum (pour un VACUUM comme un ANALYZE). Avant de l’activer sur une machine peu performante, vérifiez l’impact avec l’outil pg_test_timing (il doit montrer des durées de chronométrage essentiellement sous la microseconde).

track_functions indique si les processus doivent aussi collecter des informations sur l’exécution des routines stockées. Les valeurs sont none (par défaut), pl pour ne tracer que les routines en langages procéduraux, all pour tracer aussi les routines en C et en SQL.

update_process_title permet de modifier le titre du processus, visible par exemple avec ps -ef sous Unix. Il est à on par défaut sous Unix, mais il faut le laisser à off sous Windows pour des raisons de performance.

Avant la version 15, stats_temp_directory servait à indiquer le répertoire de stockage temporaire des statistiques, avant copie dans pg_stat/ lors d’un arrêt propre. Ce répertoire peut devenir gros, est réécrit fréquemment, et peut devenir source de contention. Il est conseillé de le stocker ailleurs que dans le répertoire de l’instance PostgreSQL, par exemple sur un RAM disque ou tmpfs (c’est le défaut sous Debian).

Ce répertoire existe toujours en version 15, notamment si vous utilisez le module pg_stat_statements. Cependant, en dehors de ce module, rien d’autre ne l’utilise. Quant au paramètre stats_temp_directory, il a disparu.

PostgreSQL propose de nombreuses vues, accessibles en SQL, pour obtenir des informations sur son fonctionnement interne. Il est possible d’avoir des informations sur le fonctionnement des bases, des processus d’arrière-plan, des tables, les requêtes en cours…

Statistiques sur les objets :

Pour les statistiques sur les objets, le système fournit à chaque fois trois vues différentes :

• Une pour tous les objets du type. Elle contient all dans le nom, pg_statio_all_tables par exemple ;
• Une pour uniquement les objets systèmes. Elle contient sys dans le nom, pg_statio_sys_tables par exemple ;
• Une pour uniquement les objets non-systèmes. Elle contient user dans le nom, pg_statio_user_tables par exemple.

Les accès logiques aux objets (tables, index et routines) figurent dans les vues pg_stat_xxx_tables, pg_stat_xxx_indexes et pg_stat_user_functions.

SELECT * FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts' \gx

-[ RECORD 1 ]-------+------------------------------
relid               | 200784
schemaname          | public
relname             | pgbench_accounts
seq_scan            | 6
last_seq_scan       | 2024-11-14 11:37:16.851753+01
seq_tup_read        | 104077729
idx_scan            | 1
last_idx_scan       | 2024-11-13 15:48:32.962717+01
idx_tup_fetch       | 1319553
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_tup_newpage_upd   | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | ø
last_autovacuum     | ø
last_analyze        | ø
last_autoanalyze    | ø
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

Les accès physiques aux objets sont visibles dans les vues pg_statio_xxx_indexes, pg_statio_xxx_tables et pg_statio_xxx_sequences. Une vision plus globale est disponible dans pg_stat_io (apparue avec PostgreSQL 16).

 SELECT * FROM pg_statio_user_tables WHERE relname = 'pgbench_accounts' \gx

-[ RECORD 1 ]---+-----------------
relid           | 200784
schemaname      | public
relname         | pgbench_accounts
heap_blks_read  | 2993285
heap_blks_hit   | 8720337
idx_blks_read   | 277804
idx_blks_hit    | 6114553
toast_blks_read | ø
toast_blks_hit  | ø
tidx_blks_read  | ø
tidx_blks_hit   | ø

Des statistiques globales par base sont aussi disponibles, dans pg_stat_database : le nombre de transactions validées et annulées, quelques statistiques sur les sessions, et quelques statistiques sur les accès physiques et en cache, ainsi que sur les opérations logiques.

SELECT * FROM pg_stat_database WHERE datname ='pgbench' \gx

-[ RECORD 1 ]-------+------------------------------
relid               | 200784
schemaname          | public
relname             | pgbench_accounts
seq_scan            | 7
last_seq_scan       | 2024-11-14 15:25:08.632708+01
seq_tup_read        | 199954505
idx_scan            | 1001638
last_idx_scan       | 2024-11-14 15:33:15.346497+01
idx_tup_fetch       | 38260013
n_tup_ins           | 0
n_tup_upd           | 91491186
n_tup_del           | 0
n_tup_hot_upd       | 304994
n_tup_newpage_upd   | 91186192
n_live_tup          | 98976910
n_dead_tup          | 16426
n_mod_since_analyze | 1481762
n_ins_since_vacuum  | 0
last_vacuum         | 2024-11-14 14:41:20.893236+01
last_autovacuum     | 2024-11-14 16:06:01.192891+01
last_analyze        | 2024-11-14 14:41:45.544659+01
last_autoanalyze    | ø
vacuum_count        | 1
autovacuum_count    | 1
analyze_count       | 1
autoanalyze_count   | 0

pg_stat_bgwriter stocke les statistiques d’écriture des buffers des background writer, et du checkpointer (jusqu’en version 16 incluse) et des sessions elles-mêmes. On peut ainsi voir si les backends écrivent beaucoup ou peu. À partir de PostgreSQL 17, apparaît pg_stat_checkpointer qui reprend les champs sur les checkpoints et en ajoute quelques-uns. Cette vue permet de vérifier que les checkpoints sont réguliers, donc peu gênants.

Exemple (version 17) :

TABLE pg_stat_bgwriter \gx

-[ RECORD 1 ]----+------------------------------
buffers_clean    | 3004
maxwritten_clean | 26
buffers_alloc    | 24399160
stats_reset      | 2024-11-05 15:12:27.556173+01

TABLE pg_stat_checkpointer \gx

-[ RECORD 1 ]-------+------------------------------
num_timed           | 282
num_requested       | 2
restartpoints_timed | 0
restartpoints_req   | 0
restartpoints_done  | 0
write_time          | 605908
sync_time           | 3846
buffers_written     | 20656
stats_reset         | 2024-11-05 15:12:27.556173+01

Écritures :

pg_stat_bgwriter stocke les statistiques d’écriture des buffers des Background Writer, Checkpointer et des sessions elles-mêmes.

Requêtes

pg_stat_activity est une des vues les plus utilisées et est souvent le point de départ d’une recherche. Elle donne la liste des processus en cours sur l’instance, en incluant entre autres :

• le numéro de processus sur le serveur (pid) ;
• la base de données, le nom dutilisateur, l’adresse et le port du client ;
• les dates de début d’ordre, de transaction ou de session ;
• son statut (active ou non) ;
• la requête en cours, ou la dernière requête si la session ne fait rien ;
• le nom de l’application s’il a été renseigné avec le paramètre application_name ;
• le type de processus : session d’un utilisateur (client backend), processus interne…

SELECT datname, pid, usename, application_name,
       backend_start, state, backend_type, query
FROM   pg_stat_activity \gx

-[ RECORD 1 ]----+-------------------------------------------------------------
datname          | ¤
pid              | 26378
usename          | ¤
application_name |
backend_start    | 2019-10-24 18:25:28.236776+02
state            | ¤
backend_type     | autovacuum launcher
query            |
-[ RECORD 2 ]----+-------------------------------------------------------------
datname          | ¤
pid              | 26380
usename          | postgres
application_name |
backend_start    | 2019-10-24 18:25:28.238157+02
state            | ¤
backend_type     | logical replication launcher
query            |
-[ RECORD 3 ]----+-------------------------------------------------------------
datname          | pgbench
pid              | 22324
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.167611+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + -3810 WHERE…
-[ RECORD 4 ]----+-------------------------------------------------------------
datname          | postgres
pid              | 22429
usename          | postgres
application_name | psql
backend_start    | 2019-10-28 10:27:09.599426+01
state            | active
backend_type     | client backend
query            | select datname, pid, usename, application_name, backend_start…
-[ RECORD 5 ]----+-------------------------------------------------------------
datname          | pgbench
pid              | 22325
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.172585+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + 4360 WHERE…
-[ RECORD 6 ]----+-------------------------------------------------------------
datname          | pgbench
pid              | 22326
usename          | test_performance
application_name | pgbench
backend_start    | 2019-10-28 10:26:51.178514+01
state            | active
backend_type     | client backend
query            | UPDATE pgbench_accounts SET abalance = abalance + 2865 WHERE…
-[ RECORD 7 ]----+-------------------------------------------------------------
datname          | ¤
pid              | 26376
usename          | ¤
application_name |
backend_start    | 2019-10-24 18:25:28.235574+02
state            | ¤
backend_type     | background writer
query            |
-[ RECORD 8 ]----+-------------------------------------------------------------
datname          | ¤
pid              | 26375
usename          | ¤
application_name |
backend_start    | 2019-10-24 18:25:28.235064+02
state            | ¤
backend_type     | checkpointer
query            |
-[ RECORD 9 ]----+-------------------------------------------------------------
datname          | ¤
pid              | 26377
usename          | ¤
application_name |
backend_start    | 2019-10-24 18:25:28.236239+02
state            | ¤
backend_type     | walwriter
query            |

Les textes des requêtes sont tronqués à 1024 caractères : c’est un problème courant. Il est conseillé de monter le paramètre track_activity_query_size à plusieurs kilooctets.

Cette vue fournit aussi les wait events, qui indiquent ce qu’une session est en train d’attendre. Cela peut être très divers et inclut la levée d’un verrou sur un objet, celle d’un verrou interne, la fin d’une entrée-sortie… L’absence de wait event indique que la requête s’exécute. À noter qu’une session avec un wait event peut rester en statut active.

Les détails sur les champs wait_event_type (type d’événement en attente) et wait_event (nom de l’événement en attente) sont disponibles dans le tableau des événements d’attente. de la documentation.

À partir de PostgreSQL 17, la vue pg_wait_events peut être directement jointe à pg_stat_activity, et son champ description évite d’aller voir la documentation :

SELECT datname, application_name, pid,
       wait_event_type, wait_event, query, w.description
FROM pg_stat_activity a
  LEFT OUTER JOIN pg_wait_events w
  ON (a.wait_event_type = w.type AND a.wait_event = w.name)  
WHERE backend_type='client backend'
AND   wait_event IS NOT NULL
ORDER BY wait_event DESC LIMIT 4 \gx

-[ RECORD 1 ]----+-------------------------------------------------------------
datname          | pgbench_20000_hdd
application_name | pgbench
pid              | 786146
wait_event_type  | LWLock
wait_event       | WALWriteLock
query            | UPDATE pgbench_accounts SET abalance = abalance + 4055 WHERE…
description      | ø
-[ RECORD 2 ]----+-------------------------------------------------------------
datname          | pgbench_20000_hdd
application_name | pgbench
pid              | 786190
wait_event_type  | IO
wait_event       | WalSync
query            | UPDATE pgbench_accounts SET abalance = abalance + -1859  WHERE…
description      | Waiting for a WAL file to reach durable storage
-[ RECORD 3 ]----+-------------------------------------------------------------
datname          | pgbench_20000_hdd
application_name | pgbench
pid              | 786145
wait_event_type  | IO
wait_event       | DataFileRead
query            | UPDATE pgbench_accounts SET abalance = abalance + 3553 WHERE…
description      | Waiting for a read from a relation data file
-[ RECORD 4 ]----+-------------------------------------------------------------
datname          | pgbench_20000_hdd
application_name | pgbench
pid              | 786143
wait_event_type  | IO
wait_event       | DataFileRead
query            | UPDATE pgbench_accounts SET abalance = abalance + 1929 WHERE…
description      | Waiting for a read from a relation data file

Le processus de la ligne 2 attend une synchronisation sur disque du journal de transaction (WAL), et les deux suivants une lecture d’un fichier de données. (La description vide en ligne 1 est un souci de la version 17.2).

Pour entrer dans le détail des champs liés aux connexions :

• backend_type est le type de processus : on filtrera généralement sur client backend, mais on y trouvera aussi des processus de tâche de fond comme checkpointer, walwriter, autovacuum launcher et autres processus de PostgreSQL, ou encore des workers lancés par des extensions ;
• datname est le nom de la base à laquelle la session est connectée, et datid est son identifiant (OID) ;
• pid est le processus du backend, c’est-à-dire du processus PostgreSQL chargé de discuter avec le client, qui durera le temps de la session (sauf parallélisation) ;
• usename est le nom de l’utilisateur connecté, et usesysid est son OID dans pg_roles ;
• application_name est un nom facultatif, et il est recommandé que l’application cliente le renseigne autant que possible avec SET application_name TO 'nom_outil_client' ;
• client_addr est l’adresse IP du client connecté (NULL si connexion sur socket Unix), et client_hostname est le nom associé à cette IP, renseigné uniquement si log_hostname a été passé à on (cela peut ralentir les connexions à cause de la résolution DNS) ;
• client_port est le numéro de port sur lequel le client est connecté, toujours s’il s’agit d’une connexion IP.

Une requête parallélisée occupe plusieurs processus, et apparaîtra sur plusieurs lignes de pid différents. Le champ leader_pid indique le processus principal. Les autres processus disparaîtront dès la requête terminée.

Pour les champs liés aux durées de session, transactions et requêtes :

• backend_start est le timestamp de l’établissement de la session ;
• xact_start est le timestamp de début de la transaction ;
• query_start est le timestamp de début de la requête en cours, ou de la dernière requête exécutée ;
• status vaut soit active, soit idle (la session ne fait rien) soit idle in transaction (en attente pendant une transaction) ;
• backend_xid est l’identifiant de la transaction en cours, s’il y en a une ;
• backend_xmin est l’horizon des transactions visibles, et dépend aussi des autres transactions en cours.

Rappelons qu’une session durablement en statut idle in transaction bloque le fonctionnement de l’autovacuum car backend_xmin est bloqué. Cela peut mener à des tables fragmentées et du gaspillage de place disque.

Depuis PostgreSQL 14, pg_stat_activity peut afficher un champ query_id, c’est-à-dire un identifiant de requête normalisée (dépouillée des valeurs de paramètres). Il faut que le paramètre compute_query_id soit à on ou auto (le défaut, et alors une extension peut l’activer). Ce champ est utile pour retrouver une requête dans la vue de l’extension pg_stat_statements, par exemple.

Certains champs de cette vue ne sont renseignés que si le paramètre track_activities est à on (valeur par défaut, qu’il est conseillé de laisser ainsi).

À noter qu’il ne faut pas interroger pg_stat_activity au sein d’une transaction, son contenu pourrait sembler figé.

Verrous :

pg_locks permet de voir les verrous posés sur les objets (principalement les relations comme les tables et les index). Le processus (pid) est la clé commune pour la rapprocher de pg_stat_activity.

Archivage et réplication :

pg_stat_archiver donne des informations sur l’archivage des journaux de transaction et notamment sur les erreurs d’archivage. L’exemple suivant montre un archivage en erreur :

TABLE pg_stat_archiver \gx

-[ RECORD 1 ]------+------------------------------
archived_count     | 1637
last_archived_wal  | 0000000100000007000000E3
last_archived_time | 2024-11-14 16:00:00.418887+01
failed_count       | 13254
last_failed_wal    | 0000000100000007000000E4
last_failed_time   | 2024-11-14 16:01:37.347793+01
stats_reset        | 2024-11-05 14:58:00.515774+01

pg_stat_replication donne des informations sur les serveurs secondaires connectés. Les statistiques sur les conflits entre application de la réplication et requêtes en lecture seule sont disponibles dans pg_stat_database_conflicts.

Si les réplications se font par des slots de réplication (optionnels en réplication physique), pg_stat_replication_slots donne des informations sur leur état et leur retard.

Autres vues :

Des vues plus spécialisées existent :

pg_stat_ssl donne des informations sur les connexions SSL : version SSL, suite de chiffrement, nombre de bits pour l’algorithme de chiffrement, compression, Distinguished Name (DN) du certificat client.

pg_stat_progress_vacuum, pg_stat_progress_analyze, pg_stat_progress_create_index, pg_stat_progress_cluster, pg_stat_progress_basebackup et pg_stat_progress_copy donnent respectivement des informations sur la progression des VACUUM, des ANALYZE, des créations d’index, des commandes de VACUUM FULL et CLUSTER, de la commande de réplication BASE BACKUP et des COPY.

pg_stat_slru permet de suivre les accès à différents petits caches internes de PostgreSQL (à partir de PostgreSQL 17).

Réinitialisation :

Ces vues contiennent des compteurs cumulatifs. L’évolution en fonction du temps est souvent gérée par les nombreux outils qui font appel à ces vues. Il existe une fonction pour réinitialiser les compteurs de certaines vues (pas toutes) :

SELECT pg_stat_reset_shared('archiver') ;

Afin de calculer les plans d’exécution des requêtes au mieux, le moteur a besoin de statistiques sur les données qu’il va interroger. Il est très important pour lui de pouvoir estimer la sélectivité d’une clause WHERE, l’augmentation ou la diminution du nombre d’enregistrements entraînée par une jointure, tout cela afin de déterminer le coût approximatif d’une requête, et donc de choisir un bon plan d’exécution.

Il ne faut pas les confondre avec les statistiques d’activité, vues précédemment !

Les statistiques sont collectées dans la table pg_statistic. La vue pg_stats affiche le contenu de cette table système de façon plus accessible.

Les statistiques sont collectées sur :

• chaque colonne de chaque table ;
• les index fonctionnels.

Le recueil des statistiques s’effectue quand on lance un ordre ANALYZE sur une table, ou que l’autovacuum le lance de son propre chef.

Les statistiques sont calculées sur un échantillon égal à 300 fois le paramètre STATISTICS de la colonne (ou, s’il n’est pas précisé, du paramètre default_statistics_target, 100 par défaut).

La vue pg_stats affiche les statistiques collectées :

\d pg_stats

                     View "pg_catalog.pg_stats"
         Column         |   Type   | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
 schemaname             | name     |           |          |
 tablename              | name     |           |          |
 attname                | name     |           |          |
 inherited              | boolean  |           |          |
 null_frac              | real     |           |          |
 avg_width              | integer  |           |          |
 n_distinct             | real     |           |          |
 most_common_vals       | anyarray |           |          |
 most_common_freqs      | real[]   |           |          |
 histogram_bounds       | anyarray |           |          |
 correlation            | real     |           |          |
 most_common_elems      | anyarray |           |          |
 most_common_elem_freqs | real[]   |           |          |
 elem_count_histogram   | real[]   |           |          |

• inherited : la statistique concerne-t-elle un objet utilisant l’héritage (table parente, dont héritent plusieurs tables) ;
• null_frac : fraction d’enregistrements dont la colonne vaut NULL ;
• avg_width : taille moyenne de cet attribut dans l’échantillon collecté ;
• n_distinct : si positif, c’est le nombre de valeurs distinctes ; si négatif, c’est la fraction de valeurs distinctes pour cette colonne dans la table. Il est possible de forcer la valeur de ce champ s’il est constaté que la collecte des statistiques le calcule mal. Par exemple, pour indiquer à l’optimiseur que chaque valeur apparaît statistiquement deux fois :

ALTER TABLE matable ALTER COLUMN yyy SET (n_distinct = -0.5) ;
ANALYZE matable ;

• most_common_vals et most_common_freqs : les valeurs les plus fréquentes de la table, et leur fréquence. Le nombre de valeurs collectées est au maximum celui indiqué par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target. Le défaut de 100 échantillons sur 30 000 lignes peut être modifié comme ci-après (sachant que le temps de planification augmente exponentiellement avec ce paramètre, et qu’il vaut mieux ne pas dépasser la valeur 1000) :

ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ;

• histogram_bounds : les limites d’histogramme sur la colonne. Les histogrammes permettent d’évaluer la sélectivité d’un filtre par rapport à sa valeur précise. Ils permettent par exemple à l’optimiseur de déterminer que 4,3 % des enregistrements d’une colonne noms commencent par un A, ou 0,2 % par AL. Le principe est de regrouper les enregistrements triés dans des groupes de tailles approximativement identiques, et de stocker les limites de ces groupes (on ignore les most_common_vals, pour lesquelles il y a déjà une mesure plus précise). Le nombre d’histogram_bounds est calculé de la même façon que les most_common_vals ;
• correlation : le facteur de corrélation statistique entre l’ordre physique et l’ordre logique des enregistrements de la colonne. Il vaudra par exemple 1 si les enregistrements sont physiquement stockés dans l’ordre croissant, -1 si ils sont dans l’ordre décroissant, ou 0 si ils sont totalement aléatoirement répartis. Ceci sert à affiner le coût d’accès aux enregistrements ;
• most_common_elems et most_common_elems_freqs : les valeurs les plus fréquentes si la colonne est un tableau (NULL dans les autres cas), et leur fréquence. Le nombre de valeurs collectées est au maximum celui indiqué par le paramètre STATISTICS de la colonne, ou à défaut par default_statistics_target ;
• elem_count_histogram : les limites d’histogramme sur la colonne si elle est de type tableau.

Parfois, il est intéressant de calculer des statistiques sur un ensemble de colonnes ou d’expressions. Dans ce cas, il faut créer un objet statistique en indiquant les colonnes et/ou expressions à traiter et le type de statistiques à calculer (voir la documentation de CREATE STATISTICS).

Le langage SQL décrit le résultat souhaité. Par exemple :

SELECT path, filename
FROM file
JOIN path ON (file.pathid=path.pathid)
WHERE path LIKE '/usr/%'

Cet ordre décrit le résultat souhaité. Nous ne précisons pas au moteur comment accéder aux tables path et file (par index ou parcours complet par exemple), ni comment effectuer la jointure (PostgreSQL dispose de plusieurs méthodes). C’est à l’optimiseur de prendre la décision, en fonction des informations qu’il possède.

Les informations les plus importantes pour lui, dans le contexte de cette requête, seront :

• quelle fraction de la table path est ramenée par le critère path LIKE '/usr/%’ ?
• y a-t-il un index utilisable sur cette colonne ?
• y a-t-il des index utilisables sur file.pathid, sur path.pathid ?
• quelles sont les tailles des deux tables ?

La stratégie la plus efficace ne sera donc pas la même suivant les informations retournées par toutes ces questions.

Par exemple, il pourrait être intéressant de charger les deux tables séquentiellement, supprimer les enregistrements de path ne correspondant pas à la clause LIKE, trier les deux jeux d’enregistrements et fusionner les deux jeux de données triés (cette technique est un merge join). Cependant, si les tables sont assez volumineuses, et que le LIKE est très discriminant (il ramène peu d’enregistrements de la table path), la stratégie d’accès sera totalement différente : nous pourrions préférer récupérer les quelques enregistrements de path correspondant au LIKE par un index, puis pour chacun de ces enregistrements, aller chercher les informations correspondantes dans la table file (c’est un nested loop).

Afin de choisir un bon plan, le moteur essaie des plans d’exécution. Il estime, pour chacun de ces plans, le coût associé. Afin d’évaluer correctement ces coûts, il utilise plusieurs informations :

• Les statistiques sur les données, qui lui permettent d’estimer le nombre d’enregistrements ramenés par chaque étape du plan et le nombre d’opérations de lecture à effectuer pour chaque étape de ce plan ;
• Des informations de paramétrage lui permettant d’associer un coût arbitraire à chacune des opérations à effectuer. Ces informations sont les suivantes :
  • seq_page_cost (1 par défaut) : coût de la lecture d’une page disque de façon séquentielle (au sein d’un parcours séquentiel de table par exemple) ;
  • random_page_cost (4 par défaut) : coût de la lecture d’une page disque de façon aléatoire (lors d’un accès à une page d’index par exemple) ;
  • cpu_tuple_cost (0,01 par défaut) : coût de traitement par le processeur d’un enregistrement de table ;
  • cpu_index_tuple_cost (0,005 par défaut) : coût de traitement par le processeur d’un enregistrement d’index ;
  • cpu_operator_cost (0,0025 par défaut) : coût de traitement par le processeur de l’exécution d’un opérateur.

Ce sont les coûts relatifs de ces différentes opérations qui sont importants : l’accès à une page de façon aléatoire est par défaut 4 fois plus coûteux que de façon séquentielle, du fait du déplacement des têtes de lecture sur un disque dur classique à plateaux. Ceci prend déjà en considération un potentiel effet du cache. Sur une base fortement en cache, il est donc possible d’être tenté d’abaisser le random_page_cost à 3, voire 2,5, ou des valeurs encore bien moindres dans le cas de bases totalement en mémoire.

Le cas des disques SSD est particulièrement intéressant. Ces derniers n’ont pas à proprement parler de tête de lecture. De ce fait, comme les paramètres seq_page_cost et random_page_cost sont principalement là pour différencier un accès direct et un accès après déplacement de la tête de lecture, la différence de configuration entre ces deux paramètres n’a pas lieu d’être si les index sont placés sur des disques SSD. Dans ce cas, une configuration très basse et pratiquement identique (voire identique) de ces deux paramètres est préconisée :

# pour un SSD
seq_page_cost = 1
random_page_cost = 1.1

effective_io_concurrency a pour but d’indiquer le nombre d’opérations disques possibles en même temps pour un client (prefetch). Seuls les parcours Bitmap Scan sont impactés par ce paramètre. Selon la documentation, pour un système disque utilisant un RAID matériel, il faut le configurer en fonction du nombre de disques utiles dans le RAID (n s’il s’agit d’un RAID 1, n-1 s’il s’agit d’un RAID 5 ou 6, n/2 s’il s’agit d’un RAID 10). Avec du SSD, il est possible de monter à plusieurs centaines, étant donné la rapidité de ce type de disque. Ces valeurs doivent être réduites sur un système très chargé. Une valeur excessive mène au gaspillage de CPU. Le défaut deffective_io_concurrency est seulement de 1, et la valeur maximale est 1000.

(Avant la version 13, le principe était le même, mais la valeur exacte de ce paramètre devait être 2 à 5 fois plus basse comme le précise la formule des notes de version de la version 13.)

Le paramètre maintenance_io_concurrency a le même sens que effective_io_concurrency, mais pour les opérations de maintenance. Celles-ci peuvent ainsi se voir accorder plus de ressources qu’une simple requête. Le défaut est de 10, et il faut penser à le monter aussi comme effective_io_concurrency sur des disques plus rapides qu’un simple disque magnétique.

seq_page_cost, random_page_cost, effective_io_concurrency et maintenance_io_concurrency peuvent être paramétrés par tablespace, afin de refléter les caractéristiques de disques différents.

La mise en place du parallélisme dans une requête représente un coût : il faut mettre en place une mémoire partagée, lancer des processus… Ce coût est pris en compte par le planificateur à l’aide du paramètre parallel_setup_cost. Par ailleurs, le transfert d’enregistrement entre un worker et le processus principal a également un coût représenté par le paramètre parallel_tuple_cost.

Ainsi une lecture complète d’une grosse table peut être moins coûteuse sans parallélisation du fait que le nombre de lignes retournées par les workers est très important. En revanche, en filtrant les résultats, le nombre de lignes retournées peut être moins important, la répartition du filtrage entre différents processeurs devient « rentable » et le planificateur peut être amené à choisir un plan comprenant la parallélisation.

Certaines autres informations permettent de nuancer les valeurs précédentes. effective_cache_size est la taille totale du cache. Il permet à PostgreSQL de modéliser plus finement le coût réel d’une opération disque, en prenant en compte la probabilité que cette information se trouve dans le cache du système d’exploitation ou dans celui de l’instance, et soit donc moins coûteuse à accéder.

Le parcours de l’espace des solutions est un parcours exhaustif. Sa complexité est principalement liée au nombre de jointures de la requête et est de type exponentiel. Par exemple, planifier de façon exhaustive une requête à une jointure dure 200 microsecondes environ, contre 7 secondes pour 12 jointures. Une autre stratégie, l’optimiseur génétique, est donc utilisée pour éviter le parcours exhaustif quand le nombre de jointure devient trop élevé.

Pour plus de détails, voir l’article sur les coûts de planification issu de notre base de connaissance.

Les paramètres suivants peuvent être modifiés par session.

Nombre de tables considérées :

En principe, le planificateur ne tient pas compte de l’ordre des tables dans la clause FROM et peut décider de commencer la requête par n’importe laquelle. Cependant, la complexité des plans d’exécution à étudier explose avec le nombre de tables et de combinaisons de jointures, avec toutes leurs possibilités. Les paramètres from_collapse_limit et join_collapse_limit définissent le nombre de tables prises en compte à la fois.

• join_collapse_limit (à 8 par défaut) définit le nombre de tables prises en compte dans le FROM (vision simplifiée). S’il y a plus de tables, elles seront jointes au premier résultat dans un deuxième temps. Cela peut donner des résultats catastrophiques si le critère de filtrage le plus utile est sur la neuvième table du FROM !

• from_collapse_limit remonte les tables dans une sous-requête dans le FROM, pourvu de ne pas dépasser cette valeur. On le définit habituellement à la même valeur que join_collapse_limit.

Comme exemple de plan désastreux à cause d’un critère de filtrage sur une table non prise en compte, voir ce plan. Monter join_collapse_limit de 8 à 9 bascule vers un plan bien meilleur. Une autre solution aurait été de remonter la table INNER JOIN contacts, qui porte le critère, plus haut dans le FROM. Mais il n’est pas toujours possible de modifier le code, qui d’ailleurs peut être dynamique.

Pour éviter de se soucier de ce problème, il est fréquent de monter les valeurs de join_collapse_limit et from_collapse_limit à 10 ou 12 quand on utilise parfois autant de tables. Des valeurs plus élevées (jusque 20 ou plus) sont plus dangereuses, car le temps de planification peut monter très haut (centaines de millisecondes, voire pire). Mais elles se justifient dans certains domaines. Il est alors préférable de positionner ces valeurs élevées uniquement au niveau de la session, de l’écran, ou de l’utilisateur avec SET ou ALTER ROLE … SET ….

À l’inverse, descendre join_collapse_limit à 1 permet de dicter l’ordre d’exécution au planificateur, une mesure de dernier recours.

Parallèment, il ne sera pas inutile de juger de la pertinence d’une requête avec autant de tables.

Optimiseur génétique :

Ce qui suit suppose que join_collapse_limit dépasse 12.

PostgreSQL, pour les requêtes trop complexes, bascule vers un optimiseur appelé GEQO (GEnetic Query Optimizer). Comme tout algorithme génétique, il fonctionne par introduction de mutations aléatoires sur un état initial donné. Il permet de planifier rapidement une requête complexe, et de fournir un plan d’exécution acceptable.

Le code source de PostgreSQL décrit le principe, résumé aussi dans ce schéma :

Ce mécanisme est configuré par des paramètres dont le nom commence par geqo. Exceptés ceux ci-dessous, il est déconseillé d’y toucher sans une bonne connaissance des algorithmes génétiques.

• geqo, par défaut à on, permet d’activer/désactiver GEQO ;
• geqo_threshold, par défaut à 12, est le nombre d’éléments minimum à joindre dans un FROM avant d’optimiser celui-ci par GEQO au lieu du planificateur exhaustif ;
• geqo_seed (la « graine » pour la génération aléatoire, 0 par défaut) permet de forcer la recherche d’autres plans.

À geqo_seed identique, les plans générés seront toujours les mêmes (toutes autres choses identiques par ailleurs).

En production, on ne montera pas geqo_threshold ou à peine, et en limitant dans une session. Le nombre de plans à étudier sans l’optimisation du GEQO peut devenir colossal et consommer beaucoup de RAM et de CPU.

Tous les paramètres suivants peuvent être modifiés par session.

Requêtes préparées :

Pour les requêtes préparées, par défaut l’optimiseur génère des plans personnalisés pour les cinq premières exécutions d’une requête préparée, puis il bascule sur un plan générique dès que celui-ci devient plus intéressant que la moyenne des plans personnalisés précédents. Le paramètre de configuration plan_cache_mode permet de modifier cela :

• auto active le comportement par défaut ;
• force_custom_plan force le recalcul systématique d’un plan personnalisé pour la requête (on n’économise plus le temps de planification, mais le plan est calculé pour être optimal pour les paramètres, tout en conservant la protection contre les injections SQL permise par les requêtes préparées) ;
• force_generic_plan force l’utilisation d’un seul et même plan dès le départ.

Partitionnement :

Avant la version 10, PostgreSQL ne connaissait qu’un partitionnement par héritage, où l’on crée une table parente et des tables filles héritent de celle-ci, possédant des contraintes CHECK comme critères de partitionnement, par exemple CHECK (date >='2011-01-01' and date < '2011-02-01') pour une table fille d’un partitionnement par mois. Afin que PostgreSQL ne parcourt que les partitions correspondant à la clause WHERE d’une requête, le paramètre constraint_exclusion doit valoir partition (la valeur par défaut) ou on. partition est moins coûteux dans un contexte d’utilisation classique car les contraintes d’exclusion ne seront examinées que dans le cas de requêtes UNION ALL, qui sont les requêtes générées par le partitionnement.

Pour le partitionnement déclaratif (à favoriser à partir de PostgreSQL 10), enable_partition_pruning, activé par défaut, est le paramètre équivalent.

Curseurs :

Lors de l’utilisation de curseurs, le moteur n’a aucun moyen de connaître le nombre d’enregistrements que souhaite récupérer réellement l’utilisateur : peut-être seulement les premiers enregistrements. Si c’est le cas, le plan d’exécution optimal ne sera plus le même. Le paramètre cursor_tuple_fraction, par défaut à 0,1, permet d’indiquer à l’optimiseur la fraction du nombre d’enregistrements qu’un curseur souhaitera vraisemblablement récupérer, et lui permettra donc de choisir un plan en conséquence. Si vous utilisez des curseurs, il vaut mieux indiquer explicitement le nombre d’enregistrements dans les requêtes avec LIMIT, et passer cursor_tuple_fraction à 1,0.

Synchronisation des parcours de table :

Quand plusieurs requêtes souhaitent accéder séquentiellement à la même table, les processus se rattachent à ceux déjà en cours de parcours, afin de profiter des entrées-sorties que ces processus effectuent, le but étant que le système se comporte comme si un seul parcours de la table était en cours, et réduise donc fortement la charge disque. Le seul problème de ce mécanisme est que les processus se rattachant ne parcourent pas la table dans son ordre physique : elles commencent leur parcours de la table à l’endroit où se trouve le processus auquel elles se rattachent, puis rebouclent sur le début de la table. Les résultats n’arrivent donc pas forcément toujours dans le même ordre, ce qui n’est normalement pas un problème (on est censé utiliser ORDER BY dans ce cas). Mais il est toujours possible de désactiver ce mécanisme en passant synchronize_seqscans à off.

Ces paramètres dissuadent le moteur d’utiliser un type de nœud d’exécution (en augmentant énormément son coût). Ils permettent de vérifier ou d’invalider une erreur de l’optimiseur. Par exemple :

-- création de la table de test
CREATE TABLE test2(a integer, b integer);

-- insertion des données de tests
INSERT INTO test2 SELECT 1, i FROM generate_series(1, 500000) i;

-- analyse des données
ANALYZE test2;

-- désactivation de la parallélisation (pour faciliter la lecture du plan)
SET max_parallel_workers_per_gather TO 0;

-- récupération du plan d'exécution
EXPLAIN ANALYZE SELECT * FROM test2 WHERE a<3;

                          QUERY PLAN
------------------------------------------------------------
 Seq Scan on test2  (cost=0.00..8463.00 rows=500000 width=8)
             (actual time=0.031..63.194 rows=500000 loops=1)
   Filter: (a < 3)
 Planning Time: 0.411 ms
 Execution Time: 86.824 ms

Le moteur a choisi un parcours séquentiel de table. Si l’on veut vérifier qu’un parcours par l’index sur la colonne a n’est pas plus rentable :

-- désactivation des parcours SeqScan, IndexOnlyScan et BitmapScan
SET enable_seqscan TO off;
SET enable_indexonlyscan TO off;
SET enable_bitmapscan TO off;

-- création de l'index
CREATE INDEX ON test2(a);

-- récupération du plan d'exécution
EXPLAIN ANALYZE SELECT * FROM test2 WHERE a<3;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using test2_a_idx on test2  (cost=0.42..16462.42 rows=500000 width=8)
                                  (actual time=0.183..90.926 rows=500000 loops=1)
   Index Cond: (a < 3)
 Planning Time: 0.517 ms
 Execution Time: 111.609 ms

Non seulement le plan est plus coûteux, mais il est aussi (et surtout) plus lent.

Attention aux effets du cache : le parcours par index est ici relativement performant à la deuxième exécution parce que les données ont été trouvées dans le cache disque. La requête, sinon, aurait été bien plus lente. La requête initiale est donc non seulement plus rapide, mais aussi plus sûre : son temps d’exécution restera prévisible même en cas d’erreur d’estimation sur le nombre d’enregistrements.

Si nous supprimons l’index, nous constatons que le sequential scan n’a pas été désactivé. Il a juste été rendu très coûteux par ces options de débogage :

-- suppression de l'index
DROP INDEX test2_a_idx;

-- récupération du plan d'exécution
EXPLAIN ANALYZE SELECT * FROM test2 WHERE a<3;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on test2  (cost=10000000000.00..10000008463.00 rows=500000 width=8)
                              (actual time=0.044..60.126 rows=500000 loops=1)
   Filter: (a < 3)
 Planning Time: 0.313 ms
 Execution Time: 82.598 ms

Le « très coûteux » est un coût majoré de 10 milliards pour l’exécution d’un nœud interdit.

Voici la liste des options de désactivation :

• enable_bitmapscan ;
• enable_gathermerge ;
• enable_hashagg ;
• enable_hashjoin ;
• enable_incremental_sort ;
• enable_indexonlyscan ;
• enable_indexscan ;
• enable_material ;
• enable_mergejoin ;
• enable_nestloop ;
• enable_parallel_append ;
• enable_parallel_hash ;
• enable_partition_pruning ;
• enable_partitionwise_aggregate ;
• enable_partitionwise_join ;
• enable_seqscan ;
• enable_sort ;
• enable_tidscan.

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m2_solutions.

Créer un tablespace nommé ts1 pointant vers /opt/ts1.

Se connecter à la base de données b1. Créer une table t_dans_ts1 avec une colonne id de type integer dans le tablespace ts1.

Récupérer le chemin du fichier correspondant à la table t_dans_ts1 avec la fonction pg_relation_filepath.

Supprimer le tablespace ts1. Qu’observe-t-on ?

Créer une table t3 avec une colonne id de type integer.

Insérer 1000 lignes dans la table t3 avec generate_series.

Lire les statistiques d’activité de la table t3 à l’aide de la vue système pg_stat_user_tables.

Créer un utilisateur pgbench et créer une base pgbench lui appartenant.

Écrire une requête SQL qui affiche le nom et l’identifiant de toutes les bases de données, avec le nom du propriétaire et l’encodage. (Utiliser les table pg_database et pg_roles).

Comparer la requête avec celle qui est exécutée lorsque l’on tape la commande \l dans la console (penser à \set ECHO_HIDDEN).

Pour voir les sessions connectées :

• dans un autre terminal, ouvrir une session psql sur la base b0, qu’on n’utilisera plus ;
• se connecter à la base b1 depuis une autre session ;
• la vue pg_stat_activity affiche les sessions connectées. Qu’y trouve-t-on ?

Se connecter à la base de données b1 et créer une table t4 avec une colonne id de type entier.

Empêcher l’autovacuum d’analyser automatiquement la table t4.

Insérer 1 million de lignes dans t4 avec generate_series.

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

Exécuter la commande ANALYZE sur la table t4.

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

Ajouter un index sur la colonne id de la table t4.

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

Modifier le contenu de la table t4 avec UPDATE t4 SET id = 100000;

Rechercher les lignes ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

Exécuter la commande ANALYZE sur la table t4.

Rechercher les lignes ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

Tablespace

Créer un tablespace nommé ts1 pointant vers /opt/ts1.

En tant qu’utilisateur root :

 # mkdir /opt/ts1
 # chown postgres:postgres /opt/ts1

En tant qu’utilisateur postgres :

$ psql

postgres=# CREATE TABLESPACE ts1 LOCATION '/opt/ts1';
CREATE TABLESPACE

postgres=# \db

          Liste des tablespaces
    Nom     | Propriétaire | Emplacement
------------+--------------+-------------
 pg_default | postgres     |
 pg_global  | postgres     |
 ts1        | postgres     | /opt/ts1

Se connecter à la base de données b1. Créer une table t_dans_ts1 avec une colonne id de type integer dans le tablespace ts1.

b1=# CREATE TABLE t_dans_ts1 (id integer) TABLESPACE ts1;
CREATE TABLE

Récupérer le chemin du fichier correspondant à la table t_dans_ts1 avec la fonction pg_relation_filepath.

b1=# SELECT current_setting('data_directory') || '/'
     || pg_relation_filepath('t_dans_ts1')
     AS chemin;

                               chemin
--------------------------------------------------------------------
  /var/lib/pgsql/15/data/pg_tblspc/16394/PG_15_202107181/16393/16395

Le fichier n’a pas été créé dans un sous-répertoire du répertoire base, mais dans le tablespace indiqué par la commande CREATE TABLE. /opt/ts1 n’apparaît pas ici : il y a un lien symbolique dans le chemin.

$ ls -l $PGDATA/pg_tblspc/
total 0
lrwxrwxrwx 1 postgres postgres 8 Apr 16 16:26 16394 -> /opt/ts1

$ cd /opt/ts1/PG_15_202107181/
$ ls -lR
.:
total 0
drwx------ 2 postgres postgres 18 Apr 16 16:26 16393
./16393:
total 0
-rw------- 1 postgres postgres 0 Apr 16 16:26 16395

Il est à noter que ce fichier se trouve réellement dans un sous-répertoire de /opt/ts1 mais que PostgreSQL le retrouve à partir de pg_tblspc grâce à un lien symbolique.

Supprimer le tablespace ts1. Qu’observe-t-on ?

La suppression échoue tant que le tablespace est utilisé. Il faut déplacer la table dans le tablespace par défaut :

b1=# DROP TABLESPACE ts1 ;
ERROR:  tablespace "ts1" is not empty

b1=# ALTER TABLE t_dans_ts1 SET TABLESPACE pg_default ;
ALTER TABLE

b1=# DROP TABLESPACE ts1 ;
DROP TABLESPACE

Statistiques d’activités, tables et vues système

Créer une table t3 avec une colonne id de type integer.

b1=# CREATE TABLE t3 (id integer);
CREATE TABLE

Insérer 1000 lignes dans la table t3 avec generate_series.

b1=# INSERT INTO t3 SELECT generate_series(1, 1000);
INSERT 0 1000

Lire les statistiques d’activité de la table t3 à l’aide de la vue système pg_stat_user_tables.

b1=# \x
Expanded display is on.
b1=# SELECT * FROM pg_stat_user_tables WHERE relname = 't3';

-[ RECORD 1 ]-----+-------
relid             | 24594
schemaname        | public
relname           | t3
seq_scan          | 0
seq_tup_read      | 0
idx_scan          |
idx_tup_fetch     |
n_tup_ins         | 1000
n_tup_upd         | 0
n_tup_del         | 0
n_tup_hot_upd     | 0
n_live_tup        | 1000
n_dead_tup        | 0
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0

Les statistiques indiquent bien que 1000 lignes ont été insérées.

Créer un utilisateur pgbench et créer une base pgbench lui appartenant.

b1=# CREATE ROLE pgbench LOGIN ;
CREATE ROLE

b1=# CREATE DATABASE pgbench OWNER pgbench ;
CREATE DATABASE

Écrire une requête SQL qui affiche le nom et l’identifiant de toutes les bases de données, avec le nom du propriétaire et l’encodage. (Utiliser les table pg_database et pg_roles).

La liste des bases de données se trouve dans la table pg_database :

SELECT db.oid, db.datname, datdba
FROM pg_database db ;

Une jointure est possible avec la table pg_roles pour déterminer le propriétaire des bases :

SELECT db.datname, r.rolname, db.encoding
FROM pg_database db, pg_roles r
WHERE db.datdba = r.oid ;

d’où par exemple :

  datname  | rolname  | encoding
-----------+----------+----------
 b1        | postgres |        6
 b0        | postgres |        6
 template0 | postgres |        6
 template1 | postgres |        6
 postgres  | postgres |        6
 pgbench   | pgbench  |        6

L’encodage est numérique, il reste à le rendre lisible.

Comparer la requête avec celle qui est exécutée lorsque l’on tape la commande \l dans la console (penser à \set ECHO_HIDDEN).

Il est possible de positionner le paramètre \set ECHO_HIDDEN on, ou sortir de la console et la lancer de nouveau psql avec l’option -E :

$ psql -E

Taper la commande \l. La requête envoyée par psql au serveur est affichée juste avant le résultat :

\l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

L’encodage se retrouve donc en appelant la fonction pg_encoding_to_char :

b1=# SELECT db.datname, r.rolname, db.encoding, pg_catalog.pg_encoding_to_char(db.encoding)
FROM pg_database db, pg_roles r
WHERE db.datdba = r.oid ;

  datname  | rolname  | encoding | pg_encoding_to_char
-----------+----------+----------+---------------------
 b1        | postgres |        6 | UTF8
 b0        | postgres |        6 | UTF8
 template0 | postgres |        6 | UTF8
 template1 | postgres |        6 | UTF8
 postgres  | postgres |        6 | UTF8
 pgbench   | pgbench  |        6 | UTF8

Pour voir les sessions connectées :

• dans un autre terminal, ouvrir une session psql sur la base b0, qu’on n’utilisera plus ;
• se connecter à la base b1 depuis une autre session ;
• la vue pg_stat_activity affiche les sessions connectées. Qu’y trouve-t-on ?

# terminal 1
$ psql b0

# terminal 2
$ psql b1

La table a de nombreux champs, affichons les plus importants :

# SELECT datname, pid, state, usename, application_name AS appp, backend_type, query
  FROM pg_stat_activity ;

 datname | pid  | state  | usename  | appp |         backend_type         | query
---------+------+--------+----------+------+------------------------------+--------
         | 6179 |        |          |      | autovacuum launcher          |
         | 6181 |        | postgres |      | logical replication launcher |
 b0      | 6870 | idle   | postgres | psql | client backend               |
 b1      | 6872 | active | postgres | psql | client backend               | SELECT…
         | 6177 |        |          |      | background writer            |
         | 6176 |        |          |      | checkpointer                 |
         | 6178 |        |          |      | walwriter                    |
(7 rows)

La session dans b1 est idle, c’est-à-dire en attente. La seule session active (au moment où elle tournait) est celle qui exécute la requête. Les autres lignes correspondent à des processus système.

Remarque : Ce n’est qu’à partir de la version 10 de PostgreSQL que la vue pg_stat_activity liste les processus d’arrière-plan (checkpointer, background writer….). Les connexions clientes peuvent s’obtenir en filtrant sur la colonne backend_type le contenu client backend.

SELECT datname, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname
HAVING count(*)>0;

Ce qui donnerait par exemple :

 datname | count
---------+-------
 pgbench |    10
 b0      |     5

Statistiques sur les données

Se connecter à la base de données b1 et créer une table t4 avec une colonne id de type entier.

b1=# CREATE TABLE t4 (id integer);
CREATE TABLE

Empêcher l’autovacuum d’analyser automatiquement la table t4.

b1=# ALTER TABLE t4 SET (autovacuum_enabled=false);
ALTER TABLE

NB : ceci n’est à faire qu’à titre d’exercice ! En production, c’est une très mauvaise idée.

Insérer 1 million de lignes dans t4 avec generate_series.

b1=# INSERT INTO t4 SELECT generate_series(1, 1000000);
INSERT 0 1000000

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

b1=# EXPLAIN SELECT * FROM t4 WHERE id = 100000;

                               QUERY PLAN
------------------------------------------------------------------------
 Gather  (cost=1000.00..11866.15 rows=5642 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t4  (cost=0.00..10301.95 rows=2351 width=4)
         Filter: (id = 100000)

Exécuter la commande ANALYZE sur la table t4.

b1=# ANALYZE t4;
ANALYZE

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

b1=# EXPLAIN SELECT * FROM t4 WHERE id = 100000;

                             QUERY PLAN
--------------------------------------------------------------------
 Gather  (cost=1000.00..10633.43 rows=1 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t4  (cost=0.00..9633.33 rows=1 width=4)
         Filter: (id = 100000)

Les statistiques sont beaucoup plus précises. PostgreSQL sait maintenant qu’il ne va récupérer qu’une seule ligne, sur le million de lignes dans la table. C’est le cas typique où un index serait intéressant.

Ajouter un index sur la colonne id de la table t4.

b1=# CREATE INDEX ON t4(id);
CREATE INDEX

Rechercher la ligne ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

b1=# EXPLAIN SELECT * FROM t4 WHERE id = 100000;

                               QUERY PLAN
-------------------------------------------------------------------------
 Index Only Scan using t4_id_idx on t4  (cost=0.42..8.44 rows=1 width=4)
   Index Cond: (id = 100000)

Après création de l’index, nous constatons que PostgreSQL choisit un autre plan qui permet d’utiliser cet index.

Modifier le contenu de la table t4 avec UPDATE t4 SET id = 100000;

b1=# UPDATE t4 SET id = 100000;
UPDATE 1000000

Toutes les lignes ont donc à présent la même valeur.

Rechercher les lignes ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

b1=# EXPLAIN ANALYZE SELECT * FROM t4 WHERE id = 100000;

                  QUERY PLAN
---------------------------------------------------------
 Index Only Scan using t4_id_idx on t4
       (cost=0.43..8.45 rows=1 width=4)
       (actual time=0.040..265.573 rows=1000000 loops=1)
   Index Cond: (id = 100000)
   Heap Fetches: 1000001
 Planning time: 0.066 ms
 Execution time: 303.026 ms

Là, un parcours séquentiel serait plus performant. Mais comme PostgreSQL n’a plus de statistiques à jour, il se trompe de plan et utilise toujours l’index.

Exécuter la commande ANALYZE sur la table t4.

b1=# ANALYZE t4;
ANALYZE

Rechercher les lignes ayant comme valeur 100000 dans la colonne id et afficher le plan d’exécution.

b1=# EXPLAIN ANALYZE SELECT * FROM t4 WHERE id = 100000;

                     QUERY PLAN
----------------------------------------------------------
 Seq Scan on t4
       (cost=0.00..21350.00 rows=1000000 width=4)
       (actual time=75.185..186.019 rows=1000000 loops=1)
   Filter: (id = 100000)
 Planning time: 0.122 ms
 Execution time: 223.357 ms

Avec des statistiques à jour et malgré la présence de l’index, PostgreSQL va utiliser un parcours séquentiel qui, au final, sera plus performant.

Si l’autovacuum avait été activé, les modifications massives dans la table auraient provoqué assez rapidement la mise à jour des statistiques.

La zone de mémoire partagée statique est allouée au démarrage de l’instance. Le type de mémoire partagée est configuré avec le paramètre shared_memory_type. Sous Linux, il s’agit par défaut de mmap, sachant qu’une très petite partie utilise toujours sysv (System V). (Il est en principe possible de basculer uniquement en sysv mais ceci n’est pas recommandé et nécessite un paramétrage du noyau Linux.) Sous Windows, le type est windows.

Les principales zones de mémoire partagées décrites ici sont fixes, et les tailles calculées en fonction de paramètres. Nous verrons en détail l’utilité de certaines de ces zones dans les chapitres suivants.

Shared buffers :

Les shared buffers sont le cache des fichiers de données présents sur le disque. Ils représentent de loin la volumétrie la plus importante.

Paramètre associé : shared_buffers (à adapter systématiquement)

Wal buffers :

Les wal buffers sont le cache des journaux de transaction.

Paramètre associé : wal_buffers (rarement modifié)

Données liées aux sessions :

Cet espace mémoire sert à gérer les sessions ouvertes, celles des utilisateurs, mais aussi celles ouvertes par de nombreux processus internes.

Principaux paramètres associés :

• max_connections, soit le nombre de connexions simultanées possibles (défaut : 100, souvent suffisant mais à adapter) ;
• track_activity_query_size (défaut : 1024 ; souvent monté à 10 000 s’il y a des requêtes de grande taille) ;

Verrous :

La gestion des verrous est décrite dans un autre module. Lors des mises à jour ou suppressions dans les tables, les verrous sur les lignes sont généralement stockés dans les lignes mêmes ; mais de nombreux autres verrous sont stockés en mémoire partagée. Les paramètres associés pour le dimensionnement sont surtout :

• max_connections à nouveau ;
• max_locks_per_transaction, soit le nombre de verrous possible pour une transaction (défaut : 64, généralement suffisant) ;
• max_pred_locks_per_relation, nombre de verrous possible pour une table si le niveau d’isolation « sérialisation » est choisi ;
• mais encore les paramètres liés aux nombres de divers processus internes, ou de transactions préparées.

Les SLRU :

Les SLRU (Simple Least Recently Used Buffers) sont de petits caches pour certaines métadonneés de diverses natures : transactions et sous-transactions en cours, horodatage des commits, notifications par NOTIFY…

Le mécanisme a été revu en PostgreSQL 17 et, depuis, il est possible de modifier la taille des SLRU avec les paramètres commit_timestamp_buffers, multixact_member_buffers, multixact_offset_buffers, notify_buffers, serializable_buffers, subtransaction_buffers et transaction_buffers.

Les valeurs par défaut ne font parfois que 16 blocs. Les augmenter peut être intéressant si l’on repère une contention sur l’un d’eux. Un indice est la présence récurrente de wait events avec « SLRU » dans le nom. (Pour la liste des wait events, voir la table pg_wait_events, à partir de la version 17 également. Ils se voient dans pg_stat_activity. Il existe aussi une vue pg_stat_slru.)

Modification :

Toute modification des paramètres régissant la mémoire partagée imposent un redémarrage de l’instance.

À partir de la version 15, le paramètre shared_memory_size permet de connaître la taille complète de mémoire partagée allouée (un peu supérieure à shared_buffers en pratique). Dans le cadre de l’utilisation des Huge Pages, il est possible de consulter le paramètre shared_memory_size_in_huge_pages pour connaître le nombre de pages mémoires nécessaires (mais on ne peut savoir ici si elles sont utilisées) :

postgres=# \dconfig shared*
               Liste des paramètres de configuration
            Paramètre             |             Valeur              
----------------------------------+---------------------------------
 shared_buffers                   | 12GB
 shared_memory_size               | 12835MB
 shared_memory_size_in_huge_pages | 6418
 ...

Des zones de mémoire partagée non statiques peuvent exister : par exemple, à l’exécution d’une requête parallélisée, les processus impliqués utilisent de la mémoire partagée dynamique. Depuis PostgreSQL 14, une partie peut être pré-allouée avec le paramètre min_dynamic_shared_memory (0 par défaut).

Les processus de PostgreSQL ont accès à la mémoire partagée, définie principalement par shared_buffers, mais ils ont aussi leur mémoire propre. Cette mémoire n’est utilisable que par le processus l’ayant allouée.

• Le paramètre le plus important est work_mem, qui définit la taille maximale de la mémoire de travail d’un ORDER BY, de certaines jointures, pour la déduplication… que peut utiliser un processus sur un nœud de requête, principalement lors d’opérations de tri ou regroupement.
• Autre paramètre capital, maintenance_work_mem qui est la mémoire utilisable pour les opérations de maintenance lourdes : VACUUM, CREATE INDEX, REINDEX, ajouts de clé étrangère…

Cette mémoire liée au processus est rendue immédiatement après la fin de l’ordre concerné.

• Il existe aussi logical_decoding_work_mem (défaut : 64 Mo), utilisable pour chacun des flux de réplication logique (s’il y en a, ils sont rarement nombreux).

Opérations de maintenance & maintenance_work_mem :

maintenance_work_mem peut être monté à 256 Mo à 1 Go, voire plus sur les machines récentes, car il concerne des opérations lourdes (indexation, nettoyage des index par VACUUM…). Leurs consommations de RAM s’additionnent, mais en pratique ces opérations sont rarement exécutées plusieurs fois simultanément.

Monter au-delà de 1 Go n’a d’intérêt que pour la création ou la réindexation de très gros index.

autovacuum_work_mem est la mémoire que s’autorise à prendre l’autovacuum pour les nettoyages d’index, et ce pour chaque worker de l’autovacuum (3 maximum par défaut). Par défaut, ce paramètre reprend maintenance_work_mem, et est généralement laissé tel quel.

Paramétrage de work_mem :

Pour work_mem, c’est beaucoup plus compliqué.

Si work_mem est trop bas, beaucoup d’opérations de tri, y compris nombre de jointures, ne s’effectueront pas en RAM. Par exemple, si une jointure par hachage impose d’utiliser 100 Mo en mémoire, mais que work_mem vaut 10 Mo, PostgreSQL écrira des dizaines de Mo sur disque à chaque appel de la jointure. Si, par contre, le paramètre work_mem vaut 120 Mo, aucune écriture n’aura lieu sur disque, ce qui accélérera forcément la requête.

Trop de fichiers temporaires peuvent ralentir les opérations, voire saturer le disque. Un work_mem trop bas peut aussi contraindre le planificateur à choisir des plans d’exécution moins optimaux.

Suivant la complexité des requêtes, il est possible qu’un processus utilise plusieurs fois work_mem (par exemple si une requête fait une jointure et un tri, ou qu’un nœud est parallélisé). À l’inverse, beaucoup de requêtes ne nécessitent aucune mémoire de travail.

La valeur de work_mem dépend donc beaucoup de la mémoire disponible, des requêtes et du nombre de connexions actives.

Si le nombre de requêtes simultanées est important, work_mem devra être faible. Avec peu de requêtes simultanées, work_mem pourra être augmenté sans risque.

Il n’y a pas de formule de calcul miracle. Une première estimation courante, bien que très conservatrice, peut être :

work_mem = mémoire / max_connections

On obtient alors, sur un serveur dédié avec 16 Go de RAM et 200 connexions autorisées :

work_mem = 80MB

Mais max_connections est fréquemment surdimensionné, et beaucoup de sessions sont inactives. work_mem est alors sous-dimensionné.

Plus finement, Christophe Pettus propose en première intention :

work_mem = 4 × mémoire libre / max_connections

Soit, pour une machine dédiée avec 16 Go de RAM, donc 4 Go de shared buffers, et 200 connections :

work_mem = 240MB

Dans l’idéal, si l’on a le temps pour une étude, on montera work_mem jusqu’à voir disparaître l’essentiel des fichiers temporaires dans les traces, tout en restant loin de saturer la RAM lors des pics de charge.

En pratique, le défaut de 4 Mo est très conservateur, souvent insuffisant. Généralement, la valeur varie entre 10 et 100 Mo. Au-delà de 100 Mo, il y a souvent un problème ailleurs : des tris sur de trop gros volumes de données, une mémoire insuffisante, un manque d’index (utilisés pour les tris), etc. Des valeurs vraiment grandes ne sont valables que sur des systèmes d’infocentre.

Augmenter globalement la valeur du work_mem peut parfois mener à une consommation excessive de mémoire. Il est possible de ne la modifier que le temps d’une session pour les besoins d’une requête ou d’un traitement particulier :

SET work_mem TO '30MB' ;

hash_mem_multiplier :

hash_mem_multiplier est un paramètre multiplicateur, qui peut s’appliquer à certaines opérations (le hachage, lors de jointures ou agrégations). Par défaut, il vaut 1 en versions 13 et 14, et 2 à partir de la 15. Le seuil de consommation fixé par work_mem est alors multiplié d’autant. hash_mem_multiplier permet de donner plus de RAM à ces opérations sans augmenter globalement work_mem. Il peut lui aussi être modifié dans une session.

Tables temporaires

Les tables temporaires (et leurs index) sont locales à chaque session, et disparaîtront avec elle. Elles sont tout de même écrites sur disque dans le répertoire de la base.

Le cache dédié à ces tables pour minimiser les accès est séparé des shared buffers, parce qu’il est propre à la session. Sa taille dépend du paramètre temp_buffers. La valeur par défaut (8 Mo) peut être insuffisante dans certains cas pour éviter les accès aux fichiers de la table. Elle doit être augmentée dans la session avant la création de la table temporaire.

Il ne faut pas laisser inutilement ouvertes des sessions ayant créé des tables temporaires, sinon la mémoire n’est jamais rendue.

PostgreSQL dispose de son propre mécanisme de cache. Toute donnée lue l’est de ce cache. Si la donnée n’est pas dans le cache, le processus devant effectuer cette lecture l’y recopie avant d’y accéder dans le cache.

L’unité de travail du cache est le bloc (de 8 ko par défaut) de données. C’est-à-dire qu’un processus charge toujours un bloc dans son entier quand il veut lire un enregistrement. Chaque bloc du cache correspond donc exactement à un bloc d’un fichier d’un objet. Cette information est d’ailleurs, bien sûr, stockée en en-tête du bloc de cache.

Tous les processus accèdent à ce cache unique. C’est la zone la plus importante, par la taille, de la mémoire partagée. Toute modification de données est tracée dans le journal de transaction, puis modifiée dans ce cache. Elle n’est donc pas écrite sur le disque par le processus effectuant la modification, sauf en dernière extrémité (voir Synchronisation en arrière plan).

Tout accès à un bloc nécessite la prise de verrous. Un pin lock, qui est un simple compteur, indique qu’un processus se sert du buffer, et qu’il n’est donc pas réutilisable. C’est un verrou potentiellement de longue durée. Il existe de nombreux autres verrous, de plus courte durée, pour obtenir le droit de modifier le contenu d’un buffer, d’un enregistrement dans un buffer, le droit de recycler un buffer… mais tous ces verrous n’apparaissent pas dans la table pg_locks, car ils sont soit de très courte durée, soit partagés (comme le spin lock). Il est donc très rare qu’ils soient sources de contention, mais le diagnostic d’une contention à ce niveau est difficile.

Les lectures et écritures de PostgreSQL passent toutefois toujours par le cache du système. Il n’y a pas de direct I/O comme dans certains SGBD concurrents. Les deux caches risquent donc de stocker les mêmes informations. Les algorithmes d’éviction sont différents entre le système et PostgreSQL, PostgreSQL disposant de davantage d’informations sur l’utilisation des données, et le type d’accès. La redondance est habituellement limitée, mais il existe des cas problématiques. En conséquence, des travaux sont en cours pour contourner le cache du système quand cela est pertinent. Cela implique de réimplémenter certaines fonctionnalités fournies par le noyau (prefetching, read-ahead), en les optimisant pour PostgreSQL. Une première étape apparaît en version 17. PostgreSQL sait depuis longtemps lire plusieurs blocs d’un coup (fonction pread()), mais ce n’est pas très bien adapté à la nature fragmentée de son cache. Avec l’utilisation des vectored I/O, PostgreSQL peut lire plusieurs blocs contigus sur le système de fichiers, et les écrire à des positions disjointes en mémoire virtuelle, avec un seul appel système preadv() (voir la page de manuel), (Avec un OS qui ne connait pas la fonction, comme Windows, PostgreSQL se contente d’appeler pread() plusieurs fois.) Avec la valeur par défaut d’un nouveau paramètre, io_combine_limit, et des blocs de taille standard, on a jusqu’à 16 fois moins d’appels système pour la lecture des blocs en dehors du cache PostgreSQL. En version 17, seule l’implémentation du parcours séquentiel, celle de ANALYZE, et celle de pg_prewarm utilisent cette nouvelle API. Nous conseillons de ne pas changer la valeur de ce paramètre sans tests sérieux montrant qu’un changement est bénéfique.

Dimensionner correctement ce cache est important pour de nombreuses raisons.

Un cache trop petit :

• ralentit l’accès aux données, car des données importantes risquent de ne plus s’y trouver ;
• force l’écriture de données sur le disque, ralentissant les sessions qui auraient pu effectuer uniquement des opérations en mémoire ;
• limite le regroupement d’écritures, dans le cas où un bloc viendrait à être modifié plusieurs fois.

Un cache trop grand :

• limite l’efficacité du cache système en augmentant la redondance de données entre les deux caches ;
• peut ralentir PostgreSQL, car la gestion des shared_buffers a un coût de traitement ;
• réduit la mémoire disponible pour d’autres opérations (tris en mémoire notamment), et peut provoquer plus d’erreurs de saturation de la mémoire.

Sur une machine dédiée de 32 Go de RAM, cela donne donc :

shared_buffers = 8GB

Le défaut de 128 Mo n’est donc pas adapté à un serveur sur une machine récente.

Suivant les cas, une valeur inférieure ou supérieure à 25 % sera encore meilleure pour les performances, mais il faudra tester avec votre charge (en lecture, en écriture, et avec le bon nombre de clients).

Le cache système limite la plupart du temps l’impact d’un mauvais paramétrage de shared_buffers, et il est moins grave de sous-dimensionner un peu shared_buffers que de le sur-dimensionner.

Un cache supplémentaire est disponible pour PostgreSQL : celui du système d’exploitation. Il est donc intéressant de préciser à PostgreSQL la taille approximative du cache, ou du moins de la part du cache qu’occupera PostgreSQL. Le paramètre effective_cache_size n’a pas besoin d’être très précis, mais il permet une meilleure estimation des coûts par le moteur. Il est paramétré habituellement aux alentours des ⅔ de la taille de la mémoire vive du système d’exploitation, pour un serveur dédié.

Par exemple pour une machine avec 32 Go de RAM, on peut paramétrer en première intention dans postgresql.conf :

shared_buffers = '8GB'
effective_cache_size = '21GB'

Cela sera à ajuster en fonction du comportement observé de l’application.

Les principales notions à connaître pour comprendre le mécanisme de gestion du cache de PostgreSQL sont :

Buffer pin

Un processus voulant accéder à un bloc du cache (buffer) doit d’abord épingler (to pin) ce bloc pour forcer son maintien en cache. Pour ce faire, il incrémente le compteur buffer pin, puis le décrémente quand il a fini. Un buffer dont le pin est différent de 0 est donc utilisé et ne peut être recyclé.

Buffer dirty/clean

Un buffer est dirty (sale) si son contenu a été modifié en mémoire, mais pas encore sur disque. Le fichier de données n’est donc plus à jour (mais a bien été pérennisée sur disque dans les journaux de transactions). Il faudra écrire ce bloc avant de pouvoir le supprimer du cache. Nous verrons plus loin comment.

Un buffer est clean (propre) s’il n’a pas été modifié. Le bloc peut être supprimé du cache sans nécessiter le coût d’une écriture sur disque.

Compteur d’utilisation

Cette technique vise à garder dans le cache les blocs les plus utilisés.

À chaque fois qu’un processus a fini de se servir d’un buffer (quand il enlève son pin), ce compteur est incrémenté (à hauteur de 5 dans l’implémentation actuelle). Il est décrémenté par le clocksweep évoqué plus bas.

Seul un buffer dont le compteur est à zéro peut voir son contenu remplacé par un nouveau bloc.

Clocksweep (ou algorithme de balayage)

Un processus ayant besoin de charger un bloc de données dans le cache doit trouver un buffer disponible. Soit il y a encore des buffers vides (cela arrive principalement au démarrage d’une instance), soit il faut libérer un buffer.

L’algorithme clocksweep parcourt la liste des buffers de façon cyclique à la recherche d’un buffer unpinned dont le compteur d’utilisation est à zéro. Tout buffer visité voit son compteur décrémenté de 1. Le système effectue autant de passes que nécessaire sur tous les blocs jusqu’à trouver un buffer à 0. Ce clocksweep est effectué par chaque processus, au moment où ce dernier a besoin d’un nouveau buffer.

Une table peut être plus grosse que les shared buffers. Sa lecture intégrale (lors d’un parcours complet ou d’une opération de maintenance) ne doit pas mener à l’éviction de tous les blocs du cache. PostgreSQL utilise donc plutôt un ring buffer quand la taille de la relation dépasse ¼ de shared_buffers. Un ring buffer est une zone de mémoire gérée à l’écart des autres blocs du cache.

Pour un parcours complet d’une table, cette zone est de 256 ko (taille choisie pour tenir dans un cache L2). Si un bloc y est modifié (UPDATE…), il est traité hors du ring buffer comme un bloc sale normal.

Pour un VACUUM ou un ANALYZE, la même technique est utilisée, mais les écritures se font dans le ring buffer. Sa taille est de 2 Mo (256 ko seulement jusque PostgreSQL 16 inclus). À partir de PostgreSQL 16, elle peut être augmentée pour accélérer les opérations avec le paramètre vacuum_buffer_usage_limit ou ponctuellement ainsi :

vacuumdb --buffer-usage-limit='16MB'

ou ainsi :

VACUUM (ANALYZE, BUFFER_USAGE_LIMIT '16MB') ;

En montant à quelques mégaoctets, l’accélération de la vitesse du VACUUM peut être notable. C’est aussi très intéressant pour accélérer ANALYZE.

Pour les écritures en masse (notamment COPY ou CREATE TABLE AS SELECT), une technique similaire utilise un ring buffer de 16 Mo.

Le site The Internals of PostgreSQL et un README dans le code de PostgreSQL entrent plus en détail sur tous ces sujets tout en restant lisibles.

Deux extensions sont livrées dans les contribs de PostgreSQL qui impactent le cache.

pg_buffercache permet de consulter le contenu du cache (à utiliser de manière très ponctuelle). La requête suivante indique les objets non système de la base en cours, présents dans le cache et s’ils sont dirty ou pas :

pgbench=# CREATE EXTENSION pg_buffercache ;

pgbench=# SELECT
    relname,
    isdirty,
    count(bufferid) AS blocs,
    pg_size_pretty(count(bufferid) * current_setting ('block_size')::int) AS taille
FROM pg_buffercache b
INNER JOIN pg_class c ON c.relfilenode = b.relfilenode
WHERE relname NOT LIKE 'pg\_%'
GROUP BY
        relname,
        isdirty
ORDER BY 1, 2 ;

        relname        | isdirty | blocs | taille
-----------------------+---------+-------+---------
 pgbench_accounts      | f       |  8398 | 66 MB
 pgbench_accounts      | t       |  4622 | 36 MB
 pgbench_accounts_pkey | f       |  2744 | 21 MB
 pgbench_branches      | f       |    14 | 112 kB
 pgbench_branches      | t       |     2 | 16 kB
 pgbench_branches_pkey | f       |     2 | 16 kB
 pgbench_history       | f       |   267 | 2136 kB
 pgbench_history       | t       |   102 | 816 kB
 pgbench_tellers       | f       |    13 | 104 kB
 pgbench_tellers_pkey  | f       |     2 | 16 kB

L’extension pg_prewarm permet de précharger un objet dans le cache de PostgreSQL (si le cache est assez gros, bien sûr) :

=# CREATE EXTENSION pg_prewarm ;
=# SELECT pg_prewarm ('nom_table_ou_index', 'buffer') ;

Il permet même de recharger dès le démarrage le contenu du cache lors d’un arrêt (voir la documentation).

Ces deux outils sont décrits dans le module de formation X2.

Afin de limiter les attentes des sessions interactives, PostgreSQL dispose de deux processus complémentaires, le background writer et le checkpointer. Tous deux ont pour rôle d’écrire sur le disque les buffers dirty (« sales ») de façon asynchrone. Le but est de ne pas impacter les temps de traitement des requêtes des utilisateurs, donc que les écritures soient lissées sur de grandes plages de temps, pour ne pas saturer les disques, mais en nettoyant assez souvent le cache pour qu’il y ait toujours assez de blocs libérables en cas de besoin.

Le checkpointer écrit généralement l’essentiel des blocs dirty. Lors des checkpoints, il synchronise sur disque tous les blocs modifiés. Son rôle est de lisser cette charge sans saturer les disques.

Comme le checkpointer n’est pas censé passer très souvent, le background writer anticipe les besoins des sessions, et écrit lui-même une partie des blocs dirty.

Lors d’écritures intenses, il est possible que ces deux mécanismes soient débordés. Les processus backend ne trouvent alors plus en cache de bloc libre ou libérable, et doivent alors écrire eux-mêmes dans les fichiers de données (après les journaux de transaction, bien sûr). Cette situation est évidemment à éviter, ce qui implique généralement de rendre le background writer plus agressif.

Nous verrons plus loin les paramètres concernés.

La journalisation, sous PostgreSQL, permet de garantir l’intégrité des fichiers, et la durabilité des opérations :

• l’intégrité : la base reste cohérente quoi qu’il arrive ; un arrêt d’urgence ne corrompra pas la base.
• la durabilité : toute donnée validée (COMMIT) est écrite physiquement, et un arrêt brutal immédiatement ne va pas la faire disparaître (excepté la perte de tous les disques de stockage et réplicas, bien sûr).

Pour cela, le mécanisme est relativement simple : toute modification affectant un fichier sera d’abord écrite dans le journal. Les modifications affectant les vrais fichiers de données ne sont écrites qu’en mémoire, dans les shared buffers.

Les écritures dans le journal, bien que synchrones, sont relativement performantes, car elles sont séquentielles (moins de déplacement de têtes pour les disques magnétiques). Il n’y a que le fichier en cours à synchroniser à chaque COMMIT.

Ce n’est généralement que bien plus tard que les modificationss seront écrites de façon asynchrone, soit par un processus recherchant un buffer libre, soit par le background writer, soit par le checkpointer. Ce dernier processus sait étaler la charge en écriture dans les fichiers de données sur plusieurs minutes, et dans l’idéal il est seul à s’en charger.

Il existe plusieurs configurations et astuces pour arbitrer entre le niveau de durabilité des données exigé et les contraintes de performances : secondaire en réplication synchrone pour une sécurité maximale, désactivation partielle du mécanisme d’enregistrement synchrone des journaux dans une session, tables de travail non journalisées (unlogged), regroupement des insertions pour réduire l’impact des COMMIT… Aucune ne remet en cause l’intégrité définie dans les modèle de données.

Rappelons que les journaux de transaction sont des fichiers de 16 Mo par défaut, stockés dans PGDATA/pg_wal, dont les noms comportent le numéro de timeline, un numéro de journal de 4 Go et un numéro de segment, en hexadécimal.

$ ls -l
total 2359320
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007C
-rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007D
...
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000023
-rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000024
drwx------ 2 postgres postgres    16384 Mar 26 16:28 archive_status

Le sous-répertoire archive_status est lié à l’archivage.

D’autres plus petits répertoires comme pg_xact, qui contient les statuts des transactions passées, ou pg_commit_ts, pg_multixact, pg_serial, pg_snapshots, pg_subtrans ou encore pg_twophase sont également impliqués.

Tous ces répertoires sont critiques, gérés par PostgreSQL, et ne doivent pas être modifiés !

PostgreSQL trace les modifications de données dans les journaux WAL. Ceux-ci sont générés au fur et à mesure des écritures.

Si le système ou l’instance sont arrêtés brutalement, il faut que PostgreSQL puisse appliquer le contenu des journaux non traités sur les fichiers de données. Il a donc besoin de savoir à partir d’où rejouer ces données. Ce point est ce qu’on appelle un checkpoint, ou « point de reprise ».

Les principes sont les suivants :

Toute entrée dans les journaux est idempotente, c’est-à-dire qu’elle peut être appliquée plusieurs fois, sans que le résultat final ne soit changé. C’est nécessaire, au cas où la récupération serait interrompue, ou si un fichier sur lequel la reprise est effectuée était plus récent que l’entrée qu’on souhaite appliquer.

Tout fichier de journal antérieur au dernier point de reprise valide peut être supprimé ou recyclé, car il n’est plus nécessaire à la récupération.

PostgreSQL a besoin des fichiers de données qui contiennent toutes les données jusqu’au point de reprise. Ils peuvent être plus récents et contenir des informations supplémentaires, ce n’est pas un problème.

C’est le processus checkpointer qui est responsable de l’écriture des buffers devant être synchronisés durant un checkpoint.

Plusieurs paramètres influencent le comportement des checkpoints.

Dans l’idéal les checkpoints sont périodiques. Le temps maximum entre deux checkpoints est fixé par checkpoint_timeout (par défaut 300 secondes). C’est parfois un peu court pour les instances actives.

Le checkpoint intervient aussi quand il y a beaucoup d’écritures et que le volume des journaux dépasse le seuil défini par le paramètre max_wal_size (1 Go par défaut). Un checkpoint est alors déclenché.

L’ordre CHECKPOINT déclenche aussi un checkpoint sans attendre. En fait, il sert surtout à des utilitaires.

Une fois le checkpoint terminé, les journaux sont à priori inutiles. Ils peuvent être effacés pour redescendre en-dessous de la quantité définie par max_wal_size. Ils sont généralement « recyclés », c’est-à-dire renommés, et prêt à être réécris.

Cependant, les journaux peuvent encore être retenus dans pg_wal/ si l’archivage a été activé et que certains n’ont pas été sauvegardés, ou si l’on garde des journaux pour des serveurs secondaires.

Il existe un paramètre min_wal_size (défaut : 80 Mo) qui fixe la quantité minimale de journaux à tout moment, même sans activité en écriture. Ils seront donc vides et prêts à être remplis en cas d’écriture imprévue. Bien sûr, s’il y a des grosses écritures, PostgreSQL créera au besoin des journaux supplémentaires, jusque max_wal_size, voire au-delà. Mais il lui faudra les créer et les remplir intégralement de zéros avant utilisation.

Après un gros pic d’activité suivi d’un checkpoint et d’une période calme, la quantité de journaux va très progressivement redescendre de max_wal_size à min_wal_size.

Le dimensionnement de ces paramètres est très dépendant du contexte, de l’activité habituelle, et de la régularité des écritures. Le but est d’éviter des gros pics d’écriture, et donc d’avoir des checkpoints essentiellement périodiques, même si des opérations ponctuelles peuvent y échapper (gros chargements, grosse maintenance…).

Des checkpoints espacés ont aussi pour effet de réduire la quantité totale de journaux écrits. En effet, par défaut, un bloc modifié est intégralement écrit dans les journaux à sa première modification après un checkpoint, mais par la suite seules les modifications de ce bloc sont journalisées. Espacer les checkpoints peut économiser beaucoup de place disque quand les journaux sont archivés, et du réseau s’ils sont répliqués. Par contre, un écart plus grand entre checkpoints peut allonger la restauration après un arrêt brutal, car il y aura plus de journaux à rejouer.

En pratique, une petite instance se contentera du paramétrage de base ; une plus grosse montera max_wal_size à plusieurs gigaoctets.

Pour min_wal_size, rien n’interdit de prendre une valeur élevée pour mieux absorber les montées d’activité brusques.

Enfin, le checkpoint comprend un sync sur disque final. Toujours pour éviter des à-coups d’écriture, PostgreSQL demande au système d’exploitation de forcer un vidage du cache quand checkpoint_flush_after a déjà été écrit (par défaut 256 ko). Avant PostgreSQL 9.6, ceci se paramétrait au niveau de Linux en abaissant les valeurs des sysctl vm.dirty_*. Il y a un intérêt à continuer de le faire, car PostgreSQL n’est pas seul à écrire de gros fichiers (exports pg_dump, copie de fichiers…).

Quand le checkpoint démarre, il vise à lisser au maximum le débit en écriture. La durée d’écriture des données se calcule à partir d’une fraction de la durée d’exécution des précédents checkpoints, fraction fixée par le paramètre checkpoint_completion_target. Sa valeur par défaut est celle préconisée par la documentation pour un lissage maximum, soit 0,9 (depuis la version 14, et auparavant le défaut de 0,5 était fréquemment corrigé). Par défaut, PostgreSQL prévoit donc une durée maximale de 300 × 0,9 = 270 secondes pour opérer son checkpoint, mais cette valeur pourra évoluer ensuite suivant la durée réelle des checkpoints précédents.

Il est possible de suivre le déroulé des checkpoints dans les traces si log_checkpoints est à on. De plus, si deux checkpoints sont rapprochés d’un intervalle de temps inférieur à checkpoint_warning (défaut : 30 secondes), un message d’avertissement sera tracé. Une répétition fréquente indique que max_wal_size est bien trop petit.

Comme le checkpointer ne s’exécute pas très souvent, et ne s’occupe pas des blocs salis depuis son exécution courante, il est épaulé par le background writer. Celui-ci pour but de nettoyer une partie des blocs dirty pour faire de la place à d’autres. Il s’exécute beaucoup plus fréquemment que le checkpointer mais traite moins de blocs à chaque fois.

À intervalle régulier, le background writer synchronise un nombre de buffers proportionnel à l’activité sur l’intervalle précédent. Quatre paramètres régissent son comportement :

• bgwriter_delay (défaut : 200 ms) : la fréquence à laquelle se réveille le background writer ;
• bgwriter_lru_maxpages (défaut : 100) : le nombre maximum de pages pouvant être écrites sur chaque tour d’activité. Ce paramètre permet d’éviter que le background writer ne veuille synchroniser trop de pages si l’activité des sessions est trop intense : dans ce cas, autant les laisser effectuer elles-mêmes les synchronisations, étant donné que la charge est forte ;
• bgwriter_lru_multiplier (defaut : 2) : le coefficient multiplicateur utilisé pour calculer le nombre de buffers à libérer par rapport aux demandes d’allocation sur la période précédente ;
• bgwriter_flush_after (défaut : 512 ko sous Linux, 0 ou désactivé ailleurs) : à partir de quelle quantité de données écrites une synchronisation sur disque est demandée.

Pour les paramètres bgwriter_lru_maxpages et bgwriter_lru_multiplier, lru signifie Least Recently Used (« moins récemment utilisé »). Ainsi le background writer synchronisera les pages du cache qui ont été utilisées le moins récemment.

Ces paramètres permettent de rendre le background writer plus agressif si la supervision montre que les processus backends écrivent trop souvent les blocs de données eux-mêmes, faute de blocs libres dans le cache, ce qui évidemment ralentit l’exécution du point de vue du client.

Évidemment, certaines écritures massives ne peuvent être absorbées par le background writer. Elles provoquent des écritures par les processus backend et des checkpoints déclenchés.

La journalisation s’effectue par écriture dans les journaux de transactions. Toutefois, afin de ne pas effectuer des écritures synchrones pour chaque opération dans les fichiers de journaux, les écritures sont préparées dans des tampons (buffers) en mémoire. Les processus écrivent donc leur travail de journalisation dans des buffers, ou WAL buffers. Ceux-ci sont vidés quand une session demande validation de son travail (COMMIT), qu’il n’y a plus de buffer disponible, ou que le walwriter se réveille (wal_writer_delay).

Écrire un ou plusieurs blocs séquentiels de façon synchrone sur un disque a le même coût à peu de chose près. Ce mécanisme permet donc de réduire fortement les demandes d’écriture synchrone sur le journal, et augmente donc les performances.

Afin d’éviter qu’un processus n’ait tous les buffers à écrire à l’appel de COMMIT, et que cette opération ne dure trop longtemps, un processus d’arrière-plan appelé walwriter écrit à intervalle régulier tous les buffers à synchroniser.

Ce mécanisme est géré par ces paramètres, rarement modifiés :

• wal_buffers : taille des WAL buffers, soit par défaut 1/32e de shared_buffers avec un maximum de 16 Mo (la taille d’un segment), des valeurs supérieures (par exemple 128 Mo) pouvant être intéressantes pour les très grosses charges ;
• wal_writer_delay (défaut : 200 ms) : intervalle auquel le walwriter se réveille pour écrire les buffers non synchronisés ;
• wal_writer_flush_after (défaut : 1 Mo) : au-delà de cette valeur, les journaux écrits sont synchronisés sur disque pour éviter l’accumulation dans le cache de l’OS.

Pour la fiabilité, on ne touchera pas à ceux-ci :

• wal_sync_method : appel système à utiliser pour demander l’écriture synchrone (sauf très rare exception, PostgreSQL détecte tout seul le bon appel système à utiliser) ;
• full_page_writes : doit-on réécrire une image complète d’une page suite à sa première modification après un checkpoint ? Sauf cas très particulier, comme un système de fichiers Copy On Write comme ZFS ou btrfs, ce paramètre doit rester à on pour éviter des corruptions de données (et il est alors conseillé d’espacer les checkpoints pour réduire la volumétrie des journaux) ;
• fsync : doit-on réellement effectuer les écritures synchrones ? Le défaut est on et il est très fortement conseillé de le laisser ainsi en production. Avec off, les performances en écritures sont certes très accélérées, mais en cas d’arrêt d’urgence de l’instance, les données seront totalement corrompues ! Ce peut être intéressant pendant le chargement initial d’une nouvelle instance par exemple, sans oublier de revenir à on après ce chargement initial. (D’autres paramètres et techniques existent pour accélérer les écritures et sans corrompre votre instance, si vous êtes prêt à perdre certaines données non critiques : synchronous_commit à off, les tables unlogged…)

wal_compression compresse les blocs complets enregistrés dans les journaux de transactions, réduisant le volume des WAL, la charge en écriture sur les disques, la volumétrie des journaux archivés des sauvegardes PITR.

Comme il y a moins de journaux, leur rejeu est aussi plus rapide, ce qui accélère la réplication et la reprise après un crash. Le prix est une augmentation de la consommation en CPU.

Les détails et un exemple figurent dans ce billet du blog Dalibo.

Depuis PostgreSQL 15, on peut même choisir l’algorithme : pglz, lz4 ou zstd. on est le synonyme de pglz… qui est sans doute le moins bon des trois (voir ce petit test), surtout en terme de consommation CPU.

Le coût d’un fsync est parfois rédhibitoire. Avec certains sacrifices, il est parfois possible d’améliorer les performances sur ce point.

Le paramètre synchronous_commit (défaut : on) indique si la validation de la transaction en cours doit déclencher une écriture synchrone dans le journal. Le défaut permet de garantir la pérennité des données dès la fin du COMMIT.

Mais ce paramètre peut être modifié dans chaque session par une commande SET, et passé à off s’il est possible d’accepter une petite perte de données pourtant committées. La perte peut monter à 3 × wal_writer_delay (600 ms) ou wal_writer_flush_after (1 Mo) octets écrits. On accélère ainsi notablement les flux des petites transactions. Les transactions où le paramètre reste à on continuent de profiter de la sécurité maximale. La base restera, quoi qu’il arrive, cohérente. (Ce paramètre permet aussi de régler le niveau des transactions synchrones avec des secondaires.)

Il existe aussi commit_delay (défaut : 0) et commit_siblings (défaut : 5) comme mécanisme de regroupement de transactions. S’il y au moins commit_siblings transactions en cours, PostgreSQL attendra jusqu’à commit_delay (en microsecondes) avant de valider une transaction pour permettre à d’autres transactions de s’y rattacher. Ce mécanisme, désactivé par défaut, accroît la latence de certaines transactions afin que plusieurs soient écrites ensembles, et n’apporte un gain de performance global qu’avec de nombreuses petites transactions en parallèle, et des disques classiques un peu lents. (En cas d’arrêt brutal, il n’y a pas à proprement parler de perte de données puisque les transactions délibérément retardées n’ont pas été signalées comme validées.)

Le système de journalisation de PostgreSQL étant très fiable, des fonctionnalités très intéressantes ont été bâties dessus.

Les journaux permettent de rejouer, suite à un arrêt brutal de la base, toutes les modifications depuis le dernier checkpoint. Les journaux devenus obsolète depuis le dernier checkpoint (l’avant-dernier avant la version 11) sont à terme recyclés ou supprimés, car ils ne sont plus nécessaires à la réparation de la base.

Le but de l’archivage est de stocker ces journaux, afin de pouvoir rejouer leur contenu, non plus depuis le dernier checkpoint, mais depuis une sauvegarde. Le mécanisme d’archivage permet de repartir d’une sauvegarde binaire de la base (c’est-à-dire des fichiers, pas un pg_dump), et de réappliquer le contenu des journaux archivés.

Il suffit de rejouer tous les journaux depuis le checkpoint précédent la sauvegarde jusqu’à la fin de la sauvegarde, ou même à un point précis dans le temps. L’application de ces journaux permet de rendre à nouveau cohérents les fichiers de données, même si ils ont été sauvegardés en cours de modification.

Ce mécanisme permet aussi de fournir une sauvegarde continue de la base, alors même que celle-ci travaille.

Tout ceci est vu dans le module Point In Time Recovery.

Même si l’archivage n’est pas en place, il faut connaître les principaux paramètres impliqués :

wal_level :

Il vaut replica par défaut depuis la version 10. Les journaux contiennent les informations nécessaires pour une sauvegarde PITR ou une réplication vers une instance secondaire.

Si l’on descend à minimal (défaut jusqu’en version 9.6 incluse), les journaux ne contiennent plus que ce qui est nécessaire à une reprise après arrêt brutal sur le serveur en cours. Ce peut être intéressant pour réduire, parfois énormément, le volume des journaux générés, si l’on a bien une sauvegarde non PITR par ailleurs.

Le niveau logical est destiné à la réplication logique.

(Avant la version 9.6 existaient les niveaux intermédiaires archive et hot_standby, respectivement pour l’archivage et pour un serveur secondaire en lecture seule. Ils sont toujours acceptés, et assimilés à replica.)

archive_mode & archive_command/archive_library :

Il faut qu’archive_mode soit à on pour activer l’archivage. Les journaux sont alors copiés grâce à une commande shell à fournir dans archive_command ou grâce à une bibliothèque partagée indiquée dans archive_library (version 15 ou postérieure). En général on y indiquera ce qu’exige un outil de sauvegarde dédié (par exemple pgBackRest ou barman) dans sa documentation.

La restauration d’une sauvegarde peut se faire en continu sur un autre serveur, qui peut même être actif (bien que forcément en lecture seule). Les journaux peuvent être :

• envoyés régulièrement vers le secondaire, qui les rejouera : c’est le principe de la réplication par log shipping ;
• envoyés par fragments vers cet autre serveur : c’est la réplication par streaming.

Ces thèmes ne seront pas développés ici. Signalons juste que la réplication par log shipping implique un archivage actif sur le primaire, et l’utilisation de restore_command (et d’autres pour affiner) sur le secondaire. Le streaming permet de se passer d’archivage, même si coupler streaming et sauvegarde PITR est une bonne idée. Sur un PostgreSQL récent, le primaire a par défaut le nécessaire activé pour se voir doté d’un secondaire : wal_level est à replica ; max_wal_senders permet d’ouvrir des processus dédiés à la réplication ; et l’on peut garder des journaux en paramétrant wal_keep_size (ou wal_keep_segments avant la version 13) pour limiter les risques de décrochage du secondaire.

Une configuration supplémentaire doit se faire sur le serveur secondaire, indiquant comment récupérer les fichiers de l’archive, et comment se connecter au primaire pour récupérer des journaux. Elle a lieu dans les fichiers recovery.conf (jusqu’à la version 11 comprise), ou (à partir de la version 12) postgresql.conf dans les sections évoquées plus haut, ou postgresql.auto.conf.

pgbench est un outil de test livré avec PostgreSQL. Son but est de faciliter la mise en place de benchmarks simples et rapides. Par défaut, il installe une base très simple, génère une activité plus ou moins intense et calcule le nombre de transactions par seconde et la latence. C’est ce qui sera fait ici dans cette introduction. On peut aussi lui fournir ses propres scripts.

La documentation complète est sur https://docs.postgresql.fr/current/pgbench.html. L’auteur principal, Fabien Coelho, a fait une présentation complète, en français, à la PG Session #9 de 2017.

Installation

L’outil est installé avec les paquets habituels de PostgreSQL, client ou serveur suivant la distribution.

Sur les distributions à paquets RPM (RockyLinux…), l’outil n’est pas dans le chemin par défaut, il faudra fournir le chemin complet (qui ne sera pas répété ici):

/usr/pgsql-17/bin/pgbench

Il est préférable de créer un rôle non privilégié dédié, qui possédera la base de donnée :

CREATE ROLE pgbench LOGIN PASSWORD 'unmotdepassebienc0mplexe';
CREATE DATABASE pgbench OWNER pgbench ;

Le pg_hba.conf doit éventuellement être adapté. La base par défaut s’initialise ainsi (ajouter --port et --host au besoin) :

pgbench -U -d pgbench --initialize --scale=100  pgbench

--scale permet de faire varier proportionnellement la taille de la base. À 100, la base pèsera 1,5 Go, avec 10 millions de lignes dans la table principale pgbench_accounts :

pgbench@pgbench=# \d+
                           Liste des relations
 Schéma |       Nom        | Type  | Propriétaire | Taille  | Description
--------+------------------+-------+--------------+---------+-------------
 public | pg_buffercache   | vue   | postgres     | 0 bytes |
 public | pgbench_accounts | table | pgbench      | 1281 MB |
 public | pgbench_branches | table | pgbench      | 40 kB   |
 public | pgbench_history  | table | pgbench      | 0 bytes |
 public | pgbench_tellers  | table | pgbench      | 80 kB   |

Générer de l’activité

Pour simuler une activité de 20 clients simultanés, répartis sur 4 processeurs, pendant 100 secondes :

pgbench -U pgbench -c 20 -j 4 -T100 pgbench

Pour afficher, ajouter --debug :

 UPDATE pgbench_accounts SET abalance = abalance + -3455 WHERE aid = 3789437;
 SELECT abalance FROM pgbench_accounts WHERE aid = 3789437;
 UPDATE pgbench_tellers SET tbalance = tbalance + -3455 WHERE tid = 134;
 UPDATE pgbench_branches SET bbalance = bbalance + -3455 WHERE bid = 78;
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
  VALUES (134, 78, 3789437, -3455, CURRENT_TIMESTAMP);

À la fin, s’affichent notamment le nombre de transactions (avec et sans le temps de connexion) et la durée moyenne d’exécution du point de vue du client (latency) :

 scaling factor: 100
 query mode: simple
 number of clients: 20
 number of threads: 4
 duration: 10 s
 number of transactions actually processed: 20433
 latency average = 9.826 ms
 tps = 2035.338395 (including connections establishing)
 tps = 2037.198912 (excluding connections establishing)

Modifier le paramétrage est facile grâce à la variable d’environnement PGOPTIONS :

PGOPTIONS='-c synchronous_commit=off -c commit_siblings=20' \
             pgbench -U pgbench -c 20 -j 4 -T100 pgbench  2>/dev/null

latency average = 6.992 ms
tps = 2860.465176 (including connections establishing)
tps = 2862.964803 (excluding connections establishing)

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m3_solutions.

Se connecter à la base de données b0 et créer une table t2 avec une colonne id de type integer.

Insérer 500 lignes dans la table t2 avec generate_series.

Pour réinitialiser les statistiques de t2 :

• utiliser la fonction pg_stat_reset_single_table_counters
• l’OID en paramètre est dans la table des relations pg_class, ou peut être trouvé avec 't2'::regclass

Afin de vider le cache, redémarrer l’instance PostgreSQL.

Se connecter à la base de données b0 et lire les données de la table t2.

Récupérer les statistiques IO pour la table t2 dans la vue système pg_statio_user_tables. Qu’observe-t-on ?

Lire de nouveau les données de la table t2 et consulter ses statistiques. Qu’observe-t-on ?

Lire de nouveau les données de la table t2 et consulter ses statistiques. Qu’observe-t-on ?

Ouvrir un premier terminal et laisser défiler le fichier de traces.

Dans un second terminal, activer la trace des fichiers temporaires ainsi que l’affichage du niveau LOG pour le client (il est possible de le faire sur la session uniquement).

Insérer un million de lignes dans la table t2 avec generate_series.

Activer le chronométrage dans la session (\timing on). Lire les données de la table t2 en triant par la colonne id Qu’observe-t-on ?

Configurer la valeur du paramètre work_mem à 100MB (il est possible de le faire sur la session uniquement).

Lire de nouveau les données de la table t2 en triant par la colonne id. Qu’observe-t-on ?

Se connecter à la base de données b1. Installer l’extension pg_buffercache.

Créer une table t2 avec une colonne id de type integer.

Insérer un million de lignes dans la table t2 avec generate_series.

Pour vider le cache de PostgreSQL, redémarrer l’instance.

Pour vider le cache du système d’exploitation, sous root :

# sync && echo 3 > /proc/sys/vm/drop_caches

Se connecter à la base de données b1. En utilisant l’extension pg_buffercache, que contient le cache de PostgreSQL ? (Compter les blocs pour chaque table ; au besoin s’inspirer de la requête du cours.)

Activer l’affichage de la durée des requêtes. Lire les données de la table t2, en notant la durée d’exécution de la requête. Que contient le cache de PostgreSQL ?

Lire de nouveau les données de la table t2. Que contient le cache de PostgreSQL ?

Configurer la valeur du paramètre shared_buffers à un quart de la RAM.

Redémarrer l’instance PostgreSQL.

Se connecter à la base de données b1 et extraire de nouveau toutes les données de la table t2. Que contient le cache de PostgreSQL ?

Modifier le contenu de la table t2, par exemple avec :

UPDATE t2 SET id = 0 WHERE id < 1000 ;

Que contient le cache de PostgreSQL ?

Exécuter un checkpoint. Que contient le cache de PostgreSQL ?