Partionement dynamique sous PostgreSQL

Après quelques recherches sur le partitionement sous PostgreSQL dans le but d’accélérer les requêtes sur de grosses tables (plusieurs million d’enregistrements), j’ai décidé de l’appliquer de manière automatique.

Mon problème concernait principalement deux tables, « questions » et « answers ». La première contient une série de question, la seconde contient les réponses récoltées pour chacune des questions. C’est sur cette dernière que le partitionnement sera appliqué.

La première étape est de créer les partitions automatiquement, chaque nouvelle question étant succeptible d’avoir des réponses, une partition sera créé à l’insertion d’une nouvelle question en utilisant un trigger.

CREATE or REPLACE FUNCTION insert_questions()
RETURNS TRIGGER
AS $$
BEGIN
  EXECUTE 'CREATE TABLE children.question_' || NEW.id_question || '_answers (
   CHECK (id_question = ' || NEW.id_question || ')
 ) INHERITS (answers);';
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_questions_trigger
 AFTER INSERT ON questions
 FOR EACH ROW EXECUTE PROCEDURE insert_questions();

Deuxième étape, il faut rediriger les insertions dans la table answers dans la bonne partition, la encore un trigger fera l’affaire.

CREATE OR REPLACE FUNCTION insert_answers()
RETURNS TRIGGER
AS $$
BEGIN
 EXECUTE 'INSERT INTO children.question_' || NEW.id_question || '_answers
   (id_answer, id_question, value)
   VALUES (' 
     || NEW.id_answer || ', ' 
     || NEW.id_question || ', '
     || NEW.value || ');';
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_answers_trigger
 BEFORE INSERT ON answers
 FOR EACH ROW EXECUTE PROCEDURE insert_answers();

Prochaine étape, comment répliquer des partitions dynamique ?

About the Author: Guillaume Luchet

Guillaume Luchet est Directeur de la R&D et Lead Développeur chez Bilendi Technology, entrepreneur et développeur freelance.