• Dépendent d’options de compilation
• Quasiment jamais modifiés
• Tailles de bloc ou de fichier
• block_size
• wal_block_size
• segment_size
• wal_segment_size

• Le fichier principal de configuration :

  • format clé = valeur
  • valeur entre « ' » (single quote) si chaîne de caractère
  • classé par sections
  • commenté (change requires restart)
  • paramètre config_file
  • inclusion externe supportée avec les clauses include et include_if_exists

• Authentification multiple, suivant l’utilisateur, la base et la source de la connexion.

  • pg_hba.conf (Host Based Authentication)
  • pg_ident.conf, si mécanisme externe d’authentification
  • paramètres hba_file et ident_file

• Espace de stockage d’objets
• Un simple répertoire
• Répartit la charge et la volumétrie sur plusieurs disques
• paramètres default_tablespace et temp_tablespaces

• Fichiers texte traçant l’activité
• Très paramétrables
• Gestion des fichiers soit :
  • Par PostgreSQL
  • Délégués au système d’exploitation (syslog, eventlog)

• Shared buffers ou blocs de mémoire partagée

  • Partage les blocs entre les processus
  • Cache en lecture ET écriture
  • Double emploi partiel avec le cache du système
  • Importants pour les performances

• Buffer pin
• Buffer dirty/clean
• Compteur d’utilisation (usagecount)
• Clocksweep

• Le Background Writer synchronise les buffers :
  • de façon anticipée
  • une portion des pages à synchroniser
  • paramètres : bgwriter_delay, bgwriter_lru_maxpages, bgwriter_lru_multiplier et bgwriter_flush_after
• Le checkpointer synchronise les buffers :
  • lors des checkpoints
  • synchronise toutes les dirty pages

• Garantir la durabilité des données
• Base encore cohérente après :
  • Un arrêt brutal des processus
  • Un crash machine
  • …
• Appelée Write Ahead Logging
• Écriture des modifications dans un journal avant de les effectuer.

• Point de reprise
• D’où doit-on rejouer le journal ?
• Données écrites au moins au niveau du checkpoint
• Deux moyens pour déclencher automatiquement et périodiquement un CHECKPOINT
  • max_wal_size
  • checkpoint_timeout
• Dilution des écritures
  • checkpoint_completion_target

• Réduire les appels à fsync
• Mutualiser les écritures entre transactions
• Un processus d’arrière plan
• Paramètres importants :
  • wal_buffers
  • wal_writer_delay
  • wal_writer_flush_after
  • synchronous_commit
• Attention au paramètre fsync

• Récupération à partir de vieille sauvegarde
• Sauvegarde à chaud
• Sauvegarde en continu
• Paramètres : wal_level, archive_mode, archive_command et archive_timeout

• Appliquer les journaux :

  • Non plus fichier par fichier
  • Mais entrée par entrée (en flux continu)
  • Base de Standby très proche de la production
  • Paramètres : max_wal_senders, wal_keep_segments, wal_sender_delay et wal_level

• Base de Standby accessible en lecture
• Peut basculer en lecture/écriture sans redémarrage (sessions conservées)
• Peut fonctionner avec la Streaming Replication
• Paramètres
  • sur l’esclave :

  hot_standby, max_standby_archive_delay, max_standby_streaming_delay

  • sur le maître :
  wal_level

• Collecte de deux types de statistiques différents :

  • Statistiques d’activité
  • Statistiques sur les données

• Collectées par chaque session durant son travail
• Remontées au Stats Collector
• Stockées régulièrement dans un fichier, 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 et stats_temp_directory

• 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é

• Statistiques sur les données :

  • Collectées par échantillonnage
  • Table par table (et pour certains index)
  • Colonne par colonne
  • Pour de meilleurs plans d’exécution

• 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

• 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 :
  • seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost et cpu_operator_cost
  • parallel_setup_cost et parallel_tuple_cost
  • effective_cache_size

• Pour le partitionnement : constraint_exclusion
• Pour limiter les réécritures : from_collapse_limit et join_collapse_limit
• Pour les curseurs : cursor_tuple_fraction
• Pour mutualiser les entrées-sorties : synchronize_seqscans

• GEQO :

  • Un optimiseur génétique
  • État initial, puis mutations aléatoires
  • Rapide, mais non optimal
  • Paramètes : geqo et geqo_threshold

• 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

• L’accès à la base se fait par un protocole réseau clairement défini :

  • Sur des sockets TCP (IPV4 ou IPV6)
  • Sur des sockets Unix (sous Unix uniquement)

• Les demandes de connexion sont gérées par le postmaster.

• Paramètres : port, listen_adresses, unix_socket_directory, unix_socket_group et unix_socket_permissions

• Paramètres de keepalive TCP
  • tcp_keepalives_idle
  • tcp_keepalives_interval
  • tcp_keepalives_count
• Paramètres SSL
  • ssl
  • ssl_ciphers
  • ssl_renegotiation_limit
• Autres paramètres

• PostgreSQL est un SGBD complet.

• Cela impose une conception complexe, et l’interaction de nombreux composants.

• Une bonne compréhension de cette architecture est la clé d’une bonne administration :

  • Le paramétrage est compris
  • La supervision est plus rigoureuse
• Le second module traite de la gestion des transactions (MVCC).

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

PostgreSQL utilise un modèle appelé MVCC (Multi-Version Concurrency Control).

• Gestion concurrente des transactions
• Excellente concurrence
• Impacts sur l’architecture

• Nous allons aborder :

  • Présentation de MVCC
  • Niveaux d’isolation
  • Implémentation de MVCC de PostgreSQL
  • Vacuum et son paramétrage
  • Autovacuum et son paramétrage
  • Verrouillage

• Que signifie MVCC
• Quelles solutions alternatives
• Implémentations possibles de MVCC

• MultiVersion Concurrency Control
• Contrôle de Concurrence Multi-Version
• Plusieurs versions du même enregistrement

• Une seule version de l’enregistrement en base
• Verrouillage (granularité ?)
• Contention ?
• Cohérence ?
• Annulation ?

• MVCC par UNDO :

  • Une version de l’enregistrement dans la table
  • Sauvegarde des anciennes versions
  • L’adresse physique d’un enregistrement ne change pas
  • La lecture cohérente est complexe
  • L’UNDO est complexe à dimensionner
  • L’annulation est lente

• Copy On Write (duplication à l’écriture)
• Une version d’enregistrement n’est jamais modifiée
• Toute modification entraîne une nouvelle version

• Chaque transaction (et donc session) est isolée à un certain point :
  • elle ne voit pas les opérations des autres
  • elle s’exécute indépendamment des autres
• On peut spécifier le niveau d’isolation au démarrage d’une transaction :
  • BEGIN ISOLATION LEVEL xxx;

• Autorise la lecture de données modifiées mais non validées par d’autres transactions
• Aussi appelé DIRTY READS par d’autres moteurs
• Pas de blocage entre les sessions
• Inutile sous PostgreSQL en raison du MVCC
• Si demandé, la transaction s’exécute en READ COMMITTED

• La transaction ne lit que les données validées en base
• Niveau d’isolation par défaut
• Un ordre SQL s’exécute dans un instantané (les tables semblent figées sur la durée de l’ordre)
• L’ordre suivant s’exécute dans un instantané différent

• Instantané au début de la transaction
• Ne voit donc plus les modifications des autres transactions
• Voit toujours ses propres modifications
• Peut entrer en conflit avec d’autres transactions en cas de modification des mêmes enregistrements

• Niveau d’isolation maximum
• Plus de lectures non répétables
• Plus de lectures fantômes
• Instantané au démarrage de la transaction
• Verrouillage informatif des enregistrements consultés (verrouillage des prédicats)
• Erreurs de sérialisation en cas d’incompatibilité

• Colonnes xmin/xmax
• Fichiers clog
• Avantages/inconvénients
• Opération VACUUM
• Structure Free Space Map (FSM)
• Wrap-Around
• Heap-Only Tuples (HOT)
• Visibility Map

Table initiale :

• Comment est effectuée la suppression d’un enregistrement ?
• Comment est effectuée l’annulation de la transaction 150 ?

• La CLOG (Commit Log) enregistre l’état des transactions.
• Chaque transaction occupe 2 bits de CLOG

• Avantages :
  • avantages classiques de MVCC (concurrence d’accès)
  • implémentation simple et performante
  • peu de sources de contention
  • verrouillage simple d’enregistrement
  • rollback instantané
  • données conservées aussi longtemps que nécessaire

• Inconvénients :
  • Nettoyage des enregistrements (VACUUM)
  • Tables plus volumineuses
  • Pas de visibilité dans les index

• Vue pg_stat_progress_vacuum
  • disponible dès la 9.6
• heap_blks_scanned, blocs parcourus
• heap_blks_vacuumed, blocs nettoyés
• index_vacuum_count, nombre de passes dans l’index

• MVCC a été affiné au fil des versions :

  • Heap-Only Tuples
  • Free Space Map dynamique
  • Visibility Map

Wraparound : bouclage d’un compteur

• Le compteur de transactions : 32 bits
• 4 milliards de transactions
• Qu’arrive-t-il si on boucle ?
• Quelles protections ?

• Mémoire
  • maintenance_work_mem
• Gestion du coût
  • vacuum_cost_delay
  • vacuum_cost_page_hit
  • vacuum_cost_page_miss
  • vacuum_cost_page_dirty
  • vacuum_cost_limit

• Gel des lignes
  • vacuum_freeze_min_age
  • vacuum_freeze_table_age
  • vacuum_multixact_freeze_min_age
  • vacuum_multixact_freeze_table_age

• Quantité de mémoire allouable
• Impact sur VACUUM
• Sur construction d’index (hors sujet)

• vacuum_cost_page_hit
• vacuum_cost_page_miss
• vacuum_cost_page_dirty
• vacuum_cost_limit
• vacuum_cost_delay

• Principe de FREEZE
• vacuum_freeze_min_age
• vacuum_freeze_table_age
• vacuum_multixact_freeze_min_age
• vacuum_multixact_freeze_table_age

• Autovacuum :

  • Ne plus s’occuper de VACUUM
  • Automatique
  • Suit l’activité
  • S’occupe aussi des statistiques

• autovacuum
• autovacuum_naptime
• autovacuum_max_workers
• autovacuum_work_mem

• autovacuum_vacuum_threshold
• autovacuum_vacuum_scale_factor
• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor

• autovacuum_vacuum_cost_delay
• autovacuum_vacuum_cost_limit

• autovacuum_freeze_max_age
• autovacuum_multixact_freeze_max_age

La gestion des verrous est liée à l’implémentation de MVCC.

• Verrouillage d’objets en mémoire
• Verrouillage d’objets sur disque
• Paramètres

PostgreSQL possède un gestionnaire de verrous

• Verrous d’objet
• Niveaux de verrouillage
• Deadlock
• Vue pg_locks

• Le gestionnaire de verrous possède des verrous sur enregistrements.

• Ils sont :

  • transitoires
  • pas utilisés pour prendre les verrous définitifs

• Utilisation de verrous sur disque.

• Pas de risque de pénurie de verrous.

• max_locks_per_transaction et max_pred_locks_per_transaction
• lock_timeout
• deadlock_timeout
• log_lock_waits

• PostgreSQL dispose d’une implémentation MVCC complète, permettant :
  • Que les lecteurs ne bloquent pas les écrivains
  • Que les écrivains ne bloquent pas les lecteurs
  • Que les verrous en mémoire soient d’un nombre limité
• Cela impose par contre une mécanique un peu complexe, dont les parties visibles sont la commande VACUUM et le processus d’arrière-plan Autovacuum.

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

• Sauvegarde traditionnelle
  • sauvegarde pg_dump à chaud
  • sauvegarde des fichiers à froid
• Insuffisant pour les grosses bases
  • Long à sauvegarder
  • Encore plus long à restaurer
• Perte de données potentiellement importante
  • car impossible de réaliser fréquemment une sauvegarde
• Une solution : la sauvegarde PITR

• Mettre en place la sauvegarde PITR
  • archivage manuel ou avec pg_receivewal
  • sauvegarde manuelle ou avec pg_basebackup
• Restaurer une sauvegarde PITR
• Quelques outils pour aller plus loin
  • barman
  • pitrery

• Point In Time Recovery
• À chaud
• En continu
• Cohérente

• Les journaux de transactions contiennent toutes les modifications
• Il faut les archiver
• ... et avoir une image des fichiers à un instant t
• La restauration se fait en restaurant cette image
• ... et en rejouant les journaux
  • entièrement
  • partiellement (ie jusqu'à un certain moment)

• Sauvegarde à chaud
• Rejeu d'un grand nombre de journaux
• Moins de perte de données

• Sauvegarde de l'instance complète
• Nécessite un grand espace de stockage (données + journaux)
• Risque d'accumulation des journaux en cas d'échec d'archivage
• Restauration de l'instance complète
• Impossible de changer d'architecture
• Plus complexe

• 2 étapes :
  • Archivage des journaux de transactions
  • par archiver
  • par pg_receivewal
  • Sauvegarde des fichiers
  • manuellement (outils de copie classiques)
  • pg_basebackup

• Deux méthodes
  • processus archiver
  • pg_receivewal sur un serveur secondaire

• À faire quelle que soit la méthode d'archivage
• Attention aux droits d'écriture dans le répertoire
  • la commande configurée pour la copie doit pouvoir écrire dedans
  • et potentiellement y lire

• configuration (postgresql.conf)
  • wal_level = replica
  • archive_mode = on ou always
  • archive_command = '... une commande ...'
  • archive_timeout = 0
• Ne pas oublier de forcer l'écriture de l'archive sur disque

• Par redémarrage de PostgreSQL
  • si modification de wal_level et/ou archive_mode
• Par envoi d'un signal à PostgreSQL

• Utilise le protocole de réplication
• Enregistre en local les journaux de transactions
• Permet de faire de l'archivage PITR
• Va plus loin que l'archivage standard
  • pas de archive_timeout car toujours au plus près du maître
• Nécessité d'utiliser les slots de réplication

• Modification du fichier postgresql.conf

max_wal_senders = 3
max_replication_slots = 1

• Modification du fichier pg_hba.conf

host  replication  repli_user  192.168.0.0/24  md5

• Création de l'utilisateur de réplication

CREATE ROLE repli_user LOGIN REPLICATION PASSWORD 'supersecret'

• Redémarrage du serveur PostgreSQL
• Création d'un slot de réplication

SELECT pg_create_physical_replication_slot('archivage');

• Exemple de lancement

pg_receivewal -D /data/archives -S archivage

• Plein d'autres options
  • notamment pour la connexion (-h, -p, -U)
• Journaux créés en temps réel dans le répertoire de stockage
• Mise en place d'un script de démarrage
• S'il n'arrive pas à joindre le maître
  • pg_receivewal s'arrête

• Méthode archiver
  • simple à mettre en place
  • perte au maximum d'un journal de transactions
• Méthode pg_receivewal
  • mise en place plus complexe
  • perte minimale (les quelques dernières transactions)

• Appel de la procédure stockée pg_start_backup()
• Argument 1
  • un label, libre
• Argument 2, optionnel
  • un booléen indiquant si le CHECKPOINT doit être forcé
• Argument 3, optionnel
  • un booléen indiquant si la sauvegarde est concurrente
• Aucun impact pour les utilisateurs

• Sauvegarde des fichiers à chaud
  • le répertoire principal des données
  • les tablespaces
• Ignorer
  • postmaster.pid
  • log
  • pg_wal
  • pg_replslot

• Appel de la procédure stockée pg_stop_backup()

• Réalise les différentes étapes d'une sauvegarde
  • ... via une connexion de réplication
• Configuration de réplication à faire sur le serveur à sauvegarder
• Exécution de pg_basebackup sur le serveur de sauvegarde
• Copie intégrale, pas d'incrémental
• Possible d'indiquer un slot de réplication (9.6)

$ pg_basebackup -Ft -x -c fast -P \
                -h 127.0.0.1 -U sauve -D sauve_20120625

• Dépend des besoins
• De tous les jours à tous les mois
• Plus elles sont espacées, plus la restauration est longue
  • et plus le risque d'un journal corrompu ou absent est important

• Une procédure relativement simple
• Mais qui doit être effectuée rigoureusement

• S'il s'agit du même serveur
  • arrêter PostgreSQL
  • supprimer le répertoire des données
  • supprimer les tablespaces

• Restaurer les fichiers de la sauvegarde
• Supprimer les fichiers compris dans le répertoire pg_wal restauré
  • ou mieux, ne pas les avoir inclus dans la sauvegarde initialement
• Restaurer le dernier journal de transactions connu (si disponible).

• Configuration (recovery.conf)
  • restore_command = '... une commande ...'
• Si restauration jusqu'à un certain moment
  • recovery_target_name, recovery_target_time
  • recovery_target_xid, recovery_target_lsn
  • recovery_target_inclusive
• Divers
  • recovery_target_timeline
  • pause_at_recovery_target

• Démarrer PostgreSQL

• En fin de recovery, la timeline change :
  • L'historique des données prend une autre voie
  • Le nom des WAL change pour éviter d'écraser des archives suivant le point d'arrêt
  • L'aiguillage est inscrit dans un fichier .history, archivé
• Permet de faire plusieurs restaurations PITR à partir du même basebackup
• recovery_target_timeline permet de choisir la timeline à suivre

• Gagner en place
  • ... en compressant les journaux de transactions
• Se faciliter la vie avec différents outils
  • barman
  • pitrery

• Objectif : éviter de consommer trop de place disque
• Méthode recommandée
  • outils de compression standards : gzip, bzip2, lzma
• Méthode déconseillée
  • outil de compression spécialisé : pglesslog

• Gère la sauvegarde et la restauration
  • mode pull
  • multi-serveurs
• Une seule commande (barman)
• Et de nombreuses actions
  • list-server, backup, list-backup, recover, ...

• Gère la sauvegarde et la restauration
  • mode push
  • mono-serveur
• Multi-commandes
  • archive_xlog
  • pitrery
  • restore_xlog

• Une sauvegarde
  • Fiable
  • Éprouvée
  • Rapide
  • Continue
• Mais
  • Plus complexe à mettre en place
  • Qui restaure toute l'instance

Comme tous les SGBD-R, PostgreSQL fournit des fonctionnalités avancées.

Ce module présente les fonctionnalités orientées DBA.

PostgreSQL propose de nombreuses vues système :

• Pour monitorer/remonter de la métrologie
• Pour diagnostiquer un incident
• Pour rapidement obtenir des informations sur le système

pg_stat_activity :

• Liste des processus en cours
  • sessions
  • processus en tâche de fond (10+)
• Requête en cours/dernière exécutée
• IDLE IN TRANSACTION
• Sessions en attente de verrou
• Gagne en informations au fil des versions

Quand le SSL est activé sur le serveur, cette vue indique pour chaque connexion cliente les informations suivantes :

• SSL activé ou non
• Version SSL
• Suite de chiffrement
• Nombre de bits pour algorithme de chiffrement
• Compression activée ou non
• Distinguished Name (DN) du certificat client

pg_stat_database :

Des informations globales à chaque base :

• nombre de sessions
• transactions validées/annulées
• accès blocs
• accès enregistrements
• taille et nombre de fichiers temporaires
• temps d'entrées/sorties

pg_stat_user_tables :

• statistiques niveau « ligne »
• insertions/mise à jour/suppression
• type et nombre d'accès
• opérations de maintenance
• détection des tables mal indexées ou très accédées

pg_stat_user_indexes :

• vue par index
• nombre d'accès et efficacite

pg_statio_user_tables, pg_statio_user_indexes :

• opérations au niveau bloc
• demandés à l'OS ou au cache
• calculer des hit ratios

• pg_locks :
  • visualisation des verrous en place
  • tous types de verrous sur objets
• Complexe à interpréter :
  • verrous sur enregistrements pas directement visibles
  • Article base de connaissance Dalibo

pg_stat_bgwriter

• activité des checkpoint
• visualiser le volume d'allocations et d'écritures

pg_stat_archiver (9.4+) :

• bon fonctionnement de l'archivage
• quand et combien d'erreurs d'archivages se sont produites

pg_stat_replication :

• État des esclaves connectés au maître en SR
• Mesure du lag

pg_stat_database_conflicts :

• nombre de conflits de réplication
• par type

De nombreuses fonctionnalités d'indexation sont disponibles dans PostgreSQL :

• Index multi-colonnes
• Index fonctionnels
• Index partiels
• Covering indexes
• Classes d'opérateurs
• GiN
• GIST
• BRIN
• Hash

Un index peut référencer plus d'une colonne :

• CREATE INDEX idx ON ma_table (col1,col2,col3)
• Index trié sur le n-uplet (col1,col2,col3)
• Accès direct à n'importe quelle valeur de
  • (col1,col2,col3)
  • (col1,col2)
  • (col1)

Il s'agit d'un index sur le résultat d'une fonction :

WHERE upper(a)='DUPOND'

• l'index classique ne fonctionne pas

CREATE INDEX mon_idx ON ma_table ((UPPER(a))

• La fonction doit être IMMUTABLE

• Un index partiel n'indexe qu'une partie des données d'une table, en précisant une clause WHERE à la création de l'index :

CREATE INDEX idx_partiel ON trapsnmp (date_reception)
WHERE est_acquitte=false;

• Beaucoup plus petit que l'index complet.
• Souvent dédié à une requête précise :

SELECT * FROM trapsnmp WHERE est_acquitte=false
ORDER BY date_reception

• La clause WHERE ne porte pas forcément sur la colonne indexée, c'est même souvent plus intéressant de la faire porter sur une autre colonne.

Les Covering Indexes (on trouve parfois « index couvrants » dans la littérature française) :

• Répondent à la clause WHERE
• ET contiennent toutes les colonnes demandées par la requête
• SELECT col1,col2 FROM t1 WHERE col1>12
• CREATE INDEX idx1 on T1 (col1,col2)
• Pas de visite de la table (donc peu d'accès aléatoires, l'index étant à peu près trié physiquement)

Un index utilise des opérateurs de comparaison :

• Il peut exister plusieurs façons de comparer deux données du même type
• Par exemple, pour les chaînes de caractères
  • Différentes collations
  • Tri sans collation (pour LIKE)
• CREATE INDEX idx1 ON ma_table (col_varchar varchar_pattern_ops)
• Permet SELECT ... FROM ma_table WHERE col_varchar LIKE 'chaine%'

Toutes les fonctionnalités que nous venons de voir peuvent bien sûr être utilisées simultanément :

CREATE INDEX idx_adv ON ma_table
(f(col1), col2 varchar_pattern_ops) WHERE col3<12 ;

SELECT col2 FROM ma_table
WHERE col3<12 and f(col1)=7 and col2 LIKE 'toto%' ;

• Generalized Inverted iNdex
• Index inversé ?
  • Index associe une valeur à la liste de ses adresses
  • Utile pour tableaux, listes…
• Pour chaque entrée du tableau
  • Liste d'adresses (TID) où le trouver
• Option fastupdate (8.4+)
  • à désactiver pour avoir un temps de réponse stable
• Liste compressée (9.4+)
  • alternative à bitmap

GiST : Generalized Search Tree

• Arbre de recherche généralisé
• Indexation non plus des valeurs mais de la véracité de prédicats
• Moins performants car moins sélectifs que Btree
• Mais peuvent indexer à peu près n'importe quoi
• Multi-colonnes dans n'importe quel ordre
• Sur-ensemble de Btree et Rtree

BRIN : Block Range INdex (9.5+)

• Utile pour les tables très volumineuses
  • L'index produit est petit
• Performant lorsque les valeurs sont corrélées à leur emplacement physique
• Types qui peuvent être triés linéairement (pour obtenir min/max)

Index Hash :

• Non journalisés donc facilement corrompus
• Moins performants que les Btree
• Ne gèrent que les égalités, pas < et >
• Mais plus compacts
• À ne pas utiliser

Index inutilisé :

• L'optimiseur pense qu'il n'est pas rentable
  • Il a le plus souvent raison
  • S'il se trompe : statistiques ? bug ?
• La requête n'est pas compatible
  • Clause WHERE avec fonction ?
  • Cast ?
• C'est souvent tout à fait normal

Contrainte d'exclusion : Une extension du concept d'unicité

• Unicité : n-uplet1 = n-uplet2 interdit dans une table

• Contrainte d'exclusion : n-uplet1 op n-uplet2 interdit dans une table

• op est n'importe quel opérateur indexable par GiST

CREATE TABLE circles
    ( c circle,
      EXCLUDE USING gist (c WITH &&));

• Avant la version 10
• Fractionner une table en plusieurs tables plus petites
  • meilleures performances
  • maintenance plus facile
• Type de fractionnement
  • liste
  • intervalle ou échelle de valeur
  • hachage

• Déterminer la clé de partitionnement
• Créer la table principale
• Créer les tables filles
• Rediriger les écritures
  • Écriture dans la dernière partition créée
  • Écriture en fonction de la clé
• Paramétrer PostgreSQL pour les lectures
• Tester les requêtes (plans d'exécution)

• Héritage de tables
• Table principale :
  • table mère définie normalement
• Partitions :
  • tables filles
  • héritent des propriétés de la table mère
  • mais pas les contraintes, index et droits

• Les lectures sont gérées par l'optimiseur
• constraint_exclusion change son comportement
  • off, optimisation du partitionnement désactivée
  • partition, optimisation activée pour les tables enfants ou requêtes avec UNION ALL
  • on, recherche d'une optimisation pour toutes les tables
• constraint_exclusion = partition par défaut
• L'optimisation consiste à ne parcourir que les partitions utiles

• PostgreSQL sait gérer
  • les DELETE
  • les UPDATE (tant que la clé de partitionnement n'est pas mise à jour)
• Il faut l'aider pour :
  • rediriger les INSERT dans la bonne partition
  • aider les UPDATE sur la clé de partitionnement
• On utilisera un TRIGGER

• Pas de contraintes d'unicité sur l'ensemble des tables partitionnées
  • la contrainte n'est vérifiée que dans la partition
• Performances dégradées en écriture
• Certaines requêtes ont des plans d'exécution désastreux
• L'héritage n'est pas conçu pour permettre plus de 100 partitions
• Utilisation bien plus importante des verrous

• Outils pour simplifier la gestion
  • pg_partman

• À partir de la version 10
• Mise en place et administration simplifiées car intégrées au moteur
• Gestion automatique des lectures et écritures
• Partitions
  • attacher/détacher une partition
  • contrainte implicite de partitionnement
  • expression possible pour la clé de partitionnement
  • sous-partitions possibles

• Liste de valeurs par partition

• Créer une table partitionnée :
  CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1);

• Ajouter une partition :
  CREATE TABLE t1_a PARTITION of t1 FOR VALUES IN (1, 2, 3);

• Attacher la partition :
  ALTER TABLE t1 ATTACH PARTITION t1_a FOR VALUES IN (1, 2, 3);

• Détacher la partition :
  ALTER TABLE t1 DETACH PARTITION t1_a;

• Intervalle de valeurs par partition

• Créer une table partitionnée :

  CREATE TABLE t2(c1 integer, c2 text) PARTITION BY RANGE (c1);

• Ajouter une partition :

  CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (1) TO (100);

• Détacher une partition :

  ALTER TABLE t2 DETACH PARTITION t2_1;

• Clé sur plusieurs colonnes acceptée
  • uniquement pour le partitionnement par intervalle

• Créer une table partitionnée avec une clé multi-colonnes :
  CREATE TABLE t1(c1 integer, c2 text, c3 date)
PARTITION BY RANGE (c1, c3);

• Ajouter une partition :
  CREATE TABLE t1_a PARTITION of t1
 FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11');

t1 (non partitionnée) :

INSERT INTO t1 select i, 'toto'
  FROM generate_series(0, 9999999) i;
Time: 10097.098 ms (00:10.097)

t2 (nouveau partitionnement) :

INSERT INTO t2 select i, 'toto'
  FROM generate_series(0, 9999999) i;
Time: 11448.867 ms (00:11.449)

t3 (ancien partitionnement) :

INSERT INTO t3 select i, 'toto'
  FROM generate_series(0, 9999999) i;
Time: 125351.918 ms (02:05.352)

• La table mère ne peut pas avoir de données
• La table mère ne peut pas avoir d'index
  • ni PK, ni UK, ni FK pointant vers elle
• Pas de colonnes additionnelles dans les partitions
• L'héritage multiple n'est pas permis
• Valeurs nulles acceptées dans les partitions uniquement si la table partitionnée le permet
• Partitions distantes pour l'instant pas supportées
• En cas d'attachement d'une partition
  • vérification du respect de la contrainte (Seq Scan de la table)
  • sauf si ajout au préalable d'une contrainte CHECK identique

Un espace de stockage :

• Un répertoire du système d'exploitation hors de PGDATA
• Soit pour répartition d'entrées/sorties
• Soit pour quota (taille du système de fichiers)

CREATE TABLESPACE tbs1 LOCATION '/fs1/';
ALTER TABLE ma_table SET TABLESPACE tbs1;

• seq_page_cost et random_page_cost par tablespace
• temp_tablespaces

TOAST : The Oversized-Attribute Storage Technique

• Un enregistrement ne peut pas dépasser la taille d'un bloc
• « Contournement » : champs trop grands dans table de débordement TOAST
• Éventuellement compressé : PLAIN/MAIN/EXTERNAL/EXTENDED
• Jusqu'à 1 Go par attribut
• Transparent, seulement visible par pg_class

Deux méthodes pour stocker des objets binaires :

• bytea : une colonne comme une autre de la table
  • Maxi 1 Go (à éviter)
  • Accès aléatoire à un morceau lent
• Large Object
  • Se manipule plutôt comme un fichier
  • Accès avec des primitives de type fichier
  • Maxi 4To (à éviter aussi…)
  • Objet séparé

Type natif :

• Se manipule exactement comme les autres :
  • bytea : bytea array, tableau d'octets
  • Représentation textuelle de son contenu
  • Deux formats : hex et escape (bytea_output)
• Si le champ est gros, sa récupération l'alloue intégralement en mémoire
• Toute modification d'un morceau du bytea entraîne la réécriture complète du bytea
• Intéressant pour les petits volumes, jusqu'à quelques méga-octets

Large Object :

• Totalement indépendant de la table
• Identifié par un OID (identifiant numérique unique)
• On stocke habituellement cet OID dans la table « principale »
• Suppression manuelle, par trigger, ou par batch (extensions)
• lo_create(), lo_import(), lo_seek(), lo_open(), lo_read(), lo_write()…

Unlogged Tables :

• Les données d'une table ne nécessitent pas toujours la durabilité
  • Tables temporaires « partagées » entre plusieurs sessions
  • Intégration de données
  • Cache de données générées
  • Données « matérialisées »
• Non journalisée, donc non répliquée et remise à zéro en cas de crash

• Depuis la 9.5 on peut passer d'une table journalisée à non journalisée et vice-versa
  • ALTER TABLE SET LOGGED
• Attention à la production de fichiers WAL lors du passage de UNLOGGED à LOGGED.

Full Text Search/Recherche Plein Texte

• Recherche « à la Google » :
• On n'indexe plus une chaîne de caractère mais
  • Les mots (« lexèmes ») qui la composent
  • On peut rechercher sur chaque lexème indépendamment
• Les lexèmes sont soumis à des règles spécifiques à chaque langue
• Dictionnaires filtrants (unaccent)
• S'appuie sur GIN ou GiST

Collation par colonne :

• L'ordre alphabétique pas forcément le même pour toute une base
  • Champs multi-lingues par exemple
• Possibilité de préciser la collation
  • Par colonne
  • Par index
  • Par requête
  • CREATE TABLE messages (id int,fr TEXT COLLATE "fr_FR.utf8", de TEXT COLLATE "de_DE.utf8");

SSI : Serializable Snapshot Isolation

• Chaque transaction est seule sur la base
• Si on ne peut maintenir l'illusion
  • Une des transactions en cours est annulée
• Sans blocage
• On doit être capable de rejouer la transaction
• Toutes les transactions impliquées doivent être serializable
• default_transaction_isolation=serializable dans la configuration

• Aucune information n'est cachée (Open-Source)
• Nombreuses tables et vues systèmes pour trouver l'information sur l'état du serveur
• PostgreSQL embarque des fonctionnalités performantes, complexes et parfois uniques dans le monde des bases de données Open Source

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

Ce module présente les extensions de PostgreSQL.

Les extensions permettent de rajouter des types de données, des méthodes d'indexation, des fonctions et opérateurs, des tables, des vues…

Dans le but de rajouter des fonctionnalités.

Ce sont des fonctionnalitées :

• Livrées avec le code source de PostgreSQL
• Habituellement packagées (postgresql-*-contrib)
• De qualité garantie parce que maintenues par le projet
• Mais optionnelles et désactivées par défaut
• Ou fonctionnalités en cours de stabilisation
• Documentées dans les annexes de PostgreSQL !
• Chapitre F : « Additional Supplied Modules »

Ce sont :

• Des « packages » pour PostgreSQL
• Un ensemble d'objets livrés ensemble
• Connus en tant que tels par le catalogue PostgreSQL
• CREATE EXTENSION, ALTER EXTENSION UPDATE, DROP EXTENSION
• Option CASCADE (à partir de 9.6)
• contrib <> extension

3 approches :

• Foreign Data Wrapper
• dblink (historique)
• PL/Proxy (sharding)

PostgreSQL supporte SQL/MED :

• Management of External Data
• Extension de la norme SQL ISO
• Données externes présentées comme des tables
• En lecture/écriture (si supporté par le driver et à partir de PostgreSQL 9.3)
  • PostgreSQL, Oracle, MySQL (lecture/écriture)
  • fichier CSV, fichier fixe (en lecture)
  • ODBC, JDBC, Multicorn
  • CouchDB, Redis (NoSQL)

Installer un driver (foreign data wrapper) :

CREATE EXTENSION file_fdw;

Créer un « serveur » (ici pas d'options, vu que c'est un driver fichier) :

CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ;

Créer une « foreign table »

CREATE FOREIGN TABLE statistical_data (f1 numeric, f2 numeric)
   SERVER file OPTIONS (filename '/tmp/statistical_data.csv',
                        format 'csv', delimiter ';') ;

• La version 9.5 introduit la notion d'héritage
• Une table locale peut hériter d'une table distante et inversement
• Permet le partitionnement sur plusieurs serveurs
• Pour rappel, l'héritage ne permet pas de conserver :
  • les contraintes d'unicité et référentielles ;
  • les index ;
  • les droits.

• Ajouter le FDW
• Ajouter un serveur
• Ajouter une table distante
• Lire la table distante
• Écrire dans la table distante
• Analyser la table distante
• Plus lent qu'une table locale, surtout pour les patterns d'accès complexes

• Permet le requêtage inter-bases PostgreSQL
• Simple et bien documenté
• L'auteur a voulu obtenir la même fonctionnalité que celle qui est disponible dans une base commerciale réputée... d'où le nom.
• En lecture seule sauf à écrire des triggers sur vue
• Ne transmet pas les prédicats au serveur distant : tout l'objet est systématiquement récupéré
• Plus d'intérêt depuis que le driver SQL/MED pour PostgreSQL est mature

• Une alternative à dblink
• Possibilité de distribuer les requêtes
• Utile pour le « partionnement horizontal »
• Uniquement si votre application n'utilise que des appels de fonction à la base

Stockage de données non-relationnelles :

• hstore : clé-valeur, stockage binaire, fonctions d'extraction, de requêtage, d'indexation avancée
• json : stockage texte JSON, validation syntaxique, fonctions d'extraction
• jsonb : stockage binaire de JSON, converti pour accès rapide, fonctions d'extraction, de requêtage, d'indexation avancée
• Alternative efficace et performante à Entité/Attribut/Valeur (très lent)

Stocker des données non-structurées.

CREATE EXTENSION hstore ;
CREATE TABLE demo_hstore(id serial, meta hstore);
INSERT INTO demo_hstore (meta) values ('river=>t');
INSERT INTO demo_hstore (meta) values ('road=>t,secondary=>t');
INSERT INTO demo_hstore (meta) values ('road=>t,primary=>t');
CREATE INDEX idxhstore ON demo_hstore USING gist (meta);
SELECT * FROM demo_hstore WHERE meta@>'river=>t';
 id |     meta
----+--------------
 15 | "river"=>"t"

• Ce n'est qu'un type texte
• Vérifie que le texte est au format JSON
• Fournit des fonctions de manipulation JSON
  • Mais ré-analyse du champ pour chaque appel de fonction
  • On peut indexer une propriété (index sur fonction)
  • Mais pas d'index avancé comme pour hstore
• => Peu utile (comme XML)

• Apparu en 9.4
• Stockage de JSON en un format Binaire
• Possibilités d'indexation similaires à hstore

• Construire un objet JSON depuis un ensemble : json_object_agg()
• Construire un ensemble de tuples depuis un objet JSON : jsonb_each(), jsonb_to_record()
• Manipuler des tableaux : jsonb_array_elements(), jsonb_to_recordset()

• Extension proposée
• Fournit un « langage de requête », comme tsquery
• Dépôt github

CREATE EXTENSION pg_trgm;
SELECT similarity('bonjour','bnojour');
 similarity
------------
   0.333333

CREATE TABLE test_trgm (text_data text);

INSERT INTO test_trgm(text_data) 
VALUES ('hello'), ('hello everybody'),
('helo youg man'),('hallo!'),('HELLO !');

CREATE INDEX test_trgm_idx on test_trgm
  using gist (text_data extensions.gist_trgm_ops);
SELECT text_data FROM test_trgm
 WHERE text_data like '%hello%';

Cette requête passe par l'index test_trgm_idx, malgré le % initial. On peut utiliser un index GIN aussi (comme pour le Full Text Search).

Champ texte insensible à la casse :

• Beaucoup utilisé pour compatibilité avec SQL Server/MySQL
• Les fonctions de comparaison et tri deviennent insensibles à la casse
• Nécessite une conversion de casse à chaque comparaison
• Plus lent que le type texte

CREATE EXTENSION citext;
CREATE TABLE ma_table (col_insensible citext);

Le module contrib de chiffremment

• Propose de nombreuses fonctions permettant de chiffrer et de déchiffrer des données
• Gros inconvénient : oubliez les index sur les données chiffrées
• N'oubliez pas de chiffrer la connexion (SSL)
• Permet d'avoir une seule méthode de chiffrement pour tout ce qui accède à la base

Pas une contrib :

• Un projet totalement indépendant
• Licence GPL (logiciel libre)
• Extension de PostgreSQL aux types géométriques/géographiques
• La référence des bases de données spatiales
• « quelles sont les routes qui coupent le Rhône ? »
• « quelles sont les villes adjacentes à Toulouse ? »
• « quels sont les restaurants situés à moins de 3 km de la Nationale 12 ? »

• De nombreuses fonctionnalités avancées :
  • Support des coordonnées géodésiques
  • Projections, reprojections dans systèmes de coordonnées locaux (Lambert93 en France par exemple)
  • 3D, extrusions, routage, rasters
  • Opérateurs d'analyse géométrique : enveloppe convexe, simplification…
  • Intégré aux principaux serveurs de carte, ETL, outils de manipulation
• Utilisé par IGN, BRGM, AirBNB, Mappy, Openstreetmap, Agence de l'eau…

Un certain nombre de contribs donnent accès à des informations ou des fonctions de bas niveau :

• pgstattuple : fragmentation des tables et index
• pg_buffercache : état du cache
• pg_freespacemap : liste des blocs libres
• pg_visibility : état de la visibility map
• pageinspect : inspection du contenu d'une page
• pgrowlocks : informations détaillées sur les enregistrements verrouillés
• pg_prewarm : sauvegarde et restauration de l'état du cache de la base

pgstattuple fournit une mesure (par parcours complet de l'objet) sur:

• pour une table :
  • remplissage des blocs
  • enregistrements morts
  • espace libre
• pour un index :
  • profondeur de l'index
  • remplissage des feuilles
  • fragmentation (feuilles non consécutives)

Qu'y-a-t'il dans le cache de PostgreSQL ?

Fournit une vue :

• Pour chaque page (donc pour l'ensemble de l'instance)
  • fichier (donc objet) associé
  • OID base
  • fork (0 : table, 1 : FSM, 2 : VM)
  • numéro de bloc
  • isdirty
  • usagecount

La Freespacemap :

• Est renseignée par VACUUM, par objet (table/index)
• Et consommée par les sessions modifiant des données (INSERT/UPDATE)
• Interroger la freespacemap permet de connaître l'espace libre cartographié par VACUUM
• Rarement utilisé (dans le cas de doute sur l'efficacité de VACUUM)

La Visibility Map :

• Est renseignée par VACUUM, par table
• Permet de savoir que l'ensemble des enregistrements de ce bloc est visible
• Indispensable pour les parcours d'index seul
• Interroger la visibility map permet de voir si un bloc est :
  • visible
  • gelé
• Rarement utilisé

pageinspect :

• Vision du contenu d'un bloc
• Sans le dictionnaire, donc sans décodage des données
• Affichage brut
• Utilisé surtout en debug, ou dans les cas de corruption
• Fonctions de décodage pour heap (table), bt (btree), entête de page, et FSM
• Nécessite de connaître le code de PostgreSQL

Les verrous mémoire de PostgreSQL ne verrouillent pas les enregistrements :

• Il est parfois compliqué de comprendre qui verrouille qui, à cause de quel enregistrement
• pgrowlocks inspecte une table pour détecter les enregistrements verrouillés, leur niveau de verrouillage, et qui les verrouille
• scan complet de la table (impossible de trouver autrement)

Extension à partir de PostgreSQL 9.4 :

• Charge une liste de blocs
• Dans le cache du système
  • De façon asynchrone : prefetch (Linux)
  • De façon synchrone : read (tous systèmes)
• Ou dans le cache PostgreSQL
  • De façon synchrone : buffer (tous systèmes)
• À coupler avec une capture du cache (pg_buffercache par exemple)

Capture en temps réel des requêtes :

• Vue, en mémoire partagée (volumétrie contrôlée)
• Par requête
  • Nombre d'exécution, temps cumulé d'exécution, nombre d'enregistrements retournés
  • lectures/écritures en cache, demandées au système, tris
  • temps de lecture/écriture (track_io_timing)
  • Pas d'échantillonnage, seulement des compteurs cumulés

N'est pas une extension :

• Juste un module à charger et des variables à positionner
  • LOAD 'auto_explain' dans une session
  • ou shared_preload_libraries='auto_explain' si global
• Trace le plan de toute requête dépassant une durée d'exécution dans la log
• Éventuellement l'EXPLAIN ANALYZE/BUFFERS : Attention, exécute la requête une seconde fois !
• SET auto_explain.log_min_duration = '3s'

PostgreSQL eXtension Network :

• Site WEB : pgxn.org
  • Nombreuses extensions
  • Volontariat
  • Aucune garantie de qualité
  • Tests soigneux requis
• Et optionnellement client en python pour automatisation de déploiement
• Ancêtre : pgFoundry.org, toujours en service
• Beaucoup de projets sont aussi sur github

• Un nombre toujours plus important d'extension permettant d'étendre les possibilités de PostgreSQL
• Certains modules de contribs sont inclus dans le coeur de PostgreSQL lorsqu'ils sont considérés comme matures et utiles au moteur (tsearch, xml2)
• Un site central pour les extensions PGXN.org, mais toutes n'y sont pas référencées.

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