PostgreSQL: Diferença entre texto e varchar (caracterização variável)

Qual é a diferença entre o tipo de dados de text e os tipos de dados character varying ( varchar )?

De acordo com a documentação

Se a variação de caracteres for usada sem o especificador de comprimento, o tipo aceita cadeias de qualquer tamanho. O último é uma extensão do PostgreSQL.

e

Além disso, o PostgreSQL fornece o tipo de texto, que armazena strings de qualquer tamanho. Embora o texto do tipo não esteja no padrão SQL, vários outros sistemas de gerenciamento de database SQL também o possuem.

Então qual a diferença?

Não há diferença, sob o capô é tudo varlena ( matriz de comprimento variável ).

Veja este artigo em Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Alguns destaques:

Para resumir tudo:

  • char (n) – ocupa muito espaço quando se lida com valores menores que n (preenche-os em n ), e pode levar a erros sutis devido à adição de espaços à direita, além de ser problemático alterar o limite
  • varchar (n) – é problemático alterar o limite no ambiente ativo (requer bloqueio exclusivo ao alterar a tabela)
  • varchar – assim como o texto
  • texto – para mim um vencedor – sobre (n) tipos de dados porque não tem seus problemas, e sobre varchar – porque tem nome distinto

O artigo faz testes detalhados para mostrar que o desempenho de inserções e seleções para todos os 4 tipos de dados é semelhante. Ele também analisa detalhadamente maneiras alternativas de restringir o comprimento quando necessário. Restrições baseadas em function ou domínios fornecem a vantagem do aumento instantâneo da restrição de comprimento, e com base no fato de que diminuir uma restrição de comprimento de string é rara, depesz conclui que uma delas é geralmente a melhor escolha para um limite de comprimento.

Como ” Tipos de caracteres ” na documentação indica, varchar(n) , char(n) e text são todos armazenados da mesma maneira. A única diferença é que são necessários ciclos extras para verificar o comprimento, se um for dado, e o espaço extra e o tempo necessários para preencher o preenchimento char(n) .

No entanto, quando você precisa apenas armazenar um único caractere, existe uma pequena vantagem no desempenho ao usar o tipo especial "char" (manter as aspas duplas – elas fazem parte do nome do tipo). Você obtém access mais rápido ao campo e não há sobrecarga para armazenar o tamanho.

Acabei de fazer uma tabela de 1.000.000 "char" aleatória escolhida do alfabeto minúsculo. Uma consulta para obter uma distribuição de freqüência ( select count(*), field ... group by field ) leva cerca de 650 milissegundos, contra cerca de 760 nos mesmos dados usando um campo de text .

ATUALIZANDO BENCHMARKS PARA 2016 (pg9.5 +)

E usando benchmarks “pure SQL” (sem qualquer script externo)

  1. use qualquer string_generator com UTF8

  2. principais benchmarks:

    2.1. INSERIR

    2.2. SELECT comparando e contando


 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; 

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

Faça 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 use EXPLAIN ANALYZE .

ATUALIZADO NOVAMENTE 2018 (pg10)

pouco edite para adicionar os resultados do 2018 e reforçar as recomendações.


Resultados em 2016 e 2018

Meus resultados, depois da média, em muitas máquinas e muitos testes: todos iguais
(estatisticamente menos que o desvio padrão).

Recomendação

  • Use o tipo de dados de text ,
    evite varchar(x) antigo porque às vezes não é um padrão, por exemplo, nas cláusulas CREATE FUNCTION varchar(x) varchar(y) .

  • limite expresso (com o mesmo desempenho varchar !) com a cláusula CHECK no CREATE TABLE
    Por exemplo, CHECK(char_length(x)< =10) .
    Com uma perda insignificante de desempenho em INSERT / UPDATE, você também pode controlar intervalos e estrutura de string
    ex. CHECK(char_length(x)>5 AND char_length(x)< =20 AND x LIKE 'Hello%')

No manual do PostgreSQL

Não há diferença de desempenho entre esses três tipos, além de aumentar o espaço de armazenamento ao usar o tipo de preenchimento em branco e alguns ciclos de CPU extras para verificar o tamanho ao armazenar em uma coluna restrita por comprimento. Enquanto o caractere (n) tem vantagens de desempenho em alguns outros sistemas de database, não há tal vantagem no PostgreSQL; de fato, o caractere (n) é geralmente o mais lento dos três devido a seus custos adicionais de armazenamento. Na maioria das situações, deve-se usar texto ou caractere variável.

Eu costumo usar texto

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

text e varchar têm conversões de tipos implícitos diferentes. O maior impacto que eu notei é o manuseio de espaços à direita. Por exemplo …

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

retorna true, false, true e não true, true, true como você poderia esperar.

Na minha opinião, varchar(n) tem suas próprias vantagens. Sim, todos eles usam o mesmo tipo subjacente e tudo isso. Mas, deve-se salientar que os índices no PostgreSQL têm seu limite de tamanho de 2712 bytes por linha.

TL: DR: Se você usar o tipo de text sem restrição e tiver índices nessas colunas, é muito possível que você atinja esse limite para algumas de suas colunas e obtenha um erro ao tentar inserir dados, mas usando varchar(n) , você pode evitar isso.

Mais alguns detalhes: O problema aqui é que o PostgreSQL não dá nenhuma exceção ao criar índices para o tipo de text ou varchar(n) onde n é maior que 2712. No entanto, ele dará erro quando um registro com tamanho compactado maior que 2712 é tentado ser inserido. Isso significa que você pode inserir 100.000 caracteres de string que são compostos por caracteres repetitivos facilmente porque serão compactados muito abaixo de 2712, mas você pode não conseguir inserir uma string com 4000 caracteres porque o tamanho compactado é maior que 2712 bytes. Usando varchar(n) onde n não é muito maior que 2712, você está seguro desses erros.

Um pouco OT: Se você estiver usando o Rails, a formatação padrão de páginas da Web pode ser diferente. Para formulários de input de dados, as checkboxs de text são roláveis, mas as checkboxs de character varying ( string Rails) são de uma linha. As exibições de exibição são o tempo necessário.

character varying(n) , varchar(n) – (ambos iguais). O valor será truncado para n caracteres sem gerar um erro.

character(n) , char(n) – (ambos iguais). de comprimento fixo e irá 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 

Nós obtemos os resultados:

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