Les partitions sont un excellent mécanisme pour réduire la taille de vos données interrogeables. PG_partman est un outil qui permet aux administrateurs de base de données de créer des partitions dans la base de données Postgres. Il regorge de fonctionnalités et offre de nombreuses fonctionnalités pour créer et gérer des tables partitionnées.
Idéalement, lorsque des partitions sont créées à l’aide de pg_partman, nous avons besoin d’un mécanisme de surveillance pour nous assurer que nous en avons assez pour accueillir nos données entrantes au fil du temps. Cet article explique comment surveiller et créer automatiquement des partitions dans pg_partman sur une infrastructure AWS.
Cependant, si nous n’avons pas de mécanisme de surveillance en place et que nous manquons de partitions, nous sommes dans une situation difficile. Nous devrons écrire un script personnalisé pour créer de nouvelles partitions et copier les données d’une partition par défaut. Cet article explique comment sortir d’une situation de partition problématique.
Le problème
Disons que nous travaillons pour une société de marketing qui envoie des communications aux utilisateurs au nom de ses clients. Nous avons une table customer_comms, qui enregistre toutes les communications envoyées aux clients partitionnées par mois. La table est interrogée au moment de l’exécution pour vérifier si les clients disposent d’un crédit sur leur compte pour envoyer le prochain message à l’utilisateur.
La table customer_comms ressemble à ce qui suit :
Partitioned table "public.customer_comms"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+---------------------------------------------------+----------+-------------+--------------+-------------
id | bigint | | not null | nextval('cusotmer_comms_parent_id_seq'::regclass) | plain | | |
customer_id | bigint | | not null | | plain | | |
user_id | bigint | | not null | | plain | | |
title | character varying(500) | | not null | | extended | | |
body | text[] | | not null | | extended | | |
sent_at | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (sent_at)
Indexes:
"customer_comms_sent_at_idx" btree (sent_at)
Partitions: customer_comms_p2022_10 FOR VALUES FROM ('2022-10-01 00:00:00+05:30') TO ('2022-11-01 00:00:00+05:30'),
customer_comms_p2022_11 FOR VALUES FROM ('2022-11-01 00:00:00+05:30') TO ('2022-12-01 00:00:00+05:30'),
customer_comms_p2022_12 FOR VALUES FROM ('2022-12-01 00:00:00+05:30') TO ('2023-01-01 00:00:00+05:30'),
customer_comms_p2023_01 FOR VALUES FROM ('2023-01-01 00:00:00+05:30') TO ('2023-02-01 00:00:00+05:30'),
customer_comms_default DEFAULTObservez attentivement que nos partitions sont épuisées à partir du mois de février 2023. Si notre application tente d’insérer des données dans la table au mois de février 2023, elle ira dans la table par défaut customer_comms_default. Bien que les écritures soient préservées à cause de la table par défaut, cela ralentira certainement nos lectures à partir de la table customer_comms.
Les lectures sont lentes car toutes les données qui entrent dans les tables se trouvent désormais dans une seule partition au lieu d’être réparties sur plusieurs partitions. Si nous ne créons pas de partitions pour les mois à venir, nos lectures deviendraient très lentes, en fonction de la quantité de données que nous insérons dans notre table.
Ainsi, une solution évidente qui nous vient à l’esprit consiste à créer de nouvelles partitions pour accueillir les futures données. Maintenant, nous savons que des partitions peuvent être créées sur une table existante en utilisant la commande run_maintainence() de pg_partman. Mais, lorsque nous essayons d’exécuter cette commande, nous rencontrons une erreur comme celle ci-dessous :
acme_marketing=# select * from partman.run_maintenance
('public.customer_comms',true,true);
ERROR: updated partition constraint for default partition
"customer_comms_default" would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.customer_comms
ATTACH PARTITION public.customer_comms_p2023_02 FOR VALUES
FROM ('2023-02-01 00:00:00+05:30') TO ('2023-03-01 00:00:00+05:30')"L’erreur dans l’essentiel ci-dessus indique que les nouvelles partitions n’ont pas pu être créées car les données existent déjà dans la partition par défaut. C’est la situation difficile dont nous voulons sortir. Imaginez que votre application écrive des données sur la table partitionnée à des volumes élevés, mais que tout se passe dans la partition par défaut, ce qui ralentit vos lectures. C’est un problème dont vous voulez vraiment vous sortir.
La solution
Comme nous l’avons vu, le problème n’est pas simple, mais la solution est assez simple et directe. Les étapes décrites ci-dessous vous aideront à sortir d’une telle situation :
Étape 1 : Arrêtez les écritures
Arrêtez les e-mails entrants vers l’application pendant une brève période. L’arrêt des écritures garantit qu’aucune autre donnée n’est écrite dans la partition par défaut. Nous pouvons rediriger vos écritures vers une file d’attente et lire plus tard à partir de la file d’attente et insérer nos données. Alternativement, nous pouvons supprimer l’application, mais c’est le pire des cas.
Étape 2 : déplacer les données de la partition par défaut
Maintenant que les écritures sont arrêtées sur notre table partitionnée, nous pouvons désormais déplacer en toute sécurité les données hors de la partition par défaut. Nous pouvons créer une table temporaire et y déplacer toutes nos données de partition par défaut. Une fois que les données sont déplacées hors de la partition par défaut, nous pouvons les tronquer.
Voici un exemple de la façon dont nous procéderions dans le cas de notre table customer_comms :
-- CREATE TEMP TABLE
CREATE TABLE customer_comms_temp AS
SELECT * FROM public.customer_comms_default;
-- TRUNCATE DEFAULT TABLE
TRUNCATE TABLE public.customer_comms_default;Étape 3 : Exécuter la maintenance
Nous pouvons maintenant exécuter la maintenance sur notre table et cela devrait fonctionner avec succès. Comme il n’y a plus de données dans la partition par défaut, l’erreur que nous avons eue plus tôt ne se produira pas. Voici comment exécuter la maintenance sur la table dans notre exemple :
acme_marketing=# select partman.run_maintenance('public.customer_comms', true, true);
run_maintenance
-----------------
(1 row)Étape 4 : Vérifiez vos partitions
Comme la commande de maintenance ci-dessus est réussie, nous pouvons vérifier nos partitions et nous assurer qu’elles sont créées correctement. Voici à quoi ressemble notre table customer_comms :
acme_marketing=# \d+ customer_comms;
Partitioned table "public.customer_comms"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+---------------------------------------------------+----------+-------------+--------------+-------------
id | bigint | | not null | nextval('cusotmer_comms_parent_id_seq'::regclass) | plain | | |
customer_id | bigint | | not null | | plain | | |
user_id | bigint | | not null | | plain | | |
title | character varying(500) | | not null | | extended | | |
body | text[] | | not null | | extended | | |
sent_at | timestamp with time zone | | not null | now() | plain | | |
Partition key: RANGE (sent_at)
Indexes:
"customer_comms_sent_at_idx" btree (sent_at)
Partitions: customer_comms_p2022_10 FOR VALUES FROM ('2022-10-01 00:00:00+05:30') TO ('2022-11-01 00:00:00+05:30'),
customer_comms_p2022_11 FOR VALUES FROM ('2022-11-01 00:00:00+05:30') TO ('2022-12-01 00:00:00+05:30'),
customer_comms_p2022_12 FOR VALUES FROM ('2022-12-01 00:00:00+05:30') TO ('2023-01-01 00:00:00+05:30'),
customer_comms_p2023_01 FOR VALUES FROM ('2023-01-01 00:00:00+05:30') TO ('2023-02-01 00:00:00+05:30'),
customer_comms_p2023_02 FOR VALUES FROM ('2023-02-01 00:00:00+05:30') TO ('2023-03-01 00:00:00+05:30'),
customer_comms_p2023_03 FOR VALUES FROM ('2023-03-01 00:00:00+05:30') TO ('2023-04-01 00:00:00+05:30'),
customer_comms_p2023_04 FOR VALUES FROM ('2023-04-01 00:00:00+05:30') TO ('2023-05-01 00:00:00+05:30'),
customer_comms_p2023_05 FOR VALUES FROM ('2023-05-01 00:00:00+05:30') TO ('2023-06-01 00:00:00+05:30'),
customer_comms_default DEFAULTÉtape 5 : Copiez les données
Maintenant que nous avons récupéré de l’erreur, nous pouvons copier avec succès les données dans nos partitions. Rappelez-vous la table temporaire que nous avons créée à l’étape 2 ci-dessus. Nous devons lui ajouter un index sur la colonne de partition afin de pouvoir l’interroger et écrire des requêtes de sélection pouvant insérer des données dans les partitions.
Pour notre exemple, nous créons un index sur la colonne sent_at car il s’agit de notre colonne de partition. Nous créons un index sur la colonne sent_at dans la table temporaire. Voici comment procéder :
acme_marketing=# CREATE INDEX sent_at_idx
ON customer_comms_temp(sent_at);
CREATE INDEXNous pouvons maintenant interroger la table temporaire et réinsérer des données dans nos partitions. Voici comment nous procédons…