Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Principe :

Sous PostgreSQL, les tables temporaires sont créées dans une session, et disparaissent à la déconnexion. Elles ne sont pas visibles par les autres sessions. Elles ne sont pas journalisées, ce qui est très intéressant pour les performances. Elles s’utilisent comme les autres tables, y compris pour l’indexation, les triggers, etc.

Les tables temporaires semblent donc idéales pour des tables de travail temporaires et « jetables ».

Aspect technique :

Les tables temporaires sont créées dans un schéma temporaire pg_temp_…, ce qui explique qu’elles ne sont pas visibles dans le schéma public.

Physiquement, par défaut, elles sont stockées sur le disque avec les autres données de la base, et non dans base/pgsql_tmp comme les fichiers temporaires. Il est possible de définir des tablespaces dédiés aux objets temporaires (fichiers temporaires et données des tables temporaires) à l’aide du paramètre temp_tablespaces, à condition de donner des droits CREATE dessus aux utilisateurs. Le nom du fichier d’une table temporaire est reconnaissable car il commence par t. Les éventuels index de la table suivent les même règles.

Exemple :

CREATE TEMP TABLE travail (x int PRIMARY KEY) ;

EXPLAIN (COSTS OFF, ANALYZE, BUFFERS, WAL)
INSERT INTO travail SELECT i FROM generate_series (1,1000000) i ;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Insert on travail (actual time=1025.752..1025.753 rows=0 loops=1)
   Buffers: shared hit=13, local hit=2172174 read=4 dirtied=7170 written=10246
   I/O Timings: read=0.012
   ->  Function Scan on generate_series i (actual time=77.112..135.624 rows=1000000 loops=1)
 Planning Time: 0.028 ms
 Execution Time: 1034.984 ms

SELECT pg_relation_filepath ('travail') ;

 pg_relation_filepath
-----------------------
 base/13746/t7_5148873

\d pg_temp_7.travail

                 Table « pg_temp_7.travail »
 Colonne |  Type   | Collationnement | NULL-able | Par défaut
---------+---------+-----------------+-----------+------------
 x       | integer |                 | not null  |
Index :
    "travail_pkey" PRIMARY KEY, btree (x)

Cache :

Dans les plans d’exécution avec BUFFERS, l’habituelle mention shared est remplacée par local pour les tables temporaires. En effet, leur cache disque dédié est au niveau de la session, non des shared buffers. Ce cache est défini par le paramètre temp_buffers (exprimé par session, et à 8 Mo par défaut). Ce paramètre peut être augmenté, avant la création de la table. Bien sûr, on risque de saturer la RAM en cas d’abus ou s’il y a trop de sessions, comme avec work_mem. Ce cache n’empêche pas l’écriture des petites tables temporaires sur le disque.

Pour éviter de recréer perpétuellement la même table temporaire, une table unlogged (voir plus bas) sera sans doute plus indiquée. Le contenu de cette dernière sera aussi visible des autres sessions, ce qui est pratique pour suivre la progression d’un traitement, faciliter le travail de l’autovacuum, ou déboguer. Sinon, il est fréquent de pouvoir remplacer une table temporaire par une CTE (clause WITH) ou un tableau en mémoire.

L’extension pgtt émule un autre type de table temporaire dite « globale » pour la compatibilité avec d’autres SGBD.

Une table unlogged est une table non journalisée. Comme la journalisation est responsable de la durabilité, une table non journalisée n’a pas cette garantie.

La non-journalisation de la table implique aussi que ses données ne sont pas répliquées vers des serveurs secondaires, et que les tables ne peuvent figurer dans une publication (réplication logique). En effet, les modes de réplication natifs de PostgreSQL utilisent les journaux de transactions. Pour la même raison, une restauration de sauvegarde PITR ne restaurera pas le contenu de la table. Le bon côté est qu’on allège la charge sur la sauvegarde et la réplication.

Les contraintes doivent être respectées même si la table unlogged est vidée : une table normale ne peut donc avoir de clé étrangère pointant vers une table unlogged. La contrainte inverse est possible, tout comme une contrainte entre deux tables unlogged.

À part ces limitations, les tables unlogged se comportent exactement comme les autres. Leur intérêt principal est d’être en moyenne 5 fois plus rapides à la mise à jour. Elles sont donc à réserver à des cas d’utilisation particuliers, comme :

• table de spooling/staging ;
• table de cache/session applicative ;
• table de travail partagée entre sessions ;
• table de travail systématiquement reconstruite avant utilisation dans le flux applicatif ;
• et de manière générale toute table contenant des données dont on peut accepter la perte sans impact opérationnel ou dont on peut regénérer aisément les données.

Les tables unlogged ne doivent pas être confondues avec les tables temporaires (non journalisées et visibles uniquement dans la session qui les a créées). Les tables unlogged ne sont pas ignorées par l’autovacuum (les tables temporaires le sont). Abuser des tables temporaires a tendance à générer de la fragmentation dans les tables système, alors que les tables unlogged sont en général créées une fois pour toutes.

Une table unlogged se crée exactement comme une table journalisée classique, excepté qu’on rajoute le mot UNLOGGED dans la création.

Il est possible de basculer une table à volonté de normale à unlogged et vice-versa.

Quand une table devient unlogged, on pourrait imaginer que PostgreSQL n’a rien besoin d’écrire. Malheureusement, pour des raisons techniques, la table doit tout de même être réécrite. Elle est défragmentée au passage, comme lors d’un VACUUM FULL. Ce peut être long pour une grosse table, et il faudra voir si le gain par la suite le justifie.

Les écritures dans les journaux à ce moment sont théoriquement inutiles, mais là encore des optimisations manquent et il se peut que de nombreux journaux soient écrits si les sommes de contrôles ou wal_log_hints sont activés. Par contre il n’y aura plus d’écritures dans les journaux lors des modifications de cette table, ce qui reste l’intérêt majeur.

Quand une table unlogged devient logged (journalisée), la réécriture a aussi lieu, et tout le contenu de la table est journalisé (c’est indispensable pour la sauvegarde PITR et pour la réplication notamment), ce qui génère énormément de journaux et peut prendre du temps.

Par exemple, une table modifiée de manière répétée pendant un batch, peut être définie unlogged pour des raisons de performance, puis basculée en logged en fin de traitement pour pérenniser son contenu.

Une des nouveautés les plus visibles et techniquement pointues de la v11 est la « compilation à la volée » (Just In Time compilation, ou JIT) de certaines expressions dans les requêtes SQL. Le JIT n’est activé par défaut qu’à partir de la version 12.

Dans certaines requêtes, l’essentiel du temps est passé à décoder des enregistrements (tuple deforming), à analyser des clauses WHERE, à effectuer des calculs. En conséquence, l’idée du JIT est de transformer tout ou partie de la requête en un programme natif directement exécuté par le processeur.

Cette compilation est une opération lourde qui ne sera effectuée que pour des requêtes qui en valent le coup, donc qui dépassent un certain coût. Au contraire de la parallélisation, ce coût n’est pas pris en compte par le planificateur. La décision d’utiliser le JIT ou pas se fait une fois le plan décidé, si le coût calculé de la requête dépasse un certain seuil.

Le JIT de PostgreSQL s’appuie actuellement sur la chaîne de compilation LLVM, choisie pour sa flexibilité. L’utilisation nécessite un PostgreSQL compilé avec l’option --with-llvm et l’installation des bibliothèques de LLVM.

Sur Debian, avec les paquets du PGDG, les dépendances sont en place dès l’installation.

Sur Rocky Linux/Red Hat 8 et 9, l’installation du paquet dédié suffit :

 # dnf install postgresql14-llvmjit

Sur CentOS/Red Hat 7, ce paquet supplémentaire nécessite lui-même des paquets du dépôt EPEL :

 # yum install epel-release
 # yum install postgresql14-llvmjit

Les systèmes CentOS/Red Hat 6 ne permettent pas d’utiliser le JIT.

Si PostgreSQL ne trouve pas les bibliothèques nécessaires, il ne renvoie pas d’erreur et continue sans tenter de JIT. Pour tester si le JIT est fonctionnel sur votre machine, il faut le chercher dans un plan quand on force son utilisation ainsi :

SET jit=on;
SET jit_above_cost TO 0 ;
EXPLAIN (ANALYZE) SELECT 1;

                                QUERY PLAN
-------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4) (… rows=1 loops=1)
 Planning Time: 0.069 ms
 JIT:
   Functions: 1
   Options: Inlining false, Optimization false, Expressions true,
            Deforming true
   Timing:  Generation 0.123 ms, Inlining 0.000 ms, Optimization 0.187 ms,
            Emission 2.778 ms, Total 3.088 ms
 Execution Time: 3.952 ms

La documentation officielle est assez accessible : https://doc.postgresql.fr/current/jit.html

Le JIT ne peut pas encore compiler toute une requête. La version actuelle se concentre sur des goulots d’étranglement classiques :

• le décodage des enregistrements (tuple deforming) pour en extraire les champs intéressants ;
• les évaluations d’expressions, notamment dans les clauses WHERE pour filtrer les lignes ;
• les agrégats, les GROUP BY…

Les jointures ne sont pas (encore ?) concernées par le JIT.

Le code résultant est utilisable plus efficacement avec les processeurs actuels qui utilisent les pipelines et les prédictions de branchement.

Pour les détails, on peut consulter notamment cette conférence très technique au FOSDEM 2018 par l’auteur principal du JIT, Andres Freund.

De l’avis même de son auteur, l’algorithme de déclenchement du JIT est « naïf ». Quatre paramètres existent (hors débogage).

jit = on (défaut à partir de la v12) active le JIT si l’environnement technique évoqué plus haut le permet.

La compilation n’a cependant lieu que pour un coût de requête calculé d’au moins jit_above_cost (par défaut 100 000, une valeur élevée). Puis, si le coût atteint jit_inline_above_cost (500 000), certaines fonctions utilisées par la requête et supportées par le JIT sont intégrées dans la compilation. Si jit_optimize_above_cost (500 000) est atteint, une optimisation du code compilé est également effectuée. Ces deux dernières opérations étant longues, elles ne le sont que pour des coûts assez importants.

Ces seuils sont à comparer avec les coûts des requêtes, qui incluent les entrées-sorties, donc pas seulement le coût CPU. Ces seuils sont un peu arbitraires et nécessiteront sans doute un certain tuning en fonction de vos requêtes et de vos processeurs.

Des contre-performances dues au JIT ont déjà été observées, menant à monter les seuils. Le JIT est trop jeune pour que les développeurs de PostgreSQL eux-mêmes aient des règles d’ajustement des valeurs des différents paramètres. Il est fréquent de le désactiver ou de monter radicalement les seuils de déclenchement.

Un exemple de plan d’exécution sur une grosse table donne :

# EXPLAIN (ANALYZE) SELECT sum(x), count(id)
  FROM bigtable WHERE id + 2 > 500000  ;

                                QUERY PLAN
-------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3403866.94..3403866.95 rows=1 width=16) (…)
   ->  Gather        (cost=3403866.19..3403866.90 rows=7 width=16)
                (actual time=11778.983..11784.235 rows=8 loops=1)
         Workers Planned: 7
         Workers Launched: 7
         ->  Partial Aggregate  (cost=3402866.19..3402866.20 rows=1 width=16)(…)
               ->  Parallel Seq Scan on bigtable  (…)
                     Filter: ((id + 2) > 500000)
                     Rows Removed by Filter: 62500
 Planning Time: 0.047 ms
 JIT:
   Functions: 42
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing:  Generation 5.611 ms, Inlining 422.019 ms, Optimization 229.956 ms,
            Emission 125.768 ms, Total 783.354 ms
 Execution Time: 11785.276 ms

Le plan d’exécution est complété, à la fin, des informations suivantes :

• le nombre de fonctions concernées ;
• les temps de génération, d’inclusion des fonctions, d’optimisation du code compilé…

Dans l’exemple ci-dessus, on peut constater que ces coûts ne sont pas négligeables par rapport au temps total. Il reste à voir si ce temps perdu est récupéré sur le temps d’exécution de la requête… ce qui en pratique n’a rien d’évident.

Sans JIT, la durée de cette requête était d’environ 17 s. Ici le JIT est rentable.

Vu son coût élevé, le JIT n’a d’intérêt que pour les requêtes utilisant beaucoup le CPU et où il est le facteur limitant.

Ce seront donc surtout des requêtes analytiques agrégeant beaucoup de lignes, comprenant beaucoup de calculs et filtres, et non les petites requêtes d’un ERP.

Il n’y a pas non plus de mise en cache du code compilé.

Si gain il y a, il est relativement modeste en deçà de quelques millions de lignes, et devient de plus en plus important au fur et à mesure que la volumétrie augmente, à condition bien sûr que d’autres limites n’apparaissent pas (bande passante…).

Documentation officielle : https://docs.postgresql.fr/current/jit-decision.html

L’indexation FTS est un des cas les plus fréquents d’utilisation non-relationnelle d’une base de données : les utilisateurs ont souvent besoin de pouvoir rechercher une information qu’ils ne connaissent pas parfaitement, d’une façon floue :

• recherche d’un produit/article par rapport à sa description ;
• recherche dans le contenu de livres/documents…

PostgreSQL doit donc permettre de rechercher de façon efficace dans un champ texte. L’avantage de cette solution est d’être intégrée au SGBD. Le moteur de recherche est donc toujours parfaitement à jour avec le contenu de la base, puisqu’il est intégré avec le reste des transactions.

Le principe est de décomposer le texte en « lexèmes » propres à chaque langue. Cela implique donc une certaine forme de normalisation, et permettent aussi de tenir compte de dictionnaires de synonymes. Le dictionnaire inclue aussi les termes courants inutiles à indexer (stop words) propres à la langue (le, la, et, the, and, der, daß…).

Décomposition et recherche en plein texte utilisent des fonctions et opérateurs dédiés, ce qui nécessite donc une adaptation du code. Ce qui suit n’est qu’un premier aperçu. La recherche plein texte est un chapitre entier de la documentation officielle.

Adrien Nayrat a donné une excellente conférence sur le sujet au PGDay France 2017 à Toulouse (slides).

to_tsvector analyse un texte et le décompose en lexèmes, et non en mots. Les chiffres indiquent ici les positions et ouvrent la possibilité à des scores de proximité. Mais des indications de poids sont possibles.

Autre exemple de décomposition d’une phrase :

SHOW default_text_search_config ;

 default_text_search_config
----------------------------
 pg_catalog.french

SELECT to_tsvector (
'La documentation de PostgreSQL est sur https://www.postgresql.org/') ;

                    to_tsvector
----------------------------------------------------
 'document':2 'postgresql':4 'www.postgresql.org':7

Les mots courts et le verbe « être » sont repérés comme termes trop courants, la casse est ignorée, même l’URL est décomposée en protocole et hôte. On peut voir en détail comment la FTS a procédé :

SELECT description, token, dictionary, lexemes
FROM ts_debug('La documentation de PostgreSQL est sur https://www.postgresql.org/') ;

   description   |       token        | dictionary  |       lexemes
-----------------+--------------------+-------------+----------------------
 Word, all ASCII | La                 | french_stem | {}
 Space symbols   |                    | ¤           | ¤
 Word, all ASCII | documentation      | french_stem | {document}
 Space symbols   |                    | ¤           | ¤
 Word, all ASCII | de                 | french_stem | {}
 Space symbols   |                    | ¤           | ¤
 Word, all ASCII | PostgreSQL         | french_stem | {postgresql}
 Space symbols   |                    | ¤           | ¤
 Word, all ASCII | est                | french_stem | {}
 Space symbols   |                    | ¤           | ¤
 Word, all ASCII | sur                | french_stem | {}
 Space symbols   |                    | ¤           | ¤
 Protocol head   | https://           | ¤           | ¤
 Host            | www.postgresql.org | simple      | {www.postgresql.org}
 Space symbols   | /                  | ¤           | ¤

Si l’on se trompe de langue, les termes courants sont mal repérés (et la recherche sera inefficace) :

SELECT to_tsvector ('english',
'La documentation de PostgreSQL est sur https://www.postgresql.org/');

                                   to_tsvector
----------------------------------------------------------------------------------
 'de':3 'document':2 'est':5 'la':1 'postgresql':4 'sur':6 'www.postgresql.org':7

Pour construire un critère de recherche, to_tsquery est nécessaire :

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

Les termes à chercher peuvent être combinés par &, | (ou), ! (négation), <-> (mots successifs), <N> (séparés par N lexèmes). @@ est l’opérateur de correspondance. Il y en a d’autres.

Il existe une fonction phraseto_tsquery pour donner une phrase entière comme critère, laquelle sera décomposée en lexèmes :

SELECT livre, contenu FROM textes
WHERE
      livre ILIKE 'Les Misérables Tome V%'
AND ( to_tsvector ('french',contenu)
        @@ phraseto_tsquery('c''est la fautes de Voltaire')
  OR  to_tsvector ('french',contenu)
        @@ phraseto_tsquery('nous sommes tombés à terre')
    );

                     livre                       |           contenu
-------------------------------------------------+----------------------------
…
Les misérables Tome V Jean Valjean, Hugo, Victor | Je suis tombé par terre,
Les misérables Tome V Jean Valjean, Hugo, Victor | C'est la faute à Voltaire,

Les lexèmes, les termes courants, la manière de décomposer un terme… sont fortement liés à la langue.

Des configurations toutes prêtes sont fournies par PostgreSQL pour certaines langues :

# \dF
        Liste des configurations de la recherche de texte
   Schéma   |    Nom     |              Description
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language
 pg_catalog | irish      | configuration for irish language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language
 pg_catalog | turkish    | configuration for turkish language

La recherche plein texte est donc directement utilisable pour le français ou l’anglais et beaucoup d’autres langues européennes. La configuration par défaut dépend du paramètre default_text_search_config, même s’il est conseillé de toujours passer explicitement la configuration aux fonctions. Ce paramètre peut être modifié globalement, par session ou par un ALTER DATABASE SET.

En demandant le détail de la configuration french, on peut voir qu’elle se base sur des « dictionnaires » pour chaque type d’élément qui peut être rencontré : mots, phrases mais aussi URL, entiers…

# \dF+ french
Configuration « pg_catalog.french » de la recherche de texte
Analyseur : « pg_catalog.default »
      Jeton      | Dictionnaires
-----------------+---------------
 asciihword      | french_stem
 asciiword       | french_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | french_stem
 hword_asciipart | french_stem
 hword_numpart   | simple
 hword_part      | french_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | french_stem

On peut lister ces dictionnaires :

# \dFd
            Liste des dictionnaires de la recherche de texte
   Schéma   |       Nom       |               Description
------------+-----------------+---------------------------------------------
…
 pg_catalog | english_stem    | snowball stemmer for english language
…
 pg_catalog | french_stem     | snowball stemmer for french language
…
 pg_catalog | simple          | simple dictionary: just lower case
                              | and check for stopword
…

Ces dictionnaires sont de type « Snowball », incluant notamment des algorithmes différents pour chaque langue. Le dictionnaire simple n’est pas lié à une langue et correspond à une simple décomposition après passage en minuscule et recherche de termes courants anglais : c’est suffisant pour des éléments comme les URL.

D’autres dictionnaires peuvent être combinés aux existants pour créer une nouvelle configuration. Le principe est que les dictionnaires reconnaissent certains éléments, et transmettent aux suivants ce qu’ils n’ont pas reconnu. Les dictionnaires précédents, de type Snowball, reconnaissent tout et doivent donc être placés en fin de liste.

Par exemple, la contrib unaccent permet de faire une configuration négligeant les accents. La contrib dict_int fournit un dictionnaire qui réduit la précision des nombres pour réduire la taille de l’index. La contrib dict_xsyn permet de créer un dictionnaire pour gérer une liste de synonymes. Mais les dictionnaires de synonymes peuvent être gérés manuellement. Les fichiers adéquats sont déjà présents ou à ajouter dans $SHAREDIR/tsearch_data/ (par exemple /usr/pgsql-14/share/tsearch_data sur Red Hat/CentOS ou /usr/share/postgresql/14/tsearch_data sur Debian).

Par exemple, en utilisant le fichier d’exemple $SHAREDIR/tsearch_data/synonym_sample.syn, dont le contenu est :

postgresql      pgsql
postgre pgsql
gogle   googl
indices index*

on peut définir un dictionnaire de synonymes, créer une nouvelle configuration reprenant french, et y insérer le nouveau dictionnaire en premier élément :

CREATE TEXT SEARCH DICTIONARY messynonymes (template=synonym, synonyms='synonym_sample');

CREATE TEXT SEARCH CONFIGURATION french2 (copy=french);

ALTER TEXT SEARCH CONFIGURATION french2
ALTER MAPPING FOR asciiword,hword,asciihword,word
WITH messynonymes, french_stem ;

À l’usage :

SELECT to_tsvector ('french2', 'PostgreSQL s''abrège en pgsql ou Postgres') ;

         to_tsvector
-------------------------
 'abreg':3 'pgsql':1,5,7

Les trois versions de « PostgreSQL » ont été reconnues.

Pour une analyse plus fine, on peut ajouter d’autres dictionnaires linguistiques depuis des sources extérieures (Ispell, OpenOffice…). Ce n’est pas intégré par défaut à PostgreSQL mais la procédure est dans la documentation.

Des « thesaurus » peuvent être même être créés pour remplacer des expressions par des synonymes (et identifier par exemple « le meilleur SGBD » et « PostgreSQL »).

Principe :

Sans indexation, une recherche FTS fonctionne, mais parcourra entièrement la table. L’indexation est possible, avec GIN ou GiST. On peut stocker le vecteur résultat de to_tsvector dans une autre colonne de la table, et c’est elle qui sera indexée. Jusque PostgreSQL 11, il est nécessaire de le faire manuellement, ou d’écrire un trigger pour cela. À partir de PostgreSQL 12, on peut utiliser une colonne générée (il est nécessaire de préciser la configuration FTS) :

ALTER TABLE textes
ADD COLUMN vecteur tsvector
GENERATED ALWAYS AS (to_tsvector ('french', contenu)) STORED ;

Les critères de recherche porteront sur la colonne vecteur :

SELECT * FROM textes
WHERE vecteur @@ to_tsquery ('french','Roméo <2> Juliette');

Cette colonne sera ensuite indexée par GIN pour avoir des temps d’accès corrects :

CREATE INDEX on textes USING gin (vecteur) ;

Alternative : index fonctionnel

Plus simplement, il peut suffire de créer juste un index fonctionnel sur to_tsvector ('french', contenu). On épargne ainsi l’espace du champ calculé dans la table.

Par contre, l’index devra porter sur le critère de recherche exact, sinon il ne sera pas utilisable. Cela n’est donc pertinent que si la majorité des recherches porte sur un nombre très restreint de critères, et il faudra un index par critère.

CREATE INDEX idx_fts ON public.textes
USING gin (to_tsvector('french'::regconfig, contenu))

SELECT * FROM textes
WHERE to_tsvector ('french', contenu) @@ to_tsquery ('french','Roméo <2> Juliette');

Exemple complet de mise en place de FTS :

• Création d’une configuration de dictionnaire dédiée avec dictionnaire français, sans accent, dans une table de dépêches :

CREATE TEXT SEARCH CONFIGURATION depeches (COPY= french);

CREATE EXTENSION unaccent ;

ALTER TEXT SEARCH CONFIGURATION depeches ALTER MAPPING FOR
hword, hword_part, word WITH unaccent,french_stem;

• Ajout d’une colonne vectorisée à la table depeche, avec des poids différents pour le titre et le texte, ici gérée manuellement avec un trigger.

CREATE TABLE depeche (id int, titre text, texte text) ;

ALTER TABLE depeche ADD vect_depeche tsvector;

UPDATE depeche
SET vect_depeche =
(setweight(to_tsvector('depeches',coalesce(titre,'')), 'A') ||
setweight(to_tsvector('depeches',coalesce(texte,'')), 'C'));

CREATE FUNCTION to_vectdepeche( )
 RETURNS trigger
 LANGUAGE plpgsql
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$
BEGIN
  NEW.vect_depeche :=
    setweight(to_tsvector('depeches',coalesce(NEW.titre,'')), 'A') ||
    setweight(to_tsvector('depeches',coalesce(NEW.texte,'')), 'C');
  return NEW;
END
$function$;

CREATE TRIGGER trg_depeche before INSERT OR update ON depeche
FOR EACH ROW execute procedure to_vectdepeche();

• Création de l’index associé au vecteur :

 CREATE INDEX idx_gin_texte ON depeche USING gin(vect_depeche);

• Collecte des statistiques sur la table :

ANALYZE depeche ;

• Utilisation basique :

SELECT titre,texte FROM depeche WHERE vect_depeche @@
to_tsquery('depeches','varicelle');
SELECT titre,texte FROM depeche WHERE vect_depeche @@
to_tsquery('depeches','varicelle & médecin');

• Tri par pertinenence :

SELECT titre,texte
FROM depeche
WHERE vect_depeche @@ to_tsquery('depeches','varicelle & médecin')
ORDER BY ts_rank_cd(vect_depeche, to_tsquery('depeches','varicelle & médecin'));

• Cette requête peut s’écrire aussi ainsi :

SELECT titre,ts_rank_cd(vect_depeche,query) AS rank
FROM depeche, to_tsquery('depeches','varicelle & médecin') query
WHERE query@@vect_depeche
ORDER BY rank DESC ;

Une recherche FTS est directement possible sur des champs JSON. Voici un exemple :

CREATE TABLE commandes (info jsonb);

INSERT INTO commandes (info)
VALUES
   (
      '{ "client": "Jean Dupont",
         "articles": {"produit": "Enveloppes A4","qté": 24}}'
   ),
   (
      '{ "client": "Jeanne Durand",
         "articles": {"produit": "Imprimante","qté": 1}}'
   ),
   (
      '{ "client": "Benoît Delaporte",
         "items": {"produit": "Rame papier normal A4","qté": 5}}'
   ),
   (
      '{ "client": "Lucie Dumoulin",
         "items": {"produit": "Pochette Papier dessin A3","qté": 5}}'
   );

La décomposition par FTS donne :

SELECT to_tsvector('french', info) FROM commandes ;

                  to_tsvector
------------------------------------------------
 'a4':5 'dupont':2 'envelopp':4 'jean':1
 'durand':2 'imprim':4 'jeann':1
 'a4':4 'benoît':6 'delaport':7 'normal':3 'papi':2 'ram':1
 'a3':4 'dessin':3 'dumoulin':7 'luc':6 'papi':2 'pochet':1

Une recherche sur « papier » donne :

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"}

Plus d’information chez Depesz : Full Text Search support for json and jsonb.

Afficher le nom du journal de transaction courant.

Créer une base pgbench vierge, de taille 80 (environ 1,2 Go). Les tables doivent être en mode unlogged.

Afficher la liste des objets unlogged dans la base pgbench.

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

Passer l’ensemble des tables de la base pgbench en mode logged.

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

Repasser toutes les tables de la base pgbench en mode unlogged.

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

Réinitialiser la base pgbench toujours avec une taille 80 mais avec les tables en mode logged. Que constate-t-on ?

Réinitialiser la base pgbench mais avec une taille de 10. Les tables doivent être en mode unlogged.

Compter le nombre de lignes dans la table pgbench_accounts.

Simuler un crash de l’instance PostgreSQL.

Redémarrer l’instance PostgreSQL.

Compter le nombre de lignes dans la table pgbench_accounts. Que constate-t-on ?

Vous aurez besoin de la base textes. La base est disponible en deux versions : complète sur https://dali.bo/tp_gutenberg (dump de 0,5 Go, table de 21 millions de lignes dans 3 Go) ou https://dali.bo/tp_gutenberg10 pour un extrait d’un dizième. Le dump peut se restaurer par exemple dans une nouvelle base, et contient juste une table nommée textes.

curl -kL https://dali.bo/tp_gutenberg -o /tmp/gutenberg.dmp
createdb gutenberg
pg_restore -d gutenberg /tmp/gutenberg.dmp
# le message sur le schéma public exitant est normale
rm -- /tmp/gutenberg.dmp

Ce TP utilise la version complète de la base textes basée sur le projet Gutenberg. Un index GIN va permettre d’utiliser la Full Text Search sur la table textes.

Créer un index GIN sur le vecteur du champ contenu (fonction to_tsvector).

Quelle est la taille de cet index ?

Quelle performance pour trouver « Fantine » (personnage des Misérables de Victor Hugo) dans la table ? Le résultat contient-il bien « Fantine » ?

Trouver les lignes qui contiennent à la fois les mots « affaire » et « couteau » et voir le plan.

Tables non journalisées

Afficher le nom du journal de transaction courant.

SELECT pg_walfile_name(pg_current_wal_lsn()) ;

     pg_walfile_name
--------------------------
 000000010000000100000024

Créer une base pgbench vierge, de taille 80 (environ 1,2 Go). Les tables doivent être en mode unlogged.

$ createdb pgbench
$ /usr/pgsql-14/bin/pgbench -i -s 80 --unlogged-tables pgbench

dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
8000000 of 8000000 tuples (100%) done (elapsed 4.93 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 8.84 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 5.02 s,
vacuum 1.79 s, primary keys 2.02 s).

Afficher la liste des objets unlogged dans la base pgbench.

SELECT relname FROM pg_class
WHERE relpersistence = 'u' ;

        relname
-----------------------
 pgbench_accounts
 pgbench_branches
 pgbench_history
 pgbench_tellers
 pgbench_branches_pkey
 pgbench_tellers_pkey
 pgbench_accounts_pkey

Les 3 objets avec le suffixe pkey correspondent aux clés primaires des tables créées par pgbench. Comme elles dépendent des tables, elles sont également en mode unlogged.

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

SELECT pg_walfile_name(pg_current_wal_lsn()) ;

     pg_walfile_name
--------------------------
 000000010000000100000024

Comme l’initialisation de pgbench a été réalisée en mode unlogged, aucune information concernant les tables et les données qu’elles contiennent n’a été inscrite dans les journaux de transaction. Donc le journal de transaction est toujours le même.

Passer l’ensemble des tables de la base pgbench en mode logged.

ALTER TABLE pgbench_accounts SET LOGGED;
ALTER TABLE pgbench_branches SET LOGGED;
ALTER TABLE pgbench_history SET LOGGED;
ALTER TABLE pgbench_tellers SET LOGGED;

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

SELECT pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name
--------------------------
 000000010000000100000077

Comme toutes les tables de la base pgbench ont été passées en mode logged, une réécriture de celles-ci a eu lieu (comme pour un VACUUM FULL). Cette réécriture additionnée au mode logged a entraîné une forte écriture dans les journaux de transaction. Dans notre cas, 83 journaux de transaction ont été consommés, soit approximativement 1,3 Go d’utilisé sur disque.

Il faut donc faire particulièrement attention à la quantité de journaux de transaction qui peut être générée lors du passage d’une table du mode unlogged à logged.

Repasser toutes les tables de la base pgbench en mode unlogged.

ALTER TABLE pgbench_accounts SET UNLOGGED;
ALTER TABLE pgbench_branches SET UNLOGGED;
ALTER TABLE pgbench_history SET UNLOGGED;
ALTER TABLE pgbench_tellers SET UNLOGGED;

Afficher le nom du journal de transaction courant. Que s’est-il passé ?

SELECT pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name
--------------------------
 000000010000000100000077

Le processus est le même que précedemment, mais, lors de la réécriture des tables, aucune information n’est stockée dans les journaux de transaction.

Réinitialiser la base pgbench toujours avec une taille 80 mais avec les tables en mode logged. Que constate-t-on ?

$ /usr/pgsql-14/bin/pgbench -i -s 80 -d pgbench

dropping old tables...
creating tables...
generating data (client-side)...
8000000 of 8000000 tuples (100%) done (elapsed 9.96 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 16.60 s (drop tables 0.11 s, create tables 0.00 s, client-side generate 10.12 s,
vacuum 2.87 s, primary keys 3.49 s).

On constate que le temps mis par pgbench pour initialiser sa base est beaucoup plus long en mode logged que unlogged. On passe de 8,84 secondes en unlogged à 16,60 secondes en mode logged. Cette augmentation du temps de traitement est due à l’écriture dans les journaux de transaction.

Réinitialiser la base pgbench mais avec une taille de 10. Les tables doivent être en mode unlogged.

$ /usr/pgsql-14/bin/pgbench -i -s 10 -d pgbench --unlogged-tables

dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.60 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.24 s (drop tables 0.02 s, create tables 0.02 s, client-side generate 0.62 s,
vacuum 0.27 s, primary keys 0.31 s).

Compter le nombre de lignes dans la table pgbench_accounts.

SELECT count(*) FROM pgbench_accounts ;

  count
---------
 1000000

Simuler un crash de l’instance PostgreSQL.

$ ps -ef | grep postmaster

postgres   697  1  0 14:32 ?   00:00:00 /usr/pgsql-14/bin/postmaster -D ...

$ kill -9 697

Redémarrer l’instance PostgreSQL.

$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data start

Compter le nombre de lignes dans la table pgbench_accounts. Que constate-t-on ?

SELECT count(*) FROM pgbench_accounts ;

 count
-------
     0

Lors d’un crash, PostgreSQL remet tous les objets unlogged à zéro.

Indexation Full Text

Créer un index GIN sur le vecteur du champ contenu (fonction to_tsvector).

textes=# CREATE INDEX idx_fts ON textes
USING gin (to_tsvector('french',contenu));
CREATE INDEX

Quelle est la taille de cet index ?

La table « pèse » 3 Go (même si on pourrait la stocker de manière beaucoup plus efficace). L’index GIN est lui-même assez lourd dans la configuration par défaut :

textes=# SELECT pg_size_pretty(pg_relation_size('idx_fts'));
 pg_size_pretty
----------------
 593 MB
(1 ligne)

Quelle performance pour trouver « Fantine » (personnage des Misérables de Victor Hugo) dans la table ? Le résultat contient-il bien « Fantine » ?

textes=# EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM textes
WHERE to_tsvector('french',contenu) @@ to_tsquery('french','fantine');

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on textes  (cost=107.94..36936.16 rows=9799 width=123)
                          (actual time=0.423..1.149 rows=326 loops=1)
   Recheck Cond: (to_tsvector('french'::regconfig, contenu)
                  @@ '''fantin'''::tsquery)
   Heap Blocks: exact=155
   Buffers: shared hit=159
   ->  Bitmap Index Scan on idx_fts  (cost=0.00..105.49 rows=9799 width=0)
                              (actual time=0.210..0.211 rows=326 loops=1)
         Index Cond: (to_tsvector('french'::regconfig, contenu)
                      @@ '''fantin'''::tsquery)
         Buffers: shared hit=4
 Planning Time: 1.248 ms
 Execution Time: 1.298 ms

On constate donc que le Full Text Search est très efficace du moins pour le Full Text Search + GIN : trouver 1 mot parmi plus de 100 millions avec 300 enregistrements correspondants dure 1,5 ms (cache chaud).

Si l’on compare avec une recherche par trigramme (extension pg_trgm et index GIN), c’est bien meilleur. À l’inverse, les trigrammes permettent des recherches floues (orthographe approximative), des recherches sur autre chose que des mots, et ne nécessitent pas de modification de code.

Par contre, la recherche n’est pas exacte, « Fantin » est fréquemment trouvé. En fait, le plan montre que c’est le vrai critère retourné par to_tsquery('french','fantine') et transformé en 'fantin'::tsquery. Si l’on tient à ce critère précis il faudra ajouter une clause plus classique contenu LIKE '%Fantine%' pour filtrer le résultat après que le FTS ait « dégrossi » la recherche.

Trouver les lignes qui contiennent à la fois les mots « affaire » et « couteau » et voir le plan.

10 lignes sont ramenées en quelques millisecondes :

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM textes
WHERE to_tsvector('french',contenu) @@ to_tsquery('french','affaire  & couteau')
;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on textes  (cost=36.22..154.87 rows=28 width=123)
                       (actual time=6.642..6.672 rows=10 loops=1)
   Recheck Cond: (to_tsvector('french'::regconfig, contenu)
                  @@ '''affair'' & ''couteau'''::tsquery)
   Heap Blocks: exact=10
   Buffers: shared hit=53
   ->  Bitmap Index Scan on idx_fts  (cost=0.00..36.21 rows=28 width=0)
                             (actual time=6.624..6.624 rows=10 loops=1)
         Index Cond: (to_tsvector('french'::regconfig, contenu)
                      @@ '''affair'' & ''couteau'''::tsquery)
         Buffers: shared hit=43
 Planning Time: 0.519 ms
 Execution Time: 6.761 ms

Noter que les pluriels « couteaux » et « affaires » figurent parmi les résultats puisque la recherche porte sur les léxèmes 'affair'' & ''couteau'.