PostgreSQL: diferencia entre texto y varchar (carácter variable)

¿Cuál es la diferencia entre el tipo de datos de text y los tipos de datos que character varying ( varchar )?

De acuerdo con la documentación

Si se utiliza la variación de caracteres sin el especificador de longitud, el tipo acepta cadenas de cualquier tamaño. Esta última es una extensión de PostgreSQL.

y

Además, PostgreSQL proporciona el tipo de texto, que almacena cadenas de cualquier longitud. Aunque el texto tipo no está en el estándar SQL, también lo tienen otros sistemas de administración de bases de datos SQL.

Entonces, ¿cuál es la diferencia?

No hay diferencia, debajo del capó todo es varlena ( conjunto de longitud variable ).

Consulta este artículo de Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Un par de aspectos destacados:

Para resumir todo esto:

  • char (n): ocupa demasiado espacio cuando se trata de valores menores que n (los ajusta a n ), y puede dar lugar a errores sutiles debido a la adición de espacios finales, además de que es problemático cambiar el límite
  • varchar (n): es problemático cambiar el límite en el entorno en vivo (requiere locking exclusivo mientras se altera la tabla)
  • varchar – al igual que el texto
  • texto – para mí un ganador – sobre (n) tipos de datos porque carece de sus problemas, y sobre varchar – porque tiene un nombre distinto

El artículo realiza pruebas detalladas para mostrar que el rendimiento de las inserciones y selecciones para los 4 tipos de datos es similar. También se analiza detalladamente formas alternativas de restringir la longitud cuando sea necesario. Las restricciones basadas en funciones o dominios proporcionan la ventaja del aumento instantáneo de la restricción de longitud, y sobre la base de que la disminución de una restricción de longitud de cadena es rara, depesz concluye que una de ellas suele ser la mejor opción para un límite de longitud.

Como señala ” Tipos de caracteres ” en la documentación, varchar(n) , char(n) y el text se almacenan de la misma manera. La única diferencia es que se necesitan ciclos adicionales para verificar la longitud, si se da una, y el espacio extra y el tiempo requerido 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). Obtienes un acceso más rápido al campo y no hay sobrecarga para almacenar la longitud.

Acabo de hacer una tabla de 1,000,000 de caracteres aleatorios elegidos del alfabeto de minúsculas. Una consulta para obtener una distribución de frecuencia ( select count(*), field ... group by field ) toma alrededor de 650 milisegundos, frente a aproximadamente 760 en los mismos datos usando un campo de text .

ACTUALIZANDO LOS BENCHMARKS PARA 2016 (pg9.5 +)

Y utilizando puntos de referencia de “SQL puro” (sin ningún script externo)

  1. utiliza cualquier string_generator con UTF8

  2. principales puntos de referencia:

    2.1. INSERTAR

    2.2. SELECCIONAR 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 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) ); 

Realiza 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 usa EXPLAIN ANALYZE .

ACTUALIZADO OTRA VEZ 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: de todos modos
(estadísticamente menos desviación estándar tham).

Recomendación

  • Usar text tipo de datos,
    evite el viejo 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 !) con CHECK cláusula en CREATE TABLE
    por ejemplo, CHECK(char_length(x)< =10) .
    Con una pérdida insignificante de rendimiento en INSERT / UPDATE, también puede controlar los rangos y la estructura de la cadena
    por ejemplo 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 de relleno en blanco, y algunos ciclos de CPU adicionales para verificar la longitud cuando se almacena en una columna de longitud limitada. Si bien el carácter (n) tiene ventajas de rendimiento en algunos otros sistemas de bases de datos, no existe tal ventaja en PostgreSQL; de hecho, el carácter (n) suele ser el más lento de los tres debido a sus costos adicionales de almacenamiento. En la mayoría de las situaciones, se debe usar texto o caracteres en su lugar.

Usualmente 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 espacios finales. Por ejemplo …

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

devuelve true, false, true y no true, true, true como cabría esperar.

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

TL; DR: si utiliza el tipo de text sin restricción y tiene índices en estas columnas, es muy posible que llegue a este límite para algunas de sus columnas y obtenga un error cuando intente insertar datos pero con el uso de varchar(n) , puedes prevenirlo

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

Algo OT: si usa Rails, el formato estándar de las páginas web puede ser diferente. Para los formularios de entrada de datos, text cuadros de text son desplazables, pero los cuadros de character varying (Cadenas de Rails) son de una sola línea. Mostrar vistas son tan largas como sea necesario.

character varying(n) , varchar(n) – (Ambos lo mismo). el valor se truncará en n caracteres sin generar un error.

character(n) , char(n) – (Ambos son lo mismo). de 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