Często się zdarza, że programista jest zmuszony zmodyfikować istniejącą strukturę bazy danych bądź zaprojektować takową od podstaw. Definiując nową tabelę musi zastanowić się jakiego typu powinna być kolumna przechowująca określone dane. Jest to dosyć istotna decyzja, ponieważ jej skutki mogą być odczuwalne, w dotkliwy sposób, w późniejszym etapie realizacji projektu. Nieprzemyślana struktura bazy danych może doprowadzić do problemów wydajnościowych lub znacząco utrudnić (a nawet uniemożliwić w obecnej postaci) dalszą rozbudowę projektu.
Niniejszy wpis przedstawia i szczegółowo opisuje dostępne typy znakowe (character types) w PostgreSQL. Tekst dotyczy aktualnej stabilnej wersji 9.5.3, chyba że zostanie bezpośrednio wskazana inna wersja. Jednakże w większości przypadków, przedstawiona tutaj wiedza może zostać wykorzystana do pracy ze starszymi wersjami PostgreSQL.
Znakowe typy danych można podzielić generalnie na dwa rodzaje:
- Przechowujące wartości tekstowe o ograniczonej liczbie znaków:
character(n), character varying(n). - Przechowujące wartości tekstowe o nieograniczonej liczbie znaków:
character varying,text.
Ograniczone łańcuchy znaków
Jak zostało już wspomniane, PostgreSQL oferuje dwa podstawowe typy danych pozwalające przechowywać łańcuch znaków (string) o ograniczonej długości, określonej parametrem n:
character(n)– kolumna tego typu ma stałą wielkość. Alias:char(n).character varying(n)– kolumna tego typu ma zmienną wielkość. Alias:varchar(n).
W przypadku kolumn wspomnianych typów, próba wstawienia tekstu dłuższego niż określone n zakończy się błędem. Wyjątkiem jest sytuacja, w której znaki przekraczające limit są białymi znakami (np. spacjami). W tym przypadku tekst zostanie przycięty do możliwej maksymalnej długości n. Takie zachowanie wynika ze standardu SQL. Jeszcze jednym interesującym wymaganiem standardu SQL, respektowanym przez PostgreSQL, jest przycinanie zbyt długiego łańcucha znaków w chwili jawnego rzutowania na typ char(n) lub varchar(n). Opisane zachowania prezentuje poniższy przykład:
-- Utworzenie testowej tabeli
CREATE TABLE string_fixed_values(fixed_text char(5), var_text varchar(5));
-- Próba wstawienia zbyt długich wartości
INSERT INTO string_fixed_values VALUES ('abcdefg','abcdefg');
BŁĄD: wartość zbyt długa dla typu znakowego (5)
********** Błąd **********
BŁĄD: wartość zbyt długa dla typu znakowego (5)
Stan SQL: 22001
-- Wstawienie zbyt długiego tekstu, gdzie przekraczającymi znakami są spacje
INSERT INTO string_fixed_values VALUES ('abc ', 'cde ');
-- Wstawienie zbyt długich wartości tekstowych, ale z jawnym rzutowaniem
INSERT INTO string_fixed_values VALUES ('abcdefg'::CHAR(5), 'abcdefg'::VARCHAR(5));
-- Wyświetlenie zawartości tabeli
SELECT * FROM string_fixed_values;
fixed_text | var_text
------------+----------
abc | cde
abcde | abcde
(2 wiersze)
-- Usunięcie testowej tabeli
DROP TABLE string_fixed_values;Jak widać na powyższym listingu, nadmiarowe spacje zostały przycięte do wymaganej długości, podobnie jak wartości jawnie rzutowane na typy char(5) oraz varchar(5).
Jeśli łańcuch znaków jest krótszy niż określona długość n, to w przypadku typu char(n) wartość kolumny zostanie wypełniona spacjami. Oznacza to, że wszystkie dane w kolumnie tego typu mają jednakową, stałą długość. W przypadku kolumny typu varchar(n), wartość przechowywana będzie miała dokładnie taką samą długość jak podany łańcuch znaków.
Typ char bez określonej długości jest równoważny z podaniem typu char(1). Trochę inaczej wygląda sytuacja z typem varchar, bez podania długości – w tym przypadku kolumna może przyjąć łańcuch o dowolnej długości. Należy tylko pamiętać, że drugi przypadek jest rozszerzeniem PostgreSQL, a nie standardem SQL. Więcej informacji na temat typów nieograniczonych znajduje się w następnym rozdziale.
Istotny jest fakt, że spacje dodawane na koniec łańcucha znaków, w przypadku typu char(n), nie mają semantycznego znaczenia i są ignorowane podczas porównywania dwóch wartości tego typu. W porównaniach, gdzie białe znaki mają znaczenie, takie zachowanie może prowadzić do nieprzewidzianych rezultatów. Spacje końcowe (trailing spaces) są usuwane automatycznie podczas konwersji wartości typu char(n) na jeden z pozostałych typów znakowych. Należy zauważyć, że spacje mają znaczenie semantyczne w typach nieograniczonych, tj. varchar i text oraz podczas procesu dopasowywania wzorca (operator LIKE oraz wyrażenia regularne). Przykład:
SELECT 'a ' = 'a'; --false
SELECT 'a '::CHAR(3) = 'a'::CHAR(3); --true
SELECT 'a '::VARCHAR(3) = 'a'::VARCHAR(3); --false
SELECT 'a '::TEXT = 'a'::TEXT; --false
SELECT 'a '::CHAR(3) LIKE 'a'; -- false
SELECT 'a '::CHAR(3) ~ '^a$' -- false
SELECT 'a '::CHAR(2) COLLATE "C" < E'a\n'::CHAR(2); -- trueWiększość powyższych wyrażeń powinna być zrozumiała. Dodatkowych objaśnień może wymagać ostatni przykład:SELECT 'a '::CHAR(2) COLLATE "C" < E'a\n'::CHAR(2). Wyrażenie to zwraca wartość true, pomimo tego, że C locale traktuje spację jako znak o wartości większej niż znak nowej linii.
Porównania (collations) pozwalają określić porządek sortowania oraz klasyfikację poszczególnych znaków. Krótko mówiąc, jest to zestaw reguł, które określają, który znak jest pierwszy (ma mniejszą wartość) w danym alfabecie (zestawie znaków). PostgreSQL, w ramach rozszerzenia standardu SQL, akceptuje tzw. znaki ucieczki (escape characters) i traktuje je jak znaki specjalne (podobnie jak np. w języku C). Aby wskazać łańcuch znaków, który zawiera znaki ucieczki, należy dodać prefiks
SELECT ‘test\n’::CHAR(5) = E’test\n’::CHAR(5); — falseE(małe lub duże) przed literałem łańcuchowym:
Nieograniczone łańcuchy znaków
W celu umieszczenia w bazie danych długich danych tekstowych o nieokreślonej długości, zaleca się używanie następujących typów danych (zamiast arbitralnie ustawiać maksymalną długość):
text– typ, który przyjmuje łańcuch znakowy o dowolnej długości. Należy pamiętać, że nie jest to standard SQL, ale większość dzisiejszych systemów bazodanowych oferuje typy danych o analogicznych właściwościach.character varying(alias:varchar) – bez określonej długościn, może być stosowany jako zamiennik typutext.
Dla przypomnienia: typy danych przedstawione w tym rozdziale nie mają stałej długości – wartość tekstowa przechowywana w kolumnie będzie miała taką samą długość jak zapisywany łańcuch znaków.
Wydajność
Zgodnie z dokumentacją PostgreSQL, nie ma żadnej znaczącej różnicy w wydajności, pomiędzy przedstawionymi typami znakowymi, oprócz większego zapotrzebowania na przestrzeń do przechowywania danych typu char(n) oraz kilku dodatkowych cykli CPU, sprawdzających czy zapisywany łańcuch zmieści się w kolumnie z ograniczoną liczą znaków. Podczas gdy character(n) góruje wydajnościowo w niektórych systemach bazodanowych, w PostgreSQL nie ma tej przewagi. W zasadzie character(n) jest najwolniejszy z trzech dostępnych typów, ze względu na dodatkowe koszty przechowywania i wolniejsze sortowanie. W znacznej większości przypadków należy używać typów text oraz character varying.
Do przechowywania krótkich łańcuchów znaków (do 126 bajtów) wykorzystuje się tyle samo bajtów, ile liczy łańcuch, plus jeden dodatkowy bajt (w przypadku character(n) brane są pod uwagę również spacje uzupełniające). Dłuższe łańcuchy potrzebują 4 dodatkowych bajtów. Długie łańcuchy znaków są automatycznie kompresowane przez system, więc fizycznie mogą zajmować mniej przestrzeni na dysku niż krótsze nieskompresowane łańcuchy. Bardzo długie wartości są również umieszczane w tabelach wewnętrznych (background tables), więc nie przeszkadzają w natychmiastowym dostępie do krótszych wartości. Najdłuższym możliwym łańcuchem znaków, który może zostać zapisany w bazie, jest ten o długości ok. 1GB. Oczywiście wartość n, stanowiąca limit długości, musi być mniejsza niż maksymalna dopuszczalna długość. Nie warto zmieniać tego zachowania ze względu na wielobajtowe kodowanie znaków (unicode), gdzie liczba znaków w łańcuchu może znacząco różnić się od liczby zajmowanych bajtów.
Gdyby spojrzeć na kod źródłowy PostgreSQL (w języku C), można zauważyć, że wszystkie znakowe typy danych są wewnętrznie przechowywane przez tą samą strukturę (plik src/include/c.h):
struct varlena
{
char vl_len_[4]; /* Do not touch this field directly! */
char vl_dat[FLEXIBLE_ARRAY_MEMBER]; /* Data content is here */
};Skrypt testujący wydajność
Teoretycznie, skoro wszystkie typy znakowe są przechowywane za pomocą tej samej struktury, nie powinno być znaczących różnic wydajnościowych. Wypadałoby jednak sprawdzić czy rzeczywiście tak jest. W tym celu został utworzony dedykowany skrypt Ruby, dostępny tutaj (skrypt jest inspirowany tym artykułem). Wspomniany skrypt jest właściwie mini-aplikacją o strukturze przedstawionej poniżej:
├───bin
| postgresql_character_types.rb
├───data
└───lib
database_manager.rb
data_generator.rb
display_helper_methods.rb
bin/postgresql_character_types.rb– jest punktem wejściowym aplikacji. W celu przeprowadzenia testów należy uruchomić ten plik, będący głównym skryptem.data– folder przechowujący wygenerowane pliki z danymi testowymi (o tym za chwilę).lib– folder zawierający skrypty pomocnicze:database_manager.rb– kod odpowiedzialny za operacje bazodanowe.require 'pg' class DatabaseManager def initialize(user, password, db_name = 'postgres') @connection = PG.connect dbname: db_name, user: user, password: password end def create_database(name) @connection.exec "CREATE DATABASE #{name}" end def drop_database_if_exists(name) result = @connection.exec "SELECT COUNT(1) AS db_exists FROM pg_database WHERE datname='#{name}'" if result[0]['db_exists'] == '1' @connection.exec "DROP DATABASE #{name}" end end def create_table(name, *columns) columns_string = columns.join ',' @connection.exec "CREATE TABLE #{name} (#{columns_string})" end def drop_table_if_exists(name) @connection.exec "DROP TABLE IF EXISTS #{name} CASCADE" end def copy_from_file(table_name, file_name) @connection.exec "COPY #{table_name} FROM '#{file_name}'" end def create_index(name, table_name, field_name) @connection.exec "CREATE INDEX #{name} ON #{table_name} (#{field_name})" end def select_from_table(name, columns, where) @connection.exec "SELECT #{columns} FROM #{name} WHERE #{where}" end enddata_generator.rb– kod odpowiedzialny za generowanie danych testowych.class DataGenerator ALLOWED_STRING_CHARACTERS = [' ', *'0'..'9', *'A'..'Z', *'a'..'z'] def self.generate_strings(length, count) strings = [] 1.upto(count) do string = (1..length).map {ALLOWED_STRING_CHARACTERS.sample }.join strings.push string end strings end enddisplay_helper_methods.rb– kod odpowiedzialny za sformatowane wyświetlanie danych wyjściowych.def print_benchmark_report(times) trials = times.length avg = times.inject(:+) / trials printf_args = [trials, avg, times.min, times.max] printf "Trials: %i\tAverage: %.2fs\tMin: %.2fs\tMax: %.2fs\n", *printf_args end
Omawiana aplikacja przeprowadza testy wydajnościowe w następujący sposób:
- Definiuje stałe konfiguracyjne używane w dalszej części skryptu:
DB_NAME = 'performance_test' TABLE_NAME = 'strings_test' STRING_LENGTHS = [10, 50, 100] STRINGS_COUNT = 500_000 TRIALS = 5 SELECTS_COUNT = 20 - Tworzy testową bazę danych (jeśli baza już istnieje, próbuje ją usunąć i utworzyć na nowo):
puts '[*] Connecting to the PostgreSQL server...' db = DatabaseManager.new pg_user, pg_password puts "[*] Remove database '#{DB_NAME}' if exists..." db.drop_database_if_exists DB_NAME puts "[*] Create database '#{DB_NAME}'..." db.create_database DB_NAME - Tworzy pliki tekstowe z losowo wygenerowanymi łańcuchami znaków o różnej długości. Aktualnie generowane są 3 pliki tekstowe, które zawierają kolejno:
- 500 000 losowo wygenerowanych łańcuchów o długości 10 znaków.
- 500 000 losowo wygenerowanych łańcuchów o długości 50 znaków.
- 500 000 losowo wygenerowanych łańcuchów o długości 100 znaków.
puts '[*] Create test data files:' FileUtils.mkdir_p "#{BASEDIR}/data" STRING_LENGTHS.each do |length| file_name = "#{BASEDIR}/data/strings_#{length}.txt" puts "- #{file_name}" File.open(file_name, 'w') do |f| f.puts DataGenerator::generate_strings(length, STRINGS_COUNT) end end
- Dla kolejnych długości łańcucha znaków (10, 50, 100):
- tworzy tabelę z kolumną określonego typu (
char(n), varchar(n), varchar, text); - wrzuca wygenerowane wcześniej łańcuchy znaków określonej długości do właśnie utworzonej tabeli;
- zakłada indeks na kolumnę w tabeli.
- tworzy tabelę z kolumną określonego typu (
- Sekwencja jest powtarzana 5 razy dla każdej długości łańcucha i dla każdej takiej sekwencji liczony jest czas wykonania. Z każdej kombinacji długości łańcucha i typu danych wyciągany jest minimalny, maksymalny oraz średni czas wykonania.
puts '[*] Testing of: create table, load data and create index...' STRING_LENGTHS.each do |length| puts "- Testing string length: #{length}" ["char(#{length})", "varchar(#{length})", 'varchar', 'text'].each do |data_type| times = [] 1.upto(TRIALS) do db.drop_table_if_exists TABLE_NAME time_elapsed = Benchmark.realtime do db.create_table TABLE_NAME, "string #{data_type}" db.copy_from_file TABLE_NAME, File.absolute_path("#{BASEDIR}/data/strings_#{length}.txt") db.create_index 'idx_string', TABLE_NAME, 'string' end times.push time_elapsed end printf "\t- %-20s ", "#{data_type}:" print_benchmark_report times end end - Krok analogiczny do dwóch poprzednich, z tą różnicą, że najpierw jest tworzony indeks w nowo utworzonej tabeli, a dopiero później ładowane są odpowiednie dane do tabeli.
puts '[*] Testing of: create table with index and then load data...' STRING_LENGTHS.each do |length| puts "- Testing string length: #{length}" ["char(#{length})", "varchar(#{length})", 'varchar', 'text'].each do |data_type| times = [] 1.upto(TRIALS) do db.drop_table_if_exists TABLE_NAME time_elapsed = Benchmark.realtime do db.create_table TABLE_NAME, "string #{data_type}" db.create_index 'idx_string', TABLE_NAME, 'string' db.copy_from_file TABLE_NAME, File.absolute_path("#{BASEDIR}/data/strings_#{length}.txt") end times.push time_elapsed end printf "\t- %-20s ", "#{data_type}:" print_benchmark_report times end end - Dla określonych typów danych (
char(n), varchar(n), varchar, text):- tworzy tabelę;
- ładuje do tabeli 500 000 łańcuchów o długości 100 znaków;
- zakłada indeks na kolumnę w tabeli.
- 5 razy wykonuje 20 zapytań
SELECT, prosząc o losowy łańcuch z tabeli.
- Dla każdej z pięciu prób liczony jest czas wykonania i na końcu wyciągany jest minimalny, maksymalny oraz średni czas wykonania wszystkich prób w ramach jednego typu danych.
puts '[*] Testing of: searching indexed big tables...' length = STRING_LENGTHS.last file_name = File.absolute_path("#{BASEDIR}/data/strings_#{length}.txt") puts "- Testing string length: #{length}, selects count: #{SELECTS_COUNT}" ["char(#{length})", "varchar(#{length})", 'varchar', 'text'].each do |data_type| db.drop_table_if_exists TABLE_NAME db.create_table TABLE_NAME, "string #{data_type}" db.copy_from_file TABLE_NAME, file_name db.create_index 'idx_string', TABLE_NAME, 'string' times = [] 1.upto(TRIALS) do time_elapsed = Benchmark.realtime do 1.upto(SELECTS_COUNT) do where = "string = '#{File.readlines(file_name).sample}'" db.select_from_table TABLE_NAME, '*', where end end times.push time_elapsed end printf "\t- %-20s ", "#{data_type}:" print_benchmark_report times end
Skrypt można uruchomić w następujący sposób:
ruby bin/postgresql_character_types.rb <POSTGRES_USER>
Użytkownik zostaje wówczas poproszony o podanie hasła dla użytkownika bazodanowego POSTGRES_USER. Po wpisaniu prawidłowego hasła, skrypt rozpoczyna swoje działanie. Aby zdefiniować własne parametry, wystarczy zmodyfikować wartości przedstawione w punkcie 1.
Wyniki pomiarów
Testy wykazały, że nie ma znaczących różnic wydajnościowych pomiędzy poszczególnymi typami danych, zarówno podczas umieszczani danych w tabeli, jak i podczas jej przeszukiwania.
Warto również wspomnieć, że czasy mogą być różne, w zależności od sprzętu, na którym wykonywany jest skrypt. Dodatkowo może być tutaj wliczony również narzut czasowy wynikający z tego, że wykorzystany został język Ruby wraz z biblioteką pg. Nie jest to jednak istotnie, ponieważ celem testu nie było wyliczenie bezwzględnych wartości czasowych, a jedynie porównanie wartości dla poszczególnych typów danych.
Wewnętrzne typy danych
W PostgreSQL istnieją jeszcze dwa typy znakowe o stałej długości, używane wewnętrznie przez system bazodanowy:
name(64 B) – używany tylko do przechowywania identyfikatorów w wewnętrznych katalogach i nie powinien być używany przez użytkowników bazy. Długość typu jest obecnie ustawiona na 64 bajty (63 bajty użytkowe + terminator), ale w kodzie źródłowym C powinno się korzystać ze stałejNAMEDATALENdo odczytywania długości typuname. Długość jest ustawiana podczas kompilacji. Domyślna długość może się zmienić w kolejnych wersjach."char"(1 B) – specjalny typ używany wewnętrznie w katalogach systemowych jako uproszczony typ enumeratywny. Typ"char"(cudzysłów jest istotny) różni się od typuchar(1)tym, że używa tylko jednego bajta do przechowywania danych (jak zostało wspomniane wcześniej, typchar(1)używa 2 bajtów: długość łańcucha plus jeden dodatkowy bajt).
Powyższe typy zostały wspomniane bardziej jako ciekawostka, ponieważ nie zaleca się ich używania w produkcyjnych bazach danych.
Podsumowanie
Jeśli chodzi o szybkość działania, wszystkie znakowe typy danych PostgreSQL zachowują się podobnie. Wydaje się, że najmniej korzystnym rozwiązaniem jest używanie typu char(n) ze względu na większe zapotrzebowanie na przestrzeń do przechowywania danych (w przypadku gdy przechowywane łańcuchy mogą być krótsze). Dodatkowo, jego używanie może prowadzić do trudno wykrywalnych błędów poprzez automatyczne dodawanie spacji (trailing spaces), które nie mają semantycznego znaczenia.
Ogólnie rzecz biorąc, typów z ograniczeniem (char(n) oraz varchar(n)) powinno się używać tylko w przypadkach gdy użytkownik ma pewność, że zapisywany łańcuch nigdy nie będzie dłuższy niż zdefiniowany limit. Jeśli jest inaczej, zmiana limitu może okazać się kosztowna – szczególnie w przypadku dużych baz produkcyjnych.
W pozostałych przypadkach, gdzie nie wiadomo jaka będzie maksymalna długość zapisywanego tekstu, warto korzystać z typów bez ograniczeń: varchar oraz text. W zasadzie mogą być one używane zamiennie, ale text wydaje się być mniej enigmatyczny – widząc strukturę tabeli, od razu widać intencje projektanta tabeli.
Należy pamiętać o tym, że opisywane tutaj typy danych respektują wielkość znaków (są case-sensitive). Oznacza to, że porównując łańcuch znaków, zapisany w każdym z omówionych typów, z innym łańcuchem, należy mieć na uwadze iż znaki w obu łańcuchach muszą być tej samej wielkości, jeśli porównanie ma dać wynik pozytywny. Aby porównywać łańcuchy, nie bacząc na wielkość znaków, w większości przypadków stosuje się funkcję lower (opisaną tutaj). PostgreSQL posiada jednak bardzo ciekawy moduł dodatkowy, który wprowadza nowy typ łańcuchowy, gdzie wielkość znaków nie ma znaczenia (jest case-insensitive). Modułem tym jest CITEXT.
