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ć.
