PostgreSQL: Diferencia entre texto y varchar (variación de caracteres)

Como "Tipos de caracteres " en la documentación señala, varchar(n), char(n), y text se almacenan de la misma manera. La única diferencia es que se necesitan ciclos adicionales para verificar la longitud, si se da uno, y el espacio y tiempo adicionales requeridos si se necesita relleno para char(n).

Sin embargo, cuando solo necesita almacenar un solo carácter, hay una ligera ventaja de rendimiento al usar el tipo especial "char" (mantenga las comillas dobles-son parte del nombre del tipo). Te vuelves más rápido acceso al campo, y no hay sobrecarga para almacenar la longitud.

Acabo de hacer una tabla de 1.000.000 aleatorios "char" elegidos del alfabeto en minúsculas. Una consulta para obtener una distribución de frecuencia (select count(*), field ... group by field) tarda unos 650 milisegundos, frente a unos 760 en los mismos datos utilizando un campo text.

ACTUALIZACIÓN DE LOS PARÁMETROS DE REFERENCIA PARA 2016 (pg9.5+)

Y usando benchmarks "pure SQL" (sin ningún script externo)

1. Usar cualquier string_generator con UTF8

2. Puntos de referencia principales:

   2.1. INSÉRTESE

   2.2. SELECCIONE comparar y contar

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,$1)||chr( 9824 + (random()*10)::int )
  ), ' ' ) as s
  FROM generate_series(1, $2) i(x);
$f$ LANGUAGE SQL IMMUTABLE;

Preparar una prueba específica (ejemplos)

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) );

Realizar una prueba básica:

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

Y otras pruebas,

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;

... Y utilizar EXPLAIN ANALYZE.

ACTUALIZADO DE NUEVO 2018 (pg10)

Poca edición para agregar los resultados de 2018 y reforzar las recomendaciones.

Resultados en 2016 y 2018

Mis resultados, después de la media, en muchas máquinas y muchas pruebas: todos iguales
(estadísticamente menos desviación estándar tham).

Recomendación

• Use text datatype,
  evite old varchar(x) porque a veces no es un estándar, por ejemplo, en las cláusulas CREATE FUNCTION varchar(x)≠varchar(y).

• Límites expresos(con el mismo rendimiento varchar!) by with CHECK clause in the CREATE TABLE
  por ejemplo, CHECK(char_length(x)<=10).
  Con una pérdida insignificante de rendimiento en INSERT/UPDATE también puede controlar rangos y estructura de cadenas
  e. g.CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

En el manual de PostgreSQL

No hay diferencia de rendimiento entre estos tres tipos, aparte de un mayor espacio de almacenamiento cuando se utiliza el tipo acolchado en blanco, y algunos ciclos de CPU adicionales para comprobar la longitud cuando se almacena en una columna de longitud limitada. Mientras que character(n) tiene ventajas de rendimiento en algunos otros sistemas de bases de datos, no hay tal ventaja en PostgreSQL; de hecho, character (n) es generalmente el más lento de los tres debido a sus costos de almacenamiento adicionales. En la mayoría de las situaciones en su lugar, se deben usar variaciones de texto o caracteres.

Normalmente uso texto

Referencias: http://www.postgresql.org/docs/current/static/datatype-character.html

Text y varchar tienen diferentes conversiones de tipo implícito. El mayor impacto que he notado es el manejo de los espacios finales. Por ejemplo ...

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

Devuelve true, false, true y no true, true, true como es de esperar.

En mi opinión, varchar(n) tiene sus propias ventajas. Sí, todos usan el mismo tipo subyacente y todo eso. Pero, debe señalarse que los índices en PostgreSQL tienen su límite de tamaño de 2712 bytes por fila.

TL; DR: Si utiliza text type sin una restricción y tiene índices en estas columnas, es muy posible que alcance este límite para algunas de sus columnas y obtenga un error cuando intente insertar datos, pero con el uso de varchar(n), puede evitar se.

Algunos más detalles: El problema aquí es que PostgreSQL no da ninguna excepción al crear índices para el tipo text o varchar(n) donde n es mayor que 2712. Sin embargo, dará error cuando se intente insertar un registro con un tamaño comprimido superior a 2712. Significa que puede insertar 100.000 caracteres de cadena que se compone de caracteres repetitivos fácilmente porque se comprimirá muy por debajo de 2712, pero es posible que no pueda insertar alguna cadena con 4000 caracteres porque el tamaño comprimido es mayor que 2712 bytes. Usando varchar(n) donde n no es demasiado mayor que 2712, estás a salvo de estos errores.

Algo OT: Si está utilizando Rails, el formato estándar de las páginas web puede ser diferente. Para los formularios de entrada de datos, las casillas text son desplazables, pero las casillas character varying (Rails string) son de una línea. Mostrar vistas son el tiempo que sea necesario.

character varying(n), varchar(n) - (Ambos iguales). el valor se truncará a n caracteres sin generar un error.

character(n), char(n) - (Ambos iguales). longitud fija y se rellenará con espacios en blanco hasta el final de la longitud.

text - Longitud ilimitada.

Ejemplo:

Table test:
   a character(7)
   b varchar(7)

insert "ok    " to a
insert "ok    " to b

Obtenemos los resultados:

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