Discussion :

[vinch999]

Bonjour,
J'aimerais partitionner une table avec pour objectif d'augmenter les perfs en lecture, voici le détail :

Table Historique(id,start_date,end_date,value,flag).

Mon champs flag (true/false) est calculé de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
if (start_date < now () - 10 years) flag =true ;
else flag = false

J'aimerais stocker dans une autre partition les données dont start_date < now () - 10 years, donc les vieux historiques ayant flag = true

Est-ce faisable?
J'ai vu qu'on pouvait envoyer dans des partitions différentes via trigger insert/update/delete. J'ai compris ce principe.
Mais comment déplacer une entrée qui était dans une partition vers une autre? (vu que l'emplacement devra évoluer dans le temps (via la date courante))

Merci d'avance pour l'aide

[punkoff]

Une partition n'est pas dynamique.

C'est un découpage physique qui répond à des règles de partitionnement précises.

Ce que vous voulez faire correspond plus à un archivage qu'à autre chose.

D'un point de vue général, le partitionnement sous postgresql est assez chiant à mettre en place et maintenir.

[vinch999]

Ok c'est donc pour cela que je ne trouvais rien à ce sujet
Selon vous,
Un index sur le flag pourrait-il suffire à obtenir de bonnes performances de lecture ?
Sachant que le besoin est le plus souvent de lire les données récentes et non les historiques anciens.

je dois effectuer par exemple des requêtes du style:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM table WHERE flag = false

Le fait de mettre ce filtre permettrait d'éviter de parcourir trop d'entrées dans les tables.

J'essaie d'optimiser le tout, car il y a de nombreuses jointures à faire, avec pour chaque table des centaines de milliers de lignes.

[punkoff]

Tout dépend des traitements et du volume traité (en Go et non en nombre de ligne)

Rien ne vous empêche d'avoir 2 tables, 1 active et une d'historisation.

Avec un batch qui tourne toutes les semaines, par exemple, vous transférez les lignes "anciennes" dans la table d'historisation

[SQLpro]

Ok c'est donc pour cela que je trouvais rien à ce sujet

ben vous avez pas bien cherché, parce que sur ce site j'ai écrit un article : http://blog.developpez.com/sqlpro/p1...paraison_postg
qui déconseille d'ailleurs le partitionnement PG qui en sus d'être imbitable est contre performant !!!!

Selon vous,
Un index sur le flag pourrait-il suffir à obtenir de bonnes performances de lecture?

pas testé, mais il me parait strictement impossible de mettre un index sur une colonne calculée non déterministe !

Sachant que le besoin est le plus souvent de lire les données récentes et non les historiques anciens.

Vous pouvez faire un index filtré qui sera réajusté tous les jours, semaines, mois, trimestre, années... par un job de PGAgent.

je dois effectuer par exemple des queries du style:
SELECT * FROM table WHERE flag = false

Cherchez pas, commencez par virer le SELECT * !

Le fait de mettre ce filtre permettrait d'éviter de parcourir trop d'entrées dans les tables.

J'essaie d'optimiser le tout car il y a de nombreuses jointures à faire, avec pour chaque table des centaines de milliers de rows.

Attention, PG n'est as un SGBDR taillé pour de nombreuses jointures sur des tables volumineuses. À partir de 12 jointures, l'optimiseur change de stratégie et la qualité d'un plan baisse notablement. Il est possible de réajuster cela en trifouillant les paramètres du GEQO et ceux du nombre d'entrées des tables de stats.

A +

[estofilo]

Apparemment il s'agit plutôt d'un archivage à 2 tables (récent/pas récent) que de partitionnement.

Il suffit de faire régulièrerement un déplacement des lignes d'une table à l'autre.
En postgres 9.1 et plus:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
WITH lignes AS
  (DELETE FROM table_courante WHERE date_start < now() - '10 years'::interval RETURNING *) 
INSERT INTO table_archivage SELECT * FROM lignes;

Le champ flag serait inutile et même contre-productif.

Pour lire des 2 tables en même temps, utiliser une vue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE VIEW table_totale AS 
  select * from table_archivage
UNION ALL
  select * from table_courante;