FAQ - Bazy danych (PostgreSQL)Oficjalne FAQ grupy pl.comp.bazy-danych znajduje się pod adresem www.dbf.pl/faq. Tutaj znajdziesz zbiór ciekawych wypowiedzi, na które natknąłem się podczas lektury archiwum tej grupy. Większość z nich dotyczy bazy danych PostgreSQL.
Poziomy izolacji transakcji(Filip Sielimowicz, czerwiec 2004) Poziom izolacji transakcji (transaction isolation levels) to stopień uniezależnienia się transakcji od siebie nawzajem. Im wyższy poziom izolacji tym większa pewność, że podczas wykonywania jednej transakcji zmiany dokonywane w tym samym czasie przez inne transakcje nie wpłyną na nią. Obecnie można mówić o czterech poziomach izolacji: Read uncommited Read commited Repeatable read Serializable Transakcje (PG)(Adam Buraczewski, maj 2004) 1. Jak transakcja zostanie rozpoczęta jawnie, za pomocą BEGIN albo
START TRANSACTION, to żeby wprowadzone w jej ramach zmiany w bazie
były zachowane, musi być wykonany COMMIT. "Relation with OID xxxx does not exist" (PG)(Adam Buraczewski, maj 2004) Najpierw otwieram transakcje, tworzę tabelę tymczasową, na niej wykonują się pewne operacje w funkcji, po czym tabela ta jest mi nie potrzebna, zostaje usunięta i transakcja zostaje zamknięta. W momencie gdy chce wykonać ten cykl operacji jeszcze raz, przy pierwszym działaniu na tej nowo stworzonej tabeli pojawia sie blad jak w temacie. To jest wina tego że Postgres nie odświeża swoich
wewnętrznych struktur. Pierwsze uruchomienie procedury w PL/pgSQL,
czy też stworzenie Prepared Statement za pomocą PREPARE powoduje, że
polecenia SQL są parsowane, nazwy tabel i kolumn zamieniane są na ich
wewnętrzne identyfikatory i jest to zapamiętywane do końca sesji. Gdy
tabela przestanie istnieć, a potem zostanie ponownie stworzona nowa
tabela, to nawet jak będzie miała tę samą nazwę, to będzie miała nowy
wewnętrzny identyfikator (OID) i funkcja się wywali. Wykrywanie błędów i ich zgłaszanie (PG)(Adam Buraczewski, maj 2004) Gdy natrafisz na dziwne błędy powodujące błędne działanie postgresa lub wysypanie serwera
i nie będziesz mógł znaleźć rozwiązania, powinieneś zgłosić błąd autorom na
listę pgsql-bugs. Sposób postępowania jest zawsze taki sam: Quota (PG)(Adam Buraczewski, maj 2004) W przypadku Postgresa nie jest to proste :( Cały serwer działa na jednym koncie uniksowym, zatem quota założona w np. Linuksie nie spełni swojej roli -- pozostaje więc rozwiązanie ze sprawdzaniem wielkości bazy za pomocą np. contrib/dbsize, albo jakieś własne przeróbki Postgresa (co pewnie robią firmy hostingowe). Jest co prawda obecnie rozwiązanie w postaci różnych lokaliazacji dla różnych baz (polecenie initlocation i później parametr -D dla polecenia createdb), można więc niby założyć quotę dla każdej bazy osobno (np. na różnych partycjach), ale nie jest to zbyt szczęśliwe rozwiązanie -- wystarczy bowiem że jedna z baz przekroczy quotę a cały serwer przestanie działać. Jedyną pociechą jest to, że Postgres doskonale radzi sobie w sytuacji, gdy nie może zapisywać danych na dysku, bo nie ma miejsca (albo przekroczono quotę) -- po prostu zatrzymuje się z odpowiednim komunikatem, a po zwolnieniu miejsca i jego ponownym uruchomieniu "odgrywa" transakcje zapisane w strukturach WAL i kontynuuje działanie. Nie ma mowy o uszkodzeniach plików czy niespójności danych w bazie w takiej sytuacji. Być może sytuacja poprawi się po wprowadzeniu do Postgresa obsługi tablespaces -- jeżeli autorom tego rozwiązania dobrze pójdzie będzie to już w 7.5. Niewątpliwie wielu użytkowników tego systemu oczekuje takiej funkcjonalności :) Czy PostgreSQL obsługuje wieloprocesorowe serwery?(Adam Buraczewski, maj 2004) Tak, był zresztą testowany nawet na 64-procesorowych maszynach, AFAIR. Ma dobrze zrobioną synchronizację międzyprocesową i mechanizm blokad, dzięki którym pracuje wydajnie w takich sytuacjach. Ponieważ Postgres dla każdego połączonego z bazą klienta uruchamia nowy proces i ten proces zajmuje się realizacją wszystkich wydawanych poleceń SQL, to przy większej liczbie połączeń do serwera wszystkie procesory będą wykorzystywane, na dodatek raczej równomiernie. Gorzej, gdy do serwera łączy się na raz tylko jeden klient, albo gdy jeden z klientów wykonuje bardzo obciążające procesor rzeczy -- Postgres nie wykorzystuje jeszcze wątków, zatem nie potrafi takiego "ciężkiego" zadania rozdzielić na kilka procesorów i może powstać sytuacja, że jeden procesor będzie obciążony w 100%, a drugi będzie trochę "się nudził". :( Niemniej jednak Postgresa opłaca się stawiać na wieloprocesorowych maszynach, zwykle przecież pracuje w sieci, w której równocześnie z bazy danych korzysta kilku-kilkudziesięciu użytkowników. W wersji 7.5 zostanie wydzielony oddzielny proces "Background Writer", który będzie się zajmował zapisywaniem zmodyfikowanych tabel na dysku (dotychczas robił to każdy proces obsługujący użytkowników osobno) -- powinno to trochę zrównoważyć obciążenie procesorów. Indeksy na polach typu int2 (PG)(Adam Buraczewski, kwiecień 2004) PostgreSQL wykorzystuje indeksy na takich polach, ale w wersjach do 7.4.x włącznie nie zawsze tam gdzie oczekiwałby programista. Otóż posłuży się indeksem, gdy z kolumną typu int2 porównujesz wartość typu int2 (jawnie podając typ), np.:
... where kolumna_typu_int2 = 7::int2 ... albo (gdy wartość jest w '' to nie ma określonego typu): ... where kolumna_typu_int2 = '7' ... natomiast nie posługuje się, gdy z kolumną typu int2 porównujesz wartość która nie jest typu int2, np:
... where kolumna_typu_int2 = 7::int4 ... albo (domyślnie liczby całkowite bez podania typu są typu int4): ... where kolumna_typu_int2 = 7 ... To samo dotyczy innych porównań pomiędzy danymi różnych typów. Tom Lane poprawił to już i w Postgresie 7.5, gdy kolumna jest typu A a porównywana wartość jest typu B i da się zrzutować wartość typu B na typ A, to Postgres indeks wykorzysta. Póki co należy więc sprawdzać, dokonywać jawnego rzutowania, oraz czekać na Postgresa 7.5 :) Logi (PG)(Adam Buraczewski, kwiecień 2004) Nie ma takiego standardowego miejsca, tzn. plik jest tam gdzie go wskażesz uruchamiając Postgresa. Jeżeli uruchamiasz go za pomocą pg_ctl, to jest opcja -l: pg_ctl -l /ścieżka/do/logu start jeżeli uruchamiasz wywołując program postmaster (pg_ctl to robi), to wystarczy przekierować standardowe wyjście i standardowe wyjście błędów do jakiegoś pliku, np.: postmaster ...opcje... >> /ścieżka/do/logu 2>&1 Ja polecam użycie pg_ctl. Pamiętaj tylko, że opcję -l trzeba podać także przy restartowaniu Postgresa (pg_ctl restart), czego w dokumentacji do niego jakoś nie piszą. Druga sprawa, to że zamiast do pliku, możesz przekierować sobie komunikaty do jakiegoś potoku, albo jakiegoś programu który będzie je dzielił, filtrował itp. Możesz też przekierować do sysloga, ale to już nie opcją przy wywołaniu pg_ctl czy postmaster, lecz ustawieniami w postgresql.conf, co Ci już w tym wątku napisano. Trzecia sprawa, to to co ma być umieszczane w logach. Domyślnie trafiają tam błędy, ostrzeżenia i uwagi, czyli mniej więcej to co dostaje także użytkownik korzystający z np. programu psql. Więcej możesz skonfigurować w pliku postgresql.conf, osobiście polecam włączenie log_connections, log_pid, log_timestamp oraz ustawienie log_min_error_statement = ERROR, będziesz wtedy wiedział kto i skąd się łączy i jakie ewentualnie są błędy w działaniu aplikacji. Dla hardcore'owców chcących śledzić wszystko jest opcja log_statement. Natomiast osoby rozwijające aplikacje powinny włączyć sobie po stronie psql większą precyzję komunikatów, można to zrobić np. wpisując do pliku ~/.psqlrc polecenie: Typy indeksów (PG)(Adam Buraczewski, lipiec 2003) Są do wyboru 4: B-tree, R-tree, GiST oraz Hash. Czym one się różnią? Jaka jest zasada wybierania typu indeksu? Generalnie w Postgresie najczęściej stosuje się indeksy typu btree, bo działają wydajnie, pozwalają na tworzenie indeksów wielokolumnowych i operują na większości wbudowanych typów danych. Są też wykorzystywane przy sortowaniu (ORDER BY), chociaż niestety nie przy operatorach MIN i MAX (taki urok Postgresa, są ku temu głębsze powody, a zamiast nich lepiej używaj "ORDER BY kolumna LIMIT 1" oraz "ORDER BY kolumna DESC LIMIT 1", wówczas indeksy zostaną użyte). Można chyba powiedzieć że btree są najlepiej przetestowanym typem indeksów. Z poszczególnymi typami indeksów związane
są tzw. strategie, określające przy jakiego typu operacjach Postgres
może wykorzystywać dany indeks. Z indeksem typu btree związanych jest
5 strategii, ponumerowanych następująco (plik nbtree.h w źródłach
Postgresa): Indeksy typu btree wymagają więc od indeksowanego typu danych, aby miał 5 operatorów, które będą rozróżniać wartości "mniejsze", "równe" i "większe", cokolwiek to dla danego typu znaczy. Operatory te służą do układania elementów w wyważone drzewo binarne (no, prawie) i do poruszania się po nim. Wstawianie elementów do indeksu powoduje przebudowanie i wyważenie drzewa, tak że wyszukanie elementu będzie odbywało się w czasie mniej więcej logarytmicznym. Indeks btree pozwala na wyszukanie elementu (strategia 3) i łatwe przeszukiwanie elementów w kolejności rosnącej (strategie 4 i 5) lub malejącej (strategie 1 i 2), co ma wykorzystanie w np. frazie ORDER BY oraz w innych miejscach w których Postgres wewnętrznie przegląda indeks w jakiejś kolejności (np. ma to miejsce gdy używasz GROUP BY). Przy takim przeglądaniu indeksu w kolejności rosnącej/malejącej najpierw wyszukiwany jest element najmniejszy/największy, a kolejne elementy są pobierane poprzez odpowiednie przeglądanie liści drzewa, jest to więc także szybkie. Indeks typu btree pozwala też na indeksowanie wartości NULL, może być więc wykorzystywany przy testach IS NULL i IS NOT NULL, oraz pozwala na sprawdzanie unikalności kluczy jest więc zakładany automatycznie na pola tabel z flagą UNIQUE oraz PRIMARY KEY. Jak widać same zalety ;-) Żeby już zamknąć sprawę indeksów btree warto wspomnieć, że gdy będziesz po raz pierwszy zakładał taki indeks na tabeli, to Postgres zrobi najpierw zwykłe sortowanie tej tabeli, wynik umieści w buforze (a gdy to mu się tam nie zmieści to na dysku -- wielkość bufora określa parametr sort_mem w postgresql.conf) i użyje go do zbudowania indeksu. Potem zwolni tą pamięć/dysk, ale dla dużej tabeli, zajmującej wiele GB to może być czasochłonne. Zadbaj więc też o odpowiednio dużo miejsca na dysku. Późniejsze wstawianie jest już szybkie, a dzięki sprytnym algorytmom drzewo jest cały czas wyważone. Niestety, do Postgresa 7.3.x włącznie w niesprzyjających okolicznościach indeksy btree mogą się rozrastać ponad miarę (ma problemy z odzyskiwaniem miejsca w indeksie), trzeba więc raz na jakiś czas wywoływać polecenie REINDEX, co przebudowuje indeks na nowo (z całym etapem sortowania tabeli włącznie). Z kolei indeks typu hash określa tylko jedną strategię, oznaczoną numerkiem 1 - "równe". Wymaga więc tylko jednego operatora, który będzie porównywał dwa obiekty i sprawdzał czy są identyczne. Indeks tego typu nie może być więc wykorzystany przy sortowaniu, nadaje się co najwyżej tam gdzie można porównywać obiekty na "identyczność" -- przy złączeniach czy gdy użyjesz operatora "=" we frazie WHERE (w tych obydwóch miejscach indeks btree też będzie działał oczywiście). Działanie indeksu hash wynika z jego budowy: każda wartość przed zindeksowaniem jest przepuszczana przez specjalną funkcję hashującą, która daje w wyniku 32-bitową liczbę całkowitą. Część bitów z tej liczby jest potem używanych jako indeks do tablicy o dosyć skomplikowanej budowie. W tej tablicy zapisane są wskazania do wierszy w tabeli, w których znajduje się dana wartość. Jest jednak pewien problem: funkcja hashująca może dawać tą samą wartość (a więc i ten sam indeks w tablicy) dla kilku różnych wartości wejściowych, po znalezieniu więc danego wiersza w tabeli, Postgres musi jeszcze sprawdzić czy faktycznie znalazł właściwą wartość (czy tylko inną, która daje ten sam wynik funkcji hashującej), co trochę spowalnia przeszukiwanie. Teoretycznie indeks typu hash jest więc szybki, bo funkcja hashująca działa szybko (ta w Postgresie jest zdaje się całkiem niezła i naprawdę szybka, do nowych typów można dostarczyć własną albo skorzystać ze standardowej) i wystarczy tylko policzyć jej wartość żeby przejść do odpowiedniego elementu indeksu, a ten wskaże wiersz tabeli (jest więc niby mniej więcej stały czas dostępu). Niestety, nie jest to takie proste, bo ze względu na oszczędność dysku indeks jest zorganizowany w postaci skomplikowanych list i tablic oraz organizujących to wszystko struktur sterujących -- problem pojawia się zwłaszcza gdy jest wiele wartości dających tą samą wartość funkcji hashującej -- rozrasta się wówczas lista stron tzw. "overflow pages", która na dodatek jest przeszukiwana od końca (!), co znacznie spowalnia pracę Postgresa. Z różnych innych cech indeksu hash: nie indeksuje wartości NULL, nie może być więc wykorzystywany przy testach IS NULL i IS NOT NULL (tzn. działa jakby to był indeks częściowy tylko na wierszach o wartościach różnych od NULL). Nie pozwala na użycie go do sprawdzania unikalności wartości (ograniczenie sztczne, bo mógłby to robić, ale po prostu nie oprogramowano tego). Nadaje się za to znakomicie do typów danych, w których operacje "większe" i "mniejsze" nie istnieją (jakieś niestandardowe typy danych, które ciężko mi sobie teraz wyobrazić) i jego kod jest często wykorzystywany wewnętrznie w Postgresie do organizacji struktur w RAM (np. cache), gdzie nie ma takich problemów z organizacją tablicy jak na dysku -- jest też więc nieźle przetestowany. Może też być wykorzystany do złączeń (JOIN), a w Postgresie 7.4 bedą go wykorzystywać funkcje agregujące i fraza GROUP BY, bo okazało się że znacznie przyspiesza niektóre tego typu operacje. Dużą zaletą indeksów typu hash jest szybkość ich budowania, znacznie większa niż indeksów typu btree, gdyż nie wymagają wstępnego sortowania tabel. Mają też mniejsze wymagania pamięciowe/dyskowe niż btree. Indeksy typu rtree zostały natomiast wymyślnone jakby specjalnie do typów figur geometrycznych, które istnieją od dawna w Postgresie (Postgres był projektowany i używany m.in. do analizy danych geograficznych, zdjęć satelitarnych, systemów CAD itp.). Jest dostępnych 8 strategii, opisujących m.in. operacje zawierania się jednego elementu w innym, nakładania się ("overlap"), wzajemnego położenia ("left", "right") oraz oczywiście porównania ("same"). Uwaga: nie wszystkie strategie muszą być użyte w danym typie (chociaż ich określony podzbiór jest obowiązkowy)! Obiekty w drzewie rtree są zorganizowane tak, że uwzględnione są ich wzajemne relacje przestrzenne, a dla każdego poddrzewa określana jest figura otaczająca, w której "zawierają się" pozostałe figury tego poddrzewa. Dzięki temu wyszukiwanie przebiega tak: sprawdzane jest czy szukana figura zawiera się w danej gałęzi drzewa, a gdy to ma miejsce, następuje zawężenie poszukiwań i przejrzenie poszczególnych gałęzi tego poddrzewa. Gdy figura nie zawiera się w figurze "otaczającej" dla danej gałęzi, to gałąź w ogóle nie jest w przeszukiwaniu uwzględniana. Przy wstawianiu elementów do indeksu rtree następuje wyważanie drzewa, ale pociąga to za sobą wyznaczanie nowych figur "otaczających" dla niektórych poddrzew. Drzewa rtree, podobnie jak indeksy hash, nie pozwalają na indeksowanie wartości NULL, nie można za ich pomocą tworzyć indeksów unikalnych ani wielokolumnowych, budowane są też w jednym przebiegu pętli, bez dodatkowego etapu sortowania obecnego przy btree -- budowanie indeksu rtree jest więc raczej powolne. Indeksy gist to już zupełnie inna bajka. Weszły do Postgresa niedawno (gdy deweloperzy Postgresa dostali specjalną zgodę autora algorytmu). Nie ma w nich ani założeń ani ograniczeń co do liczby i typów strategii (można więc teoretycznie indeksować dowolny typ danych, z dowolnymi operatorami, jest tylko sztuczne ograniczenie liczby strategii do 100) i teoretycznie mogą w większości przypadków zastąpić wszystkie pozostałe, specjalizowane typy indeksów. W contrib jest przykład ich wykorzystania zamiast btree do typów INTEGER i TIMESTAMP, a także różnych innych, "dziwnych" typów danych (moduły: ltree, tsearch). W przypadku indeksów gist dane są przechowywane w postaci "skompresowanej", cokolwiek to nie znaczy dla danego typu. W przypadku modułu tsearch, służącego do realizowania Full Text Search w Postgresie, napisy są przechowywane w postaci tokenów: głosek, sylab itp., a nie znaków, co przyspiesza ich wyszukiwanie i pozwala na wyszukiwanie wyrazów zbliżonych. W drzewach gist można umieszczać różne złożone typy danych, w tym tablicowe i geometryczne. Metody organizacji drzewa: podziału na poddrzewa, wyważania itp. są wyznaczane przez indeks gist na podstawie funkcji dołączonych do danej klasy operatorów -- mogą więc być inne dla różnych typów danych, tak żeby wyszukiwanie z ich udziałem było możliwie najszybsze. Można więc powiedzieć, że gist są tylko "szkieletem" do organizowania różnych specjalizowanych indeksów o budowie drzewiastej -- w każdym razie wymagają od programisty znacznie więcej wiedzy niż btree, rtree czy (zwłaszcza) hash. Coż, tym się płaci za uniwersalność. Indeksy gist, podobnie jak btree, pozwalają na wykorzystanie ich do
indeksów wielokolumnowych, ale ich obecna postać nie pozwala na
wykorzystanie ich do badania unikalności (tu pozostaje więc nadal
btree). Obsługują za to wartości NULL, choć w sposób bardziej
ograniczony niż w btree: jedynie w indeksach wielokolumnowych i tylko
w ostatnich kolumnach (np. w indeksie 3-kolumnowym NULL może wystąpić
tylko naraz w kolumnach 2 i 3 albo w samej kolumnie 3, w przeciwnym
razie wiersz nie zostanie w ogóle zindeksowany). Podsumowując, wygląda na to że do większości przypadków rzeczywiście nadaje się tylko indeks typu btree, zwłaszcza że od niego nie uciekniesz (jest domyślnie zakładany dla pól PRIMARY KEY i UNIQUE). Indeksy typu hash są dosyć ograniczone, ale też warto sprawdzić, czy ich zastosowanie nie przyspieszy wyszukiwania w twoich danych. Natomiast indeksy rtree i gist pozostają chyba jedynie dla bardziej złożonych typów danych (rtree dla geometrycznych, gist dla wszystkich). W Postgresie 7.4 w kwestii indeksów dojdzie jeszcze jedna fajna rzecz: indeksy funkcyjne zostaną rozszerzone tak, że zamiast wywołania funkcji będzie można określić dowolne wyrażenie, podobnie jak np. jest to obecnie w klauzuli CHECK. Klauzula WHERE w złączeniach (PG)(Adam Buraczewski, lipiec 2003) Jaka jest różnica w stosowaniu klauzuli WHERE i bez niej przy złączeniach tabel, tzn. ... FROM t1 JOIN t2 ON ... oraz ... FROM t1, t2 WHERE t1.id = t2.id ... Począwszy od PostgreSQLa 7.4 obydwa zapisy są równoważne. Niedawno Tom Lane napracował się, aby optymalizator traktował je tak samo. W dotychczasowych wersjach Postgresa (do 7.3.x włącznie) jest jednak różnica: w składni ze słowem JOIN kolejność złączania jest brana z treści zapytania, podczas gdy w składni, w której warunki są wymienione w WHERE, optymalizator sam próbuje znaleźć najlepszą kolejność złączania. Mało tego, w składni z WHERE, gdy tabel wymienionych we frazie FROM jest odpowiednio dużo (reguluje to odpowiednia zmienna ustawiana przez SET), to włącza się postgresowa "sztuczna inteligencja", czyli optymalizator oparty o algorytmy genetyczne. Ze względu na te różnice często należało eksperymentować z kolejnością tabel występujących w JOIN, a po znalezieniu optymalnego rozwiązania nie miało się pewności czy zapytanie nadal będzie szybkie gdy dane się zmienią. W Postgresie 7.4 zapytania wykorzystujące składnię z JOIN będą już automatycznie optymalizowane, w tym też z użyciem algorytmów genetycznych. |