Fichier postgresql.conf et guide de configuration utilisateur générale (Global User Configuration [GUC]) annotés

PostgreSQL 7.4.x


Table des matières

Connexions et authentification
Paramètres de connexion
Sécurité et authentification
Utilisation des ressources
Mémoire
Free Space Map
Utilisation des ressources disque
Options WAL
Paramètres
Checkpoints
Optimisation des requêtes
Méthodes du planificateur
Constantes de coût du planificateur
Optimisation génétique de requêtes (Genetic Estimate Query Optimizer, GEQO)
Statistiques concernant les requêtes et les index
Autres modificateurs de requêtes
Options de journalisation et de messages
Syslog
Quand journaliser
Que journaliser
Journalisation des statistiques
Valeur par défaut des connexions client
Comportement des instructions
Locale et formattage
Autres valeurs par défaut
Gestion des verrous
Compatibilité de version et de plateforme Compatibility
Versions précédentes de PostgreSQL
Compatibilité plateforme et client
Options pour les développeurs

Connexions et authentification

Paramètres de connexion

Tableau 1. 

ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
tcpip_sockettrue, falseAucunefalsenon-i Positionné à true, le serveur accepte les connexions TCP/IP. Dans le cas contraire, seules les connexions par la socket de domaine Unix sont autorisées. Sauf dans le cas d'un serveur de test, ce paramètre doit probablement être positionné à true. Il est recommandé de faire cela après avoir configuré le fichier pg_hba.conf, pour sécuriser les accès.
max_connections2 à Int Max14ko RAM32Non-N # Nombre maximum de connexions concurrentes à un serveur de bases de données. La valeur par défaut est 32 (à moins qu'elle n'ait été modifiée lors de la construction du serveur). Paramètre important. À conserver le plus bas possible pour la configuration de l'application. En combinaison avec une application web à connexions persistantes, il doit être possible de l'abaisser vers 10-16.
superuser_reserved_connections0 à max_connections - 1Réduit le nombre de connexions standard disponibles2Non  Nombre de connexions réservées aux superutilisateurs PostgreSQL. Au plus max_connections connexions peuvent être actives simultanément. Lorsque le nombre de connexions concurrentes atteint max_connections moins superuser_reserved_connections, seules les connexions de superutilisateurs sont encore autorisées. Cela protège l'accès des superutilisateurs en cas d'engorgement de la base. Ce paramètre ne doit être positionné à 0 que lorsqu'il est certain que toutes les connexions ne sont jamais utilisées. (NDR : je positionne souvent ce paramètre à 1 puisque je ne me connecte à la base en superutilisateur qu'en cas de problème.)
port129 à 32768Aucune5432Non-p # Le port TCP sur lequel le serveur écoute.

Le changement du port PostgreSQL peut être une protection contre les vers de bases et les script kiddies. Néanmoins, il ne faudra pas oublier de donner l'option de port à tous les logiciels et bibliothèques effectuant une connexion, ce qui peut s'avérer pénible.

unix_socket_directory  ''Non-k $ Répertoire du socket de domaine Unix sur lequel le serveur écoute les connexions de clients. Par défaut, c'est /tmp, mais le paramètre peut être modifié à la compilation. Ces options s'appliquent aux connexions UDP vers le serveur, utilisées en général uniquement à partir d'une console sur le serveur. (NDR : Comme je considère les connexions UDP non sécurisées, je les désactive. Je n'ai donc pas de recommendation particulière.)
unix_socket_group  ''Non  Groupe propriétaire du socket de domaine Unix (l'utilisateur propriétaire de ce socket est toujours celui qui exécute le serveur). Combiné avec l'option UNIX_SOCKET_PERMISSIONS, ce paramètre peut être utilisé comme mécanisme supplémentaire de contrôle des accès pour ce type de socket. Par défaut, c'est une chaîne vide, donc le groupe par défaut de l'utilisateur.  
unix_socket_permissions  0777Non 

Droits d'accès au socket de domaine Unix. Les sockets de domaine Unix utilisent le système habituel de gestion des droits des systèmes de fichiers Unix. La valeur de l'option doit être précisée sous la forme numérique acceptée par les outils système chmod et umask. L'utilisation du format octal impose un 0 (zéro) en début de nombre.

 

Sécurité et authentification

Tableau 2. 

ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
authentication_timeout1-600 sec 60Non  Temps maximum laissé à un client pour réussir l'authentification, en secondes. Si un client potentiel n'a pas terminé la séquence d'authentification pendant ce laps de temps, le serveur met fin à la connexion. Cela permet d'éviter qu'un client bloqué n'occupe indéfiniment une connexion. Cette option, lue au démarrage, ne peut être positionnée qu'en ligne de commande ou dans le fichier postgresql.conf.
ssltrue, falseVoir les notesfalseNon-lAutoriser les connexions SSL SSL chiffre le traffic sur le port TCP/IP de sorte que les données transférées ne puissent pas être lues normalement. Du fait du chiffrement, cela implique une charge intense du CPU à la fois sur le client et le serveur. Cela peut aussi accroître la taille des données transférées. Néanmoins, SSL peut être difficile à configurer, et tous les clients ne supportent pas l'accès SSL.
krb_server_keyfile  ''Non  Positionne l'implantation du fichier de clés du serveur Kerberos. Utilisé uniquement pour l'authentification Kerberos des utilisateurs.
virtual_host  ''Non-h x Positionne le nom d'hôte ou l'adresse TCP/IP sur lequel/laquelle le postmaster écoute les connexions des applications clientes. Par défaut, écoute sur toutes les adresses configurées (y compris localhost). Essentiel pour les bases sur un réseau sécurisé comprenant une DMZ, ou lorsque le serveur de bases de données possède une adresse TCP/IP publique et une privée.
db_user_namespacetrue, false falseNon  Permet les noms d'utilisateur par base de données. Désactivé par défaut. Par défaut, les utilisateurs peuvent se connecter à n'importe quelle base. Dans certains cas, ce scénario n'est pas acceptable. Dans de tels cas, il est possible d'utiliser cette option. Cette fonctionnalité s'entend comme une mesure intermédiaire en attendant une solution complète. Cette option sera supprimée par la suite.

Utilisation des ressources

Mémoire

Note

L'augmentation de la plupart de ces paramètres impose de règler les options du noyau système à la valeur de la mémoire autorisée par processus ou par utilisateur. La documentation en ligne donne des informations sur les commandes en fonction des systèmes d'exploitation. Sauf indication contraire, toutes les options mémoire s'additionnent pour déterminer la quantité totale de mémoire utilisée par PostgreSQL.

Tableau 3. 

ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
shared_buffers16 à Int Max8K RAM64Non-B x Positionne le nombre de tampons de mémoire partagée utilisé par le serveur de bases de données. Le minimum est 2 X max_connections.

Positionne la taille du tampon mémoire de PostgreSQL où les requêtes sont stockées avant d'être passées au tampon noyau du système. Ce n'est qu'une zone de stockage, et non le total de la taille mémoire disponible pour le serveur. De ce fait, il est fortement déconseillé de positionner ce nombre à une part importante de la RAM, cela ayant des conséquences désastreuses sur les performances avec beaucoup de systèmes d'exploitation. Les membres de la liste de diffusion pgsql-performance ont abouti à une échelle utile se situant entre 1000 et 6000, fonction de la RAM, de la taille de la base de données et du nombre de requêtes concurrentes. Pour les serveurs qui disposent d'une grande quantité de RAM (supérieure à 1 Go), l'augmentation de ce paramètre vers 6 à 15% de la RAM disponible a donné de bons résultats dans la plupart des cas. L'analyse réelle du meilleur paramétrage n'est pas exactement comprise, et l'expérience plus que le calcul permet l'ajustement de ce paramètre.

Une règle empirique consiste à observer l'utilisation de la mémoire partagée par PostgreSQL à l'aide d'outils tel ipcs pour déterminer la valeur du paramètre. Cela ne représente toutefois que la moitié du paramétrage. Il est également nécessaire de régler effective_cache_size pour que PostgreSQL puisse utiliser efficacement la mémoire disponible.

sort_mem 64 à Int Max1 KB1024Oui-S #

Précise la quantité de mémoire utilisable par les opérations de tri interne et les tables de hachage avant d'utiliser des fichiers temporaires. La valeur est indiquée en kilooctets, la valeur par défaut est 1024 Ko (soit 1 Mo). En cas de requête complexe, de nombreuses opérations de tri ou de hachage peuvent s'exécuter en parallèle ; chacune peut utiliser la quantité de mémoire indiquée par la valeur de ce paramètre avant de commencer à utiliser des fichiers temporaires. De plus, de nombreuses sessions peuvent effectuer ces opérations en parallèle. La mémoire totale utilisée peut être plusieurs fois la valeur de SORT_MEM. Les opérations de tri sont utilisées par ORDER BY, DISTINCT et les jointures.

Les options en ligne de commande nécessitent d'utiliser -o 'option'.

sort_mem est difficile à ajuster. Une plus grande valeur est utilisée pour : les bases de données volumineuses, les requêtes complexes, une grande quantité de RAM disponible. Une plus petite valeur est recherchée pour : une faible quantité de RAM ou de nombreux accès concurrents. Trouver le bon compromis peut s'avérer délicat.

Une autre façon d'ajuster cette valeur consiste à surveiller les fichiers temporaires de PostgreSQL (dans PGDATA/base/OID_BASE/pgsql_tmp) et d'accroître la valeur de sort_mem si de nombreuses requêtes permutent avec ces fichiers.

Il ne faut pas oublier que ce paramètre peut être positionné par connexion. Ainsi, dans le cas où seules quelques requêtes très complexes sont à exécuter, la valeur peut être augmentée avant leur exécution, mais conservée basse pour les autres connexions.

vacuum_mem1024 à Int Max1 Ko8192Oui  Précise la quantité maximale de mémoire que VACUUM peut utiliser pour conserver une trace des lignes à ré-utiliser. La valeur est précisée en Ko. La valeur par défaut est de 8 192 ko. Une valeur supérieure peut accélérer le nettoyage de grandes tables dont de nombreuses lignes ont été supprimées.

Ce paramètre n'utilise de RAM que lors des VACUUM. Il peut donc être augmenté sur les machines disposant de beaucoup de RAM pour accéler les VACUUM (mais pas plus de 20% de la RAM disponible).

Ce paramètre peut de plus être positionné à l'exécution. Il peut alors être judicieux de le régler à une valeur basse pour les VACUUM réguliers et de le positionner à une valeur plus élevée pour les VACUUM FULL de nuit/hebdomadaires/périodiques.

Free Space Map

Tableau 4. 

ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
max_fsm_pages1000 à Int Max6 octets RAM10000Non  Positionne le nombre maximum de pages disque pour lesquels l'espace libre est recherché dans la carte de l'espace libre partagé. La valeur par défaut est 10 000. Cette option ne peut être positionnée qu'au démarrage du serveur.

Un paramétrage correct de la FSM peut éliminer, ou au moins, retarder l'obligation d'exécuter VACUUM FULL et REINDEX. La meilleure façon de le régler est la suivante :

calculer la fréquence du VACUUM (normal) de la base en fonction des écritures ;

    utiliser la base en fonctionnement normal et exécuter VACUUM VERBOSE ANALYZE à la place de VACUUM, en sauvegardant la sortie dans un fichier ;

      calculer le nombre de pages maximum réclamé par VACUUM et utiliser ce chiffre.

          Si l'approche précédente n'est pas réalisable, essayer d'estimer le nombre total de lignes qui peuvent être actualisées ou supprimées entre deux VACUUM, et utiliser la moitié de ce nombre, en l'augmentant en fonction de la quantité de travail qu'un VACUUM FULL périodique continue d'effectuer.

          Pour les base de données qui connaissent des « pics » d'activité (rafales d'un million de mises à jour mais pas d'autre activité des minutes ou des heures durant), ce nombre peut être impossible à optimiser. Les lignes insérées n'ont pas d'impact sur la FSM. Enfin, si le serveur est peu fourni en RAM, augmenter cette valeur peut s'avérer contre-productif.

          max_fsm_relations10 à Int Max40 octets RAM100Non  Positionne le nombre maximum de relations (tables) pour lesquels l'espace libre est recherché dans la carte de l'espace libre partagé. Ce paramètre doit être positionné au nombre de tables attendues dans la base... entre 300 et 500. PostgreSQL a un comportement étrange s'il ne possède pas assez de FSM_relations.

          Utilisation des ressources disque

          Tableau 5. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          max_files_per_process25 à Int Max 1000Non  Fixe le nombre maximum de fichiers simultanément ouverts par chaque sous-processus du serveur. La valeur par défaut est 1 000. La limite actuellement utilisée par le code est la plus petite valeur du paramètre et le résultat de sysconf(_SC_OPEN_MAX). Toutefois, sur les systèmes pour lesquels sysconf retourne une valeur raisonnable, il n'est pas nécessaire de s'inquiéter de ce paramètre. Mais sur la plupart des plateformes (et notamment BSD), sysconf retourne une valeur beaucoup plus grande que ce que le système peut réellement prendre en charge lorsque de nombreux processus essayent tous d'ouvrir ce nombre de fichiers. Si le message d'erreur « Trop de fichiers ouverts » ("Too many open files") apparaît, il faut alors essayer de réduire ce paramètre. Il ne peut être fixé qu'au démarrage du serveur ou dans le fichier postgresql.conf. Modifié dans le fichier de configuration, il n'affectera que les sous-processus serveur ouverts après modification. D'après la documentation, utilisé essentiellement pour BSD. À considérer uniquement à l'appartion de messages « Trop de fichiers ouverts » ("Too many open files").
          preload_librariesChemin de fichierVoir les notesVideNon  Indique les bibliothèques à précharger au démarrage du serveur. Une fonction d'initialisation peut optionnellement être appelée pour chaque bibliothèque. Pour cela, il suffit d'ajouter un double-point et le nom de la fonction d'initialisation après le nom de la bibliothèque. '$libdir/mylib:mylib_init' implique, par exemple, le préchargement de mylib et l'exécution de la fonction mylib_init. Si plusieurs bibliothèques doivent être chargées, leurs noms sont séparés par des virgules. Si mylib n'est pas trouvée, le serveur ne démarrera pas. En revanche, si init_mylib n'est pas trouvée, mylib est préchargée sans exécuter la fonction d'initialisation. Le préchargement (et l'initialisation éventuelle) d'une bibliothèque partagée permet d'éviter le temps de chargement de la bibliothèque la première fois qu'elle est utilisée. Cela se traduit par un temps de démarrage du serveur plus long et des temps d'appel des bibliothèques qui ne sont pas chargées en mémoire plus courts.

          Options WAL

          Paramètres

          Tableau 6. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          fsynctrue, falseVoir les notestrueNon-F (off) Lorsque cette option est activée, le serveur PostgreSQL utilise les appels système fsync() pour s'assurer que les mises à jour sont physiquement écrites sur le disque. Cela garantit la récupération d'une bases de données cohérente après une panne matérielle ou système. Ce fonctionnement n'a aucune incidence sur la gestion des pannes du serveur de bases de données.

          Note

          Depuis la version 7.2, la désactivation de fsync n'arrête PAS le WAL. Toutefois, il n'y a plus de points de reprise. La journalisation des écritures anticipées (Write-Ahead Logging ou WAL) ne doit être désactivée (fsync=off) que sur les bases en lecture seule ou celles qu'il est possible de régénérer à l'aide de logiciels externes. Tandis que du RAID associé à un système de haute-disponibilité électrique peuvent aider à la protection des données, la désactivation de fsync impose la restauration des données à partir de sauvegardes en cas de pannes matériel ou électrique.

          D'un autre côté, le WAL implique une baisse de performances lors des écritures, à plus forte raison sur des systèmes mono-disques. Pour l'essentiel l'activité nécessaire aux opérations de lecture/écriture est doublée à chaque mise à jour. De plus, les fonctionnalités d'amélioration des performances par cache disque matériel ou logiciel sont désactivées.

          Si le WAL est arrêté, les options qui suivent n'ont pas d'intérêt.

          wal_sync_methodfsync, fdatasync, open_sync ou open_datasync Dépend de la plateformeNon  Méthode utilisée pour forcer la mise à jour des WAL sur le disque. Les valeurs possibles sont FSYNC (fsync() est appelé à chaque validation), FDATASYNC (fdatasync() est appelé à chaque validation), OPEN_SYNC (écrit les fichiers WAL avec l'option O_SYNC d'open()) et OPEN_DATASYNC (écrit les fichiers WAL avec l'option O_DSYNC d'open()). Ces choix ne sont pas tous disponibles sur toutes les plateformes. Il s'agit de l'appel système utilisé pour synchroniser les WAL sur disque. La valeur par défaut a été réglée pour chaque OS en fonction de la documentation de l'OS. En revanche, aucun test comparatif n'a été mené. Il est possible que le changement de méthode accélère l'écriture sur une plateforme spécifique, mais cela ne doit être tenté que si les ressources nécessaires à des tests comparatifs et de régression sont disponibles. La modification des valeurs par défaut peut altérer la protection par le WAL.
          wal_buffers4 à Int Max8K RAM8Non  Nombre de tampons de pages disque alloués dans la mémoire partagée pour la journalisation du WAL. L'accroissement de ce paramètre peut accélérer les écritures du WAL pour les transactions volumineuses. À ce jour, personne n'a rendu compte d'une analyse de l'impact de ce paramètre.

          Checkpoints

          Tableau 7. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          checkpoint_segments1 à Int Max16 Mo sur disque3Non  Distance maximale entre deux points de contôle WAL automatiques, en segments de fichier journal (chaque segment représente normalement 16 Mo). Ce paramètre est augmenté lorsque la base requiert de nombreuses écritures d'arrière-plan pour diminuer la fréquence des points de reprise (et ainsi diminuer l'activité du disque). Si la place disque manque ou que l'environnement présente un risque de coupures de courant inattendues, ce paramètre peut être diminué. En effet, toute transaction non visée par un point de reprise sera supprimée au redémarrage.
          checkpoint_timeout30 à 3600Voir les notes300Non  Temps maximal entre deux points de contôle WAL automatiques, en secondes.  
          checkpoint_warning0 à Int MaxVoir les notes0Non  Un message est envoyé dans les journaux du serveur si la fréquence des points de contrôle engendrés par le remplissage des fichiers de segments de points de contrôle est supérieure à ce temps, en secondes. Zéro désactive les alertes. Nouvelle option qui peut être utile pour affiner les checkpoint_segments sur les bases à forte activité d'écriture. Des alertes nombreuses dans les journaux incitent à accroitre le paramètre checkpoint_segments.
          commit_delay0-100000Voir les notes0Oui  Indique le délai, en microsecondes, entre l'écriture d'une validation dans le tampon WAL et la purge du tampon sur disque. Un délai positif peut permettre la validation de plusieurs transactions avec un seul appel système fsync(), si la charge système est suffisamment élevée pour que de nouvelles transactions soient prêtes pendant cet interval. Mais ce délai est perdu dans le cas contraire. Ainsi, le délai n'est exécuté que si au moins commit_siblings autres transactions sont actives au moment où le processus serveur a écrit sa validation. Ces deux paramètres sont configurés ensemble pour un environnement à fort volume de petites transactions. Activés, ils permettent de purger en même temps sur disques des transactions sans relation entre elles, qui autrement ne le seraient pas, avec à la clé un potentiel accroissement des performances. En revanche, si l'espace disque est restreint ou que la stabilité électrique de l'environnement ne peut être garantie, il est préférable de ne pas modifier ces paramètres.
          commit_siblings1-1000Voir les notes5Oui  Indique le nombre minimum de transactions concurrentes ouvertes avant l'éxécution du délai COMMIT_DELAY. Plus la valeur est grande, plus la probabilité de valider une autre transaction pendant cet interval grandit.

          Optimisation des requêtes

          Méthodes du planificateur

          Tableau 8. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          enable_hashagg

          enable_hashjoin

          enable_indexscan

          enable_mergejoin

          enable_nestloop

          enable_seqscan

          enable_sort

          enable_tidscan

          true, false trueOui

          -fi

          -fm

          -fn

          -fs

          -ft*

          Active ou désactive l'utilisation des types de plan respectifs par le planificateur de requêtes. Activé par défaut, on les utilise pour déboguer le planificateur de requêtes.

          Les options en ligne de commande requièrent l'utilisation de -o 'option'.

          Ces options ne devraient être utilisées que pour tester les requêtes ; il est courant, par exemple, de positionner « enable_seqscan = false » pour déterminer si le planificateur n'omet pas inutilement un index. Quoi qu'il en soit, seules des circonstances extraordinaires nécessitent qu'un de ces paramètres soit positionné à false dans le fichier .conf.

          Constantes de coût du planificateur

          Tableau 9. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          effective_cache_size0 à Int Max8 Ko1000Oui  Fournit à l'optimiseur une estimation de la taille efficace du cache disque (c'est-à-dire la partie du cache disque du noyau utilisée pour les fichiers de données PostgreSQL). Elle est mesurée en pages disque, chacune de 8 ko. Ce paramètre, bien que souvent négligé, peut, s'il est correctement réglé conduire à une meilleure utilisation de la RAM sur les ordinateurs disposant de beaucoup de mémoire. (NDR : j'utilise 25% de ma « RAM disponible » [après Linux et les applications] comme niveau général, mais n'ai pas testé plus avant). Si ce paramètre est optimisé, il n'est pas nécessaire d'augmenter inutilement shared_buffers.

          random_page_cost

          cpu_tuple_cost

          cpu_index_tuple_cost

          cpu_operator_cost

          0 à Double

          0.01

          0.001

          0.0025

          4 Oui Fournit au planificateur une estimation du coût de traitement de chaque consultation de page, tuple, consultation d'index et item de clause where (respectivement) lors d'une requête. Elle est mesurée en multiples du coût de récupération séquentielle d'une page. Les coûts par défaut sont fondés entièrement sur l'expérience et ne sont probablement pas optimaux pour tout système. Par exemple, les machines disposant de disques rapides peuvent abaisser le random_page_cost. Lorsque différentes valeurs sont testées pour ces paramètres, il est nécessaire de tester une grande variété de requêtes, car un bon paramétrage pour une requête peut ne pas s'appliquer à une autre. Heureusement, ces paramètres étant tous relatifs au coût d'un parcours séquentiel, le risque qu'ils soient trop élevés dans un système équilibré est assez faible.

          Optimisation génétique de requêtes (Genetic Estimate Query Optimizer, GEQO)

          Tableau 10. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          geqotrue, false true   Active ou désactive l'optimisation génétique de requêtes. Il s'agit en fait d'un algorithme génétique de planification de requêtes qui tente d'éviter les recherches exhaustives. Activé par défaut. Divers paramètres GEQO_ permettent d'en affiner le comportement.

          GEQO a été introduit dans PostgreSQL 6.5 pour optimiser les requêtes de jointures qui utilisent trop de tables pour permettre une analyse exhaustive par le planificateur. C'était assez révolutionnaire, alors, mais n'a jamais été complètement optimisé. Depuis, sa maintenance est aléatoire. (NDR : Maintenant que de nouveaux CPU, plus rapides et plus de RAM, plus rapide, sont devenus accessible, je positionne le seuil GEQO aux environs de 20-25 tables.) Cela permet de s'assurer que GEQO n'est utilisé que lorsque c'est la seule solution. Il est évident que si PostgreSQL tourne sur une machine à CPU limité, le seuil GEQO peut être abaissé.

          GEQO peut être désactivé si aucune requête de cette complexité n'est jamais envisagée.

          geqo_threshold2 à Int Max 11Oui  L'optimisation génétique de requête est utilisée pour planifier les requêtes qui contiennent au minimum ce nombre d'éléments dans la clause FROM. Une jointure compte pour un item FROM. La valeur par défaut est 11. Pour les requêtes plus simples, il est généralement plus efficace d'utiliser une stratégie de planification déterministique, exhaustive. Ce paramètre contrôle également la façon dont l'optimiseur tente de fusionner les sous-requêtes de clause FROM avec la requête principale.  

          geqo_selection_bias

          geqo_pool_size

          geqo_effort

          geqo_generations

          geqo_random_seed

          1.5-2.0 

          2.0

          0

          1

          0

          -1

          Oui  Divers paramètres d'affinage de l'algorithme d'optimisation génétique : la taille du pool correspond au nombre d'individus d'une population. L'échelle s'étend de 128 à 1024. Lorsqu'elle est positionnée à 0 (valeur par défaut), la taille du pool est obtenue par le calcul. Elle vaut alors 2^(QS+1), avec QS le nombre d'éléments FROM de la requête. L'effort est utilisé pour calculer une valeur par défaut pour les générations. L'échelle s'étend de 1 à 80, 40 étant la valeur par défaut. generations précise le nombre d'itérations de l'algorithme. Ce nombre doit être entier et positif. Si une valeur nulle est utilisée, generations est calculé. Le nombre vaut alors effort * Log2(PoolSize). Le temps d'exécution de l'algorithme est approximativement proportionnel à la somme de la taille du pool et de generations. selection bias affine la sélectivité au sein d'une population. Ses valeurs vont de 1.5 à 2.0 (2.0 étant la valeur par défaut). random seed permet d'ajuster l'algorithme pour obtenir des résultats reproductibles. Positionné à -1, l'algorithme a un comportement non déterministe.  

          Statistiques concernant les requêtes et les index

          Tableau 11. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          stats_start_collector

          stats_reset_on_server_start

          stats_command_string

          stats_row_level

          stats_block_level

          true, false

          true

          true

          false

          false

          false

           Non  Ces drapeaux détermine les informations envoyées par les serveurs au processus de collecte des statistiques : commande en cours, statistiques d'activité au niveau bloc ou statistiques d'activité au niveau ligne. Tous sont désactivés par défaut. La collecte de statistiques coûte un peu de temps par requête, mais est inestimable pour le débogage et l'optimisation des performances. NDR : J'écris un article sur Techdocs qui explique comment utiliser les statistiques de requête pour optimiser l'utilisation des index. Elles sont particulièrement utiles pour avoir une approche « déductive » de l'indexation, qui consiste à tout indexer et supprimer ensuite les index inutiles.
          default_statistics_target1 -1000 10Oui  Positionne la cible par défaut des statistiques pour les tables dont la cible spécifique à la colonne n'a pas été positionnée à l'aide de ALTER TABLE SET STATISTICS. Plus la valeur est grande, plus long est le temps nécessaire à l'ANALYZE. En revanche, la qualité des estimations du planificateur s'améliore. Il peut s'avérer utile de positionner la cible lorsque toutes les tables principales possèdent une distribution irrégulière au sein des colonnes les plus souvent interrogées. En revanche, le temps nécessaire à ANALYZE est rallongé.

          Autres modificateurs de requêtes

          Tableau 12. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          explain_pretty_printtrue, false falseOui  Règle le format de sortie d'EXPLAIN VERBOSE, indenté ou non, pour l'affichage des arbres de requête détaillés. Essayer et voir. Le formattage supplémentaire est utile pour certains, mais pas pour tout le monde.
          from_collapse_limit0 à Int Max 8Oui  Le planificateur fusionne les sous-requêtes dans la requête principale si la liste FROM résultante ne contient pas plus de ce nombre d'éléments. Une valeur inférieure réduit le temps de planification, mais peut conduire à des plans de moindre qualité. 8 est la valeur par défaut. Il est préférable de conserver une valeur inférieure à GEQO_THRESHOLD.
          join_collapse_limit1 à Int Max 8Oui  Le planificateur résout les jointures internes (inner JOIN) explicites en listes d'éléments FROM si la liste résultante ne contient pas plus de ce nombre d'éléments. En général, ce paramètre a la même valeur que FROM_COLLAPSE_LIMIT. Le positionner à 1 empêche toute résolution des jointures internes, ce qui permet d'utiliser la syntaxe JOIN explicitement pour contrôler l'ordre de la jointure. Les valeurs intermédiaires permettent d'arbitrer entre le temps de planification et la qualité du plan. Cette option est conçue pour ceux qui préfèrent écrire leurs requêtes en utilisant la syntaxe des jointures explicites (e.g. « a join b using (1) join c using (2) »), mais apprécient néanmoins que le planificateur choisisse le meilleur ordre de jointure. En particulier, les gens migrant de MS SQL Server peuvent utiliser ce paramètre en le positionnant à une valeur modérément élevée, puisque cette base trie les jointures automatiquement.
          max_expr_depth10 à Int Max 10000Oui  Positionne la profondeur d'emboîtement maximale d'une expression. La valeur par défaut est assez élevée pour une requête habituelle, mais elle peut être augmentée au besoin. Attention toutefois à ne pas l'augmenter trop, au risque d'aboutir à un plantage du serveur pour dépassement de la taille de pile. NDR : Je ne vois pas qui pourrait avoir besoin d'augmenter ce paramètre.

          Options de journalisation et de messages

          Syslog

          Tableau 13. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          syslog

          syslog_facility

          syslog_ident

          0-2

          0

          LOCAL0

          postgres

            Non PostgreSQL autorise l'utilisation de syslog pour la journalisation. Lorsque l'option est positionnée à 1, les messages sont dirigés à la fois vers syslog et la sortie standard. Positionnée à 2, les messages ne sont envoyés que vers syslog (certains messages sont toujours dirigés vers la sortie/l'erreur standard). La valeur par défaut est 0, ce qui désactive syslog. L'option doit être positionné au démarrage du serveur. Très utile lorsqu'un outil de gestion de syslog est disponible. Dans le cas contraire, il peut être difficile de séparer la sortie de PostgreSQL de celle des autres processus.

          Quand journaliser

          Tableau 14. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          server_min_messages

          client_min_messages

          log_min_error_statement

          debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic  noticeOui-d x Permet de contrôler le détail des messages écrits dans les journaux du serveur et sur le client. Les valeurs valides sont DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL et PANIC. Les dernières envoient moins de détails dans les journaux. La valeur par défaut est NOTICE. LOG n'a pas le même comportement ici que dans CLIENT_MIN_MESSAGES. Les niveaux debug sont intéressant en phase de test des applications. Le coût en est une plus grande occupation du disque et un léger impact sur les performances (généralement inférieur à 5%). Néanmoins, l'impact sur les performances augmente considérablement si les journaux se situent sur le même disque/la même baie que les WAL de la base, puisqu'une forte sortie de débogage ampute l'activité E/S de la base. L'impact de DEBUG5 sur un système monodisque fortement transactionnel peut être très élevé. Cet avertissement vaut pour toutes les options de journalisation vues plus haut.

          Que journaliser

          Tableau 15. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          debug_print_parse

          debug_print_rewritten

          debug_print_plan

          debug_pretty_print

          true, false falseOui  Ces drapeaux activent la sortie d'informations de débogage vers les journaux du serveur. Pour chaque requête exécutée, écrit soit le texte de la requête, l'arbre syntaxique résultant, la sortie du rédacteur de requête ou le plan d'exécution. DEBUG_PRETTY_PRINT indente les affichages pour faciliter la lecture, au prix d'une écriture plus longue. Utile pour détecter les requêtes lentes, sous réserve de réussir à parcourir un journal volumineux. Particulièrement utile dans un mode interactif de surveillance des journaux lorsque les procédures stagnent ; il est parfois possible de voir à quel endroit la procédure stagne (parfois, cela n'est pas possible, parce que le journal attend une information de la base).

          log_connections

          log_pid

          log_statement

          log_duration

          log_timestamp

          true, false false

          Non

          Non

          Oui

          Oui

          Oui

            Journalise les éléments correspondants.

          Chaque paramètre a son utilité, fonction du problème attendu. (NDR : en général, je conserve log_timestamp activé, et tous les autres désactivés.)

          Note

          Un correctif a été proposé pour permettre à un utilisateur d'activer les options de journalisation, mais seul le superutilisateur peut alors les désactiver. Il n'est pas sûr que ce correctif soit intégré à la version 7.4.

          log_hostname true, falseVoir les notesfalseNon  Par défaut, les journaux de connexions ne présentent que l'adresse IP de provenance de la connexion. En activant cette option, le nom de l'hôte est également présenté. Mais, en fonction du paramétrage de la résolution de noms, cela peut induire une baisse non négligeable des performances. Cela peut être utile pour la gestion des débogages et de la sécurité, mais dans le cas où le DNS n'est pas local, les nouvelles connexions peuvent se trouver considérablement ralenties.
          log_source_port true, falseVoir les notesfalseNon  Affiche le numéro du port de sortie de l'hôte se connectant dans les journaux des messages de connexion. Cela permet, en remontant le numéro de port, de retrouver l'utilisateur qui s'est connecté. À part cela, l'intérêt est restreint, d'où sa désactivation par défaut. Implique une pénalité significative, mais non mesurée, sur les performances du fait de la recherche et des activités de journalisation supplémentaires.

          Journalisation des statistiques

          Tableau 16. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          show_parser_stats

          show_planner_stats

          show_executor_stats

          show_statement_stats

          log_parser_stats

          log_planner_stats

          log_executor_stats

          log_statement_stats

          true, false falseNon

          -tpa

          -tpl

          -te

          -s

          Pour chaque requête, les statistiques de performance du module respectif sont écrites dans le journal du serveur. C'est un outil d'analyse redoutable.

          Les options en ligne de commande requièrent l'utilisation de -o 'option'.

          Peut être positionné par le superutilisateur.

          Valeur par défaut des connexions client

          Comportement des instructions

          Tableau 17. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          search_pathpathAucune'$user,public'Oui  Cette variable précise l'ordre de consultation des schémas lorsqu'il est fait référence à un objet (table, type de données, fonction...) par un nom simple, sans composante schéma. Lorsque plusieurs objets portent le même nom dans différents schémas, le premier trouvé dans l'ordre du chemin est utilisé. Il ne peut être fait référence à un objet qui ne se trouve dans aucun des schémas du chemin qu'en qualifiant son nom avec le schéma qui le contient (schema.objet). Lorsqu'une application utilise intensément les schéma, il est possible de renverser le chemin de recherche pour remplacer les objets des schémas utilisateur par ceux du schéma public qui portent le même nom. Sinon, autant ne pas y toucher.
          default_transaction_isolationread committed, serializableVoir les notes'read committed'Oui  Chaque transaction SQL dispose d'un niveau d'isolation. Il peut être « read committed » (lecture des entrées validées) ou « serializable » (sérialisable). Ce paramètre contrôle le niveau d'isolation par défaut de chaque nouvelle transaction. La valeur par défaut est « read committed ». Le guide de l'utilisateur PostgreSQL et la documentation de la commande SET TRANSACTION fournissent des informations complémentaires. La valeur par défaut, ici, est en accord avec le comportement MVCC. « serializable » est surtout utile lors du lancement de procédures longues et éventuellement successives, ou lorsqu'une mise à jour entraîne un risque conséquent et régulier de verrou mort. En environnement fortement concurrentiel (nombreux utilisateurs), son utilisation peut avoir des conséquences sur les performances, puisque de nombreuses transactions doivent attendre la fin de la transaction sérialisée. Dans le cas d'une base mono-utilisateur, cela n'a que peu de conséquence.
          default_transaction_read_onlytrue, false falseOui  Une transaction SQL en lecture seule ne peut modifier une table temporaire. Ce paramètre contrôle le statut de lecture seule par défaut de toute nouvelle transaction. Par défaut à false (lecture/écriture).  
          statement_timeout0 à Int MaxVoir les notes0Oui  Interrompt toute instruction qui dure plus que le nombre de millisecondes précisées. La valeur zéro désactive le chronomètre. Conçu pour faciliter le fonctionnement d'une application lorsque les utilisateurs peuvent exécuter des requêtes qui monopolisent le CPU pendant plusieurs minutes, telles que les applications qui autorisent les requêtes dynamiques. Positionner ce paramètre à une valeur finie peut empêcher les utilisateurs de monopoliser les ressources, mais il faut alors gérer l'exception.

          Locale et formattage

          Tableau 18. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires

          datestyle

          timezone

          australian_timezones

           Aucune

          'iso, us'

          unknown

          false

          Oui 

          Positionne le format d'affichage des dates et les règles d'interprétation des dates ambigues en entrée .

          Positionne le fuseau horaire pour l'affichage et l'interprétation des estampilles temporelles. Le comportement par défaut utilise le fuseau horaire fourni par le système.

          Positionné à true, CST, EST et SAT sont interprétés comme des fuseaux horaires australiens et non comme des fuseaux North American Central/Eastern et samedi (Saturday).

          Permet de modifier l'affichage par défaut des dates et l'interprétation des fuseaux horaires pour coller aux standard et à la localisation de l'utilisateur.
          extra_float_digits-14 à 2Aucune0Oui  Ce paramètre permet d'ajuster le nombre de chiffres affichés pour les valeurs à virgule flottante, ce qui inclut les float4, float8 et les types de données géométriques. La valeur du paramètre est ajoutée au nombre standard de chiffres (FLT_DIG ou DBL_DIG, selon les cas). La valeur 2, la plus haute possible, permet d'inclure les chiffres partiellement significatifs ; cela est particulièrement utile pour copier des flottants dont la restauration doit être exacte. Les valeurs négatives permettent de supprimer les chiffres non souhaités.  

          lc_messages

          lc_monetary

          lc_time

          lc_numeric

          Dépendant du systèmeAucuneSpecialOui  Positionne la locale à utiliser pour le formatage des messages d'erreur, des montants monétaires, des valeurs de temps et des valeurs numériques. Les valeurs acceptables dépendent du système. Lorsque la valeur est constituée d'une chaîne vide (valeur par défaut), la valeur est héritée de l'environnement d'exécution du serveur. Ces paramètres sont positionnés par le script initdb lors de la création du répertoire PGDATA. En général positionné à la langue, la monnaie... de l'utilisateur.
          client_encodingDépendant du système d'exploitationAucunesql_asciiOui  Positionne l'encodage côté client pour les jeux de caractères multi-octet. Par défaut, l'encodage de la base est utilisé. Généralement ignoré au profit de l'encodage de la base. Ne doit être positionné par client que dans les applications multilingues, ce qui requiert un effort considérable de gestion des différents encodages.

          Autres valeurs par défaut

          Tableau 19. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          password_encryptiontrue, falseAucunetrueOui  Détermine le chiffrement du mot de passe lorsque ni ENCRYPTED ni UNENCRYPTED ne sont précisés lors de l'indication d'un mot de passe avec les commandes CREATE USER et ALTER USER. Il est préférable de laisser la valeur à true (vrai) dans tous les cas.
          dynamic_library_pathpathAucune'$libdir'Non  Si un module chargeable dynamiquement doit être ouvert et que le nom ne précise le répertoire (i.e. le nom ne contient pas de slash), le système recherche le ficheir dans ce chemin. Le nom utilisé est celui précisé par la commande CREATE FUNCTION ou LOAD. Peut être positionné par le superutilisateur.

          Gestion des verrous

          Tableau 20. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          deadlock_timeout1 à Int MaxVoir les notes1000Non  Temps d'attente, en millisecondes, avant de vérifier qu'un verrou est en condition de verrou mort. La recherche de verrou mort étant relativement lente, le serveur ne la lance pas à chaque fois qu'il attend le relâchement d'un verrou. Les auteurs estiment (de façon optimiste ?) que les verrous morts ne sont pas légion en production et de fait patientent quelques instants après la pose d'un verrou avant de rechercher les verrous morts. L'augmentation de cette valeur réduit le temps perdu à rechercher inutilement les verrous morts, mais retarde la découverte des erreurs de verrous réellement morts. La valeur par défaut est 1000 (i.e., une seconde), ce qui est probablement la plus petite valeur acceptable dans la pratique. Sur un serveur en charge, cette valeur peut être augmentée. Dans l'idéal, la valeur dépasse le temps habituel d'une transaction, de façon à accroître les chances que le verrou soit relâché avant que le serveur ne recherche les verrous morts.  
          max_locks_per_transaction10 à Int Max 64Non  La table partagée de verrous est dimensionnée d'après l'hypothèse qu'au maximum max_locks_per_transaction * max_connections objets distincts sont verrouillés au même moment. La valeur par défaut, 64, qui a prouvé au fil des ans qu'elle était suffisante, peut toutefois être augmentée lorsque différents clients attaquent de nombreuses tables au sein d'une même transaction. L'option ne peut être positionnée qu'au démarrage du serveur.  

          Compatibilité de version et de plateforme Compatibility

          Versions précédentes de PostgreSQL

          Tableau 21. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          add_missing_fromtrue, falseAucunetrueOui  Autorise le planificateur à « ajouter les clauses FROM manquantes » (add missing from clause) lorsqu'une table est omise dans la requête. Sera positionné à false dans les prochaines versions.  
          regex_flavoradvanced, extended, basicAucuneadvancedOui  La « flavor » d'une expression rationnelle peut être positionnée à advanced (avancée), extended (étendue) ou basic (basique). La valeur par défaut est advanced. L'option extended peut être utile pour une compatibilité exacte avec les versions pre-7.4 de PostgreSQL.  
          sql_inheritancetrue, falseAucunetrueOui  Contrôle la sémantique de l'héritage, en particulier l'inclusion par défaut ou non des tables filles par diverses commandes. Elles ne l'étaient pas dans les versions antérieures à la 7.1. Pour retrouver l'ancien comportement, ce paramètre est positionné à off. Toutefois, sur le long terme, il est préférable de modifier les applications afin d'utiliser le mot-clé ONLY pour exclure les tables filles.  

          Compatibilité plateforme et client

          Tableau 22. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          transform_null_equalstrue, falseAucunefalseOui  Lorsque ce paramètre est positionné, les expressions de la forme expr = NULL (ou NULL = expr) sont traitées comme expr IS NULL, c'est-à-dire qu'elles retournent true si expr s'évalue à null et false dans le cas contraire. Le comportement correct de expr = NULL est de toujours retourner null (valeur inconnue).  

          Options pour les développeurs

          Note

          Ces options ont été supprimées du fichier postgresql.conf dans la version 7.4 parce qu'elles n'ont aucune utilité pour les utilisateurs, mais sont destinées aux développeurs. La plupart des DBA n'ont aucune raison de modifier ces paramètres.

          Tableau 23. 

          ParamètreÉchelleRessourcesValeur par défautPositionnement-oCe que dit la documentationCommentaires
          wal_debug0-16 0Non  Positionné à une valeur non-nulle, active la sortie de débogage relative au WAL sur l'erreur standard. Peut-être positionné par le superutilisateur.
          trace_notifytrue, false falseOui  Engendre une grande quantité de sortie de débogage pour les commandes LISTEN et NOTIFY.

          trace_locks

          trace_userlocks

          trace_lwlocks

          debug_deadlocks

          trace_lock_oidmin

          trace_lock_table

          log_btree_build_stats

          true, false 

          false

          false

          false

          false

          16384

          0

          false

          Non  À ce jour, ces options ne sont pas documentées.  
          debug_assertionstrue, false trueOui  Active les vérifications de diverses assertions. C'est une aide au débogage. Ce paramètre peut être activé en cas de problèmes ou plantages étranges car il peut mettre en exergue des erreurs de programmation. Pour utiliser cette option, il est nécessaire de définir la macro USE_ASSERT_CHECKING à la construction de PostgreSQL (à l'aide de l'option de configuration --enable-cassert). DEBUG_ASSERTIONS est activé par défaut si PostgreSQL a été construit avec l'activation des assertions. Utile uniquement si le système PostgreSQL plante ou pour les hackers.
          zero_damaged_pagestrue, false falseNon  La détection d'un en-tête de page endommagé entraîne PostgreSQL à rapporter une erreur, interrompant la transaction en cours. Le positionnement de zero_damaged_pages à true impose au système de rapporter plutôt un avertissement, réinitialiser la page endommager et continuer le processus. Ce comportement détruit les données, à savoir toutes les lignes sur la page endommagée. Mais il permet également de passer outre l'erreur et récupérer les lignes de toutes les pages non endommagées de la table. Cette option est donc utile pour récupérer des données si une corruption a suivi un plantage matériel ou logiciel. Ce paramètre ne devrait être positionné à true que lorsque tout espoir de récupérer les données à partir des pages endommagées d'une table est perdu. Par défaut le paramètre est désactivé. Il ne peut être changé que par le superutilisateur. Peut être positionné par le superutilisateur.