La dernière fois, nous avons parlé de cohérence des données, examiné la différence entre les niveaux d'isolement des transactions du point de vue de l'utilisateur et compris pourquoi il était important de le savoir. Nous commençons maintenant à explorer comment PostgreSQL implémente l'isolement de cliché et la concurrence multiversionnelle.

Dans cet article, nous verrons comment les données sont physiquement disposées dans des fichiers et des pages. Cela nous éloigne de la discussion de l'isolement, mais une telle digression est nécessaire pour comprendre ce qui suit. Nous devrons déterminer comment le stockage des données est organisé à un faible niveau.

Les relations

Si vous regardez à l'intérieur des tables et des index, il s'avère qu'ils sont organisés de manière similaire. Les deux sont des objets de base de données qui contiennent des données composées de lignes.

Il ne fait aucun doute qu'une table est constituée de lignes, mais cela est moins évident pour un index. Cependant, imaginez un arbre B: il se compose de nœuds qui contiennent des valeurs indexées et des références à d'autres nœuds ou lignes de table. Ce sont ces nœuds qui peuvent être considérés comme des lignes d'index, et en fait, ils le sont.

En fait, quelques autres objets sont organisés de la même manière: des séquences (essentiellement des tables à une seule ligne) et des vues matérialisées (essentiellement des tables qui se souviennent de la requête). Et il y a aussi des vues régulières, qui ne stockent pas les données elles-mêmes, mais sont dans tous les autres sens similaires aux tableaux.

Tous ces objets dans PostgreSQL sont appelés la relation de mots communs. Ce mot est extrêmement impropre car c'est un terme de la théorie relationnelle. Vous pouvez faire un parallèle entre une relation et une table (vue), mais certainement pas entre une relation et un index. Mais il en est ainsi: l'origine académique de PostgreSQL se manifeste. Il me semble que ce sont les tableaux et les vues qui ont été appelés ainsi en premier, et le reste a enflé avec le temps.

Pour être plus simple, nous discuterons plus loin des tables et des index, mais les autres relations sont organisées exactement de la même manière.

Fourches et limes

Habituellement, plusieurs fourches correspondent à chaque relation. Les fourches peuvent avoir plusieurs types, et chacun d'eux contient un certain type de données.

S'il y a un fork, il est d'abord représenté par le seul fichier . Le nom de fichier est un identifiant numérique, qui peut être ajouté par une fin qui correspond au nom de la fourche.

Le fichier grandit progressivement et lorsque sa taille atteint 1 Go, un nouveau fichier du même fork est créé (des fichiers comme ceux-ci sont parfois appelés segments ). Le numéro ordinal du segment est ajouté à la fin du nom de fichier.

La limitation de 1 Go de la taille du fichier est apparue historiquement pour prendre en charge différents systèmes de fichiers, dont certains ne peuvent pas traiter des fichiers de plus grande taille. Vous pouvez modifier cette limitation lors de la construction de PostgreSQL ( ./configure --with-segsize ).

Ainsi, plusieurs fichiers sur disque peuvent correspondre à une seule relation. Par exemple, pour une petite table, il y en aura trois.

Tous les fichiers d'objets qui appartiennent à un espace de table et à une base de données seront stockés dans un répertoire. Vous devez avoir cela à l'esprit car les systèmes de fichiers ne fonctionnent généralement pas correctement avec un grand nombre de fichiers dans un répertoire.

Notez ici que les fichiers, à leur tour, sont divisés en pages (ou blocs ), généralement de 8 Ko. Nous discuterons un peu plus loin de la structure interne des pages.

Voyons maintenant les types de fourches.

La fourche principale est les données elles-mêmes: les mêmes lignes de table et d'index. Le fork principal est disponible pour toutes les relations (sauf les vues qui ne contiennent pas de données).

Les noms des fichiers du fork principal sont constitués du seul identifiant numérique. Par exemple, voici le chemin d'accès à la table que nous avons créée la dernière fois:

 => SELECT pg_relation_filepath('accounts');

  pg_relation_filepath ---------------------- base/41493/41496 (1 row)

D'où proviennent ces identifiants? Le répertoire "base" correspond au tablespace "pg_default". Le sous-répertoire suivant, correspondant à la base de données, est où se trouve le fichier d'intérêt:

 => SELECT oid FROM pg_database WHERE datname = 'test';

  oid ------- 41493 (1 row)

 => SELECT relfilenode FROM pg_class WHERE relname = 'accounts';

  relfilenode ------------- 41496 (1 row)

Le chemin est relatif, il est spécifié à partir du répertoire de données (PGDATA). De plus, pratiquement tous les chemins dans PostgreSQL sont spécifiés à partir de PGDATA. Grâce à cela, vous pouvez déplacer PGDATA en toute sécurité vers un emplacement différent - rien ne le limite (sauf qu'il peut être nécessaire de définir le chemin d'accès aux bibliothèques dans LD_LIBRARY_PATH).

De plus, en examinant le système de fichiers:

 postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41496

 -rw------- 1 postgres postgres 8192 /var/lib/postgresql/11/main/base/41493/41496

La fourchette d'initialisation n'est disponible que pour les tables non enregistrées (créées avec UNLOGGED spécifié) et leurs index. Des objets comme ceux-ci ne diffèrent en rien des objets normaux, sauf que les opérations avec eux ne sont pas enregistrées dans le journal d'écriture anticipée (WAL). Pour cette raison, il est plus rapide de travailler avec eux, mais il est impossible de récupérer les données dans l'état cohérent en cas de défaillance. Par conséquent, lors d'une récupération, PostgreSQL supprime simplement toutes les fourches de ces objets et écrit le fork d'initialisation à la place du fork principal. Il en résulte un objet vide. Nous discuterons de la journalisation en détail, mais dans une autre série.

La table "comptes" est enregistrée, et par conséquent, elle n'a pas de fourche d'initialisation. Mais pour expérimenter, nous pouvons désactiver la journalisation:

 => ALTER TABLE accounts SET UNLOGGED; => SELECT pg_relation_filepath('accounts');

  pg_relation_filepath ---------------------- base/41493/41507 (1 row)

L'exemple précise qu'une possibilité d'activer et de désactiver la journalisation à la volée est associée à la réécriture des données dans des fichiers avec des noms différents.

Un fork d'initialisation a le même nom que le fork principal, mais avec le suffixe "_init":

 postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_init

 -rw------- 1 postgres postgres 0 /var/lib/postgresql/11/main/base/41493/41507_init

La carte de l'espace libre est une fourchette qui assure le suivi de la disponibilité de l'espace libre à l'intérieur des pages. Cet espace est en constante évolution: il diminue lorsque de nouvelles versions de lignes sont ajoutées et augmente lors de l'aspiration. La carte d'espace libre est utilisée lors de l'insertion de nouvelles versions de lignes afin de trouver rapidement une page appropriée, où les données à ajouter conviendront.

Le nom de la carte d'espace libre a le suffixe "_fsm". Mais ce fichier n'apparaît pas immédiatement, mais seulement lorsque le besoin s'en fait sentir. La façon la plus simple d'y parvenir est de passer l'aspirateur sur une table (nous expliquerons pourquoi le moment venu):

 => VACUUM accounts;

 postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_fsm

 -rw------- 1 postgres postgres 24576 /var/lib/postgresql/11/main/base/41493/41507_fsm

La carte de visibilité est un fork où les pages qui ne contiennent que des versions de lignes à jour sont marquées par un bit. En gros, cela signifie que lorsqu'une transaction essaie de lire une ligne d'une telle page, la ligne peut être affichée sans vérifier sa visibilité. Dans les prochains articles, nous expliquerons en détail comment cela se produit.

 postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_vm

 -rw------- 1 postgres postgres 8192 /var/lib/postgresql/11/main/base/41493/41507_vm

Pages

Comme déjà mentionné, les fichiers sont logiquement divisés en pages.

Une page a généralement une taille de 8 Ko. La taille peut être modifiée dans certaines limites (16 Ko ou 32 Ko), mais uniquement pendant la génération ( ./configure --with-blocksize ). Une instance construite et exécutée ne peut fonctionner qu'avec des pages de même taille.

Quelle que soit la fourchette à laquelle appartiennent les fichiers, le serveur les utilise de manière assez similaire. Les pages sont d'abord lues dans le cache tampon, où les processus peuvent les lire et les modifier; puis lorsque le besoin s'en fait sentir, ils sont renvoyés sur le disque.

Chaque page a un partitionnement interne et contient en général les partitions suivantes:

        0 + ----------------------------------- +
           |  en-tête |
       24 + ----------------------------------- +
           |  tableau de pointeurs vers les versions de ligne |
    inférieur + ----------------------------------- +
           |  espace libre |
    supérieur + ----------------------------------- +
           |  versions de ligne |
  spécial + ----------------------------------- +
           |  espace spécial |
 taille de page + ----------------------------------- +

Vous pouvez facilement connaître les tailles de ces partitions en utilisant la page d'extension "research" inspecter:

 => CREATE EXTENSION pageinspect; => SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('accounts',0));

  lower | upper | special | pagesize -------+-------+---------+---------- 40 | 8016 | 8192 | 8192 (1 row)

Ici, nous regardons l'en- tête de la toute première page (zéro) du tableau. En plus de la taille des autres zones, l'en-tête contient des informations différentes sur la page, ce qui ne nous intéresse pas encore.

Au bas de la page, il y a l' espace spécial , qui est vide dans ce cas. Il n'est utilisé que pour les index, et même pas pour tous. "Au fond" reflète ici ce qui est dans l'image; il peut être plus exact de dire "dans des adresses élevées".

Après l'espace spécial, les versions de ligne sont localisées, c'est-à-dire ces mêmes données que nous stockons dans la table plus quelques informations internes.

En haut d'une page, juste après l'en-tête, il y a la table des matières: le tableau de pointeurs vers les versions de ligne disponibles dans la page.

L'espace libre peut être laissé entre les versions de ligne et les pointeurs (cet espace libre est conservé dans la carte de l'espace libre). Notez qu'il n'y a pas de fragmentation de la mémoire à l'intérieur d'une page - tout l'espace libre est représenté par une zone contiguë.

Pointeurs

Pourquoi les pointeurs vers les versions de ligne sont-ils nécessaires? Le fait est que les lignes d'index doivent en quelque sorte faire référence aux versions de ligne dans la table. Il est clair que la référence doit contenir le numéro de fichier, le numéro de la page dans le fichier et une indication de la version de la ligne. Nous pourrions utiliser le décalage depuis le début de la page comme indicateur, mais ce n'est pas pratique. Nous ne pourrions pas déplacer une version de ligne à l'intérieur de la page car cela casserait les références disponibles. Et cela entraînerait la fragmentation de l'espace à l'intérieur des pages et d'autres conséquences gênantes. Par conséquent, l'index fait référence au numéro de pointeur et le pointeur fait référence à l'emplacement actuel de la version de ligne dans la page. Et c'est l'adressage indirect.

Chaque pointeur occupe exactement quatre octets et contient:

une référence à la version en ligne
la taille de cette version en ligne
plusieurs octets pour déterminer l'état de la version de ligne

Format des données

Le format des données sur le disque est exactement le même que la représentation des données dans la RAM. La page est lue dans le cache tampon "en l'état", sans aucune conversion. Par conséquent, les fichiers de données d'une plate-forme s'avèrent incompatibles avec d'autres plates-formes.

Par exemple, dans l'architecture X86, l'ordre des octets va des octets les moins significatifs aux octets les plus significatifs (little-endian), z / Architecture utilise l'ordre inverse (big-endian), et dans ARM l'ordre peut être inversé.

De nombreuses architectures permettent l'alignement des données sur les limites des mots machine. Par exemple, sur un système x86 32 bits, les nombres entiers (type "entier", qui occupe 4 octets) seront alignés sur une limite de mots de 4 octets, de la même manière que les nombres à double précision (type "double précision" , qui occupe 8 octets). Et sur un système 64 bits, les nombres à double précision seront alignés sur une limite de mots de 8 octets. C'est une raison de plus d'incompatibilité.

En raison de l'alignement, la taille de la ligne du tableau dépend de l'ordre des champs. Habituellement, cet effet n'est pas très visible, mais parfois, il peut entraîner une croissance significative de la taille. Par exemple, si des champs de types «char (1)» et «integer» sont entrelacés, généralement 3 octets entre eux sont perdus. Pour plus de détails à ce sujet, vous pouvez consulter la présentation de Nikolay Shaplov " Tuple internals ".

Versions en ligne et TOAST

Nous discuterons des détails de la structure interne des versions de ligne la prochaine fois. À ce stade, il est seulement important pour nous de savoir que chaque version doit correspondre complètement à une page: PostgreSQL n'a aucun moyen de "prolonger" la ligne à la page suivante. La technique de stockage des attributs surdimensionnés (TOAST) est utilisée à la place. Le nom lui-même indique qu'une ligne peut être découpée en toasts.

Blague à part, TOAST implique plusieurs stratégies. Nous pouvons transmettre de longues valeurs d'attribut à une table interne séparée après les avoir divisées en petits morceaux de pain grillé. Une autre option consiste à compresser une valeur afin que la version de ligne tienne sur une page standard. Et nous pouvons faire les deux: d'abord compresser puis rompre et transmettre.

Pour chaque table principale, une table TOAST distincte peut être créée si nécessaire, une pour tous les attributs (avec un index dessus). La disponibilité d'attributs potentiellement longs détermine ce besoin. Par exemple, si une table a une colonne de type "numérique" ou "texte", la table TOAST sera immédiatement créée même si les valeurs longues ne seront pas utilisées.

Puisqu'une table TOAST est essentiellement une table ordinaire, elle a le même ensemble de fourches. Et cela double le nombre de fichiers qui correspondent à une table.

Les stratégies initiales sont définies par les types de données de la colonne. Vous pouvez les consulter à l'aide de la commande \d+ dans psql, mais comme elle génère en outre de nombreuses autres informations, nous interrogerons le catalogue système:

 => SELECT attname, atttypid::regtype, CASE attstorage WHEN 'p' THEN 'plain' WHEN 'e' THEN 'external' WHEN 'm' THEN 'main' WHEN 'x' THEN 'extended' END AS storage FROM pg_attribute WHERE attrelid = 'accounts'::regclass AND attnum > 0;

  attname | atttypid | storage ---------+----------+---------- id | integer | plain number | text | extended client | text | extended amount | numeric | main (4 rows)

Les noms des stratégies signifient:

plain - TOAST n'est pas utilisé (utilisé pour les types de données connus pour être courts, tels que "entier").
étendu - la compression et le stockage dans une table TOAST distincte sont autorisés
external - les valeurs longues sont stockées dans la table TOAST sans compression.
main - les valeurs longues sont d'abord compressées et n'entrent dans la table TOAST que si la compression n'a pas aidé.

En général, l'algorithme est le suivant. PostgreSQL vise à ce qu'au moins quatre lignes correspondent à une page. Par conséquent, si la taille de la ligne dépasse un quart de la page, l'en-tête pris en compte (2040 octets pour une page ordinaire de 8K), TOAST doit être appliqué à une partie des valeurs. Nous suivons l'ordre décrit ci-dessous et nous nous arrêtons dès que la ligne ne dépasse plus le seuil:

Nous passons d'abord par les attributs avec les stratégies "externe" et "étendue" de l'attribut le plus long au plus court. Les attributs «étendus» sont compressés (s'il est efficace) et si la valeur elle-même dépasse un quart de la page, elle pénètre immédiatement dans la table TOAST. Les attributs "externes" sont traités de la même manière, mais ne sont pas compressés.
Si après la première passe, la version ligne ne correspond pas encore à la page, nous transmettons les attributs restants avec les stratégies "externe" et "étendue" à la table TOAST.
Si cela n'a pas aidé non plus, nous essayons de compresser les attributs avec la stratégie "principale", mais les laissons dans la page du tableau.
Et seulement si après cela, la ligne n'est pas assez courte, les attributs "principaux" entrent dans la table TOAST.

Parfois, il peut être utile de modifier la stratégie de certaines colonnes. Par exemple, s'il est connu à l'avance que les données d'une colonne ne peuvent pas être compressées, nous pouvons définir la stratégie "externe" pour celle-ci, ce qui nous permet de gagner du temps en évitant les tentatives de compression inutiles. Cela se fait comme suit:

 => ALTER TABLE accounts ALTER COLUMN number SET STORAGE external;

En relançant la requête, nous obtenons:

  attname | atttypid | storage ---------+----------+---------- id | integer | plain number | text | external client | text | extended amount | numeric | main

Les tables et index TOAST sont situés dans le schéma pg_toast distinct et ne sont donc généralement pas visibles. Pour les tables temporaires, le schéma "pg_toast_temp_ N " est utilisé de la même manière que l'habituel "pg_temp_ N ".

Bien sûr, si vous aimez, personne ne vous gênera d'espionner la mécanique interne du processus. Disons que dans le tableau «comptes», il y a trois attributs potentiellement longs et qu'il doit donc y avoir un tableau TOAST. Le voici:

 => SELECT relnamespace::regnamespace, relname FROM pg_class WHERE oid = ( SELECT reltoastrelid FROM pg_class WHERE relname = 'accounts' );

  relnamespace | relname --------------+---------------- pg_toast | pg_toast_33953 (1 row)

 => \d+ pg_toast.pg_toast_33953

 TOAST table "pg_toast.pg_toast_33953" Column | Type | Storage ------------+---------+--------- chunk_id | oid | plain chunk_seq | integer | plain chunk_data | bytea | plain

Il est raisonnable que la stratégie "simple" soit appliquée aux toasts dans lesquels la ligne est découpée: il n'y a pas de TOAST de deuxième niveau.

PostgreSQL cache mieux l'index, mais il n'est pas difficile de le trouver non plus:

 => SELECT indexrelid::regclass FROM pg_index WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname = 'pg_toast_33953' );

  indexrelid ------------------------------- pg_toast.pg_toast_33953_index (1 row)

 => \d pg_toast.pg_toast_33953_index

 Unlogged index "pg_toast.pg_toast_33953_index" Column | Type | Key? | Definition -----------+---------+------+------------ chunk_id | oid | yes | chunk_id chunk_seq | integer | yes | chunk_seq primary key, btree, for table "pg_toast.pg_toast_33953"

La colonne "client" utilise la stratégie "étendue": ses valeurs seront compressées. Vérifions:

 => UPDATE accounts SET client = repeat('A',3000) WHERE id = 1; => SELECT * FROM pg_toast.pg_toast_33953;

  chunk_id | chunk_seq | chunk_data ----------+-----------+------------ (0 rows)

Il n'y a rien dans la table TOAST: les caractères répétitifs sont très bien compressés et après compression, la valeur correspond à une page de table habituelle.

Et maintenant, laissez le nom du client composé de caractères aléatoires:

 => UPDATE accounts SET client = ( SELECT string_agg( chr(trunc(65+random()*26)::integer), '') FROM generate_series(1,3000) ) WHERE id = 1 RETURNING left(client,10) || '...' || right(client,10);

  ?column? ------------------------- TCKGKZZSLI...RHQIOLWRRX (1 row)

Une telle séquence ne peut pas être compressée et elle entre dans la table TOAST:

 => SELECT chunk_id, chunk_seq, length(chunk_data), left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode(chunk_data,'escape')::text, 10) FROM pg_toast.pg_toast_33953;

  chunk_id | chunk_seq | length | ?column? ----------+-----------+--------+------------------------- 34000 | 0 | 2000 | TCKGKZZSLI...ZIPFLOXDIW 34000 | 1 | 1000 | DDXNNBQQYH...RHQIOLWRRX (2 rows)

Nous pouvons voir que les données sont divisées en morceaux de 2000 octets.

Lorsqu'une valeur longue est accessible, PostgreSQL automatiquement et de manière transparente pour l'application restaure la valeur d'origine et la renvoie au client.

Certes, il est assez gourmand en ressources de compresser et de casser puis de restaurer. Par conséquent, stocker des données massives dans PostgreSQL n'est pas la meilleure idée, surtout si elles sont fréquemment utilisées et que l'utilisation ne nécessite pas de logique transactionnelle (par exemple: numérisation de documents comptables originaux). Une alternative plus avantageuse consiste à stocker ces données sur un système de fichiers avec les noms de fichiers stockés dans le SGBD.

La table TOAST n'est utilisée que pour accéder à une valeur longue. En outre, sa propre concurrence de mutiversion est prise en charge pour une table TOAST: à moins qu'une mise à jour des données touche une valeur longue, une nouvelle version de ligne référencera la même valeur dans la table TOAST, ce qui économise de l'espace.

Notez que TOAST ne fonctionne que pour les tables, mais pas pour les index. Cela impose une limitation sur la taille des clés à indexer.

Pour plus de détails sur la structure de données interne, vous pouvez lire la documentation .

Continuez à lire .

MVCC dans PostgreSQL-2. Fourches, limes, pages