Discussion :

[punkoff]

Bonjour,


Je me posais des questions concernant la maintenance des tables d'une base pgsql.


Et plus en particulier sur les manières de gérer l'espace / la ré-organisation des tables / index.


Prenons une table avec 3.000.000 de lignes assez simple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
create table t_telephone_tel (
cli_id references t_client_cli (cli_id),
ttl_id references t_tel_type_ttl (ttl_id),
tel_num varchar(10),
primary key (cli_id, ttl_id));
 
 
CREATE INDEX idx_tel_1
  ON t_telephone_tel
  USING btree
  (tel_num COLLATE pg_catalog."default" varchar_pattern_ops, cli_id );

Mon test est le suivant :
Suppression de 300.000 lignes
vacuum analyze de la table.

Insertion de 300.000 lignes =>
la table n'a pas grossis => les lignes supprimées ont été réutilisées.
Mon index idx_tel_1 n'a pas grossis (j'en déduis qu'il a bien été ré-indéxé ?)
Par contre la taille de ma primary key a presque doublée 64Mo=> 101Mo !


Comment celà ce fait ?
Quelle possibilité ai-je à ce stade là ? Ré-org mes tables selon un cluster ?

Code :

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

1
2
 
cluster ma_table using ma_pk

Merci.

[estofilo]

La commande CLUSTER réécrirait la table dans l'ordre de l'index, mais pas l'index lui-même.
Pour remettre l'index de la clef primaire à sa taille minimale (=supprimer les trous) il faudrait faudrait lancer un REINDEX dessus.

[punkoff]

pourtant le fait de faire

Code :

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

1
2
 
CLUSTER ma_table using ma_pk;

ré-org bien mon index sur la pk, il passe de 101Mo à 64Mo (taille initiale).


Mais sinon ok pour le REINDEX ca marche bien aussi, merci.


Restera le fait que je ne comprend pourquoi l'index associé à la pk grossis dans ce cas de figure alors que l'autre index lui ne bouge pas (tous les deux sont des btree pourtant)


edit :
Pour le cluster :
delete de 300k lignes.
pas de vacuum analyze.
cluster => la table et les index sont réorganisées.

J'en déduit qu'il fait un vacuum avant de réécrire la table + un reindex.

[estofilo]

Etonnant car je ne vois de mention de ça dans la doc de cluster. Si ça réécrit l'index on s'attendrait à ce que ça soit mentionné, je trouve. Ou alors j'ai pas lu assez attentivement.

Pour les index en b-tree dont l'un augmente et l'autre pas, l'explication est peut-être sur le contenu des données. En principe l'espace libre dans un b-tree a plus de chances d'être réutilisé si on insère les mêmes valeurs qu'avant. Si par contre on insère des valeurs totalement différentes (ce qui est le cas avec les clefs auto-incrémentées) les trous dans b-tree ont plus de chances de rester tels quels. Mais c'est juste une théorie je ne sais pas ce qu'elle vaut dans le cas présent.

[SQLpro]

Contrairement à Oracle ou MS SQL Server, la maintenance par VACUUM ne défragmente pas physiquement les données. C'est pourquoi il y a toujours une fragmentation résiduelle des tables.
Pour en avoir le cœur net, il suffit de recréer la table sous un autre nom et ajouter les données dedans, puis mesurer le volume de la table.
Cela fait partit des points négatifs de PostGreSQL....
Comme je l'indiquais dans ce post : http://www.developpez.net/forums/d11...s/#post6302371
A la suite de cet article : http://www.developpez.net/forums/d11...resql-ecueils/

A +

[estofilo]

En effet VACUUM ne défragmente pas, puisque c'est le rôle d'autres commandes que sont VACUUM FULL ou CLUSTER.

C'est sûr que si on se trompe de commande on n'obtient pas le résultat escompté, que ce soit sous postgresql ou n'importe quel autre SGBD.