Różnica między tekstem a varcharem (zmienność znaków)

Jako "typy znaków " w dokumentacji wskazuje, varchar(n), char(n), i text są przechowywane w ten sam sposób. Jedyną różnicą jest to, że dodatkowe cykle są potrzebne do sprawdzenia długości, jeśli jest podana, oraz dodatkowej przestrzeni i czasu wymaganej, jeśli wypełnienie jest potrzebne dla char(n).

Jednakże, gdy trzeba przechowywać tylko jeden znak, użycie specjalnego typu "char" ma niewielką przewagę wydajnościową (zachowaj podwójne cudzysłowy-są częścią nazwy typu). Jesteś szybszy dostęp do pola i nie ma nad głową do przechowywania długości.

Właśnie zrobiłem tabelę 1,000,000 losowych "char" wybranych z alfabetu małych liter. Zapytanie o rozkład częstotliwości (select count(*), field ... group by field) zajmuje około 650 milisekund, w porównaniu z około 760 na tych samych danych za pomocą pola text.

Aktualizacja wskaźników referencyjnych na 2016 r. (pg9. 5+)

1. Użyj dowolnego string_generator z UTF8

2. Główne punkty odniesienia:

   2.1. INSERT

   2.2. SELECT comparing and counting

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;

Przygotuj konkretny test (przykłady)

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

Wykonać test podstawowy:

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

I inne testy,

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;

... I używać EXPLAIN ANALYZE.

Aktualizacja ponownie 2018 (pg10)

Mała Edycja, aby dodać Wyniki 2018 i wzmocnić zalecenia.

Wyniki w 2016 i 2018

Moje wyniki, po średniej, w wielu maszynach i wielu testach: wszystkie te same
(statystycznie mniejsze odchylenie standardowe).

Zalecenie

• Używaj text datatype,
  unikaj starego varchar(x), ponieważ czasami nie jest to standard, np. w CREATE FUNCTION klauzulach varchar(x)≠varchar(y).

• Express limits (z tą samą wydajnością varchar!) za pomocą CHECK klauzuli w CREATE TABLE
  np. CHECK(char_length(x)<=10).
  przy znikomej utracie wydajności w INSERT/UPDATE można również kontrolować zakresy i strukturę łańcuchów
  np. CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

On PostgreSQL manual

Nie ma różnicy w wydajności między tymi trzema typami, oprócz zwiększonej przestrzeni dyskowej podczas korzystania z typu pusto wyściełanego i kilku dodatkowych cykli procesora, aby sprawdzić długość podczas przechowywania w kolumnie o ograniczonej długości. Podczas gdy character(n) ma przewagę wydajnościową w niektórych innych systemach bazodanowych, w PostgreSQL nie ma takiej przewagi; w rzeczywistości character (n) jest zwykle najwolniejszy z trzech z powodu dodatkowych kosztów przechowywania. W większości sytuacji zamiast tego należy użyć tekstu lub znaków różniących się.

Zwykle używam tekstu

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

Moim zdaniem, varchar(n) ma swoje zalety. Tak, wszyscy używają tego samego typu bazowego i tak dalej. Należy jednak zaznaczyć, że indeksy w PostgreSQL mają limit wielkości 2712 bajtów na wiersz.

TL; DR: Jeśli używasz text type bez ograniczenia i masz indeksy na tych kolumnach, jest bardzo możliwe, że przekroczysz ten limit dla niektórych kolumn i otrzymasz błąd podczas próby wstawienia danych, ale używając varchar(n), możesz zapobiec to.

Kilka szczegółów: Problem polega na tym, że PostgreSQL nie podaje żadnych WYJĄTKÓW podczas tworzenia indeksów dla typu text lub varchar(n) gdzie n jest większe niż 2712. Jednak spowoduje to błąd, gdy rekord o skompresowanym rozmiarze większym niż 2712 jest próbowany wstawić. Oznacza to, że można wstawić 100.000 znak łańcucha, który składa się z powtarzających się znaków łatwo, ponieważ będzie skompresowany znacznie poniżej 2712, ale może nie być w stanie wstawić jakiś łańcuch z 4000 znaków, ponieważ skompresowany rozmiar jest większy niż 2712 bajtów. Używając varchar(n) gdzie n nie jest zbyt wiele większe niż 2712, jesteś bezpieczny od tych błędów.

Text i varchar mają różne konwersje typu implicit. Największy wpływ, jaki zauważyłem, to obsługa przestrzeni ciągłych. Na przykład ...

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

Zwraca true, false, true a nie true, true, true Jak można się spodziewać.

Nieco OT: jeśli używasz Rails, standardowe formatowanie stron internetowych może być inne. Dla formularzy wprowadzania danych text pola są przewijalne, ale character varying (Rails string) pola są Jednowierszowe. Pokaż widoki są tak długo, jak to konieczne.

Dobre wyjaśnienie z http://www.sqlines.com/postgresql/datatypes/text :

Jedyną różnicą między tekstem a VARCHAR (n) jest to, że można ograniczyć maksymalna długość kolumny VARCHAR, na przykład VARCHAR (255) nie zezwala na wstawianie ciągu o długości większej niż 255 znaków.

Zarówno TEXT jak i VARCHAR mają górny limit na 1 Gb i nie ma różnica między nimi wydajności (według PostgreSQL dokumentacja).

character varying(n), varchar(n) - (Oba te same). wartość zostanie skrócona do n znaków bez powodowania błędu.

character(n), char(n) - (oba te same). stała długość i będzie podkładka z pustymi do końca długości.

text - Nieograniczona długość.

Przykład:

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

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

Otrzymujemy wyniki:

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