Ce que nous réserve PostgreSQL 11 : le partitionnement

2018-01-24

1191 mots, 6 minutes de lecture

Jean-Christophe Arnu

Dans un post récent, Lætitia nous parlait des limitations du partitionnement de PostgreSQL et notamment :

du fait qu’il ne soit pas possible de faire «migrer» une donnée d’une partition vers une autre avec une commande UPDATE.
et de la nécessité de créer des index pour chacune des partitions (pas d'«héritage»)

Ce vendredi, deux patches ont été intégrés dans PostgreSQL et résolvent ce problème. Cet article vous propose donc d’explorer, en avant première, ces deux nouvelles fonctionnalités.

Changement de partition sur un UPDATE

Dans la version 10 de PostgreSQL il n’est pas possible de déplacer une donnée d’une partition vers une autre avec UPDATE. Si nous reprenons l’exemple donné par Lætitia dans son article, et que nous créons des partitions puis y insérons des données

CREATE TYPE custom_enum_unit AS ENUM ('°C','°F','K');

CREATE TABLE temperature (measure_timestamp TIMESTAMPTZ,
                          sensor_id INTEGER,
                          measure_value DOUBLE PRECISION,
                          measure_unite custom_enum_unit)
PARTITION BY RANGE (measure_timestamp);

CREATE TABLE temperature_201709
PARTITION OF temperature
FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');

CREATE TABLE temperature_201710
PARTITION OF temperature
FOR VALUES FROM ('2017-10-01') TO ('2017-11-01');

CREATE TABLE temperature_201711
PARTITION OF temperature
FOR VALUES FROM ('2017-11-01') TO ('2017-12-01');

CREATE TABLE temperature_201712
PARTITION OF temperature
FOR VALUES FROM ('2017-12-01') TO ('2018-01-01');

CREATE TABLE temperature_201801
PARTITION OF temperature
FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');




INSERT INTO temperature SELECT
	tval, sensorid, (100.0*random())-50.0, '°C'
	FROM  generate_series('2017-10-01 00:00:00',CURRENT_TIMESTAMP,'1 minute'::interval) tval
	CROSS JOIN generate_series(1,1000,1) sensorid

Et si maintenant nous souhaitons déplacer une donnée avec PostgreSQL 10 voici ce que nous obtenons :

> UPDATE temperature SET measure_timestamp='2018-01-22 13:01' where sensor_id=1000 and measure_timestamp='2017-11-12 13:01';
ERROR:  new row for relation "temperature_201711" violates partition constraint
DETAIL:  Failing row contains (2018-01-22 13:01:00+01, 1000, 16.2243092898279, °C).

Il est impossible d’effectuer ce déplacement de la partition du mois de novembre 2017 vers la partition du mois de janvier 2018. Il est quand même possible de réaliser cette opération en effectuant un INSERT puis un DELETE au sein d’une transaction :

> BEGIN;
BEGIN
> INSERT INTO temperature SELECT '2018-01-22 13:01',sensor_id,measure_value,measure_unite FROM temperature  where sensor_id=1000 and measure_timestamp='2017-11-12 13:01';
INSERT 0 1
> DELETE FROM temperature  where sensor_id=1000 and measure_timestamp='2017-11-12 13:01';
DELETE 1
> COMMIT;
COMMIT

Si nous vérifions nos données nous aurons bien le résultat souhaité :

> SELECT * FROM temperature WHERE sensor_id=1000 AND measure_timestamp='2017-11-12 13:01';
 measure_timestamp | sensor_id | measure_value | measure_unite
-------------------+-----------+---------------+---------------
(0 ligne)

> SELECT * FROM temperature WHERE sensor_id=1000 AND measure_timestamp='2018-01-22 13:01';
   measure_timestamp    | sensor_id |  measure_value   | measure_unite
------------------------+-----------+------------------+---------------
 2018-01-22 13:01:00+01 |      1000 | 16.2243092898279 | °C
(1 ligne)

Bien que verbeuse l’opération peut quand même se faire, mais le développeur doit prendre en compte l’aspect partitionné des données sur ces tables ce qui, parfois, ne relève pas de ses attributions.

Avec la version 11, avec le même schéma de base, nous pouvons appliquer directement l'UPDATE :

> UPDATE temperature SET measure_timestamp='2018-01-22 13:01' where sensor_id=1000 and measure_timestamp='2017-11-12 13:01';
UPDATE 1

Comment cela fonctionne-t-il dans le moteur de PostgreSQL ?

Dans l’état actuel de l’implémentation de PostgreSQL 11, l'UPDATE essaie de déplacer la donnée d’une partition ne satisfaisant plus la contrainte, vers une partition qui pourrait accepter la contrainte. Pour cela il transforme l'UPDATE en un DELETE et un INSERT comme dans la transaction ci-dessus.

Si aucune partition ne peut satisfaire les contraintes demandées par l'UPDATE, l’opération est annulée :

> UPDATE temperature SET measure_timestamp='2018-04-22 14:02' where sensor_id=1000 and measure_timestamp='2017-11-12 14:02';
ERROR:  no partition of relation "temperature" found for row
DETAIL:  Partition key of the failing row contains (measure_timestamp) = (2018-04-22 14:02:00+02).

Cependant, il faut bien noter que le patch actuel n’est pas totalement complet et pose des soucis de comportement lorsqu’il est utilisé dans un environnement soumis à concurrence. Un autre patch en attente de relecture a été proposé afin de pallier les soucis déjà observés, mais n’a pas encore été intégré lors de la commitfest. Vous pouvez d’ailleurs contribuer au projet! N’hésitez pas à vous rendre à la présentation de Lætitia au FOSDEM et à PGDay.Paris pour en savoir plus sur la contribution à PostgreSQL.

Gestion des index dans les partitions

C’est une fonctionnalité très attendue : ne pas avoir à recréer un index pour chaque partition dont la définition aurait pu être effectuée sur la table parente. C’est chose faite avec ce second patch, et comme à leur habitude, les développeurs de PostgreSQL ont poussé le concept afin de le rendre le plus souple possible.

Dans l’article de Lætitia, nous avons vu que les partitions étaient rattachées à une table parente (ou pouvaient en être détachées en fonction de ce que l’utilisateur souhaite faire).

La gestion des index des partitions fonctionne sur le même principe : lorsqu’un index est créé sur la table parente avec la commande CREATE INDEX, PostgreSQL va s’assurer que chaque partition possède l’index en question. Cette opération est effectuée de manière intelligente de telle sorte que si l’index existe déjà sur une partition, aucun nouvel index ne sera créé. Dans ce cas, l’index sera de facto défini comme étant un index de partition et sera rattaché à l’index de la table parente. Si aucun index équivalent (avec les mêmes colonnes et les mêmes propriétés) n’a été défini, PostgreSQL créera l’index de partition et l’attachera comme index de partition à l’index de la table parente.

Dans la pratique et en reprenant l’exemple de base de l’article sur le partitionnement, nous pouvons lancer la commande suivante :

> CREATE INDEX idx_temperature ON temperature(sensor_id,measure_timestamp);

Étant donnée la quantité de tuples dans les tables, cette opération prend un certain temps et laisse présumer que les index de partition sont bien créés.

Une simple inspection du catalogue des index de PostgreSQL nous confirme la bonne création des index :

> SELECT * FROM pg_indexes WHERE indexname ~ 'temperature_';
 schemaname |     tablename      |                     indexname                      | tablespace |                                                             indexdef
------------+--------------------+----------------------------------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------
 public     | temperature_201709 | temperature_201709_sensor_id_measure_timestamp_idx |            | CREATE INDEX temperature_201709_sensor_id_measure_timestamp_idx ON temperature_201709 USING btree (sensor_id, measure_timestamp)
 public     | temperature_201710 | temperature_201710_sensor_id_measure_timestamp_idx |            | CREATE INDEX temperature_201710_sensor_id_measure_timestamp_idx ON temperature_201710 USING btree (sensor_id, measure_timestamp)
 public     | temperature_201711 | temperature_201711_sensor_id_measure_timestamp_idx |            | CREATE INDEX temperature_201711_sensor_id_measure_timestamp_idx ON temperature_201711 USING btree (sensor_id, measure_timestamp)
 public     | temperature_201712 | temperature_201712_sensor_id_measure_timestamp_idx |            | CREATE INDEX temperature_201712_sensor_id_measure_timestamp_idx ON temperature_201712 USING btree (sensor_id, measure_timestamp)
 public     | temperature_201801 | temperature_201801_sensor_id_measure_timestamp_idx |            | CREATE INDEX temperature_201801_sensor_id_measure_timestamp_idx ON temperature_201801 USING btree (sensor_id, measure_timestamp)
(5 rows)

Si le partitionnement est sur plusieurs «niveaux» de hiérarchie, les index seront créés de manière récursive sur toutes les partitions de la table pour laquelle l’index de table sera créé.

Notez qu’il est possible de créer des index qui ne seront pas liés et qui seront invalides sur les tables parentes avec l’option CREATE INDEX ONLY. Mais cette utilisation n’a pas réellement d’intérêt dans le cadre de cet article, aussi, nous ne traiterons que des index créés de façons récursive.

Une fois un index défini sur une table comprenant des partitions, PostgreSQL créera automatiquement des index de partition pour chaque nouvelle partition qui sera créée :

> CREATE TABLE temperature_201802
PARTITION OF temperature
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
CREATE TABLE

> \d temperature_201802
                       Table "public.temperature_201802"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2018-02-01 00:00:00+01') TO ('2018-03-01 00:00:00+01')
Indexes:
    "temperature_201802_sensor_id_measure_timestamp_idx" btree (sensor_id, measure_timestamp)

Imaginons maintenant que l’on souhaite supprimer un index de partition. Un index de partition ne peut être supprimé par la commande DROP tant que la partition est rattachée à sa table parente :

> DROP INDEX temperature_201802_sensor_id_measure_timestamp_idx;
ERROR:  cannot drop index temperature_201802_sensor_id_measure_timestamp_idx because index idx_temperature requires it
HINT:  You can drop index idx_temperature instead.

Deux solutions s’offrent à nous pour supprimer un index de partition dont la table est attachée :

Détruire l’index de la table parente. Cette opération aura pour effet de supprimer tous les index de partition attachés à l’index de la table parente. Ce n’est probablement pas ce que vous souhaiterez faire!
Une autre technique consistera à détacher la table partition de la table parente. Ainsi, si nous détachons la table de la partition, l’index de partition est détaché de l’index de la table parente et est conservé, il est alors possible de le supprimer avec DROP :

> ALTER TABLE temperature DETACH PARTITION temperature_201802;
ALTER TABLE

> \d temperature_201802;
                       Table "public.temperature_201802"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Indexes:
    "temperature_201802_sensor_id_measure_timestamp_idx" btree (sensor_id, measure_timestamp)

On constate que l’index existe toujours, cependant, maintenant que la partition est détachée de la table parente, il nous est possible de le supprimer :

> DROP INDEX temperature_201802_sensor_id_measure_timestamp_idx;
DROP INDEX

Lorsque nous souhaitons rattacher la table, PostgreSQL crée à nouveau l’index :

> ALTER TABLE temperature ATTACH PARTITION temperature_201802 DEFAULT;
ALTER TABLE
> \d temperature_201802;
                       Table "public.temperature_201802"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature DEFAULT
Indexes:
    "temperature_201802_sensor_id_measure_timestamp_idx" btree (sensor_id, measure_timestamp)

Pour finir, si nous détachons à nouveau la partition et que nous supprimons l’index sur la table parente tous les index des partitions seront supprimés sauf celui de la partition qui aura été détachée :

> ALTER TABLE temperature DETACH PARTITION temperature_201802;
> DROP INDEX idx_temperature;
DROP INDEX

> \d temperature_*
                       Table "public.temperature_201709"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2017-09-01 00:00:00+02') TO ('2017-10-01 00:00:00+02')

                       Table "public.temperature_201710"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2017-10-01 00:00:00+02') TO ('2017-11-01 00:00:00+01')

                       Table "public.temperature_201711"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2017-11-01 00:00:00+01') TO ('2017-12-01 00:00:00+01')

                       Table "public.temperature_201712"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2017-12-01 00:00:00+01') TO ('2018-01-01 00:00:00+01')

                       Table "public.temperature_201801"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Partition of: temperature FOR VALUES FROM ('2018-01-01 00:00:00+01') TO ('2018-02-01 00:00:00+01')

                       Table "public.temperature_201802"
      Column       |           Type           | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
 measure_timestamp | timestamp with time zone |           |          |
 sensor_id         | integer                  |           |          |
 measure_value     | double precision         |           |          |
 measure_unite     | custom_enum_unit         |           |          |
Indexes:
    "temperature_201802_sensor_id_measure_timestamp_idx" btree (sensor_id, measure_timestamp)

Index "public.temperature_201802_sensor_id_measure_timestamp_idx"
      Column       |           Type           |    Definition
-------------------+--------------------------+-------------------
 sensor_id         | integer                  | sensor_id
 measure_timestamp | timestamp with time zone | measure_timestamp
btree, for table "public.temperature_201802"

Enfin, pour être tout à fait complet sur cette approche d’index de partition, il est important de noter que la commande REINDEX ne supporte pas la réindexation des index de partition. Il sera nécessaire d’effectuer manuellement cette réindexation de façon séparée, le cas échéant.

Pour ceux qui souhaiteraient aller plus loin, la table pg_depend dispose maintenant d’un nouveau type de dépendance marquée 'I' (DEPENDENCY_INTERNAL_AUTO) dans la colonne deptype permettant de nous informer des dépendances tables/index pour les relations de partitionnement (tables/tables, tables/index, index/index).

Quoi d’autre dans la version 11 ?

L’ajout d’une fonction UPDATE permettant de déplacer les données d’une partition à une autre est en passe d’être complètement terminé avec les futurs patchs en attente ; l’automatisation autour de la gestion des index autour des tables partitionnées sous PostgreSQL 11 va grandement améliorer la vie de tous les utilisateurs de PostgreSQL qui feront appel au partitionnement des données qu’ils soient DBA ou développeurs.

La commitfest bat son plein, nous la suivons de près pour vous, et vous proposons la primeur des fonctions qui demain vous seront essentielles.

LOXODATA

Ce que nous réserve PostgreSQL 11 : le partitionnement

Changement de partition sur un UPDATE

Gestion des index dans les partitions

Quoi d’autre dans la version 11 ?