Configuration de PostgreSQL

Module M2

Dalibo SCOP

24.09

29 août 2024

Sur ce document

Formation	Module M2
Titre	Configuration de PostgreSQL
Révision	24.09
PDF	https://dali.bo/m2_pdf
EPUB	https://dali.bo/m2_epub
HTML	https://dali.bo/m2_html
Slides	https://dali.bo/m2_slides
TP	https://dali.bo/m2_tp
TP (solutions)	https://dali.bo/m2_solutions

Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse formation@dalibo.com !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

Paternité
Pas d’utilisation commerciale
Partage des conditions initiales à l’identique

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.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Configuration de PostgreSQL

Les paramètres en lecture seule
Les différents fichiers de configuration
- survol du contenu
Quelques paramétrages importants :
- tablespaces
- connexions
- statistiques
- optimiseur

Paramètres en lecture seule

Options de compilation ou lors d’initdb
Quasiment jamais modifiés
- risque d’incompatabilité des fichiers, avec les outils
Tailles de bloc ou de fichier
- block_size : 8 ko
- wal_block_size : 8 ko
- segment_size : 1 Go
- wal_segment_size : 16 Mo (option --wal-segsize d’initdb en v11)

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.

Un moteur compilé avec des options non standard ne pourra pas ouvrir des fichiers n’ayant pas les mêmes valeurs pour ces options.

Des tailles non standard vous exposent à rencontrer des problèmes avec des outils s’attendant à des blocs de 8 ko. (Remontez alors le bug.)

Fichiers de configuration

postgresql.conf
postgresql.auto.conf
pg_hba.conf
pg_ident.conf

postgresql.conf

Fichier principal de configuration :

Emplacement :
- défaut/Red Hat & dérivés : répertoires des données (/var/lib/…)
- Debian : /etc/postgresql/<version>/<nom>/postgresql.conf
Format clé = valeur
Sections, commentaires (redémarrage !)

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

Les valeurs de ce fichier ne seront pas forcément les valeurs actives !

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

Surcharge des paramètres de postgresql.conf

Inclusion externe : include, include_if_exists
Surcharge dans cet ordre :
- ALTER SYSTEM SET … ( renseigne postgresql.auto.conf )
- paramètres de pg_ctl
- ALTER DATABASE | ROLE … SET paramètre = …
- SET / SET LOCAL
Consulter :
- SHOW
- pg_settings
- pg_file_settings

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.

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)

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     | notice   | 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 debug. 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      |

Survol de postgresql.conf

Emplacement de fichiers
Connections & authentification
Ressources (hors journaux de transactions)
Journaux de transactions
Réplication
Optimisation de requête
Traces
Statistiques d’activité
Autovacuum
Paramétrage client par défaut
Verrous
Compatibilité

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.

pg_hba.conf et pg_ident.conf

Authentification multiple :
- utilisateur / base / source de connexion
Fichiers :
- pg_hba.conf (Host Based Authentication)
- pg_ident.conf : si mécanisme externe d’authentification
- paramètres : hba_file et ident_file

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.

Tablespaces

Espace de stockage physique d’objets
- et non logique !
Simple répertoire (hors de PGDATA) + lien symbolique
Pour :
- répartir I/O et volumétrie
- quotas (par le FS, mais pas en natif)
- tri sur disque séparé
Utilisation selon des droits

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 '/var/lib/postgresql/tbl_ssd';

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.

Sans un réel besoin physique, il n’y a pas besoin de créer des tablespaces, et de complexifier l’administration.

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 :

Pour des raisons de sécurité et de fiabilité, le répertoire choisi ne doit pas être à la racine d’un point de montage. (Cela vaut aussi pour les répertoires PGDATA ou pg_wal).

Positionnez toujours les données dans un sous-répertoire, voire deux niveaux en-dessous du point de montage.

Par exemple, déclarez votre PGDATA dans /<point de montage>/<version majeure>/<nom instance> plutôt que directement dans /<point de montage>.

Un tablespace ira dans /<autre point de montage>/<nom répertoire>/ plutôt que directement dans /<autre point de montage>/.

(Voir Utilisation de systèmes de fichiers secondaires dans la documentation officielle, ou le bug à l’origine de ce conseil.)

Surtout, le tablespace doit impérativement être placé hors de PGDATA. Certains outils poseraient problème sinon.

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 à l’intérieur du tablespace, et en tient notamment compte dans les migrations avec pg_upgrade.

Tablespaces : mise en place

CREATE TABLESPACE chaud LOCATION '/SSD/tbl/chaud';

CREATE DATABASE nom TABLESPACE 'chaud';

ALTER DATABASE nom SET default_tablespace TO 'chaud';

GRANT CREATE ON TABLESPACE chaud TO un_utilisateur ;

CREATE TABLE une_table (…) TABLESPACE chaud ;

ALTER TABLE une_table SET TABLESPACE chaud ;  -- verrou !

ALTER INDEX une_table_i_idx SET TABLESPACE chaud ; -- pas automatique

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) :

 # mkdir /SSD/tbl/chaud
 # chown postgres:postgres /SSD/tbl/chaud
 # chmod 700 /SSD/tbl/chaud

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 :

La table ou l’index est totalement verrouillé le temps du déplacement.
Les index existants ne « suivent » pas automatiquement une table déplacée, il faut les déplacer séparément.
Par défaut, les nouveaux index ne sont pas créés automatiquement dans le même tablespace que la table, mais en fonction de default_tablespace.

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 : configuration

default_tablespace
temp_tablespaces
Droits à ouvrir :

GRANT CREATE ON TABLESPACE  ssd_tri  TO  dupont ;

Performances :
- seq_page_cost, random_page_cost
- effective_io_concurrency, maintenance_io_concurrency

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

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 critique SET default_tablespace TO 'chaud' ; -- base
ALTER ROLE etl SET default_tablespace TO 'chaud' ; -- rôle
SET default_tablespace TO 'chaud' ; -- 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/.

Ne jamais utiliser de RAM disque (comme tmpfs) pour des tablespaces de tri : la mémoire de la machine ne doit servir qu’aux applications et outils, au cache de l’OS, et aux tris en RAM. Favorisez ces derniers en jouant sur work_mem.

En cas de redémarrage, ce tablespace ne serait d’ailleurs plus utilisable. Un RAM disque est encore plus dangereux pour les tablespaces de données, bien sûr.

Il faudra ouvrir les droits aux utilisateurs ainsi :

GRANT CREATE ON TABLESPACE  ssd_tri  TO  dupont ;

Si plusieurs tablespaces de tri sont paramétrés, chaque transaction en choisira un de façon aléatoire à la création d’un objet temporaire, puis utilisera alternativement chaque tablespace. Un gros tri sera donc étalé sur plusieurs de ces tablespaces. afin de répartir la charge.

Paramètres de performances :

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) ;
random_page_cost (coût d’accès à un bloc isolé) ;
effective_io_concurrency (nombre d’I/O simultanées) et maintenance_io_concurrency (idem, pour une opération de maintenance).

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. À l’inverse, il faut tenir compte du nombre de requêtes simultanées qui utiliseront ce nœud. Le défaut est seulement de 1, et la valeur maximale est 1000. Attention, à partir de la version 13, le principe reste le même, mais la valeur exacte de ce paramètre doit être 2 à 5 fois plus élevée qu’auparavant, selon la formule des notes de version.

Toujours en version 13 apparaît maintenance_io_concurrency. 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. Le défaut est de 10, et il faut penser à le monter aussi si on adapte effective_io_concurrency.

Par exemple, sur un système paramétré pour des disques classiques, un tablespace sur un SSD peut porter ces paramètres :

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

Gestion des connexions

L’accès à la base se fait par un protocole réseau clairement défini :
- sockets TCP (IPV4 ou IPV6)
- sockets Unix (Unix uniquement)
Les demandes de connexion sont gérées par le postmaster.
Paramètres : port, listen_adresses, unix_socket_directories, unix_socket_group et unix_socket_permissions

TCP

Paramètres de keepalive TCP
- tcp_keepalives_idle
- tcp_keepalives_interval
- tcp_keepalives_count
Paramètre de vérification de connexion
- client_connection_check_interval (v14)

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.

SSL

Paramètres SSL
- ssl, ssl_ciphers, ssl_renegotiation_limit

Statistiques sur l’activité

(Ne pas confondre avec statistiques sur les données !)
Statistiques consultable par des vues systèmes
Paramètres :
- track_activities, track_activity_query_size
- track_counts, track_io_timing et track_functions
- update_process_title
- stats_temp_directory (< v15)

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.

Statistiques d’activité collectées

Accès logiques (INSERT, SELECT…) par table et index
Accès physiques (blocs) par table, index et séquence
Activité du Background Writer
Activité par base
Liste des sessions et informations sur leur activité

Vues système

Supervision / métrologie
Diagnostiquer
Vues système :
- pg_stat_user_*
- pg_statio_user_*
- pg_stat_activity : requêtes
- pg_stat_bgwriter
- pg_locks

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…

Pour les statistiques aux 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.

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).

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.

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

pg_stat_activity est une des vues les plus utilisées et est souvent le point de départ d’une recherche : elle donne des informations sur les processus en cours sur l’instance, que ce soit des processus en tâche de fond ou des processus backends associés aux clients : numéro de processus, adresse et port, date de début d’ordre, de transaction, de session, requête en cours, état, ordre SQL et nom de l’application si elle l’a renseigné. (Noter qu’avant la version 10, cette vue n’affichait que les processus backend ; à partir de la version 10 apparaissent des workers, le checkpointer, le walwriter… ; à partir de la version 14 apparaît le processus d’archivage).

=# 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            |

Cette vue fournit aussi des informations sur ce que chaque session attend. Pour les détails sur wait_event_type (type d’événement en attente) et wait_event (nom de l’événement en attente), voir le tableau des événements d’attente.

# SELECT datname, pid, wait_event_type, wait_event, query FROM pg_stat_activity
  WHERE backend_type='client backend' AND wait_event IS NOT NULL \gx

-[ RECORD 1 ]---+--------------------------------------------------------------
datname         | pgbench
pid             | 1590
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | UPDATE pgbench_accounts SET abalance = abalance + 1438 WHERE…
-[ RECORD 2 ]---+--------------------------------------------------------------
datname         | pgbench
pid             | 1591
state           | idle
wait_event_type | Client
wait_event      | ClientRead
query           | END;
-[ RECORD 3 ]---+--------------------------------------------------------------
datname         | pgbench
pid             | 1593
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES…
-[ RECORD 4 ]---+--------------------------------------------------------------
datname         | postgres
pid             | 1018
state           | idle in transaction
wait_event_type | Client
wait_event      | ClientRead
query           | delete from t1 ;
-[ RECORD 5 ]---+--------------------------------------------------------------
datname         | postgres
pid             | 1457
state           | active
wait_event_type | Lock
wait_event      | transactionid
query           | delete  from t1 ;

Des vues plus spécialisées existent :

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.

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_locks permet de voir les verrous posés sur les objets (principalement les relations comme les tables et les index).

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_archiver donne des informations sur l’archivage des wals et notamment sur les erreurs d’archivage.

Statistiques sur les données

Statistiques sur les données : pg_stats
- collectées par échantillonnage (default_statistics_target)
- ANALYZE table
- table par table (et pour certains index)
- colonne par colonne
- pour de meilleurs plans d’exécution

Affiner :

Échantillonnage

ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ;

Statistiques multicolonnes sur demande

CREATE STATISTICS nom ON champ1, champ2… FROM nom_table ;

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, nombre de valeurs distinctes, si négatif, fraction de valeurs distinctes pour cette colonne dans la table. Il est possible de forcer le nombre de valeurs distinctes, s’il est constaté que la collecte des statistiques n’y arrive pas : ALTER TABLE xxx ALTER COLUMN yyy SET (n_distinct = -0.5) ; ANALYZE xxx; par exemple indique à l’optimiseur que chaque valeur apparaît statistiquement deux fois ;
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é par ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ; (avec une évolution proportionnelle du nombre de lignes consultées) sachant que le temps de planification augmente exponentiellement et qu’il vaut mieux ne pas dépasser la valeur 1000 ;
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).

Optimiseur

SQL est un langage déclaratif :
- décrit le résultat attendu (projection, sélection, jointure, etc.)…
- …mais pas comment l’obtenir
- c’est le rôle de l’optimiseur

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).

Optimisation par les coûts

L’optimiseur évalue les coûts respectifs des différents plans
Il calcule tous les plans possibles tant que c’est possible
Le coût de planification exhaustif est exponentiel par rapport au nombre de jointures de la requête
Il peut falloir d’autres stratégies
Paramètres principaux :
- seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost
- parallel_setup_cost, parallel_tuple_cost
- effective_cache_size

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. 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 intéressante.

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. À l’inverse, il faut tenir compte du nombre de requêtes simultanées qui utiliseront ce nœud. Le défaut est seulement de 1, et la valeur maximale est 1000. Attention, à partir de la version 13, le principe reste le même, mais la valeur exacte de ce paramètre doit être 2 à 5 fois plus élevée qu’auparavant, selon la formule des notes de version.

Toujours à partir de la version 13, un nouveau paramètre apparaît : maintenance_io_concurrency. Il a le même sens que effective_io_concurrency, mais pour les opérations de maintenance, non les requêtes. 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 si nous adaptons effective_io_concurrency.

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.

Paramètres supplémentaires de l’optimiseur (1)

Partitionnement
- constraint_exclusion
- enable_partition_pruning
Réordonne les tables
- from_collapse_limit & join_collapse_limit (défaut : 8)
Requêtes préparées
- plan_cache_mode
Curseurs
- cursor_tuple_fraction
Mutualiser les entrées-sorties
- synchronize_seqscans

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

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 nouveau partitionnement déclaratif, enable_partition_pruning, activé par défaut, est le paramètre équivalent.

Pour limiter la complexité des plans d’exécution à étudier, il est possible de limiter la quantité de réécriture autorisée par l’optimiseur via les paramètres from_collapse_limit et join_collapse_limit. Le premier interdit que plus de 8 (par défaut) tables provenant d’une sous-requête ne soient déplacées dans la requête principale. Le second interdit que plus de 8 (par défaut) tables provenant de clauses JOIN ne soient déplacées vers la clause FROM. Ceci réduit la qualité du plan d’exécution généré, mais permet qu’il soit généré dans un temps raisonnable. Il est fréquent de monter les valeurs à 10 ou un peu au-delà si de longues requêtes impliquent beaucoup de tables.

Pour les requêtes préparées, 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. Ceci décrit le mode auto en place depuis de nombreuses versions. Depuis la version 12, il est possible de modifier ce comportement grâce au paramètre de configuration plan_cache_mode :

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, et l’on conserve 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.

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.

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.

Paramètres supplémentaires de l’optimiseur (2)

GEQO :
- un optimiseur génétique
- état initial, puis mutations aléatoires
- rapide, mais non optimal
- paramètres : geqo & geqo_threshold (12 tables)

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 évoqués ci-dessous, il est déconseillé de modifier les paramètres 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.

Malgré l’introduction de ces mutations aléatoires, le moteur arrive tout de même à conserver un fonctionnement déterministe. Tant que le paramètre geqo_seed ainsi que les autres paramètres contrôlant GEQO restent inchangés, le plan obtenu pour une requête donnée restera inchangé. Il est donc possible de faire varier la valeur de geqo_seed pour chercher d’autres plans (voir la documentation officielle).

Débogage de l’optimiseur

Permet de valider qu’on est en face d’un problème d’optimiseur.
Les paramètres sont assez grossiers :
- défavoriser très fortement un type d’opération
- pour du diagnostic, pas pour de la production

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.

Conclusion

Nombreuses fonctionnalités
- donc nombreux paramètres

Questions

N’hésitez pas, c’est le moment !

Quiz

https://dali.bo/m2_quiz

Travaux pratiques

Tablespace

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

But : Consulter les statistiques d’activité

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 ?

Statistiques sur les données

But : Consulter les 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.

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.

Travaux pratiques (solutions)

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.

Configuration de PostgreSQL

Sur ce document

Licence Creative Commons CC-BY-NC-SA

Marques déposées

Versions de PostgreSQL couvertes

Configuration de PostgreSQL

Au menu

Paramètres en lecture seule

Fichiers de configuration

postgresql.conf

Surcharge des paramètres de postgresql.conf

Survol de postgresql.conf

pg_hba.conf et pg_ident.conf

Tablespaces

Tablespaces : mise en place

Tablespaces : configuration

Gestion des connexions

TCP

SSL

Statistiques sur l’activité

Statistiques d’activité collectées

Vues système

Statistiques sur les données

Optimiseur

Optimisation par les coûts

Paramètres supplémentaires de l’optimiseur (1)

Paramètres supplémentaires de l’optimiseur (2)

Débogage de l’optimiseur

Conclusion

Questions

Quiz

Travaux pratiques

Tablespace

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

Statistiques sur les données

Travaux pratiques (solutions)