PostgreSQL: différence entre le texte et varchar (variation de caractères)

comme types de caractères " dans la documentation indique, 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 nécessaires si un rembourrage est nécessaire pour char(n) .

cependant, lorsque vous n'avez besoin de stocker qu'un seul caractère, il y a un léger avantage de performance à utiliser le type spécial "char" (gardez les guillemets-ils font partie du nom de type). Vous obtenez un accès plus rapide au champ, et il n'y a pas de frais généraux pour stocker la longueur.

je viens de faire une table de 1.000.000 aléatoire "char" choisi à partir de l'alphabet minuscule. Une requête pour obtenir une distribution 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 text .

UPDATING BENCHMARKS FOR 2016 (pg9.5+)

et en utilisant des benchmarks " pure SQL "(sans aucun script externe)

1. utilisez n'importe quel string_generator avec UTF8

2. principaux critères de référence:

   2.1. Ajouter

   2.2. Choisir comparer et Compter

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

Préparer les épreuves spécifiques (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 essai de base:

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

et autres essais,

CREATE INDEX q on test (f);

SELECT count(*) FROM (
  SELECT substring(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000
) t;

... Et utilisez EXPLAIN ANALYZE .

mise à jour 2018 (pg10)

peu modifier pour ajouter 2018 résultats et de renforcer les recommandations.

résultats en 2016 et 2018

mes résultats, après moyenne, dans beaucoup de machines et beaucoup de tests: tout de même

(l'écart-type tham est statistiquement inférieur).

recommandation

• utiliser text type de données,
  
  éviter l'ancienne varchar(x) parce que parfois ce n'est pas une norme, p.ex. dans CREATE FUNCTION clauses varchar(x) ≠ varchar(y) .

• express limites (avec les mêmes varchar la performance!) avec CHECK clause de la 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 gammes et la structure de chaîne de caractères
  
  e.g. CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

Sur PostgreSQL manuel

Il n'y a pas de différence de performance entre ces trois types, à part un espace de stockage accru lors de l'utilisation du type à blanc rembourré, et quelques cycles CPU supplémentaires pour vérifier la longueur lors de l'entreposage dans une colonne de longueur limitée. Alors que character (n) a des avantages de performance dans certains autres systèmes de base de données, il n'y a pas d'avantage dans PostgreSQL; en fait character (n) est généralement le plus lent des trois en raison de son stockage supplémentaire coût. Dans la plupart des cas, il est préférable d'utiliser un texte ou un caractère différent.

j'utilise habituellement du texte

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

text et varchar ont des conversions implicites de type différentes. Le plus grand impact que j'ai remarqué est la manipulation des espaces de fuite. Exemple. ..

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

retourne true, false, true et non true, true, true comme vous pourriez vous attendre.

À mon avis, varchar(n) a ses propres avantages. Oui, ils utilisent tous le même type sous-jacent et tout ça. Mais, il faut souligner que les indices dans PostgreSQL a sa limite de taille de 2712 octets par rangée.

TL; DR: Si vous utilisez text type sans contrainte et avez des index sur ces colonnes, il est très possible que vous ayez atteint cette limite pour certaines de vos colonnes et obtenir l'erreur quand vous essayez d'insérer des données , mais avec l'aide varchar(n) , vous pouvez l'empêcher.

plus de détails: le problème ici est que PostgreSQL ne donne aucune exception lors de la création des index pour le type text ou varchar(n) où n est supérieur à 2712. Cependant, cela donnera une erreur quand un enregistrement avec une taille compressée de plus de 2712 est essayé d'être inséré. Cela signifie que vous pouvez insérer 100.000 caractère de chaîne qui est composée par des caractères répétitifs facilement parce qu'il sera comprimé bien en dessous de 2712 mais vous ne pouvez pas être en mesure d'insérer une certaine chaîne avec 4000 caractères parce que la taille comprimée est supérieure à 2712 octets. En utilisant varchar(n) où n n'est pas trop supérieur à 2712, vous êtes à l'abri de ces erreurs.

un peu OT: si vous utilisez des Rails, le formatage standard des pages web peut être différent. Pour les formulaires de saisie de données, les boîtes text sont déroulables, alors que les boîtes character varying (Rails string ) sont à une ligne. Afficher les points de vue sont aussi longtemps que nécessaire.

character varying(n) , varchar(n) - (les deux mêmes). la valeur est tronquée à n caractères sans générer une erreur.



character(n) , char(n) - (les deux mêmes). longueur fixe et garniture 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