PostgreSQL: Différence entre text et varchar (caractère variable)

Quelle est la différence entre le type de données text et les types de données character varying ( varchar )?

Selon la documentation

Si un caractère variable est utilisé sans spécificateur de longueur, le type accepte les chaînes de toutes tailles. Ce dernier est une extension PostgreSQL.

et

En outre, PostgreSQL ™ fournit le type de texte, qui stocke les chaînes de n’importe quelle longueur. Bien que le texte de type ne figure pas dans le standard SQL, plusieurs autres systèmes de gestion de firebase database SQL l’ont également.

Alors, quelle est la différence?

Il n’y a pas de différence, sous le capot, tout est varlena ( tableau de longueur variable ).

Consultez cet article de Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Quelques points forts:

Pour tout résumer:

  • char (n) – prend trop de place lorsqu’il s’agit de valeurs plus courtes que n (les met à n ), et peut entraîner des erreurs subtiles en raison de l’ajout d’espaces de fin, et il est problématique de modifier la limite
  • varchar (n) – il est difficile de modifier la limite dans un environnement en direct (nécessite un locking exclusif lors de la modification de la table)
  • varchar – comme du texte
  • text – pour moi un gagnant – sur (n) des types de données car il n’a pas de problèmes, et sur varchar – parce qu’il a un nom distinct

L’article effectue des tests détaillés pour montrer que les performances des insertions et des sélections pour les 4 types de données sont similaires. Il examine également en détail les différentes manières de limiter la longueur au besoin. Les contraintes ou les domaines basés sur les fonctions offrent l’avantage d’une augmentation instantanée de la contrainte de longueur et, comme la réduction d’une contrainte de longueur de chaîne est rare, depesz en conclut que l’un d’entre eux est généralement le meilleur choix pour une limite de longueur.

Comme l’ indiquent les ” Types de caractères ” dans la documentation, varchar(n) , char(n) et text sont tous stockés de la même manière. La seule différence est que des cycles supplémentaires sont nécessaires pour vérifier la longueur, s’il y en a un, et l’espace et le temps supplémentaires requirejs si un remplissage est nécessaire pour le caractère char(n) .

Cependant, lorsque vous ne devez stocker qu’un seul caractère, l’utilisation du type spécial "char" présente un léger avantage (conservez les guillemets doubles – ils font partie du nom du type). Vous obtenez un access plus rapide au champ, et il n’y a pas de frais généraux pour stocker la longueur.

Je viens de créer un tableau de 1 000 000 de caractères aléatoires choisis dans l’alphabet minuscule. Une requête pour obtenir une dissortingbution de fréquence ( select count(*), field ... group by field ) prend environ 650 millisecondes, contre environ 760 sur les mêmes données en utilisant un champ de text .

MISE À JOUR DES BENCHMARKS 2016 (p. 9.5 +)

Et utiliser des tests “purs SQL” (sans script externe)

  1. utiliser n’importe quel ssortingng_generator avec UTF8

  2. principaux repères:

    2.1. INSÉRER

    2.2. SELECT compare et compte


 CREATE FUNCTION ssortingng_generator(int DEFAULT 20,int DEFAULT 10) RETURNS text AS $f$ SELECT array_to_ssortingng( array_agg( subssortingng(md5(random()::text),1,$1)||chr( 9824 + (random()*10)::int ) ), ' ' ) as s FROM generate_series(1, $2) i(x); $f$ LANGUAGE SQL IMMUTABLE; 

Préparer un test spécifique (exemples)

 DROP TABLE IF EXISTS test; -- CREATE TABLE test ( f varchar(500)); -- CREATE TABLE test ( f text); CREATE TABLE test ( f text CHECK(char_length(f)<=500) ); 

Effectuer un test de base:

 INSERT INTO test SELECT ssortingng_generator(20+(random()*(i%11))::int) FROM generate_series(1, 99000) t(i); 

Et d'autres tests,

 CREATE INDEX q on test (f); SELECT count(*) FROM ( SELECT subssortingng(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000 ) t; 

... Et utilisez EXPLAIN ANALYZE .

MISE À JOUR EN 2018 (pg10)

petite modification pour append les résultats de 2018 et renforcer les recommandations.


Résultats en 2016 et 2018

Mes résultats, après moyenne, dans de nombreuses machines et dans de nombreux tests: tout de même
(statistiquement moins que l'écart type).

Recommandation

  • Utilisez le type de données text ,
    évitez l'ancien varchar(x) car parfois ce n'est pas un standard, par exemple dans les clauses CREATE FUNCTION varchar(x) varchar(y) .

  • exprimer les limites (avec les mêmes performances varchar !) avec la clause CHECK dans le CREATE TABLE
    par exemple, CHECK(char_length(x)<=10) .
    Avec une perte de performance négligeable dans INSERT / UPDATE, vous pouvez également contrôler les plages et la structure des chaînes
    p.ex. CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

Sur le manuel PostgreSQL

Il n’y a pas de différence de performance entre ces trois types, à part l’augmentation de l’espace de stockage lors de l’utilisation du type à remplissage vierge et quelques cycles CPU supplémentaires pour vérifier la longueur lors du stockage dans une colonne à contrainte de longueur. Bien que character (n) présente des avantages en termes de performances dans certains autres systèmes de bases de données, PostgreSQL ™ ne présente pas un tel avantage. en fait, le caractère (n) est généralement le plus lent des trois en raison de ses coûts de stockage supplémentaires. Dans la plupart des situations, le texte ou les caractères doivent être utilisés à la place.

J’utilise habituellement du texte

Références: http://www.postgresql.org/docs/current/static/datatype-character.html

text et varchar ont des conversions de types implicites différentes. Le plus gros impact que j’ai remarqué est la gestion des espaces de fuite. Par exemple …

 select ' '::char = ' '::varchar, ' '::char = ' '::text, ' '::varchar = ' '::text 

renvoie true, false, true et not true, true, true comme prévu.

À mon avis, varchar(n) a ses propres avantages. Oui, ils utilisent tous le même type sous-jacent et tout ça. Mais, il convient de souligner que les index dans PostgreSQL ™ ont une taille limite de 2712 octets par ligne.

TL; DR: Si vous utilisez un type de text sans contrainte et des index sur ces colonnes, il est très possible que vous atteigniez cette limite pour certaines de vos colonnes et que vous obteniez des erreurs lorsque vous essayiez d’insérer des données, mais en utilisant varchar(n) . vous pouvez l’empêcher.

Quelques détails supplémentaires: Le problème ici est que PostgreSQL ne donne pas d’exceptions lors de la création d’index pour le type de text ou varchar(n)n est supérieur à 2712. Cependant, il donnera une erreur quand un enregistrement avec une taille compressée supérieure à 2712 est essayé d’être inséré. Cela signifie que vous pouvez insérer facilement 100 000 caractères de chaîne composés de caractères répétitifs, car ils seront compressés bien au-dessous de 2712, mais vous ne pourrez peut-être pas insérer de chaîne de 4 000 caractères car la taille compressée est supérieure à 2712 octets. En utilisant varchar(n)n n’est pas trop supérieur à 2712, vous êtes à l’abri de ces erreurs.

Plutôt OT: si vous utilisez Rails, le format standard des pages Web peut être différent. Pour les formulaires de saisie de données, les zones de text sont défilables, mais les zones de character varying ( ssortingng Rails) sont à une ligne. Afficher les vues est aussi longue que nécessaire.

character varying(n) , varchar(n) – (les deux identiques). La valeur sera tronquée à n caractères sans générer d’erreur.

character(n) , char(n) – (les deux sont identiques). longueur fixe et va couvrir avec des blancs jusqu’à la fin de la longueur.

text – Longueur illimitée.

Exemple:

 Table test: a character(7) b varchar(7) insert "ok " to a insert "ok " to b 

Nous obtenons les résultats:

 a | (a)char_length | b | (b)char_length ----------+----------------+-------+---------------- "ok "| 7 | "ok" | 2