Forums PostgreSQL.fr

charli0123

Membre

Interêt du partitionnement de ma table

Bonjour,

Je m'excuse d'avance si ce sujet à déjà été abordé ici , mais je n'ai pas trouvé de réponses dans la recherche.

J'ai créé une table qui à terme devrait faire entre 5 et 8 Go.

Ais-je un intérêt particulier à la partitionner ?

J'ai cru lire ici et là qu'il fallait des tables avec de gros volumes pour que le partitionnement soit utile ; mais pour moi 8 Go ... c'est un gros volume ( merci de ne pas rire ! ;-) ) .

J'ai testé son partionnement , mais avec moins d'un millions de lignes c'est moins performant.... d'où ma question ci-dessus ; j'ai testé les indexes partiels aussi mais ça n'améliore pas la situation non plus ( situation qui est tout à fait acceptable pour le moment ).

Merci d'avoir pris le temps de me lire et merci encore à ceux qui prendront le temps de me répondre pour me donner leur avis.

Cordialement

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

Pour partitionner, il faut au moins que la table soit (largement) plus grande que la ram de la machine. C'est juste une règle empirique.
Une fois qu'on en est là (à savoir : il se peut que le partitionnement soit intéressant), il faut savoir ce qu'on veut en faire :
il y a 2 principales raisons que les gens ont pour partitionner :
- pour accélérer les insertions. On utilise souvent pour ça des règles de partitionnement de type hachage
- pour accélérer les sélections. Dans ce genre de cas on fait plutôt du partitionnement par plage. Ce second partitionnement a aussi l'intérêt de faciliter la maintenance (par exemple pour les purges de vieilles données, on peut souvent supprimer une partition plutôt que de faire un énorme delete)

Malgré tout, le partitionnement a des contraintes (écriture de règles pour que les données aillent dans la bonne partition, plans d'exécution plus complexe, voire plus mauvais plans dans certains cas).

Bref, c'est pour dire que le partitionnement est une solution intéressante, mais qui ne s'applique pas partout (et même plutôt pas souvent).

On ferait mieux de partir du problème réel, et le diagnostiquer, avant d'y chercher une solution (8Go c'est plutôt petit comme base). Quel est le problème exactement ?

---

Marc.

charli0123

Membre

Re : Interêt du partitionnement de ma table

Bonjour,

Merci de cette réponse si rapide !
Effectivement je n'ai  pas été très précis .
Le but du partitionnement serait ici d'accélérer le select plutôt que l'insert.
De plus, je n'ai pas un serveur extrêmement puissant mais mon problème vient surtout du fait que beaucoup de personne auront un accès simultané aux données de cette tables en lecture ( et écriture -> beaucoup moins fréquent ) via une application PHP  et mon cher DBA se demande si le serveur va tenir le coup , vu qu'il rame déjà en développement .

Du coup on me demande d'essayer d'optimiser les accès aux tables de plus gros volume pour avoir des temps de réponses plus faibles. ( remarque : c'est toujours la faute du développeur ! ;-) )

Or pour le moment, le partionnement ( peut être m'y suis-je mal pris aussi .... )  et les indexes partiels n'y change rien et je pense moi aussi qu'une table de 8 Go n'est pas une grosse table et que c'est plutôt le serveur qui est trop faible puisqu'il "ralentit" dès que plusieurs personnes se trouvent dessus.

Mais dans  le doute ... je m'informe !

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

Le mieux serait que tu décrives ton schéma, la (ou les) requête, et si possible un explain analyze dessus. Il y a peut être des solutions plus simples que le partitionnement.

---

Marc.

charli0123

Membre

Re : Interêt du partitionnement de ma table

Désolé pour la longueur du message :
Voici un exemple simple de ce que j'ai fait

Voici ma table mère :

CREATE TABLE toto (
    cprp_toto character(6) NOT NULL,
    type_toto character varying(5) NOT NULL,
    num_toto smallint NOT NULL,
    datdeb_toto date NOT NULL,
    datfin_toto date NOT NULL,
    prix_toto numeric(10,5),
    datcre_toto timestamp without time zone,
    datmaj_toto timestamp without time zone,
    usermaj_toto character varying(12)
);

Règles d'insertion :

CREATE RULE rule_toto_achat AS ON INSERT TO toto WHERE (((((new.type_toto)::text = 'PNNPD'::text) OR ((new.type_toto)::text = 'PNN'::text)) OR ((new.type_toto)::text = 'PCTRL'::text)) OR ((new.type_toto)::text = 'PBT'::text)) DO INSTEAD INSERT INTO toto_achat (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto, prix_toto, datcre_toto, datmaj_toto, usermaj_toto) VALUES (new.cprp_toto, new.type_toto, new.num_toto, new.datdeb_toto, new.datfin_toto, new.prix_toto, new.datcre_toto, new.datmaj_toto, new.usermaj_toto);

CREATE RULE rule_toto_ces AS ON INSERT TO toto WHERE ((new.type_toto)::text = 'CES'::text) DO INSTEAD INSERT INTO toto_ces (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto, prix_toto, datcre_toto, datmaj_toto, usermaj_toto) VALUES (new.cprp_toto, new.type_toto, new.num_toto, new.datdeb_toto, new.datfin_toto, new.prix_toto, new.datcre_toto, new.datmaj_toto, new.usermaj_toto);

CREATE RULE rule_toto_pub AS ON INSERT TO toto WHERE ((new.type_toto)::text = 'PUB'::text) DO INSTEAD INSERT INTO toto_pub (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto, prix_toto, datcre_toto, datmaj_toto, usermaj_toto) VALUES (new.cprp_toto, new.type_toto, new.num_toto, new.datdeb_toto, new.datfin_toto, new.prix_toto, new.datcre_toto, new.datmaj_toto, new.usermaj_toto);

Tables Filles :
/**************************************************************************************/
CREATE TABLE toto_achat (CONSTRAINT toto_achat_type_toto_check CHECK ((type_toto)::text = 'ACH'::text)
)
INHERITS (toto);

ALTER TABLE ONLY toto_achat
    ADD CONSTRAINT pk_toto_achat UNIQUE (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto);
/**************************************************************************************/
CREATE TABLE toto_ces (CONSTRAINT toto_achat_type_toto_check CHECK ((type_toto)::text = 'CES'::text)
)
INHERITS (toto);

ALTER TABLE ONLY toto_ces
    ADD CONSTRAINT pk_toto_ces UNIQUE (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto);
/**************************************************************************************/
CREATE TABLE toto_pub (CONSTRAINT toto_achat_type_toto_check CHECK ((type_toto)::text = 'PUB'::text)
)
INHERITS (toto);

ALTER TABLE ONLY toto_pub
    ADD CONSTRAINT pk_toto_pub UNIQUE (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto);
/**************************************************************************************/

Quand la table est partitionnée :

explain select * from toto where type_toto='pub'

Result  (cost=0.00..3.02 rows=1 width=74)

  ->  Append  (cost=0.00..3.02 rows=1 width=74)

        ->  Seq Scan on toto  (cost=0.00..3.02 rows=1 width=74)

              Filter: ((type_toto)::text = 'pub'::text)

Temps d'exécution total : 3.814 ms ( moyenne de plusieurs essai )

/**************************************************************************************/
Quand la table n'est pas partitionnée :

explain select * from toto where type_toto='pub'

Result  (cost=0.00..3.99 rows=1 width=63)

  ->  Append  (cost=0.00..3.99 rows=1 width=63)

        ->  Seq Scan on toto  (cost=0.00..3.99 rows=1 width=63)

              Filter: ((type_toto)::text = 'pub'::text)

Temps d'exécution total : 3.860 ms  (moyenne de plusieurs essai )

Sinon il y a aussi des accès sur les dates mais j'ai pas le code sous la main pour fournir des requêtes plus complexes.

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

Je manque encore pas mal d'informations :

> il n'y a pas d'index sur type_toto ? Sans ça. vous avez peu d'espoir d'avoir de bonnes perfs. Merci donc d'inclure la définition des index

> Votre tentative de partitionnement ne fonctionne pas. Mais je ne vois pas pour le moment d'intérêt à partitionner
  - Il n'y a pas de contrainte sur les partitions (un CHECK sur les tables filles), du moins il n'est pas dans la liste
  - Le constraint_exclusion n'est probablement pas en place

> J'aurais besoin d'explain analyze, pas d'explain, pour aller plus loin. On se cantonne à la version sans partitionnement pour le moment.

---

Marc.

charli0123

Membre

Re : Interêt du partitionnement de ma table

Alors ,

- Effectivement je n'ai pas mis d'index sur la table toto car elle ne contient aucune données. (j'ai suivi les conseils de la doc .... non ? )

- Il n'y a effectivement qu'un index unqiue sur chaque table fille qui est celui de la clé primaire (cprp_toto, type_toto, num_toto, datdeb_toto, datfin_toto) ; je n'ai pas mis d'index sur type_toto uniquement car chaque table fille n'a forcément qu'un type possible ; c'est apparement une erreur ....

- Je pensais avoir écrit une contrainte sur chaque table fille :
exemple : CHECK ((type_toto)::text = 'PUB'::text) pour la table toto_pub comme on peut le voir dans le message précédent, ça ne suffit pas ?

- j'ai bien mis en place la variable constraint_exclusion à ON dans PosgreSql.config

- Sinon voici un explain analyse sur la table toto ( non partitionnée ) :

explain analyse select * from toto where type_toto = 'PUB'

Seq Scan on taris  (cost=0.00..3.99 rows=20 width=63) (actual time=0.025..0.082 rows=20 loops=1)

  Filter: ((type_toto)::text = 'PUB'::text)

Total runtime: 0.144 ms

3 ligne(s)

Temps d'exécution total : 2.524 ms

En espérant avoir répondu à vos questions !

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

- Pardon pour les contraintes, j'ai regardé un peu vite, elles sont là

On retourne sur l'exemple en cours : la requête s'exécute en 100 microsecondes, pour retourner 20 enregistrements, malgré le surcout dû à l'explain analyze. Je ne vois donc pas de pb pour celle là. La version partitionnée sera de toutes façons plus longue, à cause du temps de planification de la requête plus élevé. Il faudrait une requête qui pose problème, celle-ci n'est à mon avis pas un bon candidat.

---

Marc.

charli0123

Membre

Re : Interêt du partitionnement de ma table

Pour le moment il n'y a de toute façon pas assez de données pour qu'il y ait un problème de ralentissement ( ce n'est pas moi qui alimente cette table! ). La vision est à plus long terme ; en fait on imagine qu'il y aura un problème d'accès à un moment ou un autre ( vu que l'index unique contient quasiment tous les champs de la table ) quand il y aura plusieurs dizaine de millions de lignes. (l'historique se créera petit à petit ) . mais on aura jamais une table plus grosse que les 8Go annoncé au début.

le partitionnement sera donc forcément plus lent si j'ai peu de données dans ma table, ce que je conçois tout à fait. Mais sera-t-il plus performant qu'une table seule lorsqu'il y aura 100 millions de lignes dans ma table ? En gros , est-ce que ça vaut le coup d'anticiper , ou bien dois-je attendre que mon application rame pour essayer d'optimiser la table ?

un exemple de requete sans doute lourdre à terme :

select * from toto where cprp_toto= '009221' and  type='PUB' and num_toto='0' and now() between datdeb_toto and datfin_toto

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

Cette requête ne sera lourde que si elle ramène énormément d'enregistrement.

Pour ce qui est des performances, le plus classique vu le type de requêtes et de base serait de partitionner par date. Mais honnêtement, je pense que c'est un peu prématuré, surtout pour une table de 8Go.

---

Marc.

charli0123

Membre

Re : Interêt du partitionnement de ma table

Merci bien pour toutes ces réponses et pour avoir pris le temps d'analyser ma situation , je vais prendre le parti de na pas partitionner ma table pour le moment. On verra si un jour il y a de fort ralentissement .... à ce moment là , je repasserai poser ma question ;-)

Marc Cousin

Membre

Re : Interêt du partitionnement de ma table

Ok, on sera là

---

Marc.