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.

[SQLpro]

CLUSTER n'est pas à proprement parler une commande de défragmentation, mais d'organisation des lignes d'une table sous forme de CLUSTER (tri physique des lignes) ce qui libère les espaces mort des lignes, la table pouvant être à nouveau fragmentée par de nouvelles mises à jour. CLUSTER nécessite un verrou exclusif pendant toute l'opération.
Cela correspond à la création d'un index CLUSTER puis sa destruction pour MS SQL Server.
VACUUM FULL reconstruit la table et permet de libérer les espaces morts. Il ne concerne pas les index et nécessite un verrou exclusif pendant toute l'opération.
Cela correspond à DBCC CLEANTABLE sous MS SQL Server.
Pour défragmenter un index sous PG il faut le reconstruire. C'est à nouveau une opération bloquante (verrou exclusif).

Il n'existe pas d'opération de défragmentation sous PostGreSQL comme :

• ALTER INDEX ... REORGANIZE
• DBCC INDEXDEFRAG ....

qui permettent de défragmenter les index ou les tables sans poser de verrou...

Comme je l'ai dit, cela fait partit des limitations de PG.

A +

[scheu]

J'ai également le même problème d'index qui grossit dans une table à nombre de lignes constant mais dans laquelle je fais des delete puis insert tous les jours
J'ai dû mettre en place un reindex, mais qui a pour contrainte de locker la table le temps de l'opération (1h30 quand-même !)

Sous Postgresql actuellement il est possible de "défragmenter" un index sans verrou sur la table avec l'option CREATE INDEX CONCURRENTLY. En gros on créé un 2ème index, sans locker la table, sur les mêmes colonnes, puis on supprime l'ancien et on renomme le nouveau

Mais pour les indexes des PK ce n'est pas encore possible, dans la version actuelle de Postgresql (9.1), de recréer une PK avec "using index" pour spécifier un index déjà existant qu'on aurait créé avec l'option "concurrently"

C'est donc une contrainte de la version actuelle, le seul moyen étant de planifier la tâche de maintenance (le reindex par exemple) dans un créneau horaire où la table n'est pas utilisée

Pour le VACUUM FULL, c'est aussi contraignant car il locke la table le temps de l'opération. Dans les versions 8.x c'était tellement long qu'un export/import de la table était plus rapide. Là dans les dernières versions c'est apparemment plus rapide d'après la doc (je n'ai pas testé personnellement).
A toi de voir en fonction du temps que ça prend sur ta table si c'est acceptable ou pas et si tu arrives à trouver un créneau horaire pour le faire, sans que personne n'accède à la table

[punkoff]

J'ai également le même problème d'index qui grossit dans une table à nombre de lignes constant mais dans laquelle je fais des delete puis insert tous les jours
J'ai dû mettre en place un reindex, mais qui a pour contrainte de locker la table le temps de l'opération (1h30 quand-même !)

Sous Postgresql actuellement il est possible de "défragmenter" un index sans verrou sur la table avec l'option CREATE INDEX CONCURRENTLY. En gros on créé un 2ème index, sans locker la table, sur les mêmes colonnes, puis on supprime l'ancien et on renomme le nouveau

Mais pour les indexes des PK ce n'est pas encore possible, dans la version actuelle de Postgresql (9.1), de recréer une PK avec "using index" pour spécifier un index déjà existant qu'on aurait créé avec l'option "concurrently"

C'est donc une contrainte de la version actuelle, le seul moyen étant de planifier la tâche de maintenance (le reindex par exemple) dans un créneau horaire où la table n'est pas utilisée

Pour le VACUUM FULL, c'est aussi contraignant car il locke la table le temps de l'opération. Dans les versions 8.x c'était tellement long qu'un export/import de la table était plus rapide. Là dans les dernières versions c'est apparemment plus rapide d'après la doc (je n'ai pas testé personnellement).
A toi de voir en fonction du temps que ça prend sur ta table si c'est acceptable ou pas et si tu arrives à trouver un créneau horaire pour le faire, sans que personne n'accède à la table

Bleh, j'ai pas testé avec des volumes plus concéquent (combien de Go ta table ?)

Du coup, duplication de la dites table c'est pas plus rapide ?
Et as-tu testé avec l'option cluster ?

[scheu]

20 Go ma table
La duplication de table ou l'export/import c'est faisable, mais c'est long et encore faut-il que ta table ne soit pas utilisée pendant l'opération, ce qui n'est pas toujours le cas

C'est notamment une des limitations actuelles de Postgresql qui me fait penser que pour les grosses volumétries et haute-dispo (tables dispo 24h/24 et sans verrous), je conseille un SGBD payant comme Oracle ou SQL Server