• Qu’est-ce qu’un index ?
• Comment indexer une base ?
• Les index B-tree dans PostgreSQL

• Comprendre ce qu’est un index
• Maîtriser le processus de création d’index
• Connaître les différents types d’index B-tree et leurs cas d’usages

• Uniquement destinés à l’optimisation
• À gérer d’abord par le développeur
  • Markus Winand : SQL Performance Explained

• Un index permet de :
  • trouver un enregistrement dans une table directement
  • récupérer une série d’enregistrements dans une table
  • voire tout récupérer dans l’index (Index Only Scan)
• Un index facilite :
  • certains tris
  • certains agrégats
• Obligatoires et automatique pour clés primaires & unicité
  • conseillé pour clés étrangères (FK)

Un index améliore les SELECT

• Sans index :

=# SELECT * FROM test WHERE id = 10000;
Temps : 1760,017 ms

• Avec index :

=# CREATE INDEX idx_test_id ON test (id);

=# SELECT * FROM test WHERE id = 10000;
Temps : 27,711 ms

• L’index n’est pas gratuit !
• Ralentit les écritures
  • maintenance
• Place disque
• Compromis à trouver

• Lourd…

-- bloque les écritures !
CREATE INDEX ON matable ( macolonne ) ;
-- ne bloque pas, peut échouer
CREATE INDEX CONCURRENTLY ON matable ( macolonne ) ;

• Si fragmentation :

REINDEX INDEX nomindex ;
REINDEX TABLE CONCURRENTLY nomtable ;

• Paramètres :
  • maintenance_work_mem (sinon : fichier temporaire !)
  • max_parallel_maintenance_workers

• Défaut : B-tree classique (équilibré)
• UNIQUE (préférer la contrainte)
• Mais aussi multicolonne, fonctionnel, partiel, couvrant
• Index spécialisés : hash, GiST, GIN, BRIN, HNSW….

• Structure associant des clés (termes) à des localisations (pages)
• Structure de données spécialisée, plusieurs types
• Séparée de la table
• Analogies :
  • fiches en carton des bibliothèques avant l’informatique (B-tree)
  • index d’un livre technique (GIN)

• Un index ne résout pas tout
• Importance de la conception du schéma de données
• Importance de l’écriture de requêtes SQL correctes

• Type d’index le plus courant
  • et le plus simple
• Utilisable pour les contraintes d’unicité
• Supporte les opérateurs : <, <=, =, >=, >
• Supporte le tri
• Ne peut pas indexer des colonnes de plus de 2,6 ko

SELECT name FROM ma_table WHERE id = 22

• Possibilité d’indexer plusieurs colonnes :

    CREATE INDEX ON ma_table (id, name) ;

• Ordre des colonnes primordial
  • accès direct aux premières colonnes de l’index
  • pour les autres, PostgreSQL lira tout l’index ou ignorera l’index

• Index Scan
• Bitmap Scan
• Index Only Scan
  • idéal pour les performances
• et les variantes parallélisées

• On indexe pour une requête
  • ou idéalement une collection de requêtes
• Et pas « une table »

• Identifier les requêtes nécessitant un index
• Créer les index permettant de répondre à ces requêtes
• Valider le fonctionnement, en rejouant la requête avec :

     EXPLAIN (ANALYZE, BUFFERS)

• Indexation des colonnes faisant référence à une autre
• Performances des DML
• Performances des jointures

C’est souvent tout à fait normal

• Utiliser l’index est-il rentable ?
• La requête est-elle compatible ?
• Bug de l’optimiseur : rare

• L’optimiseur pense qu’il n’est pas rentable
  • sélectivité trop faible
  • meilleur chemin pour remplir d’autres critères
  • index redondant
  • Index Only Scan nécessite un VACUUM fréquent
• Les estimations de volumétries doivent être assez bonnes !
  • statistiques récentes, précises

• Pas le bon type (CAST plus ou moins explicite)

EXPLAIN SELECT * FROM clients WHERE client_id = 3::numeric;

• Utilisation de fonctions, comme :

SELECT * FROM ma_table WHERE to_char(ma_date, 'YYYY')='2014' ;

SELECT * FROM fournisseurs WHERE commentaire LIKE 'ipsum%';

• Solution :

CREATE INDEX idx1 ON ma_table (col_varchar varchar_pattern_ops) ;

• CREATE INDEX … CONCURRENTLY peut échouer

De nombreuses possibilités d’indexation avancée :

• Index partiels
• Index fonctionnels
• Index couvrants
• Classes d’opérateur

• N’indexe qu’une partie des données :

CREATE INDEX on evenements (type) WHERE  traite IS FALSE ;

• Ne sert que si la clause est logiquement équivalente !
  • ou partie de la clause (inégalités, IN)
• Intérêt : index beaucoup plus petit

• Données chaudes et froides
• Index dédié à une requête avec une condition fixe

• Éviter les index de type :

CREATE INDEX ON matable ( champ_filtre ) WHERE champ_filtre = …

• Préférer :

CREATE INDEX ON matable ( champ_resultat ) WHERE champ_filtre = …

• Un index sur a est inutilisable pour :

  SELECT … WHERE upper(a)='DUPOND'

• Indexer le résultat de la fonction :

   CREATE INDEX mon_idx ON ma_table (upper(a)) ;

• Critère identique à la fonction dans l’index
• Fonction impérativement IMMUTABLE !
  • délicat avec les conversions de dates/heures
• Ne pas espérer d’Index Only Scan

• Ne pas oublier ANALYZE après création d’un index fonctionnel
  • les statistiques peuvent même être l’intérêt majeur (<v14)
• La fonction ne doit jamais tomber en erreur
• Si modification de la fonction
  • réindexation

Les index couvrants :

• répondent à la clause WHERE
• puis ajoutent toutes les colonnes pour la requête :

SELECT client_id FROM clients WHERE type_client = 'A' ;

CREATE INDEX ON clients ( type_client, client_id ) ;

• But : obtenir un Index Only Scan

• D’abord les critères, puis les champs
• L’index devient souvent trop gros
• VACUUM fréquent nécessaire !
• Pas tous les types d’index

• Un index utilise des opérateurs de comparaison
• Texte : différentes collations = différents tris… complexes
  • Index inutilisable sur :

  WHERE col_varchar LIKE 'chaine%'

• Solution : opérateur varchar_pattern_ops :
  • force le tri caractère par caractère, sans la collation

  CREATE INDEX idx1
  ON ma_table (col_varchar varchar_pattern_ops)

• Plus généralement :
  • nombreux autres opérateurs pour d’autres types d’index

• Responsabilité de l’indexation
• Compréhension des mécanismes
• Différents types d’index, différentes stratégies

https://dali.bo/j4_quiz

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

• Index B-tree
  • multicolonnes
  • fonctionnels
  • partiels
  • couvrants
• Classes d’opérateurs
• Indexation GIN, GiST, BRIN, hash, bloom
• Indexation de motifs
• Indexation multicolonne

• Index B-tree fonctionnel, multicolonne, partiel, couvrant :
  • requête cible :

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

  • index dédié :

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

• Rappel : un index est coûteux à maintenir !

Un autre type d’index

• Définition
• Utilisation avec des données non structurées
• Utilisation avec des données scalaires
• Mise à jour

GIN : Generalized Inverted iNdex

• Index inversé généralisé
  • les champs sont décomposés en éléments (par API)
  • l’index associe une valeur à la liste de ses adresses
• Pour chaque entrée du tableau
  • liste d’adresses où le trouver
• Utilisation principale : données non scalaires
  • tableaux, listes, non structurées…

Quels tableaux contiennent une valeur donnée ?

• Opérateurs : @>, <@, =, &&, ?, ?|, ?&

SELECT * FROM matable WHERE a @> ARRAY[42] ;

CREATE INDEX ON tablo USING gin (a);

• Champs concaténés : transformer en tableaux

SELECT * FROM voitures
WHERE regexp_split_to_array(caracteristiques,',')
          @> '{"toit ouvrant","climatisation"}'  ;

CREATE INDEX idx_attributs_array ON voitures
USING gin ( regexp_split_to_array(caracteristiques,',') ) ;

• JSON :
  • opérateur jsonb_path_ops ou jsonb_ops
• Indexation de trigrammes
  • extensions pg_trgm (opérateur dédié gin_trgm_ops)
• Recherche Full Text
  • indexe les vecteurs

• Données scalaires aussi possibles
  • avec l’extension btree_gin
• GIN compresse quand les données se répètent
  • alternative à bitmap !

• Création lourde
  • maintenance_work_mem élevé
• Mise à jour lente
  • d’où l’option fastupdate
  • … à désactiver si temps de réponse instable !

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 que B-tree (moins sélectifs)
• Moins lourds que GIN

Le GiST indexe à peu près n’importe quoi

• géométries (PostGIS)
• intervalles, adresses IP, FTS…

D’autres usages recouvrent en partie ceux de GIN.

• KNN = K-Nearest neighbours (K-plus proches voisins)
  • c’est-à-dire :

  SELECT …
  ORDER BY ma_colonne <-> une_référence LIMIT 10 ;

• Très utile pour la recherche de mots ressemblants, géographique
  • Exemple :

  SELECT   p, p <-> point(18,36)
  FROM     mes_points
  ORDER BY p <-> point(18, 36)
  LIMIT    4 ;

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

  • Exemple :

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

  • Exemple : réservations de salles

• Indexation des recherches LIKE '%critère%'

• Similarité basée sur des trigrammes

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

 similarity
------------
   0.333333

• Indexation (GIN ou GiST) :

CREATE INDEX test_trgm_idx ON test_trgm
  USING gist (text_data gist_trgm_ops);

• Multicolonne dans n’importe quel ordre
• GIN ou GiST
  • extensions btree_gist ou btree_gin
• Ou bloom ?
• Quel est le meilleur ?
  • ça dépend…

BRIN : Block Range INdex

CREATE INDEX brin_demo_brin_idx ON brin_demo USING brin (age)
WITH (pages_per_range=16) ;

• Valeurs corrélées à leur emplacement physique
• Calcule des plages de valeur par groupe de blocs
  • index très compact
• Pour :
  • grosses volumétries
  • corrélation entre emplacement et valeur
  • retrier la table avec CLUSTER ?

• Basés sur un hash
• Tous types de données, quelle que soit la taille
• Ne gèrent que =
  • donc ni <, >, != …
• Mais plus compacts

• Pour identifier des requêtes
• Pour identifier des prédicats et des requêtes liées
• Pour valider un index

• pgBadger
• pg_stat_statements
• PoWA

• Extension pg_qualstats
  • avec PoWa

• Extension PostgreSQL
• Création d’index hypothétiques pour tester leur intérêt
  • avant de les créer pour de vrai
• Limitations : surtout B-Tree, statistiques <!– Masqué jusqu’au le jour où hypoPG 2 sortira
• Partitions hypothétiques (pour la v2, actuellement en beta) –>

• PoWA peut utiliser HypoPG

https://dali.bo/j5_quiz

Ce module présente des extensions plus spécifiquement destinées à améliorer les performances.

• Indexation des recherches LIKE '%critère%'

• Similarité basée sur des trigrammes

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

 similarity
------------
   0.333333

• Indexation (GIN ou GiST) :

CREATE INDEX test_trgm_idx ON test_trgm
  USING gist (text_data gist_trgm_ops);

Capture en temps réel des requêtes

• Normalisation
• Indicateurs :
  • nombre d’exécutions,
  • nombre d’enregistrements retournés
  • temps cumulé d’exécution et d’optimisation
  • lectures/écritures en cache, demandées au système, tris
  • temps de lecture/écriture (track_io_timing)
  • écritures dans les journaux de transactions
  • temps de planning (désactivé par défaut)
  • utilisation du JIT (v15)

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements ; -- dans 1 ou plusieurs bases
SELECT * FROM pg_stat_statements ;

• Vue en mémoire partagée (volumétrie contrôlée)
• Pas d’échantillonnage, seulement des compteurs cumulés
  • pg_stat_statements_reset() ou PoWA

Requêtes les plus longues en temps cumulé :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.total_exec_time DESC
 LIMIT 10 ;

Requêtes les plus fréquemment appelées :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls  AS avg_time, s.query
  FROM pg_stat_statements s
  JOIN pg_roles r     ON (s.userid=r.oid)
  JOIN pg_database d  ON (s.dbid = d.oid)
 ORDER BY s.calls DESC
 LIMIT 10;

Requêtes les plus consommatrices et hit ratio :

SELECT calls, total_exec_time, rows,
       100.0*shared_blks_hit
       /nullif(shared_blks_hit+shared_blks_read, 0) AS "hit %",
       query
FROM  pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5 ;

• Tracer les plans des requêtes lentes automatiquement
• Contrib officielle
• Mise en place globale (traces) :
  • globale :

  shared_preload_libraries='auto_explain'   -- redémarrage !

  ALTER DATABASE erp SET auto_explain.log_min_duration = '3s' ;

  • session :

  LOAD 'auto_explain' ;
  SET auto_explain.log_analyze TO true;

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

Fournit une vue :

• Pour chaque bloc de l’instance
  • fichier (donc objet) associé, base
  • fork (0 : table, 1 : FSM, 2 : VM)
  • numéro de bloc
  • isdirty
  • usagecount (0-5)
• Pour : utilisation du cache, hot blocks
• pg_buffercache_evict() (v17+)

• Charge des blocs en cache :

-- cache de PG
SELECT pg_prewarm ('pgbench_accounts', 'buffer') ;

-- cache de Linux (asynchrone)
SELECT pg_prewarm ('pgbench_accounts', 'prefetch') ;
-- cache (tous OS)
SELECT pg_prewarm ('pgbench_accounts', 'read') ;

• Ne pas oublier les index !
• N’interdit pas l’éviction
• Récupération du cache au redémarrage (v11)
  • avec un petit paramétrage

• 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

• PL/pgSQL plus performant pour l’accès aux données
• Chaque langage a son point fort
• Performances :
  • latence (pas d’allers-retours)
  • accès aux données depuis les fonctions
  • bibliothèques de chaque langage
  • index
• Langages trusted / untrusted

• COUNT(DISTINCT) est notoirement lent
• Principe d’HyperLogLog :
  • travail sur des hachages, avec perte
  • estimation statistique
  • non exact, mais beaucoup plus rapide
• Exemple :

SELECT mois, hll_cardinality(hll_add_agg(hll_hash_text( id )))
FROM voyages ;

• Type hll pour pré-agréger des données

https://dali.bo/x2_quiz

• Principes et intérêts
• Historique
• Les différents types
  • applicatif
  • par héritage (historique)
  • déclaratif (à favoriser)

• Faciliter la maintenance de gros volumes
  • VACUUM (FULL), réindexation, déplacements, sauvegarde logique…
• Gain en performance
  • parcours complet sur de plus petites tables
  • statistiques par partition plus précises
  • purge par partitions entières
  • pg_dump parallélisable
  • tablespaces différents (données froides/chaudes)
• Attention à la maintenance sur le code

…ou la réinvention de la roue

• Gestion au niveau applicatif, table par table
• Complexité pour le développeur
• Intégrité des données ?

Historique, ou pour un cas très spécifique

CREATE TABLE mammiferes (pattes int)   INHERITS (animaux) ;
CREATE TABLE primates (debout boolean) INHERITS (mammiferes) ;

• Table mère :
  • définie normalement, peut contenir des lignes
• Tables filles :
  • héritent des propriétés de la table mère
  • …mais pas des contraintes, index et droits
  • colonnes supplémentaires possibles
• Insertion applicative ou par trigger (lent !)

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

• Liste de valeurs par partition

  • statut, client, pays, année ou mois…

• Clé de partitionnement forcément mono-colonne

• Syntaxe :

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1) ;

CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);
…

• Clé de partitionnement mono- ou multicolonne
  • dates, id…
• Bornes :
  • supérieure exclue
  • MINVALUE / MAXVALUE pour infinis
• Syntaxe :

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

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

Pour une répartition uniforme des données :

• Hachage de valeurs par partition

  • indiquer un modulo et un reste

• Clé de partitionnement mono- ou multicolonnes

• Syntaxe :

CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);

CREATE TABLE t3_a PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 0);
CREATE TABLE t3_b PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 1);
CREATE TABLE t3_c PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 2);

• Clé sur plusieurs colonnes :
  • si partitionnement par intervalle ou hash (pas par liste)
  • et si 1er champ toujours présent
• Syntaxe :

CREATE TABLE t1(c1 integer, c2 text, c3 date)
PARTITION BY RANGE (c1, c3) ;

CREATE TABLE t1_a PARTITION OF t1
FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11') ;
…

S’il y a deux chemins d’accès privilégiés :

CREATE TABLE objets (id int, statut int, annee int, t text)
 PARTITION BY LIST (statut) ;

CREATE TABLE objets_123
 PARTITION OF objets FOR VALUES IN (1, 2, 3)
 PARTITION BY LIST (annee) ;

CREATE TABLE objets_123_2023
 PARTITION OF objets_123 FOR VALUES IN (2023) ;
CREATE TABLE objets_123_2024
 PARTITION OF objets_123 FOR VALUES IN (2024) ;

CREATE TABLE objets_45
 PARTITION OF objets FOR VALUES IN (4,5) ;

• Plus souple que le partitionnement multicolonne

• Pour le partitionnement par liste ou par intervalle
• Toutes les données n’allant pas dans les partitions définies iront dans la partition par défaut

CREATE TABLE t2_autres PARTITION OF t2 DEFAULT ;

• La conserver petite

ALTER TABLE … ATTACH PARTITION … FOR VALUES … ;

• La table doit préexister
• Vérification du respect de la contrainte par les données existantes
  • parcours complet de la table : lent !
  • …sauf si contrainte CHECK identique déjà ajoutée
• Si la partition par défaut a des données qui iraient dans cette partition :
  • erreur à l’ajout de la nouvelle partition
  • détacher la partition par défaut
  • ajouter la nouvelle partition
  • déplacer les données de l’ancienne partition par défaut
  • ré-attacher la partition par défaut

ALTER TABLE … DETACH PARTITION …

• Simple et rapide
• Mais nécessite un verrou exclusif
  • option CONCURRENTLY (v14+)

• Si pas de conflit entre partitions :

BEGIN ;
  ALTER TABLE … DETACH PARTITION … CONCURRENTLY ;
  ALTER TABLE … ATTACH PARTITION … FOR <nouveau critère> ;
END ;

• Sinon : déplacement de données manuel

• Sous psql : \dP
• pg_partition_tree ('logs') : liste entière des partitions
• pg_partition_root ('logs_2019') : racine d’une partition
• pg_partition_ancestors ('logs_201901') : parents d’une partition

La clé primaire doit contenir toutes les colonnes de la clé de partitionnement.

• Idem pour une contrainte unique
• Idem pour les clés étrangères vers la table partitionnée
• Pas un problème si on partitionne selon la clé
• Plus gênant si partitionnement par date, statut…
• Relâcher l’intégrité ? Séparer en deux tables ? UUID v7 ?

• Propagation automatique
• Index supplémentaires par partition possibles
• Clés étrangères entre tables partitionnées

• Mettre la clé dans la requête autant que possible
• ou : cibler les partitions directement
• Temps de planification
  • nombre de tables, d’index, leurs statistisques…
  • max ~ 100 partitions
• À activer ?
  • enable_partitionwise_aggregate
  • enable_partitionwise_join

• Changement de tablespace
• autovacuum/analyze
  • sur les partitions comme sur toute table
• VACUUM, VACUUM FULL, ANALYZE
  • sur table mère : redescendent sur les partitions
• REINDEX
  • avant v14 : uniquement par partition
• ANALYZE
  • prévoir aussi sur la table mère (manuellement…)

Sauvegarde physique : peu de différence

Avec pg_dump :

• --jobs : efficace
• --load-via-partition-root
• exclusion de partitions (v16+) :
  • --table-and-children
  • --exclude-table-and-children
  • --exclude-table-data-and-children

• Pas de création automatique des partitions
  • ni fusion/scission facile
• Planification : 100 partitions max conseillé
• Pas d’héritage multiple, schéma fixe
  • sauf ajout d’index/contraintes sur chaque partition
• Partitions distantes : sans propagation d’index
• Clé de partitionnement calculée : pas vraiment possible
• (≤v16) IDENTITY : uniquement sur la table partitionnée
• PostgreSQL récent toujours conseillé

• Tables distantes comme partitions : sharding
• (v14+) Interrogation simultanée asynchrone

• Extension pg_partman
  • automatisation
• Extensions dédiées à un domaine :
  • timescaledb
  • citus

Le partitionnement déclaratif a de gros avantages pour le DBA

• …mais les développeurs doivent savoir l’utiliser
• Préférer une version récente de PostgreSQL

https://dali.bo/v1_quiz

PostgreSQL offre des types avancés :

• UUID
• Tableaux
• Composés :
  • hstore
  • JSON : json, jsonb
  • XML
• Pour les objets binaires :
  • bytea
  • Large Objects

• Ex: d67572bf-5d8c-47a7-9457-a9ddce259f05
• Un identifiant universellement unique sur 128 bits
• Type : uuid
• Avec des inconvénients…

• Faciles à générer
  • gen_random_uuid() et d’autres
• Pouvoir désigner des entités arbitrairement
• Pouvoir exporter des données de sources différentes
  • au contraire des séquences traditionnelles
• Pouvoir fusionner des bases
• Pour toutes les clés primaires ?

• Lisibilité
• Temps de génération (mineur)
• Taille
  • 16 octets…
• Surtout : fragmentation des index
  • mauvais pour le cache
  • sauf UUID v7 (tout récent)

• uuid :
  • type simple (16 octets)
  • garantit le format
• Génération :
  • gen_random_uuid (v4, aléatoire)
  • extension uuid-ossp (v1,3,4,5)
  • extension (pg_idkit…) ou fonction en SQL (v7)

• Si vous avez besoin des UUID, préférez la version 7.
• Les séquences habituelles restent recommandables en interne.

• Collection ordonnée d’un même type
• Types integer[], text[], etc.

SELECT ARRAY [1,2,3] ;

SELECT '{1,2,3}'::integer[] ;

-- lignes vers tableau
SELECT array_agg (i) FROM generate_series (1,3) i ;

-- tableau vers lignes
SELECT unnest ( '{1,2,3}'::integer[] ) ;

CREATE TABLE demotab ( id int, liste integer[] ) ;

Quel intérêt par rapport à 1 valeur par ligne ?

• Allège certains modèles en réduisant les jointures
  • ex : champ avec des n°s de téléphone
  • si pas de contraintes
• Grosse économie en place (time series)
  • 24 octets par ligne
  • et parfois mécanisme TOAST
• Mais…
  • mise à jour compliquée/lente
  • indexation moins simple

• B-tree inutilisable
• GIN plus adapté pour recherche d’une valeur
  • opérateurs && , @>
  • mais plus lourd

• Un champ = plusieurs attributs
• De loin préférable à une table Entité/Attribut/Valeur
• Uniquement si le modèle relationnel n’est pas assez souple
• 3 types dans PostgreSQL :
  • hstore : clé/valeur
  • json : JSON, stockage texte, validation syntaxique, fonctions d’extraction
  • jsonb : JSON, stockage binaire, accès rapide, fonctions d’extraction, de requêtage, indexation avancée

Stocker des données non structurées

• Extension
• Stockage Clé/Valeur, uniquement texte
• Binaire
• Indexable
• Plusieurs opérateurs disponibles

{
  "firstName": "Jean",
  "lastName": "Dupont",
  "age": 27,
  "address": {
    "streetAddress": "43 rue du Faubourg Montmartre",
    "city": "Paris",
    "postalCode": "75009"
  }
}

• json : format texte
• jsonb : format binaire, à préférer
• jsonpath : SQL/JSON paths (requêtage)

• Type texte
  • avec validation du format
• Réservé au stockage à l’identique
• Préférer jsonb

• JSON au format Binaire
• Indexation GIN
• Langage JSONPath

SELECT  '{"auteur": "JRR Tolkien","titre":"Le Hobbit"}' IS JSON ;
-- true

SELECT  '{"auteur": "JRR Tolkien","titre":"Le Hobbit}' IS JSON ;
-- false

• Aussi : IS JSON WITH/WITHOUT UNIQUE KEYS, IS JSON ARRAY, IS JSON OBJECT, IS JSON SCALAR
• PostgreSQL 16+

SELECT datas->>'firstName' AS prenom,    -- text
       datas->'address'    AS addr       -- jsonb
FROM personnes ;

SELECT  datas #>> '{address,city}',      -- text
        datas #> '{address,city}'        -- jsonb
FROM personnes ; -- text

SELECT datas['address']['city'] as villes from personnes ;  -- jsonb, v14

SELECT datas['address']->>'city' as villes from personnes ;  -- text, v14

• Attention : pas de contrôle de l’existence de la clé !
  • et attention à la casse

• Manipuler des tableaux :
  • jsonb_array_elements(), jsonb_array_elements_text()
  • jsonb_agg() (de JSON ou de scalaires)
  • jsonb_agg()_strict (sans les NULL)

  SELECT jsonb_array_elements (datas->'phoneNumbers')->>'number'
  FROM   personnes ;

• Construire un ensemble de tuples depuis un objet JSON :
  • jsonb_each()
• Avec des types préexistants :
  • jsonb_populate_record(), jsonb_populate_recordset
• Types définis à la volée :
  • jsonb_to_record(),jsonb_to_recordset()
• Construire un JSON depuis une requête :
  • to_jsonb (<requête>)
• Attention aux types
  • jsonb_typeof()

SELECT * FROM t,
JSON_TABLE (
          t.champ_json ,
          '$ ? (@.age == 42)'
          COLUMNS (age int PATH '$.age',
                    nom text PATH '$.name')) ;

• PostgreSQL 17+
• conversion d’un JSON en vue, avec types
• clause NESTED PATH pour les tableaux dans le JSON
• paramétres : clause PASSING

Inconvénients par rapport à un modèle normalisé :

• Perte d’intégrité (types, contraintes, FK…)
• Complexité du code
• Pas de statistiques sur les clés JSON !
• Pas forcément plus léger en disque
  • clés répétées
• Lire 1 attribut = lire tout le JSON
  • voire accès table TOAST
• Mise à jour : tout ou rien
• Indexation délicate

Sans JSONPath :

SELECT * FROM personnes …

 WHERE datas->>'lastName' = 'Durand';  -- textes

 WHERE datas->'lastName'  = '"Durand"'::jsonb ; -- JSON

 WHERE datas @> '{"nom": "Durand"}'::jsonb ; -- contient

 WHERE datas ? 'spouse' ; -- attibut existe ?

 WHERE datas ?| '{spouse,children}'::text[] ; -- un des champs ?

 WHERE datas ?& '{spouse,children}'::text[] ; -- tous les champs ?

Mais comment indexer ?

• SQL:2016 introduit SQL/JSON et le langage JSONPath
• JSONPath :
  • langage de recherche pour JSON
  • concis, flexible, plus rapide
  • depuis PostgreSQL 12, étendu à chaque version

• Index fonctionnel sur un attribut précis
  • bonus : statistiques

  CREATE INDEX idx_prs_nom ON personnes ((datas->>'lastName')) ;
  ANALYZE personnes ;

• Colonne générée (dénormalisée) :
  • champ normal, indexable, facile à utiliser, rapide à lire
  • statistiques

  ALTER TABLE personnes
  ADD COLUMN lastname text
  GENERATED ALWAYS AS  ((datas->>'lastName')) STORED ;

• Préférer @> et @? que des fonctions

• Indexation « schemaless » grâce au GIN :
  • attention au choix de l’opérateur

  -- opérateur par défaut
  --  pour critères : ?, ?|, ?& , @> , @?, @@)
  CREATE INDEX idx_prs ON personnes USING gin(datas) ;
  -- index plus performant
  --  mais que @> , @?, @@
  CREATE INDEX idx_prs ON personnes USING gin(datas jsonb_path_ops) ;

Lire la documentation

• 8.14 Types JSON :
  • https://docs.postgresql.fr/current/datatype-json.html
• 9.16 Fonctions & opérateurs JSON :
  • https://docs.postgresql.fr/current/functions-json.html

• Type xml
  • stocke un document XML
  • valide sa structure
• Quelques fonctions et opérateurs disponibles :
  • XMLPARSE, XMLSERIALIZE, query_to_xml, xmlagg
  • xpath (XPath 1.0 uniquement)

• Souvent une mauvaise idée…

• 2 méthodes

  • bytea : type binaire
  • Large Objects : manipulation comme un fichier

• Un type comme les autres
  • bytea : tableau d’octets
  • en texte : bytea_output = hex ou escape
• Récupération intégralement en mémoire !
• Toute modification entraîne la réécriture complète du bytea
• Maxi 1 Go (à éviter)
  • en pratique intéressant pour quelques Mo
  • compressé/déporté (TOAST)
• Import :

SELECT pg_read_binary_file ('/chemin/fichier');

• À éviter…
  • préférer bytea
• Maxi 4 To (éviter…)
• Objet indépendant des tables
  • OID à stocker dans les tables
  • se compresse mal
• Suppression manuelle !
  • trigger
  • lo_unlink & vacuumlo
• Fonction de manipulation, modification
  • lo_create, lo_import
  • lo_seek, lo_open, lo_read, lo_write…

https://dali.bo/s22_quiz

Quelques fonctionnalités, généralement liées aux performances.

• Tables temporaires
• Tables non journalisées
• Colonnes générées
• JIT
• Recherche Full Text

CREATE TEMP TABLE  travail (…) ;

• N’existent que pendant la session
• Non journalisées : rapides !

• Ne pas en abuser !
• Ignorées par autovacuum
  • ANALYZE et VACUUM manuels !
• Paramétrage :
  • temp_buffers : cache disque pour les objets temporaires, par session, à augmenter ?

• La durabilité est parfois accessoire :
  • tables temporaires et de travail
  • caches…
• Tables non journalisées
  • non répliquées, non restaurées
  • remises à zéro en cas de crash
• Respecter les contraintes

ALTER TABLE table_normale SET UNLOGGED ;

• réécriture

ALTER TABLE table_unlogged SET LOGGED ;

• passage du contenu dans les WAL !

• DEFAULT
  • expressions très simples, modifiables
• GENERATED
  • fonctions « immutables », ne dépendant que de la ligne
  • danger sinon (ex : pas pour dates de mises à jour)
  • (avant v17) difficilement modifiables
  • peuvent porter des contraintes

• Compilation Just In Time des requêtes
• Utilise le compilateur LLVM
• Vérifier que l’installation est fonctionnelle
• Activé par défaut
  • sauf en v11 ; et absent auparavant

• Tuple deforming
• Évaluation d’expressions :
  • WHERE
  • agrégats, GROUP BY
• Appels de fonctions (inlining)
• Mais pas les jointures

• jit (défaut : on)
• jit_above_cost (défaut : 100 000)
• jit_inline_above_cost (défaut : 500 000)
• jit_optimize_above_cost (défaut : 500 000)
• À comparer au coût de la requête… I/O comprises
• Seuils arbitraires !

• Goulot d’étranglement au niveau CPU (pas I/O)
• Requêtes complexes (calculs, agrégats, appels de fonctions…)
• Beaucoup de lignes, filtres
• Assez longues pour « rentabiliser » le JIT
• Analytiques, pas ERP

…ou FTS

• Recherche « à la Google » ; fonctions dédiées
• 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
  • notamment termes courants
  • permettent une normalisation, des synonymes…

• Décomposition :

SELECT to_tsvector ('french',
                   'Longtemps je me suis couché de bonne heure');

               to_tsvector
-----------------------------------------
 'bon':7 'couch':5 'heur':8 'longtemp':1

• Recherche sur 2 mots :

SELECT * FROM textes
WHERE to_tsvector('french',contenu) @@ to_tsquery('Valjean & Cosette');

• Recherche sur une phrase : phrase_totsquery

• Configurations liées à la langue
  • basées sur des dictionnaires (parfois fournis)
  • dictionnaires filtrants (unaccent)
  • synonymes
• Extensible grâce à des sources extérieures
• Configuration par défaut : default_text_search_config

• Stocker to_tsvector (champtexte)
  • colonne mise à jour par trigger
  • ou colonne générée (v12)
• Indexation GIN ou GiST

• Vectorisation possible des JSON

SELECT info FROM commandes c
WHERE to_tsvector ('french', c.info) @@ to_tsquery('papier') ;

                            info
----------------------------------------------------------------
 {"items": {"qté": 5, "produit": "Rame papier normal A4"},
            "client": "Benoît Delaporte"}
 {"items": {"qté": 5, "produit": "Pochette Papier dessin A3"},
            "client": "Lucie Dumoulin"}

https://dali.bo/s80_quiz