Re: Postgresql partitionnement

Bonjour Pierre,
Il n’y a pas de règle générale permettant de décider si vous devez partitionner ou non.

En ce qui concerne l’aspect purement performance :

· Ce sont vos exigences en termes de temps de réponse de vos requetes sql qui doivent vous amener à considérer si oui ou non, le partitionnement vous permettrait de gagner du temps :

En splittant la table principale en plusieurs partitions de sorte que vos requetes sql ne balayent que 1 ou plusieurs partitions, vous économiserez le balayage inutile de certaines partitions. [en terminologie oracle : le partition pruning].

Sachant que cette fonctionnalité peut aller au-delà de la simple économie du balayage de certaine partitions : Je n’ai pas encore testé si l’optimiseur de postgresql en est capable, mais sous oracle, la jointure entre deux tables partitionnées sur la même colonne de jointure permettra également de n’effectuer la jointure qu’entre les partitions retenues par le pruning des partitions sur les deux tables.

· Un autre cas d’utilisation du partitionnement est la gestion de l’historisation de vos données. En partitionnant sur un champ de type date/timestamp, il sera beaucoup plus rapide de dropper les partitions pour lesquels vous ne souhaitez pas garder les données, plutôt que d’effectuer un delete massif dans une seule table non partitionnée

Le partitionnement sous postgresql est assez « rustique » aussi bien dans les possibilités offertes que dans son implémentation technique (en comparaison des autres sgbd, et surtout oracle)

Néanmoins, j’utilise depuis peu pg_partman.
Pg_partman propose deux modèles de partitionnement automatisés : Par range de date , Par range de valeurs
Si ces deux modèles peuvent correspondre à vos besoin, alors pg_partman vous apporte une implémentation très robuste et très bien automatisée du partitionnement.
Je l’utilise sur une base de données qui sert à stoquer les données d’une plateforme de monitoring zabbix.
J’ai récemment partitionné une table de 400 millions d’enregistrement en 3heures, sur une plateforme très modeste (en termes de temps de réponse disque), sans que cela nécessite une interruption de service.
Comme pour beaucoup de contribs, vous devrez sans doute aller faire un tour dans le code pgpsql de pg_partman pour en saisir certaines subtilités et limitations.

[cid:image001(dot)png(at)01CF1288(dot)A584CDB0]

Olivier Bernhard
Database Administrator

Direct: +33 1 41 27 76 47
Mobile: +33 6 42 70 64 92

From: pgsql-fr-generale-owner(at)postgresql(dot)org [mailto:pgsql-fr-generale-owner(at)postgresql(dot)org] On Behalf Of pierre crumeyrolle
Sent: mercredi 15 janvier 2014 17:08
To: pgsql-fr-generale(at)postgresql(dot)org
Subject: Re: [pgsql-fr-generale] Postgresql partitionnement

je viens de tomber sur cette article qui a trait au partitionnement : comparaison partitionnement postgresql et Sqlserver

cet article montre comment on mets en place le partitionnement postgresql , j'ai l'impression que ça correspond à ce que décrit François

par contre les conclusions sont pas très engageantes , qu'est ce que vous en penser ?

ci joint le lien => http://blog.developpez.com/sqlpro/p10378/langage-sql-norme/partitionner_une_table_comparaison_postg

Le 15/01/2014 16:59, François Bouhet a écrit :
Bonjour Pierre,

Pour utiliser correctement le partitionnement, il y a quelques pièges à éviter, notamment dans l'interrogation des tables.
Pour que l'analyseur de requêtes utilise le partitionnement, le where de la requête devra contenir un critère de la contrainte d'exclusion.

En ce qui concerne les performances, cela change du tout au tout.. autant que de mettre (ou pas) des index sur les champs qui apparaissent dans le where !
:-)

Voici un exemple extrait d'une de mes bases de données : stockage de données boursières en Europe
La table mère s'appelle cours_action_europe. On crée des tables filles pour chaque place de cotation identifiée par un code sur 5 chiffres (tous les codes finissent par 5 chiffres qui définissent la place de cotation)
CREATE TABLE cours_action_10018() INHERITS (cours_action_europe) ;
ALTER TABLE cours_action_10018 ADD CONSTRAINT place_cotation_definie_v2_10018 CHECK (code_eurofidai %100000=10018) ;

CREATE TABLE cours_action_10025() INHERITS (cours_action_europe) ;
ALTER TABLE cours_action_10025 ADD CONSTRAINT place_cotation_definie_v2_10025 CHECK (code_eurofidai %100000=10025) ;

Comme il peut y avoir 50 millions de lignes sur une place, on partitionne à nouveau et on crée des tables petites-filles pour chaque année, la contrainte est sur la date, soit :
CREATE TABLE cours_action_10018_1986() INHERITS (cours_action_10018) ;
ALTER TABLE cours_action_10018_1986 ADD CONSTRAINT date_cotation_1986_intervalle_check CHECK ( (date_cotation >= '1986-01-01') AND (date_cotation <= '1986-12-31') ) ;
CREATE TABLE cours_action_10018_1987() INHERITS (cours_action_10018) ;
ALTER TABLE cours_action_10018_1987 ADD CONSTRAINT date_cotation_1987_intervalle_check CHECK ( (date_cotation >= '1987-01-01') AND (date_cotation <= '1987-12-31') ) ;
...
CREATE TABLE cours_action_10018_2012() INHERITS (cours_action_10018) ;
ALTER TABLE cours_action_10018_2012 ADD CONSTRAINT date_cotation_2012_intervalle_check CHECK ( (date_cotation >= '2012-01-01') AND (date_cotation <= '2012-12-31') ) ;

Les deux requêtes suivantes n'ont pas du tout le même temps d'exécution :
SELECT * FROM cours_action_europe WHERE code_eurofidai=110001409310018 ;
SELECT * FROM cours_action_europe WHERE (code_eurofidai=110001409310018) AND (code_eurofidai%100000=10018) ;

En effet, la première ne permet pas à l'analyseur de requêtes de déduire dans quelle table il peut directement aller, la seconde utilise la contrainte qui exclut les tables filles entre elles.

Pour être clair, la seconde requête revient pratiquement à écrire ceci :
SELECT * FROM cours_action_10018 WHERE (code_eurofidai=110001409310018) ;
Les tables soeurs cours_action_xxxxx de la table cours_action_10018 ne sont pas parcourues.

Pour les insertions, il faut en général écrire des déclencheurs, Les insertions sont évidemment beaucoup moins rapides. Si l'on peut insérer directement dans la bonne table (feuille terminale de l'arbre de l'héritage) et éviter le déclencheur, c'est mieux.

Cordialement
François Bouhet

On 15/01/2014 15:53, pierre crumeyrolle wrote:
Est simple à mettre en place ?
en terme de performances quel temps de réponse constaté ?

Le 15/01/2014 14:34, Cédric Villemain a écrit :

Bonjour Pierre,

quelqu'un a t il déjà mis en place du partitionnement sur une base
postgresql ( table de plus de 100 millions de lignes )
oui, peut-être pourriez-vous préciser votre question ?

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org<mailto:pgsql-fr-generale(at)postgresql(dot)org>)

--

Administrateur Systèmes et Base de données

téléphone: +33 (0)4 76 63 53 89 ; fax: +33 (0)4 76 63 53 86

Adresse postale:

CNRS / EUROFIDAI UPS 3390

150, rue de la Chimie BP 47

38040 GRENOBLE Cedex 9 France

On 15/01/2014 13:37, pierre crumeyrolle wrote:
bonjour,

quelqu'un a t il déjà mis en place du partitionnement sur une base postgresql ( table de plus de 100 millions de lignes )

cordialement
Pierre Crumeyrolle

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org<mailto:pgsql-fr-generale(at)postgresql(dot)org>)

--

Administrateur Systèmes et Base de données

téléphone: +33 (0)4 76 63 53 89 ; fax: +33 (0)4 76 63 53 86

Adresse postale:

CNRS / EUROFIDAI UPS 3390

150, rue de la Chimie BP 47

38040 GRENOBLE Cedex 9 France