Performance avec des varchar(255)

Version imprimable

17/02/2015, 09h33
joel74

Performance avec des varchar(255)

Bonjour,
je suis en train de traduire une base de données Sybase en Postgres. Dans cette base, il y a de nombreux champs déclarés en varchar(255) alors que le contenu des champs est à peine rempli. Pour moi, c'est de l'espace disque gaspillé et quelque part de la mémoire utilisée inutilement et des performances moindres.
Je n'ai pas réussi à convaincre l'équipe qui gère les applications liées à cette base du bienfait de réduire la capacité des varchar, je n'ai pas d'arguments sur le fait que Postgres serait plus performant.
Quelqu'un aurait-il une expérience là-dessus. Je n'ai rien vu dans la doc postgres.
merci

Joel
17/02/2015, 11h25
Waldar

Et bien vos collègues ont raison. En terme de stockage, que vous stockiez deux octets dans un varchar(2) ou un varchar(255), ça prend exactement la même place.
Percevez la taille maximale d'une colonne comme une contrainte plutôt que comme une caractéristique de stockage.
27/02/2015, 06h27
ericd69

Salut,

Dans PostgreSQL, tu peux mettre varchar tout court ou mieux text. Pour lui c'est la même chose. Si tu précises la taille, c'est que tu veux imposer la troncature de ta chaîne de caractères quand elle est atteinte.
Donc la question à te poser, c'est : est-ce que tu as besoin d'une limite de taille ou non pour ces valeurs ? Tu peux vérifier cela dans la doc, c'est marqué clairement dedans.
:zoubi:
27/02/2015, 09h27
joel74

Bonjour,
Pour moi, les réponses ne sont pas satisfaisantes. Si quelqu'un a des paragraphes précis de la doc postgres à me faire lire, il faudrait me les donner. La seule référence que j'ai trouvée dans la doc postgres 9.0 (chap 8.3 - Types caractères) ou c'est dit que pour les chaînes de grande taille, il vaut mieux utiliser le type text et qu'un varchar(5) est plus performant qu'un char(5).
Donc, je ne suis toujours pas convaincu qu'en mettant des varchar(255) (ou varchar tout court) partout, les performances de l'application ne soient pas impactées. De plus, la sémantique fonctionnelle associée aux champs disparaîtrait dans la base (ce qui moi me choque) et d'autre part on accède à la base soit par des requêtes JDBC, soit par Hibernate (je l'avais pas dit d'accord). Il y a bien quelque part une couche logicielle qui réserve de la mémoire pour récupérer ces fameux varchar(255).
Voila pourquoi je n'ai toujours pas compris, peut-être que l'impact des performances ne se situe-t-il pas au niveau de postgres lui-même, mais ailleurs ?
27/02/2015, 18h34
ericd69
Salut,

Tiens, le lien retrouvé dans google en mettant "PostgreSQL varchar text" ;)

Regarde plus particulièrement l'encadré nommé "tip"...

L'idée est simple dans PostgreSQL:
- text dans le cas général.
- varchar(n) quand tu veux limiter OBLIGATOIREMENT à une taille des valeurs maximale, pénalité de temps d'exécution lors de l'écriture ou la modification à cause de test sur la longueur et l'éventuelle troncature.
- char(n) idem que varchar(n) mais en plus il y a un padding à gauche éventuel pour combler jusqu'à n caractère avec des espaces donc plus grande pénalité de temps à l'écriture ou la modification et pénalité de place.
C'est dû à la façon dont ces types sont implémentés dans ce SGBD, en lecture ça ne change rien sauf à rentrer un texte de plus de 1000 caractères car là une partie est stockée dans la table (la partie indexable directement avec les réglages par défaut) et une autre à part dans un fichier auxiliaire, si je me rappelle bien :zoubi:

Dans tous les cas, je te conseille utf8...

Vu que tu convertis depuis un autre SGBD, lis bien les spécificités aussi pour les nombres, sous peine, là, de risquer quelques problèmes :roll:

Cordialement :)
28/02/2015, 19h27
SQLpro
Citation:

Envoyé par joel74

je suis en train de traduire une base de données Sybase en Postgres. Dans cette base, il y a de nombreux champs déclarés en varchar(255) alors que le contenu des champs est à peine rempli. Pour moi, c'est de l'espace disque gaspillé et quelque part de la mémoire utilisée inutilement et des performances moindres.

Commencez par lire l'article que j'ai écrit au sujet du stockage dans SQL Server : http://blog.developpez.com/sqlpro/fi...PostGreSQL.pdf

Vous y verrez que :
"Une colonne de type VARCHAR(n) coute 1 octet de plus que la longueur de la donnée stockée."
Bref, que vous aillez du VARCHAR(255) ou très adapté, cecl n'a aucune incidence en terme de performances pure...
En revanche cela peut en avoir en terme de qualité des données et cela peut donc rejaillir sur les performances du fait de la possibilité de saisir n'importe quoi... Un petit exemple. Si vous avez besoin de stocker une information dont la taille est fixe et complète (par exemple un n° de sécurité sociale, un n° de compte en banque, un code postal...) alors il est préférable de fixer le type avec un CHAR(n). Cela évitera une saisie anormale de caractères en trop. Plus généralement, si vous mettez n'importe quel type de données pour n'importe quel information, il ne faudra pas s'étonner que les utilisateurs y saisissent n'importe quoi.... Une exemple, j'ai vu un logiciel qui avait effectivement tout en VARCHAR(250) et dans certaines colonne faite pour un usage déterminé (par exemple NOM_MARITAL) on y stockait des informations de toutes natures n'ayant rien à y voir !!!

Le VARCHAR possède un avantage, celui de réduire le volume des données si les données sont de longueur aléatoires.

Mais il possède de nombreux inconvénients :
- La recherche d'une information dans une zone dont le commencement est toujours situé à un emplecment différent, oblige à un algorithme plus complexe qui si la donnée était toujours à la même place, ce qui est possible en utilisant du CHAR. Autrement dit, la recherche en VARCHAR est plus lente que la recherche en CHAR. Je vous rassure, la différence est minime... Mais PG n'est pas très optimisé pour ce genre de recherche (il oblige systématiquement à lire tous les octets de la ligne pour parvenir à lire les données de la bonne colonne, là ou d'autres SGBDR procèdent par "sauts" (SQL Server par exemple).
- Les lignes des tables, comme les lignes d'index se fragmentent rapidement du fait de l'usage du VARCHAR lorsqu'il y a des UPDATE... EN effet il n'est pas possible d'agrandir un emplacement ou l'on a stocké n caractères alors que la nouvelle valeur possède n + m caractères... Il faut alors la stocker ailleurs ce qui génère des trous.... Et là c'est bien plus grave, car les moyens de remédier à la fragmentation des tables et index est encore très rudimentaires dans PsotGreSQL...
- En sus et contrairement à Oracle ou SQL Server, PostGreSQL n'optimise pas le placement des types : dans SQL Server par exemple, tous les types de données fixe sont placés en tête du "slot de ligne" (c'est comme cela que l'on nomme un "enregistrement" car ce terme est impropre). Ce qui fait que si votre table possède du VARCHAR et du CHAR, l'accès aux données CHAR est immédiat. On sait toujours ou se trouve ladite colonne par rapport au début de la ligne... Les VARCHARs étant regroupés à la fin.
Pour votre information, ceci est décrit notamment aux pages 11, 12 de l'article cité.

A +
02/03/2015, 09h48
joel74

Bonjour,

Merci pour vos réponses bien utiles. Pouvez-vous me donner des explications sur les 2 points suivants :

Citation:

Dans tous les cas, je te conseille utf8...

Vu que tu convertis depuis un autre SGBD, lis bien les spécificités aussi pour les nombres, sous peine, là, de risquer quelques problèmes

Pour l'instant, j'avais traduit en LATIN1, UTF8 est-il mieux adapté ?

Pour les nombres : tinyint devient smallint
numeric(X) identitity devient serial (les clés étrangères pointant sur des serial deviennent bigint).

Pour les dates : tout est traduit en timestamp.

Merci.

Joel
02/03/2015, 18h05
ericd69

Salut,

Pour l'encodage de caractères, les différents ISO (comme latin 1, latin 2, latin 3, etc...) sont des sous-ensembles de valeurs choisies pour encoder de l’Unicode sur 1 à 2 octets maximum mais en s'interdisant de pouvoir prendre en compte tout une partie des caractères possibles.
UTF8 permet, lui, d'encoder toutes les caractères possibles d'Unicode sur 1 à 5 octets. En pratique l'implémentation se limite souvent à 4 octets vu que ça couvre toutes les langues vivantes et une partie des langues mortes. L'avantage d'UTF8 est que tu peux mixer des caractères latins, chinois, arabes, etc... dans la même chaîne de caractères. Cela peut s'avérer pratique dans tout un tas cas (citation dans une langue par exemple)... La majorité des langues sont codées sur 1 à 2 ou 3 octets/caractères, ce qui est le cas pour le français (2 maximum).
Du coup, l'UTF8 est potentiellement plus souple, ne prend pas plus de place et est souvent l'encodage par défaut de plein d'applications comme les navigateurs ou PostgreSQL (si tu as fais son installation tu as dû t'en rendre compte).

Pour les entiers, PostgreSQL n'utilise les types de bases du processeur (entiers signés/non signés en 8, 16, 32 ou 64 bits) mais seulement les entiers signés 16, 32 ou 64 bits. donc quand tu t'en sert comme identifiant tu perd 1 bit donc la moitié de l'espace de valeurs, vu qu'en général on n'utilise pas les valeurs négatives. Tu peux formellement te créer des types pour les entiers purement positifs mais comme ils seront basés sur les entiers signés tu auras le même champs de valeurs possibles. A noter que tu as aussi des types pour les intervalles de nombres. Tu as aussi smallserial selon les versions de PostgreSQL, serial et bigserial.

Pour les dates, tu as toutes une variété de type (GMT, GMT sans décalage, timestamp, etc...). Le transtypage est automatique depuis une chaîne texte.

Pour plus d'infos la doc est là.
03/03/2015, 08h13
joel74

Ok,

merci pour toutes ces réponses, je pense que l'on peut clore la discussion.

Cordialement

Joel
10/03/2015, 18h08
SQLpro

Citation:

Envoyé par ericd69

Du coup, l'UTF8 est potentiellement plus souple, ne prend pas plus de place et est souvent l'encodage par défaut de plein d'applications comme les navigateurs ou PostgreSQL (si tu as fais son installation tu as dû t'en rendre compte).

Désolé de vous contredire, mais aucun SGBDR ne stocke les données en UTF8... En effet ceci n'est pas compatible avec l'indexation... Tous ces SGBDR font croire qu'ils stockent en UTF8 mais ce n'est pas vrai. Ils restituent en UTF8 ce qui n'est pas la même chose... et une collation n'est pas un jeu de caractères !
Pour le stockage, tous les SGBDR utilisent un format interne qui est sur un pas fixe (2 octets en général, 3 pour MySQLmerde...).

A +
15/03/2015, 04h45
ericd69
Je n'ai jamais dit qu'un charset est une collation... je ne sais pas ou tu as vu que je disais ça vu que tu fais une citation sur ce que je dis...:aie:
ou alors tu l'as inclus sur ce que je disais, bref pas grave :zoubi:

Le charset (ISO-8859-xx, UTF-8, Unicode, etc.) est le codage des caractères.

La collation est un ensemble de règles d'ordonnancement des caractères d'un charset en tenant compte de celles utilisées dans une ou plusieurs langues. Dans PostgreSQL, la collation est par charset et locale (règles linguistiques pour une langue données) et la collation la plus simple est la collation "C" qui se contente de classer les caractères par valeur croissante....

Ce que tu dis sur le format interne est vrai... et faux... en fait, ils se contentent de convertir la séquence de 1 à 2 octets qui sert à encoder presque tous les charsets en la convertissant en un nombre 16 bits... utiliser un nombre ou une séquence ne change rien en terme algorithmique mais implique juste un surcoût de temps de traitement pour la séquence.

Avantages:
- tu facilites l'implémentation de presque toutes les fonctions sur les chaines de caractères et tu les rends indépendantes de l'encodage.
- le principe des collations revient à des comparaison de nombre plutôt que de séquences, ce qui accélère les choses aussi...
Désavantage:
- pour les séquences ne tenant pas sur 16 bits comme l'UTF-8 (1 à 5 octets), tu corromps la valeur (vu qu'elle est tronquée violemment), ce qui peut être désastreux (scripts asiatiques non simplifiés, scripts de langues anciennes).
- pour les scripts de langues européennes, tu perds beaucoup de place vu que la majorité des lettres utilisées sont sur 1 octet.
j'ai longuement cherché dans la doc de PostgreSQL, ils stipulent que tout est stocké en UTF-8 (1 à 5 octets) sans "l'optimisation" dont tu parles, ce qui n'est pas impossible vu que les bibliothèque c pour l'UTF-8 sont très optimisées depuis longtemps... maintenant si tu as des billes vérifiables (vu que dans les autres SGBD, c'est clairement dit) qui disent le contraire pour PostgreSQL n'hésite pas à partager...
:P

Dans tous les cas le texte, est toujours stocké dans son encodage. Ce qui explique que tu dois explicitement transcoder un texte de son encodage vers celui d'un autre s'ils n'ont pas le même, pour des opérations sur les 2...

cordialement
18/03/2015, 21h32
SQLpro

Citation:

Envoyé par ericd69

j'ai longuement cherché dans la doc de PostgreSQL, ils stipulent que tout est stocké en UTF-8 (1 à 5 octets) sans "l'optimisation" dont tu parles, ce qui n'est pas impossible vu que les bibliothèque c pour l'UTF-8 sont très optimisées depuis longtemps... maintenant si tu as des billes vérifiables (vu que dans les autres SGBD, c'est clairement dit) qui disent le contraire pour PostgreSQL n'hésite pas à partager...

C'est déjà fait depuis longtemps... Lit mon article en particulier les pages 11 et 12

http://blog.developpez.com/sqlpro/fi...PostGreSQL.pdf

A