jak dobrze pisać update’y do bazy danych?

teoretycznie napisanie polecenia update jest bardzo proste:

update tabelka set pole = ‘xxx' where id = yy;

tylko czy to na pewno słuszny sposób?

otóż nie.

problem polega na tym, że w postgresie działa cos co nazywa się mvcc. nie wnikając w szczegóły (jak kogoś z was bardzo interesują, odsyłam do dokumentacji postgresa) – powoduje to, że każdy update to tak naprawdę delete + insert.

efekty – tabelka rośnie. rosną też indeksy na polach które nie zostały zaktualizowane!

zostałem przez ten efekt dosyć boleśnie “ugryziony" gdy okazało się, że dosyć spora tabelka (1.5 miliona rekordów, 5 gigabajtów wielkości) rośnie mi jak na drożdżach, bo bardzo często robię w niej update jednego pola. a że na tabelce jest 15 indeksów to efekt się multiplikował.

rozwiązanie? wykonywanie update'a tylko gdy naprawdę jest konieczny:

update tabelka set pole = ‘xxx' where id = yy and pole <> ‘xxx';

sprawa się nieco komplikuje gdy pole może być null'em, ale na szczęście jest (mało znany) operator “is distinct from", i możemy zapisać zamiast:

update tabelka set pole = ‘xxx' where id = yy and (pole is null or pole <> ‘xxx');

to:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

co istotne – jeśli już modyfikujemy choć jedno pole – to modyfikacje kolejnych nie mają tego złego efektu.

więc nie musimy robić:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

update tabelka set pole_2 = ‘x2' where id = yy and pole_2 is distinct from ‘x2';

itd.

wystarczy (i zdecydowanie lepiej zadziała):

update tabelka set pole = ‘xxx', pole_2 = ‘x2' where id = yy and (pole is distinct from ‘xxx' or pole_2 is distinct from ‘x2');
dodam jeszcze, że o ile vacuum świetnie sobie radzi z odzyskiwaniem miejsca po update'ach w plikach tabel, o tyle z miejscem w plikach indeksów nie jest tak słodko 🙁
inną opcją (dodatkowo pomagającą) jest:

jesli macie dużą tabelkę (kilkanaście kolumn, sporo danych) i tylko jedno/kilka pól jest często aktualizowanych, przy czym reszta danych (w szczególności indeksy) nie jest dotykana – warto się zastanowić by te kolumny wydzielić do oddzielnej tablicy i połączyć relacją 1-do-1.

od razu ostrzegam: to nie zawsze da dobry rezultat. ale może warto spojrzeć. zmniejszone pliki to zmniejszone zapotrzebowanie na pamięć do cache'a dyskowego. a więc cały system zaczyna szybciej działać.

4 thoughts on “jak dobrze pisać update’y do bazy danych?”

  1. > każdy update to tak naprawdę delete + insert.

    tak z ciekawości, jeśli mamy tabelę t i w niej jedną kolumnę c i wiesze o wartościach 1,1,1,2,3 i damy update t set c=1 to co zwróci pgsql jako liczbę zmodyfikowanych wieszy?

  2. Czyli że niby 5? To w ogóle dziwnie. Jeśli zgodnie z tym co napisałeś pgsql usunął każdą wartość i wpisał ją na nowo, to powinien zwrócić chyba 10…

    Tak zachowuje sie konkurencyjny produkt, ktorego nazwy nie wymienię, żeby Cię nie denerwować, w przypadku użycia polecenia REPLACE, którego działanie jest z grubsza takie samo: usuwa wiersz i wpisuje go na nowo. Natomiast jeśli wartości nie byłby usuwane, to pgsql powinien zwrócić 2 (zmodyfikował 2 i 3).

    Ale dla 5 to ja nie widze żadnego uzasadnienia.

  3. dlaczego 5 dziwne? było 5 rekordów. i 5 zostało zaktualizowanych. wewnętrzna reprezentacja operacji update nie ma wiekszego znaczenia.
    update 5 informuje cie, ze 5 rekordow zostalo zmodyfikowanych.
    a co informowania “update 2”.
    postgres zmodyfikowal 5 rekordów.
    to, że nie zmieniło się żadne z ich pól widocznych dla użytkownika to jedno, a faktyczne update’ to co innego.
    postgres nie ma mozliwosci sprawdzic na poziomie analizy zapytania czy jakis rekord zostanie faktycznie zmodyfikowany czy nie – dochodzą rule i triggery.
    a poza tym – zawsze ulegają zmianie pola “wewnetrzne” postgresa:
    > select cmin, cmax, xmin, xmax, * from t;
    cmin | cmax | xmin | xmax | c
    ——+——+——–+——+—
    0 | 0 | 376448 | 0 | 1
    (1 row)

    > update t set c = 1;
    UPDATE 1

    > select cmin, cmax, xmin, xmax, * from t;
    cmin | cmax | xmin | xmax | c
    ——+——+——–+——+—
    0 | 0 | 376456 | 0 | 1
    (1 row)

    tak wiec – update jesli “zamatchuje” (where …) jakis rekord, to na pewno go update’uje – nawet jesli nowo utworzony rekord bedzie mial identyczne dane. tak to działa.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.