PostgreSQL Antipatterns: boj s hordami „mrtvých“

Zvláštnosti vnitřních mechanismů PostgreSQL umožňují, aby byl v některých situacích velmi rychlý a v jiných „nepříliš rychlý“. Dnes se zaměříme na klasický příklad konfliktu mezi tím, jak funguje DBMS, a tím, co s ním dělá vývojář – Principy UPDATE vs MVCC.

Krátký příběh z skvělý článek:

Když je řádek upraven příkazem UPDATE, jsou ve skutečnosti provedeny dvě operace: DELETE a INSERT. V aktuální verze řetězce xmax je nastaveno na číslo transakce, která provedla UPDATE. Poté se vytvoří novou verzi stejný řádek; jeho hodnota xmin se shoduje s hodnotou xmax předchozí verze.

Nějaký čas po dokončení této transakce stará nebo nová verze, v závislosti na COMMIT/ROOLBACK, bude uznáno "mrtvý" (mrtvé n-tice) při míjení VACUUM podle tabulky a vyčištěno.

To se však nestane hned, ale problémy s „mrtvými“ lze získat velmi rychle - opakovaným nebo hromadná aktualizace záznamů ve velkém stole a o něco později narazíte na stejnou situaci VAKUUM vám nepomůže.

#1: Rád to hýbu

Řekněme, že vaše metoda pracuje na obchodní logice a najednou si uvědomí, že by bylo nutné aktualizovat pole X v nějakém záznamu:

UPDATE tbl SET X = <newX> WHERE pk = $1;

Poté, jak provádění postupuje, ukáže se, že by mělo být aktualizováno také pole Y:

UPDATE tbl SET Y = <newY> WHERE pk = $1;

... a pak taky Z - proč ztrácet čas maličkostmi?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

Kolik verzí tohoto záznamu máme nyní v databázi? Ano, 4 kusy! Jeden z nich je relevantní a 3 po vás bude nutné vyčistit pomocí [auto]VACUUM.

Nedělejte to tímto způsobem! Použití aktualizace všech polí v jedné žádosti — téměř vždy lze logiku metody změnit takto:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: Použití JE DISTINCT FROM, Luku!

Takže jsi ještě chtěl aktualizovat mnoho, mnoho záznamů v tabulce (například při použití skriptu nebo převodníku). A do scénáře letí něco takového:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

Požadavek v přibližně této podobě se vyskytuje poměrně často a téměř vždy nikoli na vyplnění prázdného nového pole, ale na opravu některých chyb v údajích. Přitom ona sama ke správnosti existujících údajů se vůbec nepřihlíží - ale marně! To znamená, že záznam je přepsán, i když obsahoval přesně to, co se chtělo – ale proč? Pojďme to opravit:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

Mnoho lidí si není vědomo existence tak skvělého operátora, takže zde je cheat IS DISTINCT FROM a další logické operátory, které vám pomohou:

... a něco málo o operacích na komplexu ROW()- výrazy:

#3: Svou milou poznám podle... blokování

jsou spuštěny dva stejné paralelní procesy, z nichž každý se pokusí označit záznam, že je „probíhá“:

UPDATE tbl SET processing = TRUE WHERE pk = $1;

I když tyto procesy skutečně dělají věci nezávisle na sobě, ale v rámci stejného ID, druhý klient bude na tento požadavek „uzamčen“, dokud nebude dokončena první transakce.

Řešení č. 1: úkol je redukován na předchozí

Pojďme to znovu přidat IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

V této podobě druhý požadavek prostě v databázi nic nezmění, vše je již tak, jak má být - k blokování tedy nedojde. Dále zpracujeme fakt „nenalezení“ záznamu v použitém algoritmu.

Řešení č. 2: poradní zámky

Velké téma na samostatný článek, ve kterém si můžete přečíst o způsoby aplikace a „hrabání“ doporučujícího blokování.

Řešení č. 3: hloupé hovory

Ale to je přesně to, co by se vám mělo stát současná práce se stejným záznamem? Nebo jste se například popletli s algoritmy pro volání obchodní logiky na straně klienta? A když se nad tím zamyslíte?...

Zdroj: www.habr.com