Découverte des fonctionnalités

Module A2

Dalibo SCOP

24.04

17 avril 2024

Sur ce document

Formation Module A2
Titre Découverte des fonctionnalités
Révision 24.04
PDF https://dali.bo/a2_pdf
EPUB https://dali.bo/a2_epub
HTML https://dali.bo/a2_html
Slides https://dali.bo/a2_slides

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

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.

Découverte des fonctionnalités

PostgreSQL

Au menu

  • Fonctionnalités du moteur

  • Objets SQL

  • Connaître les différentes fonctionnalités et possibilités

  • Découvrir des exemples concrets

Fonctionnalités du moteur

  • Standard SQL
  • ACID : la gestion transactionnelle
  • Niveaux d’isolation
  • Journaux de transactions
  • Administration
  • Sauvegardes
  • Réplication
  • Supervision
  • Sécurité
  • Extensibilité

Respect du standard SQL

  • Excellent support du SQL ISO
  • Objets SQL
    • tables, vues, séquences, routines, triggers
  • Opérations
    • jointures, sous-requêtes, requêtes CTE, requêtes de fenêtrage, etc.

ACID

Gestion transactionnelle : la force des bases de données relationnelles :

  • Atomicité (Atomic)
  • Cohérence (Consistent)
  • Isolation (Isolated)
  • Durabilité (Durable)

MVCC

  • MultiVersion Concurrency Control
  • Le « noyau » de PostgreSQL
  • Garantit les propriétés ACID
  • Permet les accès concurrents sur la même table
    • une lecture ne bloque pas une écriture
    • une écriture ne bloque pas une lecture
    • une écriture ne bloque pas les autres écritures…
    • …sauf pour la mise à jour de la même ligne

Transactions

  • Une transaction = ensemble atomique d’opérations
  • « Tout ou rien »
  • BEGIN obligatoire pour grouper des modifications
  • COMMIT pour valider
    • y compris le DDL
  • Perte des modifications si :
    • ROLLBACK / perte de la connexion / arrêt (brutal ou non) du serveur
  • SAVEPOINT pour sauvegarde des modifications d’une transaction à un instant t
  • Pas de transactions imbriquées

Niveaux d’isolation

  • 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
  • Nous pouvons spécifier le niveau d’isolation au démarrage d’une transaction
    • BEGIN ISOLATION LEVEL xxx;
  • Niveaux d’isolation supportés
    • read commited (défaut)
    • repeatable read
    • serializable

Fiabilité : journaux de transactions

  • Write Ahead Logs (WAL)
  • Chaque donnée est écrite 2 fois sur le disque !
  • Sécurité quasiment infaillible
  • Avantages :
    • WAL : écriture séquentielle
    • un seul sync sur le WAL
    • fichiers de données : en asynchrone
    • sauvegarde PITR et de la réplication fiables

Sauvegardes

  • Sauvegarde des fichiers à froid
    • outils système
  • Import/Export logique
    • pg_dump, pg_dumpall, pg_restore
  • Sauvegarde physique à chaud
    • pg_basebackup
    • sauvegarde PITR

Réplication

  • Réplication physique
    • instance complète
    • même architecture
  • Réplication logique (PG 10+)
    • table par table / colonne par colonne avec ou sans filtre (PG 15)
    • voire opération par opération
  • Asynchrones ou synchrone
  • Asymétriques

Extensibilité

  • Extensions
    • CREATE EXTENSION monextension ;
    • nombreuses : contrib, packagées… selon provenance
    • notion de confiance (v13+)
    • dont langages de procédures stockées !
  • Système des hooks
  • Background workers

Sécurité

  • Fichier pg_hba.conf
  • Filtrage IP
  • Authentification interne (MD5, SCRAM-SHA-256)
  • Authentification externe (identd, LDAP, Kerberos…)
  • Support natif de SSL

Objets SQL

  • Instances
  • Objets globaux :
    • Bases
    • Rôles
    • Tablespaces
  • Objets locaux :
    • Schémas
    • Tables
    • Vues
    • Index
    • Routines

Organisation logique

Organisation logique d’une instance

Instances

  • Une instance
    • un répertoire de données
    • un port TCP
    • une configuration
    • plusieurs bases de données
  • Plusieurs instances possibles sur un serveur

Rôles

  • Utilisateurs / Groupes
    • Utilisateur : Permet de se connecter
  • Différents attributs et droits

Tablespaces

  • Répertoire physique contenant les fichiers de données de l’instance
  • Une base peut
    • se trouver sur un seul tablespace
    • être répartie sur plusieurs tablespaces
  • Permet de gérer l’espace disque et les performances
  • Pas de quota

Bases

  • Conteneur hermétique
  • Un rôle ne se connecte pas à une instance
    • il se connecte forcément à une base
  • Une fois connecté, il ne voit que les objets de cette base
    • contournement : foreign data wrappers, dblink

Schémas

  • Espace de noms
  • Sous-ensemble de la base
  • Non lié à un utilisateur
  • Résolution des objets : search_path
  • pg_catalog, information_schema
    • pour catalogues système (lecture seule !)

Tables

Par défaut, une table est :

  • Permanente
    • si temporaire, vivra le temps de la session (ou de la transaction)
  • Journalisée
    • si unlogged, perdue en cas de crash, pas de réplication
  • Non partitionnée
    • partitionnement possible par intervalle, valeur ou hachage

Vues

  • Masquer la complexité
    • structure : interface cohérente vers les données, même si les tables évoluent
    • sécurité : contrôler l’accès aux données de manière sélective
  • Vues matérialisées
    • à rafraîchir à une certaine fréquence

Index

  • Algorithmes supportés
    • B-tree (par défaut)
    • Hash
    • GiST / SP-GiST
    • GIN
    • BRIN (version 9.5)
    • Bloom (version 9.6)
  • Type
    • Mono ou multi-colonnes
    • Partiel
    • Fonctionnel
    • Couvrant

Types de données

  • Types de base
    • natif : int, float
    • standard SQL : numeric, char, varchar, date, time, timestamp, bool
  • Type complexe
    • tableau
    • JSON (jsonb), XML
    • vecteur (données LLM, FTS)
  • Types métier
    • réseau, géométrique, etc.
  • Types créés par les utilisateurs
    • structure SQL, C, Domaine, Enum

Contraintes

  • CHECK
    • prix > 0
  • NOT NULL
    • id_client NOT NULL
  • Unicité
    • id_client UNIQUE
  • Clés primaires
    • UNIQUE NOT NULL ==> PRIMARY KEY (id_client)
  • Clés étrangères
    • produit_id REFERENCES produits(id_produit)
  • EXCLUDE
    • EXCLUDE USING gist (room WITH =, during WITH &&)

Colonnes à valeur générée

  • Valeur calculée à l’insertion
  • DEFAULT
  • Identité
    • GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  • Expression
    • GENERATED ALWAYS AS ( generation_expr ) STORED

Langages

  • Procédures & fonctions en différents langages
  • Par défaut : SQL, C et PL/pgSQL
  • Extensions officielles : Perl, Python
  • Mais aussi Java, Ruby, Javascript…
  • Intérêts : fonctionnalités, performances

Fonctions & procédures

  • Fonction
    • renvoie une ou plusieurs valeurs
    • SETOF ou TABLE pour plusieurs lignes
  • Procédure (v11+)
    • ne renvoie rien
    • peut gérer le transactionnel dans certains cas

Opérateurs

  • Dépend d’un ou deux types de données
  • Utilise une fonction prédéfinie :
CREATE OPERATOR //
  (FUNCTION=division0,
  LEFTARG=integer,
  RIGHTARG=integer);

Triggers

  • Opérations : INSERT, UPDATE, DELETE, TRUNCATE
  • Trigger sur :
    • une colonne, et/ou avec condition
    • une vue
    • DDL
  • Tables de transition
  • Effet sur :
    • l’ensemble de la requête (FOR STATEMENT)
    • chaque ligne impactée (FOR EACH ROW)
  • N’importe quel langage supporté

Questions

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

Quiz