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.

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 Manager dans la documentation pg_partman.

Note

L'extension pg_partman est prise en charge sur Aurora PostgreSQL versions 12.6 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 et created_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 table operation.

• Deux clés étrangères, l'une (fk_orga_membership)) pointant vers la table externe organization 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 type native en raison de ses performances et de sa flexibilité. Le type partman 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éation dans la documentation pg_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 :

1. Modifiez le groupe de paramètres associé à votre instance de base de données et ajoutez pg_cron à la valeur du paramètre shared_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 Aurora.

2. Exécutez la commande CREATE EXTENSION pg_cron; à l'aide d'un compte disposant des autorisations rds_superuser. Cela permet d'activer l'extension pg_cron. Pour plus d'informations, consultez Planification de la maintenance avec l'extension PostgreSQL pg_cron.

3. Exécutez la commande UPDATE partman.part_config pour ajuster les paramètres pg_partman de la table data_mart.events.

4. Exécutez la commande SET . . . pour configurer la table data_mart.events avec les clauses suivantes :

   1. infinite_time_partitions = true, – Configure la table pour créer automatiquement de nouvelles partitions sans aucune limite.

   2. retention = '3 months', – Configure la table pour présenter une rétention maximale de trois mois.

   3. 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.

5. Exécutez la commande SELECT cron.schedule . . . pour faire un appel de fonction pg_cron. Cet appel définit la fréquence à laquelle le planificateur exécute la procédure de maintenance pg_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 maintenance dans la documentation pg_partman.