PostgreSQL: Differenza tra testo e varchar (carattere che varia)

Qual è la differenza tra il tipo di dati di text e i tipi di dati character varying ( varchar )?

Secondo la documentazione

Se il carattere che varia è usato senza identificatore di lunghezza, il tipo accetta stringhe di qualsiasi dimensione. Quest’ultima è un’estensione PostgreSQL.

e

Inoltre, PostgreSQL fornisce il tipo di testo, che memorizza stringhe di qualsiasi lunghezza. Sebbene il testo del tipo non sia nello standard SQL, anche altri sistemi di gestione del database SQL lo possiedono.

Quindi qual è la differenza?

Non c’è differenza, sotto il cofano è tutto varlena ( array a lunghezza variabile ).

Dai un’occhiata a questo articolo di Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Un paio di punti salienti:

Per riassumere:

  • char (n) – occupa troppo spazio quando si tratta di valori più brevi di n (li aggiunge a n ) e può portare a errori sottili a causa dell’aggiunta di spazi finali, inoltre è problematico modificare il limite
  • varchar (n) – è problematico modificare il limite in ambiente live (richiede un blocco esclusivo mentre si altera la tabella)
  • varchar – proprio come il testo
  • testo – per me un vincitore – su (n) tipi di dati perché manca dei loro problemi, e su varchar – perché ha un nome distinto

L’articolo esegue test dettagliati per dimostrare che le prestazioni degli inserti e delle selezioni per tutti e 4 i tipi di dati sono simili. Ci vuole anche uno sguardo dettagliato a modi alternativi per limitare la lunghezza quando necessario. Vincoli o domini basati su funzioni forniscono il vantaggio dell’aumento istantaneo del vincolo di lunghezza, e sulla base del fatto che la riduzione di un vincolo di lunghezza della stringa è rara, Depesz conclude che uno di questi è solitamente la scelta migliore per un limite di lunghezza.

Come indica ” Tipi di caratteri ” nella documentazione, varchar(n) , char(n) e il text sono tutti memorizzati allo stesso modo. L’unica differenza è che sono necessari cicli extra per controllare la lunghezza, se ne viene fornita una, e lo spazio e il tempo extra necessari se il padding è necessario per char(n) .

Tuttavia, quando è necessario solo memorizzare un singolo carattere, c’è un leggero vantaggio in termini di prestazioni nell’usare il tipo speciale "char" (mantenere le doppie virgolette – fanno parte del nome del tipo). Ottieni un accesso più rapido al campo e non ci sono spese generali per archiviare la lunghezza.

Ho appena creato una tabella di 1.000.000 di "char" casuali scelti dall’alfabeto in minuscolo. Una query per ottenere una distribuzione di frequenza ( select count(*), field ... group by field ) impiega circa 650 millisecondi, contro circa 760 sugli stessi dati utilizzando un campo di text .

AGGIORNAMENTO DEL BANCO DI RIFERIMENTO PER IL 2016 (pag. 9.5 +)

E usando benchmark “pure SQL” (senza alcun script esterno)

  1. usa qualsiasi string_generator con UTF8

  2. principali parametri di riferimento:

    2.1. INSERIRE

    2.2. SELEZIONA il confronto e il conteggio


 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; 

Preparare un test specifico (esempi)

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

Esegui un test di base:

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

E altri test,

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

AGGIORNATO ANCORA 2018 (pg10)

piccola modifica per aggiungere i risultati del 2018 e rafforzare i consigli.


Risultati nel 2016 e 2018

I miei risultati, dopo la media, in molte macchine e molti test: tutti uguali
(statisticamente meno deviazione standard tham).

Raccomandazione

  • Usa il tipo di dati di text ,
    evita old varchar(x) perché a volte non è uno standard, ad esempio nelle clausole CREATE FUNCTION varchar(x) varchar(y) .

  • limiti espressi (con le stesse prestazioni varchar !) con la clausola CHECK nella CREATE TABLE
    ad esempio CHECK(char_length(x)<=10) .
    Con una perdita di prestazioni trascurabile in INSERT / UPDATE puoi anche controllare intervalli e struttura delle stringhe
    es. CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

Sul manuale di PostgreSQL

Non vi è alcuna differenza di prestazioni tra questi tre tipi, a parte lo spazio di archiviazione aumentato quando si utilizza il tipo con riempimento vuoto e alcuni cicli aggiuntivi della CPU per verificare la lunghezza quando si memorizza in una colonna con limite di lunghezza. Mentre character (n) ha vantaggi prestazionali in alcuni altri sistemi di database, non c’è alcun vantaggio in PostgreSQL; infatti, il carattere (n) è solitamente il più lento dei tre a causa dei suoi costi di archiviazione aggiuntivi. Nella maggior parte delle situazioni dovrebbero invece essere usati testo o caratteri diversi.

Di solito uso il testo

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

text e varchar hanno diverse conversioni implicite di tipo. L’impatto più grande che ho notato è la gestione degli spazi finali. Per esempio …

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

restituisce true, false, true e non true, true, true come ci si potrebbe aspettare.

Secondo me, varchar(n) ha i suoi vantaggi. Sì, usano tutti lo stesso tipo sottostante e tutto il resto. Ma, va sottolineato che gli indici in PostgreSQL hanno il limite di dimensione di 2712 byte per riga.

TL; DR: Se si utilizza il tipo di text senza un vincolo e si hanno indici su queste colonne, è molto probabile che si raggiunga questo limite per alcune colonne e si ottenga un errore quando si tenta di inserire dati ma utilizzando varchar(n) , puoi prevenirlo.

Alcuni ulteriori dettagli: Il problema qui è che PostgreSQL non fornisce eccezioni quando si creano indici per tipo di text o varchar(n) dove n è maggiore di 2712. Tuttavia, darà un errore quando un record con dimensioni compresse maggiori di 2712 si cerca di essere inserito. Significa che puoi inserire 100.000 caratteri di stringa che sono composti da caratteri ripetitivi facilmente perché saranno compressi molto al di sotto di 2712 ma potresti non essere in grado di inserire una stringa con 4000 caratteri perché la dimensione compressa è maggiore di 2712 byte. Usando varchar(n) dove n non è molto più grande di 2712, sei al sicuro da questi errori.

Un po ‘OT: se stai usando Rails, la formattazione standard delle pagine web potrebbe essere diversa. Per i moduli di inserimento dati le caselle di text sono scorrevoli, ma le caselle con character varying (Rails string ) sono a una riga. Mostra viste sono lunghe quanto necessario.

character varying(n) , varchar(n) – (Entrambi uguali). il valore verrà troncato in n caratteri senza generare un errore.

character(n) , char(n) – (Entrambi uguali). lunghezza fissa e si bloccherà con spazi vuoti fino alla fine della lunghezza.

text – lunghezza illimitata.

Esempio:

 Table test: a character(7) b varchar(7) insert "ok " to a insert "ok " to b 

Otteniamo i risultati:

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