Le SGBD Postgres Pro Standard est conçu pour livrer nos produits aux utilisateurs plus rapidement que nous ne pouvons le faire via PostgreSQL. Ces fonctionnalités qui ne sont pas encore incluses dans PostgreSQL, mais qui s'y trouvent sur un chemin solide, nous les incluons dans Postgres Pro Standard. De plus, Postgres Pro Standard comprend certaines extensions qui sont demandées par nos clients, mais qui ne sont pas disponibles dans la distribution PostgreSQL standard.
Parfois, il existe des exceptions lorsque, dans Postgres Pro Standard, à la demande des utilisateurs et pour leur satisfaction, des fonctionnalités moins triviales sont incluses, ce qui n'est bien placé que dans Postgres Pro Enterprise. En particulier, c'est PTRACK, à ce sujet ci-dessous.
Pas tous, mais une bonne part des extensions et utilitaires supplémentaires inclus dans Standard, a été développé par Postgres Professional. Tous les correctifs Postgres Pro ont été inventés et mis en œuvre par nos propres efforts. Commençons par les améliorations qui ont nécessité une intervention dans le moteur de base de données.
Postgres Pro Standard diffère de PostgreSQL à deux niveaux: l'ensemble des extensions et des utilitaires qui se trouvent dans l'assemblage et le noyau lui-même. Certains correctifs utiles ont été appliqués au noyau qui optimisent les performances (par exemple, un détecteur de verrouillage sans freinage) et des correctifs qui augmentent l'efficacité des utilitaires et des extensions (par exemple, pour que pg_probackup fonctionne à pleine puissance, le correctif PTRACK 2.0 est appliqué). Les différences entre la version principale de Standard et PostgreSQL sont minimisées pour la plus grande compatibilité possible. Disons que l'extension pg_pathman fait partie de Standard, mais elle peut être téléchargée depuis le github, construite et installée sur la vanilla PostgreSQL, il n'y aura pas de problèmes de compatibilité.
Commençons par les changements dans le noyau.
Vérification des versions ICU
Dans PostgreSQL, par défaut, ils sont utilisés pour comparer des chaînes en les comparant à l'aide de la bibliothèque standard C. Mais il y a aussi la possibilité d'utiliser la bibliothèque
ICU développée par IBM dans le même but. Cette bibliothèque est précieuse pour nous principalement parce qu'elle fournit un tri indépendant de la plate-forme. C'est pourquoi, par exemple, il est utilisé dans 1C, et les assemblages PostgreSQL "pour un" fonctionnent depuis longtemps avec cette bibliothèque.
De plus, les comparaisons de chaînes via l'ICU sont parfois plus rapides que via libc, et le nombre de caractères connus est plus important. En général, une bibliothèque utile. Postgres Pro Standard travaille avec lui depuis la toute première version (9.5). Dans PostgreSQL, travailler avec ICU est possible depuis la version 10.
La bibliothèque est utile, mais vous devez garder à l'esprit certaines situations d'urgence. Supposons qu'un utilisateur de SGBD ait décidé de mettre à niveau le système d'exploitation. Avec le système d'exploitation, la bibliothèque ICU peut également être mise à niveau et l'ordre des mots dans le tri changera. Après cela, immédiatement tous les index deviendront inutilisables: la recherche d'index donnera des résultats incorrects. Dans de tels cas, la base a déclaré que la version de l'USI avait changé et s'était arrêtée.
Mais c'est une décision douloureusement difficile. Après des discussions et une enquête auprès des clients, il a été décidé d'adoucir le comportement. Désormais, seules les versions de COLLATION (règles de tri) sont vérifiées. Si les versions de COLLATION utilisées dans la base de données ont changé, la base de données émet un avertissement au démarrage du SGBD, mais il ne s'arrête pas. Il rappelle également à l'utilisateur au début de chaque session.
Optimisation des verrous, jointures et GROUP BY
Le mécanisme de détection de blocage peut dégrader les performances. Le standard ne peut plus: le patch du noyau lui permet de fonctionner sans freinage. Après des améliorations majeures du mécanisme de vérification, ces problèmes n'apparaissent que sur un grand nombre de cœurs et de connexions.
Meilleure estimation du nombre de résultats de jointures en présence d'indices appropriés.
Vous pouvez maintenant utiliser des index appropriés pour grouper et trier les champs. Cette fonctionnalité a d'abord été incluse dans Standard 11.1.1 et Enterprise 11.2.1. Notre Standard 12 en possède également un.
Fedor Sigaev, CTO de Postgres Professional, a offert ces correctifs utiles à la communauté, ils sont à l'étude et, espérons-le, seront inclus dans la version PG 13.
Nous illustrons l'optimisation du fonctionnement GROUP BY par des exemples: ils sont clairs et facilement reproductibles.
L'intérêt de ce patch est que Postgres n'a pas optimisé l'ordre des champs listés dans GROUP BY. Et le temps d'exécution dépend de la séquence de regroupement (avec le même résultat de requête). Il y a des détails dans la
discussion sur la liste de diffusion des
pirates .
Si la valeur de la première colonne à traiter est unique, rien d'autre ne doit être comparé. Si vous partez d'une autre colonne, vous devez comparer.
Se rendre au test:
DROP TABLE IF EXISTS btg; SELECT i AS id, i/2 AS p, format('%60s', i%2) AS v INTO btg FROM generate_series(1, 1000000) i;
Dans le champ de texte v, 60 espaces sont générés, suivis des chiffres 0 ou 1. Les entrées ressemblent à ceci:
SELECT * FROM btg ORDER BY id DESC LIMIT 3; id | p | v
VACUUM btg; ANALYSE btg; SET enable_hashagg=off; SET max_parallel_workers= 0; SET max_parallel_workers_per_gather = 0;
Regroupez les résultats:
VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY p, v;
Plan PostgreSQL:
QUERY PLAN
Maintenant dans l'ordre inverse: v, et alors seulement p:
EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p; QUERY PLAN
Il s'avère que l'inverse est sensiblement plus lent. En effet, le premier champ
v analysé avec un petit écart de valeurs. Vous devez faire beaucoup de vérifications sur les champs restants (ici - le champ p).
Voyons comment la même requête fonctionnera avec un correctif qui sélectionne l'ordre optimal pour le traitement des colonnes:
QUERY PLAN
Et dans l'ordre inverse:
QUERY PLAN
Le plan dit que ça et là l'ordre de traitement est le même: Clé de tri: p, v. En conséquence, le temps est approximativement le même. Comparez maintenant ce qui se passe lorsque l'index est utilisé.
CREATE INDEX ON btg(p, v); SET enable_seqscan=off; SET enable_bitmapscan=off; VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p ;
Dans PostgreSQL:
QUERY PLAN
Et dans l'ordre inverse:
QUERY PLAN
Maintenant en standard:
QUERY PLAN
Et dans l'ordre inverse:
QUERY PLAN
Le temps est de nouveau le même, ce qui est naturel: en fait, les actions sont les mêmes.
Remplacement d'un octet nul au démarrage
Postgres Pro n'accepte pas zéro octet (0x00) dans les données, donc avec COPY FROM ils doivent être remplacés,
sinon il y aura une erreur . Il s'agit du véritable problème rencontré par le client lors de l'importation de données à partir d'un fichier CSV. Sa solution consiste à remplacer les octets nuls par le caractère ASCII donné. Il doit être différent des caractères QUOTE et DELIMITER utilisés lors de l'exécution de COPY FROM; sinon, le résultat peut être inattendu. Par défaut, la valeur de la variable nul_byte_replacement_on_import (string) '\ 0', c'est-à-dire qu'aucun remplacement n'est effectué.
WaitLSN
LSN est un
numéro séquentiel dans le journal , c'est-à-dire un pointeur vers une position dans le WAL (Log Sequence Number). La commande WAITLSN attend de lire le LSN spécifié. Si l'application fonctionne avec le maître et la réplique, vous devez vous assurer qu'ils sont synchrones de temps en temps. WAITLSN est un mécanisme interprocessus dans PostgrePro qui contrôle la synchronisation pendant la
réplication synchrone . Par défaut, le temps d'attente est illimité. Vous pouvez abandonner l'attente en appuyant sur Ctrl + C ou en arrêtant le serveur postgres. Vous pouvez également définir le délai d'expiration en ajoutant l'indicateur TIMEOUT ou vérifier l'état du LSN cible sans attendre en utilisant l'indicateur NOWAIT.
Supposons qu'une application effectue une certaine action, reçoit le numéro LSN du SGBD sur le maître et souhaite maintenant s'assurer que les actions sur la réplique seront synchronisées avec le maître, c'est-à-dire l'application peut être sûre que ce qu'elle a enregistré sur l'assistant est déjà arrivé sur la réplique et qu'elle est prête à être lue. Par défaut, cela n'est généralement pas garanti. WAITLSN vous permet de contrôler cette interaction et de sélectionner un mode veille de INFINITELY par défaut, à TIMEOUT et NOWAIT.
Relecture des variables de l'ancien recovery.conf
Sur un signal SIGHUP, PostgreSQL relit postgresql.conf, mais pas recovery.conf. Un correctif de noyau relativement nouveau introduit dans Standard et Enterprise 10.4.1. forcé de relire et recovery.conf. Mais dans Postgres 12, il n'y a aucun fichier recovery.conf: toutes ses variables sont transférées vers postgresql.conf. Néanmoins, bien que le fichier entier soit relu, les variables de recovery.conf n'ont pas été redéfinies par SIGHUP, mais ont nécessité un redémarrage de Postgres. En Standard, cela n'est pas obligatoire: tout est lu et redéfini.
Prise en charge de PTRACK
PTRACK 2.0 est un mécanisme PTRACK repensé pour les versions Standard et Enterprise 11 et antérieures. Au niveau du SGBD, cela a fonctionné grâce au patch du noyau, et maintenant l'extension ptrack a été ajoutée au
patch . PTRACK 2.0 suit les modifications de la page de données et fournit une interface pour récupérer ces informations. Il peut être utilisé à la fois à des fins de diagnostic, par exemple, pour se faire une idée du degré de «mutation» de l'instance par rapport à un moment précis, défini comme un numéro séquentiel dans le journal (LSN) et pour créer des sauvegardes incrémentielles.
En règle générale, la partie la plus difficile et «coûteuse» d'une procédure de sauvegarde incrémentielle consiste à isoler un sous-ensemble de pages modifiées de l'ensemble des pages d'un cluster. Étant donné que le serveur peut assumer cette tâche et fournir rapidement des informations sur les pages modifiées, le temps des sauvegardes incrémentielles à l'aide de PTRACK est considérablement réduit.
PTRACK 2.0 utilise une table de hachage d'une taille spécifiée dans la mémoire partagée, périodiquement synchronisée avec le fichier ptrack.map.
En raison d'une modification fondamentale du mécanisme de fonctionnement interne et d'une interface utilisateur incompatible avec les anciennes versions, l'extension ptrack n'est disponible que dans la 12e version de PostgresPro Standard et Enterprise, et sera disponible en tant que correctif et extension sur PostgreSQL 12.
Modification des commandes dans psql pour Windows
La prise en charge avancée de la modification des commandes d'entrée dans psql pour Windows est implémentée à l'aide de WinEditLine. Vous pouvez maintenant afficher simultanément les caractères de différents alphabets (en particulier, le cyrillique est normalement affiché sur les fenêtres non russes).
Structure de package unifiée
La structure des packages de paquets binaires pour toutes les distributions Linux est unifiée afin de simplifier la migration entre eux et de permettre d'installer plusieurs produits différents basés sur PostgreSQL sans aucun conflit. Cela peut être trouvé dans le
chapitre 16 de la documentation.
Maintenant sur les extensions:
dump_stat
Il est apparu dès 9,5. Lors du transfert ou de la restauration de données, les statistiques accumulées ne sont généralement pas transférées. Si vous le réassemblez avec la commande ANALYSER, il sera exécuté pour l'ensemble du cluster, et non pour la base de données spécifiée. Cela peut nécessiter beaucoup de temps supplémentaire pour les grandes bases de données.
L'extension dump_stat
fournit des fonctions qui vous permettent de décharger et de restaurer le contenu de la table pg_statistic. Lorsque vous effectuez un téléchargement / récupération de données, vous pouvez utiliser dump_stat pour transférer des statistiques existantes vers un nouveau serveur, sans avoir à exécuter la commande ANALYSER pour l'ensemble du cluster.
La fonction dump_statistic décharge le contenu du catalogue système pg_statistic. Il produit un INSERT pour chaque tuple dans pg_statistic, à l'exception de ceux qui contiennent des statistiques sur les tables dans les schémas information_schema et pg_catalog.
jsquery
Rappelez-vous qu'il
s'agit d'une extension pour travailler avec JSON (B), pas JS. Il fournit un ensemble de fonctions pour traiter ces types de données. Il s'agit d'un langage de requête spécial pour une recherche efficace, à l'aide d'index, dans JSON (B). Dans l'
article sur le hub, vous pouvez voir quelques exemples de jsquery et d'autres méthodes de travail avec JSON (B), par exemple JSONPath (les deux développements de notre société).
analyse_en ligne
Cette extension
fournit un ensemble de fonctions qui mettent à jour immédiatement les statistiques dans les tables qui sont spécifiées après les opérations INSERT, UPDATE, DELETE ou SELECT INTO qu'elles contiennent. L'auteur de l'extension est Fedor Sigaev.
Pour utiliser le module online_analyze, vous devez charger la bibliothèque partagée:
LOAD 'online_analyze';
Les mises à jour des statistiques peuvent être personnalisées. Par exemple, définissez un pourcentage de la taille de la table ou le nombre minimum (seuil) de modifications de ligne, après quoi les statistiques seront immédiatement collectées.
pg_pathman
L'
extension pg_pathman dans Postgres Professional a été créée plus tôt que dans le noyau PostgreSQL et a implémenté un ensemble complet de fonctions assez complet pour créer des partitions. Par conséquent, de nombreuses opérations avec des sections peuvent être effectuées avec l'un et l'autre mécanisme. Il est seulement conseillé de ne pas mélanger les sections créées par le partitionnement déclaratif et pg_pathman.
Cependant, de nombreuses opérations pg_pathman sont encore plus rapides et certaines fonctionnalités manquent dans PostgreSQL. Par exemple, création (découpe) automatique de sections. Dans PostgreSQL, vous devez définir les limites de chaque section. Si nous remplissons des données pour lesquelles on ne sait pas à l'avance combien de sections elles peuvent et doivent être dispersées, il est pratique de simplement définir l'intervalle et de laisser le logiciel couper les sections lui-même - autant que nécessaire. pg_pathman sait comment, PostgreSQL non. Mais, à partir de PG 11, il y a une section par défaut (par défaut), où vous pouvez vider tous les enregistrements qui ne tombent pas dans des sections avec des limites spécifiées.
Il existe un accord de base avec les dirigeants de la communauté PostgreSQL qui, à l'avenir, sera le meilleur, tandis que les fonctionnalités uniques de pg_pathman tomberont dans la branche principale. Mais jusque-là, pg_pathman peut faciliter la vie des administrateurs de bases de données d'applications et des programmeurs d'applications.
Créez une extension:
CREATE EXTENSION pg_pathman;
pg_pathman vous permet de diviser de grands tableaux en sections et fournit une API pratique - un ensemble de fonctions pour créer des sections et travailler avec elles. Par exemple, en utilisant la fonction
create_range_partitions(relation REGCLASS, expression TEXT, start_value ANYELEMENT, p_interval INTERVAL, p_count INTEGER DEFAULT NULL, partition_data BOOLEAN DEFAULT TRUE);
on peut demander
SELECT create_range_partitions('log', 'dt', NULL::date, '1 month'::interval);
après quoi nous ajoutons des sections:
SELECT add_range_partition('log', NULL, '2017-01-01'::date, 'log_archive', 'ts0'); SELECT add_range_partition('log', '2017-01-01'::date, '2017-02-01'::date, 'log_1'); SELECT add_range_partition('log', '2017-02-01'::date, '2017-03-01'::date', log_2');
Le journal d'archivage sera créé dans l'espace table ts0, les autres sont par défaut. Mais vous ne pouvez pas spécifier de sections explicitement, mais faites confiance à cette opération SGBD en définissant l'intervalle et en créant des sections en une seule étape:
SELECT create_range_partitions('log', 'dt', '2017-01-01'::date, '1 month'::interval);
Sur une table simple, cela ressemblera à ceci:
CREATE TABLE pg_pathmania(id serial, val float); INSERT INTO pg_pathmania(val) SELECT random() * 1000 FROM generate_series(1, 1000); SELECT create_range_partitions('pg_pathmania', 'id', 0, 50); test_parti=# \d+ pg_pathmania Table "public.pg_pathmania" Column | Type | Collation | Nullable | Default | Storage | S tats target | Description
Dans PostgreSQL, nous devions créer chaque section avec notre propre équipe. Dans de tels cas, bien sûr, ils écrivent un script qui génère automatiquement le code DDL requis. Vous n'avez pas besoin d'écrire de scripts dans pg_pathman, tout est déjà là. Mais ce n'est pas le plus intéressant. Nous allons insérer un enregistrement qui non seulement n'obtient pas d'ID dans aucune des sections existantes, mais ne tombe pas non plus dans la section la plus proche:
INSERT INTO pg_pathmania(id, val) VALUES (2000, 277.835794724524);
Encore une fois, vérifiez le contenu du tableau avec \ d + pg_pathmania:
Child tables: pg_pathmania_1, pg_pathmania_10, ... pg_pathmania_39, pg_pathmania_4, pg_pathmania_40, pg_pathmania_41,
Voici ce qui s'est passé: pg_pathman a vu que l'enregistrement avec id = 2000 ne tombe pas dans les sections déjà créées, a calculé combien elles doivent être créées, connaissant l'intervalle RANGE avec lequel la table a été partitionnée auparavant, et a créé la section où tombe le nouvel enregistrement, et, bien sûr, toutes les sections entre la limite supérieure des anciennes sections et la limite inférieure de la nouvelle section. C'est très pratique, et dans les cas où les valeurs du champ de fractionnement des données mises à jour sont mal prédites, c'est un sérieux avantage de pg_pathman.
pg_query_state
Cette extension que nous avons développée nous
permet de connaître l' état actuel des demandes dans le processus de diffusion. Il existe depuis la version 9.5 et est dû à la naissance des nombreuses demandes des administrateurs clients.
Le fait est que EXPLAIN ANALYZE vous permet d'afficher les statistiques d'exécution collectées à partir de chaque nœud de l'arborescence du plan, mais ces statistiques ne sont collectées qu'une fois la requête terminée. Mais dans la vie, hélas, il y a des situations où vous devez regarder ce que la demande n'est pas encore terminée et peut-être ne va pas se terminer. pg_query_state vous permet d'afficher les statistiques actuelles d'une requête exécutée dans un processus de maintenance externe. Dans ce cas, le format de la sortie résultante est presque identique à la sortie de la commande EXPLAIN ANALYZE habituelle.
Utilitaires:
pgBouncer
Il s'agit d' un
extracteur de connexion si
populaire qu'il serait étrange d'en parler ici. C'est juste qu'il fait partie de Standard, et il devra être installé séparément dans le cas de vanilla PostgreSQL.
pg_probackup
pg_probackup est l'un de nos développements les plus populaires. Il s'agit d'un gestionnaire de sauvegarde et de récupération développé et mis à jour principalement par Anastasia Lubennikova, Grigory Smolkin et la communauté des utilisateurs.
Avantages compétitifs de pg_probackup: sauvegarde incrémentielle avec granularité de bloc (8 Ko), trois modes de sauvegarde incrémentielle (PAGE, DELTA, PTRACK), vérification d'intégrité de la sauvegarde à la demande, vérification de cluster PostgreSQL, compression de sauvegarde, récupération partielle, etc.
Le mode de copie incrémentielle PTRACK, reposant sur
l'extension du même nom dans le cadre du mécanisme repensé - PTRACK 2.0 - est devenu encore plus rapide et est désormais sans équivoque le plus rapide et le moins cher des modes pg_probackup.
pg_repack
pg_repack est un utilitaire populaire, son fonctionnement est similaire à VACUUM FULL ou
CLUSTER . Il reconditionne non seulement les tables, supprimant les vides, mais sait également comment restaurer l'ordre physique des index clusterisés. Contrairement à CLUSTER et VACUUM FULL, il effectue ces opérations «en déplacement», sans verrous de table exclusifs et fonctionne généralement efficacement. Il n'est pas inclus dans la version vanille.
pg_variables
À propos de cette extension sur un habr il y a un
article intéressant
de notre employé Ivan Frolkov. La raison de l'extension est que travailler avec des résultats intermédiaires est parfois gênant et coûteux. L'article explore des alternatives. Les plus courants sont les tables temporaires.
En tant qu'entrepôt de données temporaire, l'extension pg_variables est beaucoup plus productive que les tables temporaires (les tests pgbench sont dans l'article), et c'est plus pratique: l'ensemble de données est défini par une paire «package - variable», qui peut être passée en tant que paramètres, renvoyée par une fonction, etc. Il existe des fonctions set / get pour travailler avec des variables. Ainsi, par exemple, vous pouvez stocker de nombreuses variables (package est le nom du package, et l'expression après la virgule décimale est les variables de ce package:
SELECT pgv_set_int('package','#'||n,n), n FROM generate_series(1,1000000) AS gs(n);
Les variables ont une propriété intéressante: pas un bug ou un avantage, mais une fonctionnalité: les données stockées par le moyen d'extension existent en dehors des transactions - elles sont enregistrées à la fois en cas de fixation d'une transaction et en cas de retour en arrière; De plus, même lors de l'exécution d'une commande distincte, des données partielles peuvent être obtenues:
SELECT pgv_insert('package', 'errs', row(n)) FROM generate_series(1,5) AS gs(n) WHERE 1.0/(n-3)<>0; ERROR: there is a record in the variable "errs" with same key test_parti=# SELECT * FROM pgv_select('package','errs') AS r(i int); i
D'une part, cela n'est pas très pratique - dans certains cas, il est nécessaire de prévoir la suppression de données saisies incorrectement, mais dans d'autres, cela peut s'avérer très utile - par exemple, enregistrer certaines données même en cas de retour en arrière d'une transaction. La
documentation contient des détails.
En conclusion, quelques extensions supplémentaires:
sr_plan, plantuner
sr_plan enregistre et restaure les plans de requête. Incluez-le comme ceci:
SET sr_plan.write_mode = true;
Après cela, les plans de toutes les requêtes suivantes seront stockés dans la table sr_plans jusqu'à ce que cette variable soit définie sur false. Les plans de toutes les demandes, y compris les demandes répétées, sont enregistrés.
plantuner prend en charge les indications permettant au planificateur de connecter ou de déconnecter les index spécifiés lors de l'exécution d'une requête. Il n'y a que deux variables GUC: enable_index / desable_index:
SET plantuner.disable_index='id_idx2';
Extensions pour la recherche en texte intégral: shared_ispell, pg_tsparser
L'extension shared_ispell, qui vous permet de placer des
dictionnaires en mémoire partagée, est en Standard et non en PostgreSQL. Notre ensemble hunspell-dict a des dictionnaires pour les langues:
- hunspell_en_us,
- hunspell_fr,
- hunspell_nl_nl,
- hunspell_ru_ru
L'
extension pg_tsparser est
un analyseur de recherche de texte
alternatif . Cette extension modifie la stratégie d'analyse de texte standard pour les mots comprenant des traits de soulignement, ainsi que les chiffres et les lettres séparés par des traits de soulignement. En plus des parties individuelles du mot renvoyé par défaut, pg_tsparser renvoie également le mot entier. Ceci est très important pour la documentation technique ou les articles comme celui-ci, dans lesquels se trouve le code du programme, et il contient des mots comme "pg_tsparser", "pg_probackup", "jsonb_build_object". Cet analyseur perçoit ces mots non seulement comme un ensemble de composants, mais également comme un seul jeton, et améliore ainsi la qualité de la recherche.
Extensions pour 1C
- mchar est un type de données facultatif pour la compatibilité avec Microsoft SQL Server;
- fulleq - fournit un opérateur d'égalité supplémentaire pour la compatibilité avec Microsoft SQL Server;
- fasttrun — - , pg_class.
, PostgresPro Standard PostgreSQL. , , , ,
.