PostgreSQL 10 : ICU & Abbreviated Keys
A peu près tout le monde a entendu parler du partitionnement et de la réplication logique dans PostgreSQL 10. Avez-vous entendu parler du support des règles de collation ICU (International Components for Unicode)?
Cet article va présenter en quoi consiste cette nouvelle fonctionnalité mais également les gains possibles en exploitant les abbreviated keys.
Table des matières
Court rappel sur le collationnement
En base de données, le collationnement correspond à des règles de classement de caractères.
Voici quelques exemples :
1create table t1 (c1 text);
2insert into t1 values ('cote'),('coté'),('côte'),('côté');
Deux exemples de tris en fonctions de deux locales : française et allemande
1select * from t1 order by c1 collate "de_DE";
2 c1
3------
4 cote
5 coté
6 côte
7 côté
8
9select * from t1 order by c1 collate "fr_FR";
10 c1
11------
12 cote
13 côte
14 coté
15 côté
Observez bien l’ordre de tri, en langue française le tri se fait sur le dernier accent ce qui n’est pas le cas de l’allemand.
Par défaut, Postgres utilise le collationnement de l’environnement. Il est possible de spécifier le collationnement à la création de l’instance, d’une base, d’une table… Même au niveau d’une colonne.
Il existe aussi un collationnement particulier “C” où postgres effectue le tri selon l’ordre de codage des caractères.
La table pg_collation du catalogue système énumère les différentes collations. La colonne collprovider indique la source :
c: libci: ICU
Par exemple :
1-[ RECORD 1 ]-+-----------------------
2collname | en-US-x-icu
3collnamespace | 11
4collowner | 10
5collprovider | i
6collencoding | -1
7collcollate | en-US
8collctype | en-US
9collversion | 153.64
10-[ RECORD 4 ]-+-----------------------
11collname | en_US
12collnamespace | 11
13collowner | 10
14collprovider | c
15collencoding | 6
16collcollate | en_US.utf8
17collctype | en_US.utf8
18collversion |
Collations ICU
Postgres s’appuie sur les librairies du système d’exploitation pour effectuer les opérations de tri. Sous linux, il s’appuie sur la très connue glibc.
L’intérêt majeur de s’appuyer sur cette librairie est de ne pas avoir à réécrire et maintenir tout un ensemble de règles de tri.
Cependant, cette approche peut présenter un inconvénient:
On doit faire “confiance” à cette librairie. Au sein d’une même distribution (et même release), la libc retournera toujours les mêmes résultats. Les choses peuvent se corser si on doit comparer les résultats obtenus depuis des libcs différentes. Par exemple, en utilisant une distribution différente ou une release différente. C’est pourquoi il est déconseillé de mettre des serveurs en streaming replication avec des distributions différentes : les index pourraient ne pas être cohérents. Cf : The dangers of streaming across versions of glibc: A cautionary tale
Les collations ICU sont plus riches. Il est possible de changer l’ordre des tris. Par exemple les mascules avant les minuscules: Setting Options. Peter Geoghegan a donné quelques exemples sur la mailing list hackers : What users can do with custom ICU collations in Postgres 10
Histoire des abbreviated keys dans PostgreSQL
Petit tour dans le passé :) La version 9.5 améliorait l’algorithme de tri grâce aux abbreviated keys. Les gains annoncés étaient vraiment important, ici entre 40 et 70% : Use abbreviated keys for faster sorting of text datums
En gros, les abbreviated keys permettent de mieux exploiter le cache des processeurs. Peter Geoghegan, qui est l’auteur du patch, apporte une explication sur son blog : Abbreviated keys: exploiting locality to improve PostgreSQL’s text sort performance
Malheureusement cette fonctionnalité a dû être désactivée dans la version 9.5.2: Disable abbreviated keys for string-sorting in non-C locales
La raison? Un bug dans certaines version de la libc! Ce bug entrainait une corruption de l’index: Abbreviated keys glibc issue
Quel est le lien avec les collations ICU? C’est tout simple, avec une collation ICU le moteur ne s’appuie plus sur la libc. Il est donc possible d’utiliser les Abbreviated keys.
On peut aller le vérifier dans le code :
1src/backend/utils/adt/varlena.c
21876 /*
31877 * Unfortunately, it seems that abbreviation for non-C collations is
41878 * broken on many common platforms; testing of multiple versions of glibc
51879 * reveals that, for many locales, strcoll() and strxfrm() do not return
61880 * consistent results, which is fatal to this optimization. While no
71881 * other libc other than Cygwin has so far been shown to have a problem,
81882 * we take the conservative course of action for right now and disable
91883 * this categorically. (Users who are certain this isn't a problem on
101884 * their system can define TRUST_STRXFRM.)
111885 *
121886 * Even apart from the risk of broken locales, it's possible that there
131887 * are platforms where the use of abbreviated keys should be disabled at
141888 * compile time. Having only 4 byte datums could make worst-case
151889 * performance drastically more likely, for example. Moreover, macOS's
161890 * strxfrm() implementation is known to not effectively concentrate a
171891 * significant amount of entropy from the original string in earlier
181892 * transformed blobs. It's possible that other supported platforms are
191893 * similarly encumbered. So, if we ever get past disabling this
201894 * categorically, we may still want or need to disable it for particular
211895 * platforms.
221896 */
231897 #ifndef TRUST_STRXFRM
241898 if (!collate_c && !(locale && locale->provider == COLLPROVIDER_ICU))
251899 abbreviate = false;
261900 #endif
Quelques tests
Quand j’ai préparé ma présentation sur le fonctionnement du Full Text Search dans Postgres, j’ai souhaité travailler sur un jeu de donnée “réel”: la base de donnée de stackoverflow.
Voici les résultats de la création de 3 index sur la colonne “title” de la table posts (38Go):
- Un ignorant la collation:
collate "C" - Un utilisant la collation en_US de la libc:
collate "en_US" - Un utilisant la collation en_US de la librairie ICU
collate "en-US-x-icu"
J’active quelques options pour avoir la durée d’exécution de la requête et des informations sur le tri:
1\timing
2set client_min_messages TO log;
3set trace_sort to on;
1create index idx1 on posts (title collate "C");
2LOG: begin index sort: unique = f, workMem = 6291456, randomAccess = f
3LOG: varstr_abbrev: abbrev_distinct after 160: 56.532166 (key_distinct: 59.707363, norm_abbrev_card: 0.353326, prop_card: 0.200000)
4LOG: varstr_abbrev: abbrev_distinct after 321: 110.782140 (key_distinct: 121.985752, norm_abbrev_card: 0.345116, prop_card: 0.200000)
5[...]
6LOG: varstr_abbrev: abbrev_distinct after 10485760: 523091.461475 (key_distinct: 4215367.096361, norm_abbrev_card: 0.049886, prop_card: 0.002693)
7LOG: varstr_abbrev: abbrev_distinct after 20971522: 852125.989455 (key_distinct: 8800364.815018, norm_abbrev_card: 0.040633, prop_card: 0.001750)
8LOG: performsort starting: CPU: user: 21.88 s, system: 17.27 s, elapsed: 104.98 s
9LOG: performsort done: CPU: user: 43.55 s, system: 17.27 s, elapsed: 126.65 s
10LOG: internal sort ended, 3519559 KB used: CPU: user: 48.14 s, system: 18.40 s, elapsed: 142.19 s
11CREATE INDEX
12Time: 142380.670 ms (02:22.381)
Ici, postgres exploite les abbreviated keys car il n’y a pas de règles de collation. Il a juste à trier selon le codage des caractères. Le tri ne tient donc pas compte des règles de collationnement.
1create index idx2 on posts (title collate "en_US");
2LOG: begin index sort: unique = f, workMem = 6291456, randomAccess = f
3LOG: performsort starting: CPU: user: 20.10 s, system: 17.32 s, elapsed: 104.80 s
4LOG: performsort done: CPU: user: 137.52 s, system: 17.32 s, elapsed: 222.25 s
5LOG: internal sort ended, 3519559 KB used: CPU: user: 142.41 s, system: 18.10 s, elapsed: 237.97 s
6CREATE INDEX
7Time: 238159.675 ms (03:58.160)
Postgres utilise la libc, il ne peut donc pas exploiter les abbreviated keys.
1create index idx3 on posts (title collate "en-US-x-icu");
2LOG: begin index sort: unique = f, workMem = 6291456, randomAccess = f
3LOG: varstr_abbrev: abbrev_distinct after 160: 55.475952 (key_distinct: 59.707363, norm_abbrev_card: 0.346725, prop_card: 0.200000)
4LOG: varstr_abbrev: abbrev_distinct after 321: 110.782140 (key_distinct: 121.985752, norm_abbrev_card: 0.345116, prop_card: 0.200000)
5[...]
6LOG: varstr_abbrev: abbrev_distinct after 10485760: 337228.120654 (key_distinct: 4215367.096361, norm_abbrev_card: 0.032161, prop_card: 0.002693)
7LOG: varstr_abbrev: abbrev_distinct after 20971522: 521498.943210 (key_distinct: 8800364.815018, norm_abbrev_card: 0.024867, prop_card: 0.001750)
8LOG: performsort starting: CPU: user: 30.22 s, system: 16.78 s, elapsed: 105.65 s
9LOG: performsort done: CPU: user: 66.86 s, system: 16.78 s, elapsed: 142.31 s
10LOG: internal sort ended, 3519559 KB used: CPU: user: 71.23 s, system: 18.04 s, elapsed: 157.79 s
11CREATE INDEX
12Time: 157979.957 ms (02:37.980)
Postgres utilise la librairie ICU, il peut exploiter les abbreviated keys. Le gain est de l’ordre de 34%. Contrairement au premier exemple, le tri tient compte des règles de collationnement en_US.
Note : Si comme moi il vous manquait des collations système (lors de l’initdb), il est possible d’importer les nouvelles collations grâce à la fonction pg_import_system_collations. Par exemple :
1select pg_import_system_collations('pg_catalog');
2 pg_import_system_collations
3-----------------------------
4 6