Glossaire Postgres
Les termes PostgreSQL expliqués avec du SQL que vous pouvez exécuter
Chaque entrée propose une courte définition, un exemple qui s’exécute sur un PostgreSQL standard et des liens vers la documentation officielle dont elle s’inspire.
Sécurité au niveau des lignes (RLS)
La sécurité au niveau des lignes permet à une table de déterminer, ligne par ligne, quelles lignes chaque rôle peut voir ou modifier, à l’aide de politiques créées avec CREATE POLICY.
Parcours séquentiel
Un parcours séquentiel lit chaque ligne d’une table du début à la fin. EXPLAIN l’affiche sous le nom Seq Scan ; c’est le choix normal pour les petites tables ou les requêtes qui renvoient beaucoup de lignes.
Parcours d’index
Un parcours d’index utilise un index pour trouver les lignes correspondantes, puis les récupère dans la table. EXPLAIN l’affiche sous le nom Index Scan ; un Index Only Scan peut se passer de la table.
Pool de connexions
Un pool de connexions maintient ouvert un ensemble de connexions à la base de données et les partage entre de nombreux clients, car chaque connexion PostgreSQL est servie par son propre processus serveur.
VACUUM et autovacuum
VACUUM récupère l’espace occupé par les versions de lignes mortes que laissent les mises à jour et les suppressions, met à jour la carte de visibilité et empêche le rebouclage des identifiants de transaction (wraparound). Autovacuum l’exécute à votre place.
Contrôle de concurrence multiversion (MVCC)
Le MVCC permet aux lectures et aux écritures de s’exécuter en même temps : chaque instruction voit un instantané des données, et les mises à jour écrivent de nouvelles versions de lignes au lieu d’écraser les lignes en place.
Journal des transactions (WAL)
Le journal des transactions enregistre chaque modification avant qu’elle n’atteigne les fichiers de données, ce qui permet à PostgreSQL de récupérer après un plantage. Le WAL alimente aussi la réplication et la restauration à un instant donné.
EXPLAIN et EXPLAIN ANALYZE
EXPLAIN montre comment PostgreSQL prévoit d’exécuter une requête ; EXPLAIN ANALYZE l’exécute en plus et indique le nombre réel de lignes et la durée de chaque étape.
Clé primaire
Une clé primaire est une colonne ou un ensemble de colonnes qui identifie de manière unique chaque ligne. PostgreSQL l’impose avec un index B-tree unique et NOT NULL.
Clé étrangère
Une clé étrangère exige que les valeurs d’une table correspondent à une ligne d’une autre table : vous ne pouvez donc pas pointer vers une ligne qui n’existe pas. ON DELETE détermine ce qui se passe lors de la suppression de cette ligne.
Niveaux d’isolation des transactions
Le niveau d’isolation détermine ce qu’une transaction voit des modifications des transactions concurrentes. PostgreSQL utilise Read Committed par défaut et propose aussi Repeatable Read et Serializable.
Interblocage (deadlock)
Un interblocage se produit lorsque deux transactions attendent chacune un verrou détenu par l’autre. PostgreSQL le détecte et annule l’une d’elles ; prendre les verrous dans un ordre cohérent permet de l’éviter.
Index B-tree
Le B-tree est le type d’index par défaut de PostgreSQL. Il gère les comparaisons d’égalité et d’intervalle sur des données triables et peut renvoyer les lignes déjà triées.
Index GIN
Un index GIN (index inversé généralisé) associe chaque élément contenu dans une valeur, comme un élément de tableau, une clé JSONB ou un mot de recherche textuelle, aux lignes qui le contiennent.
Index partiel
Un index partiel ne couvre que les lignes qui satisfont une condition WHERE, ce qui le garde petit et rapide lorsque les requêtes ciblent un sous-ensemble bien défini d’une table.
Expression de table commune (CTE)
Une expression de table commune, écrite avec WITH, donne un nom à une sous-requête pour que le reste de l’instruction puisse l’utiliser comme une table. WITH RECURSIVE permet de traiter les arbres et les hiérarchies.
Type JSONB
jsonb stocke le JSON dans un format binaire décomposé, rapide à interroger et indexable. C’est le type JSON que la plupart des applications devraient choisir.
Vue matérialisée
Une vue matérialisée stocke le résultat d’une requête pour qu’il puisse être lu comme une table. Les lectures sont rapides, mais les données ne changent que lorsque vous exécutez REFRESH MATERIALIZED VIEW.
Partitionnement de tables
Le partitionnement divise une table logique en tables physiques plus petites, par intervalle, par liste ou par hachage, afin que les requêtes et la maintenance ne touchent que les partitions nécessaires.
Upsert avec INSERT ... ON CONFLICT
INSERT ... ON CONFLICT insère une ligne ou, si cela violait une contrainte d’unicité, met à jour la ligne existante ou l’ignore, en une seule étape atomique.
Fonctions de fenêtrage
Une fonction de fenêtrage calcule une valeur sur un ensemble de lignes liées, comme un rang ou un total cumulé, sans regrouper ces lignes en une seule comme le fait GROUP BY.
Schéma
Un schéma est un espace de noms au sein d’une base de données qui contient des tables, des vues, des fonctions et d’autres objets. search_path détermine où les noms non qualifiés sont recherchés.
Rôles et privilèges
Dans PostgreSQL, les utilisateurs comme les groupes sont des rôles. GRANT accorde à un rôle des privilèges sur des objets, et un rôle membre d’un autre rôle peut exercer les privilèges de celui-ci.
Colonne d’identité
Une colonne d’identité reçoit automatiquement ses valeurs d’une séquence implicite. C’est la manière conforme au standard SQL de créer une clé auto-incrémentée dans PostgreSQL.
Recherche plein texte
La recherche plein texte fait correspondre les documents par mots plutôt que par sous-chaînes exactes, à l’aide de tsvector, de tsquery et de l’opérateur @@. Un index GIN la rend rapide.