jakiś czas temu pisałem o tym, że mysql skaluje się gorzej od postgresa. bluszcz stwierdził, że wolałby testy z falcon'em lub soliddb.

potem pokazałem testy z falcone'em.

a dziś – dziś dam wam linka do artykułu o teście soliddb. w tym porównania wydajności soliddb do innodb. wiem, że wydajność a skalowalność to co innego. ale niestety – nikt nie zrobił (albo nie trafiłem na) testy skalowalności soliddb, więc dzielę się tym co mam.

wyniki? fajny engine. ma transakcje, klucze obce, obsługa planów optymistycznych lub pesymistycznych.

minusy? wydajność. jest gorzej niż w innodb (o myisam nie wspominając). insert – ponad 2 razy wolniej. update'y – 6 razy wolniej. delete'y trochę szybciej od innodb, ale wolniej niż myisam.

to jeszcze nie koniec. największym minusem soliddb jest fakt, że ma całkowicie zwaloną (w/g mnie, w/g nich to tylko “missing feature") obsługę locale. w dużym skrócie – indeksy możesz zakładać tylko przy standardowym locale na tekstach zawierających jedynie znaki z latin1. koniec, kropka. chcesz “ą"? no cóż. nie możesz go zindeksować.

to ja mimo wszystko podziękuję i zostanę przy postgresie.

na listę announce'ów poszedł mail informujący by *nie* instalować ostatnio wypuszczonych wersji postgresa (8.2.2 i 8.1.7). przyczyna: jest błąd w obsłudze typów tekstowych.

werjsa 8.0 i wcześniejsze podobno nie mają problemów, ale jeszcze są testowane.

nowe wersje nie zostały jeszcze oficjalnie obwieszczone, ale na ftp'ie już są.

muszę przyznać, że za mojej pamięci to pierwsza taka sytuacja.

w 8.2 pojawiły się agregaty wielokolumnowe. muszę przyznać, że nie bawiły mnie za bardzo dopóki nie trafiłem na swój dawny post na google groupsach.

tyczy się on tego jak zapisać agregat łączący wiele tekstów w jeden – rozdzielony znakiem definiowanym w zapytaniu.

nie był to kod ładny.

saint zaproponował inne rozwiązanie, ale miało one w/g mnie dwie wady:

1. konieczność obcinania końcowego separatora.
2. zwraca pusty string a nie null w przypadku odpalenia na pustym zbiorze – a ja wolę null'a 🙂

oczywiście obie te rzeczy można prosto poprawić lekko modyfikując zapytanie, ale wolałbym by logika była w funkcjach.

no i wreszcie. dzięki 8.2 można zrobić coś takiego:

CREATE OR REPLACE FUNCTION agg_text_sum(in_base TEXT, new_text TEXT, separator TEXT) RETURNS TEXT AS $BODY$
DECLARE
BEGIN
IF in_base IS NULL THEN
RETURN new_text;
END IF;
RETURN in_base || separator || new_text;
END;
$BODY$ language 'plpgsql';

CREATE aggregate sum(TEXT, TEXT) (
sfunc = agg_text_sum,
stype = TEXT
);

a potem można to stestować:

CREATE TABLE table_names as SELECT oid::INT4 as id, relname::TEXT as table_name FROM pg_class  WHERE relkind = 'r';
SELECT sum(table_name, ', ') FROM table_names;

i już. separator można dowolnie zmieniać

nie jest to może szczytowe osiągnięcie w inżynierii oprogramowania, ale może się przydać.

na ftp'ie pojawiły się nowe wersje postgresa – 8.2.2, 8.1.7, … – ogólnie – nowa wersja z każdej wersji x.y aktualnie utrzymywanej (czyli od 7.3 wzwyż).

co ciekawe – nie pojawiła się żadna informacja o tej wersji na webie ani na listach dyskusyjnych – zakładam, że oficjalna informacja wyjdzie za chwilę.

w paczce z postgresem, w danych nt. wydań jest data wydania: 2007-02-05. czyli za 3 dni.

a co jako zmiany?

poprawki związane z bezpieczeństwem – w tym bardzo ważna pozwalająca klientowi czytanie pamięci procesu postgresa – przez co można dostać dostęp do danych do których nie powinno być dostępu.

poza tym – jak zawsze przy nowych wersjach gdzie podbijana jest ostatnia cyfra – żadnych nowych funkcjonalności. trochę patchy przyspieszających, trochę poprawek łatających jakieś błędy.

ogólnie – warto instalować.

jak właśnie przeczytałem na blogu neila conwaya, jest on teraz maintainerem (jakiś pomysł na tłumaczenie na polski) projektu pgmemcache.

dzięki temu, ten zapomniany nieco projekt ma szanse z powrotem wrócić do aktywnego rozwijania.

jeśli nie wiecie co to – pgmemcache jest interfejsem do memcached – interfejsem wbudowanym w postgresa. dostępnego z poziomu sql'a.

a co to memcache? rozproszony, bardzo szybki system cache'a. bazuje na przechowywaniu par (klucz, wartość) w rozproszonym środowisku (na wielu maszynach) – tylko w ram'ie – dzieki czemu jest baaaardzo szybki.

dzięki pierwszej poprawce neila, pgmemcache kompiluje się wreszcie na 8.1 i 8.2. życie stało się słodsze.

ukazała się nowa wersja – 2.0, pakietu dbi-link. jest to pakiet umożliwiający podłączanie postgresa do innych baz danych (innych instancji postgresa, albo po prostu zupełnie innych baz), w sposób możliwie przezroczysty dla użytkownika.

przy użyciu tego pakietu, dostajemy wirtualne tabelki na których możemy prawie normalnie pracować  (jeszcze nie można join'ować), nie przejmując się tym, że tak naprawdę są to tabelki z innych baz.

wersja 2.0 zawiera olbrzymie zmiany idące w kierunku poprawienia działania, stabilności oraz ogólnej odporności.

jak się sprawdzi – za wcześnie jeszcze na wyrokowanie, ale wygląda mocno interesująco.

jeśli chodzi o to do jakich baz można się podłączyć – praktycznie do każdej. jak nazwa wskazuje do immplementacji samego połączenia do zdalnej bazy jets używana biblioteka dbi z perla, a ona ma sterowniki praktycznie do wszystkiego.

czy stanęliście kiedyś przed problemem wylosowania rekordu z tabeli? dowolnego rekordu?
oczywistym pomysłem jest:

# SELECT * FROM tabelka ORDER BY random() LIMIT 1;

no ale to jest wolne. wymaga posortowania całej tabeli. co w najlepszym układzie ma złożoność "n log n".
przykładowo u mnie na testowej tabelce trwało to 90 sekund! (1.7 miliona rekordów).
no nie za dobrze.
niektórzy mogą sugerować takie rozwiązanie:

1. znajdź maksymalne
2. SELECT * FROM tabelka WHERE id <= random() * maksymalne_id limit 1;

na oko jest ok. tzn. akurat nie jest, bo random jest funkcją volatile, i trzeba by raczej … WHERE id <= (select random() * maksymalne_id) LIMIT 1, ale to już szczegół.
czemu to jest złe?
bo wprowadza pewien istotny problem. jeśli numeracja pola id w naszej tabelce zawiera dziury (czyli jest takie id, które jest większe od minimalnego i mniejsze od maksymalnego, dla którego nie ma rekordu) – to te losowane rekordy wcale nie będą dobrze losowane.
jako ekstremalny przykład (ale dobrze pokazujący rzeczywistość) podajmy tabelkę z dwoma rekordami, o id odpowiednio: 1 i 100. rekord z id = 1 będzie wypadał 99 razy częściej niż rekord z id = 100!.
cóż więc pozostaje? siąść i płakać?
nie.
można użyć inteligencji. czyli funkcji/procedury.
przykładowo taka funkcja:

CREATE OR REPLACE FUNCTION random_record() RETURNS tabelka AS $BODY$

DECLARE

    id_min INT8;

    id_max INT8;

    range INT8;

    temp_id INT8;

    temprec RECORD;

BEGIN

    SELECT min(id) INTO id_min FROM tabelka;

    SELECT max(id) INTO id_max FROM tabelka;

    range:= 1 + ( id_max - id_min );

    LOOP

        temp_id := id_min + (random() * range::float8)::INT8;

        SELECT * INTO temprec FROM tabelka WHERE id = temp_id;

        IF found THEN

            RETURN temprec;

        END IF;

    END LOOP;

END;

$BODY$ language 'plpgsql';

co ona robi?zwraca losowy rekord. całkowicie losowy – każdy rekord ma te same szanse bycia wylosowanym.
warunki brzegowe? pole id musi być unikatowe (szokujące, nie?). no i: im więcej dziur w numeracji tym wolniej działa. ale co znaczy wolniej?
ta moja testowa tabelka ma takie dane:

# select min(id), max(id), count(*) from tabelka;

 min |   max    |  count

-----+----------+---------

   3 | 36574227 | 1721217

(1 row)

czyli jak widać – dziur jest sporo. w szczególności – dziur jest 21 razy więcej niż istniejących rekordów!
przypomnę, że

select * from tabelka order by random() limit 1;

działało na tej tabelce w około 90 sekund.
ile czasu zajmuje to funkcji?
6 kolejnych wywołań. czasy odpowiednio: 124.700, 141.442, 201.708, 94.413, 145.128, 110.076. milisekund!
jak widać – jest szybko.
problemem tej funkcji jest to, że teoretycznie może się zdarzyć, że nigdy się nie skończy. ale w/g mnie jest to gdybanie. zresztą – zawsze można dorobić warunek, że jeśli np. wykonano już 1000 strzałów niecelnych, to zwróćmy pierwszy rekord z brzegu.
i już.
czy można to jakoś dopalić?
tak.
jeśli wiecie, że tabelka w której szukacie ma dużo dziur, to dodajcie do niej jedno pole:

create sequence random_thing_seq;

alter table tabelka add column random_thing int8;

alter table tabelka alter column random_thing set default nextval('random_thing_seq');

update tabelka set random_thing = nextval('random_thing_seq') where random_thing is null;

alter table tabelka alter column random_thing set not null;

create unique index ui_random_thing on tabelka (random_thing);

i potem używajcie w funkcji random_thing a nie id.
cel ćwiczenia?
jak sie pojawi za dużo dziur w numeracji (random_thing też będzie miał dziury) to zawsze możecie:

update tabelka set random_thing = nextval('random_thing_seq');

i już dziur nie ma,
a ponieważ random_thing nie jest do niczego innego używane – jest to w pełni bezpieczne.
oczywiście po takim update'cie dobrze jest zrobić vacuum'a. a najlepiej vacuum full'a.

eh. od dawna mi tego brakowało w postgresie. i okazało się, że niepotrzebnie, bo już od 8.1 jest! wyłączanie triggerów. pojedynczo. każdy można tymczasowo wyłączyć.
na wypadek gdybyście nie wiedzieli:

# ALTER TABLE test DISABLE TRIGGER xxx;

gdzie xxx to nazwa triggera, lub słowo "ALL" jeśli chcemy wyłączyć wszystkie triggery, lub słowo "USER" jeśli chcemy wyłączyć wszystkie triggery, ale pozostawić działające klucze obce (które są realizowane triggerami).
włączenie z powrotem to:

# ALTER TABLE test ENABLE TRIGGER xxx;

jedynym problemem z tym polega na tym, że w psql'u, przy robieniu \d tabeli nie widać które triggery są wyłączone:

# create table x (id serial primary key, wstawiana text);

# CREATE TRIGGER test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test();

# CREATE TRIGGER test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u();

# alter table x disable trigger test;

# \d x

                           Table "public.x"

  Column   |  Type   |                   Modifiers

-----------+---------+------------------------------------------------

 id        | integer | not null default nextval('x_id_seq'::regclass)

 wstawiana | text    |

Indexes:

    "x_pkey" PRIMARY KEY, btree (id)

Triggers:

    test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test()

    test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u()

można sobie ręcznie sprawdzić które triggery są wyłączone:

# select t.tgname, t.tgenabled from pg_class c join pg_trigger t on c.oid = tgrelid where c.relname = 'x';

 tgname | tgenabled

--------+-----------

 test_u | t

 test   | f

(2 rows)

w 8.3 na szczęście jest to już poprawione:

# \d x

                           Table "public.x"

  Column   |  Type   |                   Modifiers

-----------+---------+------------------------------------------------

 id        | integer | not null default nextval('x_id_seq'::regclass)

 wstawiana | text    |

Indexes:

    "x_pkey" PRIMARY KEY, btree (id)

Triggers:

    test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u()

Disabled triggers:

    test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test()

słodko 🙂

standardowa instalacja postgresa zawiera 2 (lub 3) bazy:

• template0
• template1
• postgres (tylko w najnowszych wersjach)

template0 jest bezpieczna, o tyle, że nie da się do niej prosto podłączyć, więc nikt w niej nie namiesza.
baza template1 jest używana jako "podstawa" do tworzenia nowych baz. tzn. za każdym razem jak robisz: "create database xxx;" to tak naprawdę jest wykonywana kopia bazy template1.
daje to parę fajnych możliwości – np. zrobienie czegokolwiek w bazie template1 oznacza, że każda nowa baza będzie to miała automatycznie.
np. załadowane rozszerzenie, języki itd.
co jednak jeśli do template1 przez pomyłkę wrzuciliście jakieś zbędne dane? np. odtworzyliście do template1 zamiast do xxx jakąś bazę z dumpa?
ręczne kasowanie jest skomplikowane.
ideałem byłoby przywrócenie template1 do stanu początkowego.
da się to prosto zrobić.
w pierwszym kroku łączymy się psql'em do postgresa, na konto admina (zazwyczaj postgres, lub pgdba). ważne jest by połączyć się do bazy innej niż template1.
będąc tak połączonym wykonujemy 4 proste kroki:

1. update pg_database set datistemplate = false where datname = ‘template1';
2. drop DATABASE template1;
3. CREATE DATABASE template1 with template template0;
4. update pg_database set datistemplate = true where datname = ‘template1';

krok 1 jest niezbędny gdyż baza z wartością "datistemplate = true" nie może być skasowana. więc zaznaczamy, że template1 nie jest szablonem 🙂
krok 2 – kasujemy bazę template1. tu dwie ważne uwagi:

1. w czasie wykonywania drop database do dropowaniej bazy nie może być żadnych połączeń. właśnie dlatego musieliśmy się psql'em podpiąć do innej bazy.
2. między drop database template1, a create database template1 nie da sie stworzyć innych baz (a dokłaniej, nie jest to tak proste jak zazwyczaj)

krok 3 – odtwarzamy bazę template1, korzystając z szablonu template0
krok 4 – zaznaczamy template1 jako szablon.
i już to wszystko.

tu informacja dodatkowo. może to wykryliście z powyższego przykładu, ale jak nie, to piszę:
w podobny sposób można zrobić *szybką* kopię całej bazy.
np. jeśli potrzebujecie backup przed odpaleniem na bazie jakichś skomplikowanych rzeczy, można:

create database xxx_backup with template xxx;

oczywiście do tego przydałoby się też ‘encoding qqq owner yyy', ale to już szczegół.
olbrzymim plusem tej metody jest szybkość. przy czym nie szybkość tworzenia kopii. to czasem jest dłuższe niż pg_dump. to co jest istotne, to fakt iż "przywrócenie" bazy danych z takiej kopii, to proste:

drop database xxx;

alter database xxx_backup rename to xxx;

największym minusem jest fakt iż w czasie kopiowania do bazy źródłowej nie może być żadnych połączeń. czyli nie można tak skopiować bazy używanej produkcyjnie.
to spora wada. tym niemniej w środowiskach testowych/developerskich stosuję ją często z wyśmienitym skutkiem.