PostgreSQL: diferença entre texto e varchar (variação de caracteres)

Como "tipos de caracteres " na documentação indica, varchar(n), char(n), e {[2] } estão todos armazenados da mesma forma. A única diferença é que são necessários ciclos extras para verificar o comprimento, se for dado, e o espaço e tempo extra necessários se for necessário enchimento para char(n).

No entanto, quando você só precisa armazenar um único caractere, há uma pequena vantagem de desempenho para usar o tipo especial {[[4]} (Mantenha as aspas-elas fazem parte do nome do tipo). Você tem acesso mais rápido ao campo, e não há sobrecarga para armazenar o comprimento.

Acabei de fazer uma tabela de 1.000.000 aleatórios "char" escolhidos a partir do alfabeto de minúsculas. Uma consulta para obter uma distribuição de frequência (select count(*), field ... group by field) leva cerca de 650 milisegundos, vs cerca de 760 nos mesmos dados usando um campo text.

Actualização dos parâmetros de referência para 2016 (pg9.5+)

E utilizando parâmetros de referência" puros SQL " (sem qualquer script externo)

1. Usar qualquer string_ gerador com UTF8

2. Principais parâmetros de referência:

   2.1. Inserir

   2.2. Seleccione comparar e 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 ensaio específico (exemplos)

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

Efectuar um teste básico:

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

E outros testes,

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;

... E utilizar EXPLAIN ANALYZE.

Actualizado novamente 2018 (pg10)

Pequena edição para adicionar os resultados de 2018 e reforçar as recomendações.

Resultados em 2016 e 2018

Recomendação

• Use text datatype,
  evite old varchar(x) porque às vezes não é um padrão, por exemplo em CREATE FUNCTION cláusulas varchar(x)≠varchar(y).

• Limites expressos(com o mesmo desempenho!) com a Cláusula CHECK CREATE TABLE
  por exemplo CHECK(char_length(x)<=10).
  com uma perda negligenciável de desempenho em INSERT / UPDATE pode também controlar as gamas e a estrutura das cordas
  por exemplo:CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

No manual PostgreSQL

Não há diferença de desempenho entre estes três tipos, além do aumento do espaço de armazenamento ao usar o tipo acolchoado em branco, e alguns ciclos adicionais de CPU para verificar o comprimento ao armazenar em uma coluna com restrição de comprimento. Enquanto character(n) tem vantagens de desempenho em alguns outros sistemas de banco de dados, não há tal vantagem no PostgreSQL; na verdade, character (n) é geralmente o mais lento dos três por causa de seus custos adicionais de armazenamento. Na maioria situações texto ou caracteres variáveis devem ser usados em vez disso.

Normalmente uso texto

Referências: http://www.postgresql.org/docs/current/static/datatype-character.html

Texto e varchar têm diferentes conversões implícitas. O maior impacto que notei foi a manipulação de espaços de fuga. Por exemplo ...

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

Devolve true, false, true e não true, true, true como seria de esperar.

TL; DR: Se você usar text tipo sem restrição e tem índices nestas colunas, é muito possível que você atinge esse limite para algumas de suas colunas e obter o erro quando você tentar inserir dados, mas com o uso de varchar(n), você pode impedir ele.

Mais Alguns detalhes: O problema aqui é que o PostgreSQL não dá quaisquer exceções ao criar índices para text tipo ou varchar(n) onde n é maior que 2712. No entanto, ele vai dar erro quando um registro com tamanho comprimido superior a 2712 é tentado para ser inserido. Isso significa que você pode inserir 100.000 caracteres de cadeia que é composta por caracteres repetitivos facilmente porque ele será comprimido muito abaixo de 2712, mas você pode não ser capaz de inserir alguma cadeia de caracteres com 4000 caracteres porque o tamanho comprimido é superior a 2712 bytes. Usando varchar(n) onde n não é demasiado superior a 2712, estás a salvo destes erros.

character varying(n), varchar(n) - (ambos iguais). o valor será truncado para n caracteres sem criar um erro.

character(n), char(n) - (ambos iguais). comprimento fixo e vai preencher com espaços em branco até o final do comprimento.

text - comprimento ilimitado.

Exemplo:

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

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

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