Rép. : Re: Tables archivées

Il n'y a pas dans PostgreSQL de méthode aussi aboutie que la partition d'Oracle pour faire ce que tu veux.

On peut néanmoins utiliser les vues, comme le propose Jean Paul

Une autre option est d'utiliser l'héritage.
cf http://archives.postgresql.org/pgsql-performance/2004-09/msg00135.php
ou http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php

Dans les 2 cas, avec PostgreSQL v8.0, tu peux utiliser les tablespaces pour séparer les données et les index sur différents disques et parralléliser les I/O.

J'ai une autre piste, un peu différente: Garder une grosse table, mais utiliser des index partiels (sur l'année en cours).
Voir http://traduc.postgresqlfr.org/pgsql-fr/indexes-partial.html
- C'est très simple à mettre en place: tu rajoutes une clause where à la création de chaque index. C'est tout.
- Toutes tes données sont toujours accessibles, mais les données qui ne portent pas sur l'année en cours font un "table scan".
- Les index utilisés sont moins gros dont un peu plus rapides.
- Tu économises la place des index sur les années passées.
- Tu peux toujours rajouter un index "complet" sur un champ donné, si tu as un besoin précis.

Aucune des 3 solutions n'est parfaite.
Si tu fais des essais, donne nous les résultats, ce sera intéressant.

Erwan

>>> Jean-Paul ARGUDO <jean-paul(at)argudo(dot)org> 04/01/2005 12:14:38 >>>
> j'ai une table "expéditions" qui grossit de jour en jour et que je
> souhaiterais "archiver", chaque année, pour limiter la taille de la base en
> conservant, par exemple, tous les index sur l'année courante et l'année
> passée, et n'avoir qu'un seul index de recherche sur les années
> antérieures. J'aurais ainsi des tables "exped2005", "exped2004",
> "exped2003"...

Solution basée sur une vue (il doit y en avoir des plus futées, pas trop le
temps de chercher):

Créer une table par an:

create table exped2003 as
select *
from expeditions
where expedition_date between to_date('01/01/2003','DD/MM/YYYY')
and to_date('31/12/2003','DD/MM/YYYY');

optionnel : create index (qui va bien sur la table ci-dessus...);

idem pour années suivantes: s/2003/2004/g ...

Créer une vue pour une sélection sur toutes les tables:

create vue expeditions_toutes
as
select * from exped2003
union
select * from exped2004
union
...;

Requêter sur la vue expeditions_toutes qui aggrège par un UNION les données de
doutes les tables. (cf UNION ALL si doublons possibles et qu'on veut les
garder)

--
Jean-Paul Argudo

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Bonjour,
J'ai fait des tests sur ce problème et effectivement, le partitionnement
Oracle reste le meilleur (mais Oracle sans partition explosait bien
avant PG) . J'avais une table de 125 millions de lignes.
Je n'ai pas tenté la vue sur 100 tables, je suis resté sur 1 seule
grosse table. Les index partiels ne m'ont pas convaincue, peut-être
du fait de leur nombre, il me fallait en effet couvrir toute la table
et pas seulement la dernière année.
La mise en cluster sur l'index a bien amélioré les accès, mais avec des
inconvénients s'il y a des remplissages pas seulement "par le haut".
Ceci dit les résultats n'étaient pas très loin derrière Oracle avec
la version PG 8.0 beta2 et table/clé en cluster (les mises à jour
essentiellement sont plus lentes).
Valérie.

Le mar 04/01/2005 à 12:56, Erwan DUROSELLE a écrit :
> Il n'y a pas dans PostgreSQL de méthode aussi aboutie que la partition d'Oracle pour faire ce que tu veux.
>
> On peut néanmoins utiliser les vues, comme le propose Jean Paul
>
> Une autre option est d'utiliser l'héritage.
> cf http://archives.postgresql.org/pgsql-performance/2004-09/msg00135.php
> ou http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php
>
> Dans les 2 cas, avec PostgreSQL v8.0, tu peux utiliser les tablespaces pour séparer les données et les index sur différents disques et parralléliser les I/O.
>
>
> J'ai une autre piste, un peu différente: Garder une grosse table, mais utiliser des index partiels (sur l'année en cours).
> Voir http://traduc.postgresqlfr.org/pgsql-fr/indexes-partial.html
> - C'est très simple à mettre en place: tu rajoutes une clause where à la création de chaque index. C'est tout.
> - Toutes tes données sont toujours accessibles, mais les données qui ne portent pas sur l'année en cours font un "table scan".
> - Les index utilisés sont moins gros dont un peu plus rapides.
> - Tu économises la place des index sur les années passées.
> - Tu peux toujours rajouter un index "complet" sur un champ donné, si tu as un besoin précis.
>
> Aucune des 3 solutions n'est parfaite.
> Si tu fais des essais, donne nous les résultats, ce sera intéressant.
>
> Erwan
>
> >>> Jean-Paul ARGUDO <jean-paul(at)argudo(dot)org> 04/01/2005 12:14:38 >>>
> > j'ai une table "expéditions" qui grossit de jour en jour et que je
> > souhaiterais "archiver", chaque année, pour limiter la taille de la base en
> > conservant, par exemple, tous les index sur l'année courante et l'année
> > passée, et n'avoir qu'un seul index de recherche sur les années
> > antérieures. J'aurais ainsi des tables "exped2005", "exped2004",
> > "exped2003"...
>
> Solution basée sur une vue (il doit y en avoir des plus futées, pas trop le
> temps de chercher):
>
> Créer une table par an:
>
> create table exped2003 as
> select *
> from expeditions
> where expedition_date between to_date('01/01/2003','DD/MM/YYYY')
> and to_date('31/12/2003','DD/MM/YYYY');
>
> optionnel : create index (qui va bien sur la table ci-dessus...);
>
> idem pour années suivantes: s/2003/2004/g ...
>
> Créer une vue pour une sélection sur toutes les tables:
>
> create vue expeditions_toutes
> as
> select * from exped2003
> union
> select * from exped2004
> union
> ...;
>
> Requêter sur la vue expeditions_toutes qui aggrège par un UNION les données de
> doutes les tables. (cf UNION ALL si doublons possibles et qu'on veut les
> garder)
>
> --
> Jean-Paul Argudo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--

********************************************************************
* Valerie SCHNEIDER Tel : +33 (0)5 61 07 81 91 *
* METEO-FRANCE Fax : +33 (0)5 61 07 81 09 *
* DSI/DEV - Bases de donnees *
* 42, avenue G. Coriolis Email : Valerie(dot)Schneider(at)meteo(dot)fr *
* 31057 TOULOUSE Cedex - FRANCE http://www.meteo.fr *
********************************************************************
* L'information contenu dans ce mail n'a aucun caractere officiel *
********************************************************************