PostregSQL partitioning : retour d’expérience

Après plus de 2 ans d’utilisation de la méthode de partitionnement dynamique PosgreSQL décrite sur ce blog, voici un petit retour d’expérience. Tout d’abord les performances ont bien été améliorées. Cependant quelques inconvénients nous ont incité à faire évoluer cette méthode.

1. Le problème de la réplication

Le fait de créer les partitions dynamiquement rend extrêmement difficile, pour ne pas dire impossible, la réplication des partitions via Slony. Des méthodes alternatives doivent donc être mises en place (comme des dump/re-import nocturne) mais avec un risque de perte de données, si la base master tombe, les données ajoutées depuis le dernier dump seront perdues.

2. Le coût du PostgreSQL Query Planner

Même en écrivant correctement les requêtes SQL de sorte qu’une seule partition ne soit effectivement utilisée, le nombre de partitions fait tout de même augmenter considérablement le temps d’exécution. Prenons par exemple 2 tables identiques partitionnées selon le même critère, la première aura 1000 partitions et la seconde 4000. Les tables ne contiennent aucune données, ceci afin d’obtenir un temps d’exécution assez représentatif du temps pris par le query planner. Sur une requête simple, les explain sur ces deux tables sont identiques :

test=> explain select * from foo where other_id=3;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Result  (cost=0.00..73.50 rows=22 width=8)
   ->  Append  (cost=0.00..73.50 rows=22 width=8)
         ->  Seq Scan on foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (other_id = 3)
         ->  Seq Scan on foo_3 foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (other_id = 3)
(6 rows)

test=> explain select * from bar where other_id=3;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Result  (cost=0.00..73.50 rows=22 width=8)
   ->  Append  (cost=0.00..73.50 rows=22 width=8)
         ->  Seq Scan on bar  (cost=0.00..36.75 rows=11 width=8)
               Filter: (other_id = 3)
         ->  Seq Scan on bar_3 bar  (cost=0.00..36.75 rows=11 width=8)
               Filter: (other_id = 3)
(6 rows)

Mais lorsque l’on execute ces 2 requêtes, l’on obtient des temps d’exécution complètement différents:

test=> select * from foo where other_id=3;
 id_foo | other_id 
--------+----------
(0 rows)

Time: 37,419 ms
test=> select * from bar where other_id=3;
 id_bar | other_id 
--------+----------
(0 rows)

Time: 136,268 ms

Et en y réfléchissant c’est parfaitement logique, avant de savoir qu’il faut utiliser la partition bar_3, parce qu’elle correspond à la contrainte other_id = 3, le PostgreSQL Query Planner doit tout de même vérifier toutes les tables filles pour trouver lesquels correspondent à cette contrainte.

Une solution

Ces deux problèmes ont été résolu en utilisant un nombre fixe de partitions (20 dans notre cas semble être un bon compromis). Les tables filles n’étant plus créer dynamiquement, on peut les répliquer comme des tables normales avec Slony. Le nombre de partitions n’étant que de 20, on conserve un coût d’analyse de la requête par le PostgreSQL Query Planner tout à fait correct.

Les partitions ont été créées de la façon suivante

CREATE TABLE children.part_0 (
   CHECK (id_question % 20 = 0)
) INHERITS (answers);

CREATE TABLE children.part_1 (
   CHECK (id_question % 20 = 1)
) INHERITS (answers);

...

CREATE TABLE children.part_19 (
   CHECK (id_question % 20 = 19)
) INHERITS (answers);

la seule contrainte de ce nouveau système est que pour tirer plainnement parti du partitionnement il faut légèrement modifier les requêtes du type

select * from answers where id_question = 12;

pour plutôt faire

select * from answers where id_question = 12 and id_question % 20 = 12 % 20;

Cette modification a permis un gains significatif des performances avec une amélioration du temps d’éxécution allant jusqu’à 97% sur certaines requêtes.

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.