Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
Gestion des partitions PostgreSQL avec l'extension pg_partman
Le partitionnement de table PostgreSQL fournit un cadre à des fins de traitement hautes performances des entrées de données et des rapports. Utilisez le partitionnement pour les bases de données nécessitant une saisie très rapide de grandes quantités de données. Le partitionnement permet également d'interroger plus rapidement les tables volumineuses. Le partitionnement permet de maintenir les données sans affecter l'instance de base de données car il nécessite moins de ressources d'I/O.
Le partitionnement vous permet de diviser les données en morceaux de taille personnalisée à des fins de traitement. Par exemple, vous pouvez choisir de partitionner des données chronologiques pour des plages telles que les plages horaires, quotidiennes, hebdomadaires, mensuelles, trimestrielles, annuelles, personnalisées ou toute autre combinaison de celles-ci. Pour un exemple de données chronologiques, si vous partitionnez la table par heure, chaque partition contiendra une heure de données. Si vous partitionnez la table chronologique par jour, chaque partition contiendra un jour de données, etc. La clé de partition contrôle la taille d'une partition.
Lorsque vous utilisez une commande SQL INSERT
ou UPDATE
sur une table partitionnée, le moteur de base de données achemine les données vers la partition qui convient. Les partitions de table PostgreSQL qui stockent les données sont des tables enfants de la table principale.
Lors de la lecture d'une requête de base de données, l'optimiseur PostgreSQL examine la clause WHERE
de la requête et, si possible, dirige l'analyse de base de données vers les seules partitions pertinentes.
À partir de la version 10, PostgreSQL utilise le partitionnement déclaratif pour implémenter le partitionnement de table. Cette technique est également connue sous le nom de partitionnement PostgreSQL natif. Avant PostgreSQL version 10, il fallait utiliser des déclencheurs pour implémenter des partitions.
Le partitionnement de table PostgreSQL offre les fonctionnalités suivantes :
-
Création de nouvelles partitions à tout moment.
-
Plages de partitions variables.
-
Partitions détachables et ré-attachables à l'aide d'instructions DDL (data definition language).
Par exemple, les partitions détachables sont utiles pour supprimer les données historiques de la partition principale, tout en les conservant à des fins d'analyse.
-
Les nouvelles partitions héritent des propriétés de la table de base de données parent, et notamment :
-
Index
-
Clés primaires devant inclure la colonne de clé de partition
-
Clés étrangères
-
Contraintes de validation
-
Références
-
-
Création d'index pour la table complète ou chaque partition spécifique.
Vous ne pouvez pas modifier le schéma d'une partition individuelle. Vous pouvez cependant modifier la table parent (par exemple, ajouter une nouvelle colonne), qui se propage aux partitions.
Rubriques
Présentation de l'extension PostgreSQL pg_partman
Vous pouvez utiliser l'extension pg_partman
PostgreSQL pour automatiser la création et la maintenance des partitions de table. Pour plus d'informations générales, consultez PG Partition Managerpg_partman
.
Note
L'extension pg_partman
est prise en charge sur RDS PostgreSQL versions 12.5 et ultérieures.
Plutôt que de créer manuellement chaque partition, vous configurez pg_partman
avec les paramètres suivants :
-
Table à partitionner
-
Type de partition
-
Clé de partition
-
Granularité de partition
-
Options de pré-création et de gestion des partitions
Après avoir créé une table partitionnée PostgreSQL, vous l'enregistrez auprès de pg_partman
en appelant la fonction create_parent
. Cela crée les partitions nécessaires en fonction des paramètres passés dans la fonction.
L'extension pg_partman
propose également la fonction run_maintenance_proc
que vous pouvez appeler sur une base planifiée pour gérer automatiquement les partitions. Programmez cette fonction de manière à ce qu'elle s'exécute périodiquement (par exemple, toutes les heures) pour vous assurer que les partitions appropriées sont créées, si besoin. Vous pouvez également vous assurer que les partitions sont automatiquement supprimées.
Activation de l'extension pg_partman
En présence de plusieurs bases de données au sein de la même instance de base de données pour laquelle vous souhaitez gérer les partitions, activez l'extension pg_partman
séparément pour chaque base de données. Pour activer l'extension pg_partman
pour une base de données spécifique, créez le schéma de maintenance de partition, puis créez l'extension pg_partman
comme suit.
CREATE SCHEMA partman; CREATE EXTENSION pg_partman WITH SCHEMA partman;
Note
Pour créer l'extension pg_partman
, assurez-vous que vous disposez des privilèges rds_superuser
.
Si vous recevez une erreur similaire à la suivante, accordez les privilèges rds_superuser
au compte ou utilisez votre compte de super-utilisateur.
ERROR: permission denied to create extension "pg_partman" HINT: Must be superuser to create this extension.
Pour accorder des privilèges rds_superuser
, connectez-vous avec votre compte de super-utilisateur et exécutez la commande suivante :
GRANT rds_superuser TO
user-or-role
;
Pour les exemples illustrant l'utilisation de l'extension pg_partman, nous utilisons l'exemple de table et de partition de base de données ci-dessous. Cette base de données utilise une table partitionnée basée sur un horodatage. Un schéma data_mart
contient une table nommée events
avec une colonne nommée created_at
. Les paramètres suivants sont inclus dans la table events
:
-
Clés primaires
event_id
etcreated_at
, qui doivent utiliser la colonne pour guider la partition. -
Contrainte de vérification
ck_valid_operation
pour appliquer des valeurs pour une colonne de tableoperation
. -
Deux clés étrangères, l'une (
fk_orga_membership)
) pointant vers la table externeorganization
et l'autre (fk_parent_event_id
) correspondant à un clé étrangère auto-référencée. -
Deux index, l'un (
idx_org_id
) correspondant à la clé étrangère et l'autre (idx_event_type
) au type d'événement.
Les instructions DDL suivantes créent ces objets, qui sont automatiquement inclus dans chaque partition.
CREATE SCHEMA data_mart; CREATE TABLE data_mart.organization ( org_id BIGSERIAL, org_name TEXT, CONSTRAINT pk_organization PRIMARY KEY (org_id) ); CREATE TABLE data_mart.events( event_id BIGSERIAL, operation CHAR(1), value FLOAT(24), parent_event_id BIGINT, event_type VARCHAR(25), org_id BIGSERIAL, created_at timestamp, CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), CONSTRAINT fk_orga_membership FOREIGN KEY(org_id) REFERENCES data_mart.organization (org_id), CONSTRAINT fk_parent_event_id FOREIGN KEY(parent_event_id, created_at) REFERENCES data_mart.events (event_id,created_at) ) PARTITION BY RANGE (created_at); CREATE INDEX idx_org_id ON data_mart.events(org_id); CREATE INDEX idx_event_type ON data_mart.events(event_type);
Configuration des partitions à l'aide de la fonction create_parent
Après avoir activé l'extension pg_partman
, utilisez la fonction create_parent
pour configurer les partitions dans le schéma de maintenance des partitions. L'exemple suivant utilise l'exemple de table events
créé dans Activation de l'extension pg_partman. Appelez la fonction create_parent
comme suit.
SELECT partman.create_parent( p_parent_table => 'data_mart.events', p_control => 'created_at', p_type => 'native', p_interval=> 'daily', p_premake => 30);
Les paramètres sont les suivants :
-
p_parent_table
– Table parent partitionnée. Cette table doit être présente et pleinement qualifiée, y compris le schéma. -
p_control
– Colonne sur laquelle le partitionnement doit être basé. Le type de données doit être un entier ou une valeur basée sur le temps. -
p_type
– Le type est'native'
ou'partman'
. Vous devez généralement utiliser le typenative
en raison de ses performances et de sa flexibilité. Le typepartman
s'appuie sur l'héritage. -
p_interval
– Intervalle de temps ou plage d'entiers pour chaque partition. Par exemple,daily
, hourly, etc. -
p_premake
– Nombre de partitions à créer à l'avance pour prendre en charge les nouvelles insertions.
Pour une description complète de la fonction create_parent
, consultez Fonctions de créationpg_partman
.
Configuration de la maintenance des partitions à l'aide de la fonction run_maintenance_proc
Vous pouvez exécuter des opérations de maintenance des partitions pour créer automatiquement de nouvelles partitions, détacher des partitions ou supprimer d'anciennes partitions. La maintenance des partitions repose sur la fonction run_maintenance_proc
de l'extension pg_partman
, et l'extension pg_cron
, qui lance un planificateur interne. Le planificateur pg_cron
exécute automatiquement les instructions SQL, fonctions et procédures définies dans vos bases de données.
L'exemple suivant utilise l'exemple de table events
créé dans Activation de l'extension pg_partman pour définir l'exécution automatique des opérations de maintenance des partitions. Au préalable, ajoutez pg_cron
au paramètre shared_preload_libraries
dans le groupe de paramètres de l'instance de base de données.
CREATE EXTENSION pg_cron; UPDATE partman.part_config SET infinite_time_partitions = true, retention = '3 months', retention_keep_table=true WHERE parent_table = 'data_mart.events'; SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
Vous trouverez ci-dessous une explication étape par étape de l'exemple précédent :
-
Modifiez le groupe de paramètres associé à votre instance de base de données et ajoutez
pg_cron
à la valeur du paramètreshared_preload_libraries
. Pour prendre effet, cette modification implique un redémarrage de l'instance de base de données. Pour plus d'informations, consultez Modification des paramètres d'un groupe de paramètres de base de données dans Amazon RDS. -
Exécutez la commande
CREATE EXTENSION pg_cron;
à l'aide d'un compte disposant des autorisationsrds_superuser
. Cela permet d'activer l'extensionpg_cron
. Pour plus d'informations, consultez Planification de la maintenance avec l'extension PostgreSQL pg_cron. -
Exécutez la commande
UPDATE partman.part_config
pour ajuster les paramètrespg_partman
de la tabledata_mart.events
. -
Exécutez la commande
SET
. . . pour configurer la tabledata_mart.events
avec les clauses suivantes :-
infinite_time_partitions = true,
– Configure la table pour créer automatiquement de nouvelles partitions sans aucune limite. -
retention = '3 months',
– Configure la table pour présenter une rétention maximale de trois mois. -
retention_keep_table=true
– configure la table de telle sorte qu'au terme de la période de rétention, la table ne soit pas supprimée automatiquement. Les partitions antérieures à la période de rétention sont uniquement détachées de la table parent.
-
-
Exécutez la commande
SELECT cron.schedule
. . . pour faire un appel de fonctionpg_cron
. Cet appel définit la fréquence à laquelle le planificateur exécute la procédure de maintenancepg_partman
,partman.run_maintenance_proc
. Pour cet exemple, la procédure s'exécute toutes les heures.
Pour une description complète de la fonction run_maintenance_proc
, consultez Fonctions de maintenancepg_partman
.