PostgreSQL Antipatterns: kämpar med legioner av "döda"

Egenskaperna hos de interna mekanismerna i PostgreSQL gör att den är mycket snabb i vissa situationer och "inte så mycket" i andra. I dag ska vi fokusera på det klassiska exemplet på konflikten mellan hur DBMS fungerar och vad utvecklaren gör med den — UPDATE kontra MVCC-principer.

Kort sammanfattning av en utmärkt artikel:

När en rad ändras med kommandot UPDATE utförs faktiskt två operationer: DELETE och INSERT. I den aktuella versionen av raden sätts xmax till nummer på transaktionen som utförde UPDATE. Sedan skapas en ny version av samma rad; värdet xmin hos den stämmer överens med värdet xmax för den tidigare versionen.

Efter en tid, efter att denna transaktion har avslutats, kommer den gamla eller nya versionen, beroende på COMMIT/ROLLBACK, att erkännas som "döda" (dead tuples) vid körning av VACUUM på tabellen och städas bort.

Men detta kommer inte att hända omedelbart, och problemen med "de döda" kan snabbt uppstå – vid flera eller massuppdateringar av poster i en stor tabell, och lite senare stöta på en situation där även VACUUM inte kan hjälpa.

#1: I Like To Move It

Anta att din metoduart fungerar bra och plötsligt inser du att du borde uppdatera fältet X i någon post:

UPDATE tbl SET X =  WHERE pk = $1;

Sedan, under körningen, upptäcker du att fältet Y också måste uppdateras:

UPDATE tbl SET Y =  WHERE pk = $1;

… och sedan även Z – varför inte?

UPDATE tbl SET Z =  WHERE pk = $1;

Hur många versioner av denna post har vi nu i databasen? Jaha, 4 stycken! Bland dem är en aktuell, medan 3 måste städas upp av [auto]VACUUM.

Gör inte så här! Använd uppdatering av alla fält i en enda fråga – nästan alltid kan metodens logik ändras på så sätt:

UPDATE tbl SET X = , Y = , Z =  WHERE pk = $1;

#2: Use IS DISTINCT FROM, Luke!

Så, ni ville ändå uppdatera många poster i tabellen (under körning av ett skript eller konverterare, till exempel). Och skriptet innehåller något sådant:

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

Ett exempel på detta utseende av förfrågan förekommer ganska ofta och nästan alltid inte för att fylla i ett tomt nytt fält, utan för att åtgärda några fel i data. I detta fall tas korrektheten hos de redan existerande data överhuvudtaget inte i beaktande – vilket är synd! Det vill säga, posten skrivs över, även om där fanns precis det man ville – varför? Låt oss korrigera det:

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

Många känner inte till denna fantastiska operator, så här är en snabbguide till IS DISTINCT FROM och andra logiska operatorer som hjälp:

… och lite om operationer med komplexa ROW()-uttryck:

#3: А я милого узнаю по… блокировке

Startas två identiska parallella processer, där var och en försöker märka posten som "i arbete":

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

Även om dessa processer oberoende gör olika saker, kommer den andra klienten att "låsa fast" på denna begäran så länge den första transaktionen pågår.

Lösning №1: uppgiften reduceras till den föregående

Vi lägger helt enkelt till IS DISTINCT FROM:

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

I denna form kommer den andra frågan helt enkelt att göra inga ändringar i databasen, den är redan "som den ska" – så det kommer inte att uppstå någon blockering. Därefter hanterar vi redan faktumet av "ofound" posten i algoritmen.

Lösning №2: rådgivande lås

Ett stort ämne för en separat artikel, där man kan läsa om tillämpningsmetoder och "snubblor" med rådgivande låsningar.

Lösning №3: dumma anrop

Här bör det definitivt ske en samtidig hantering av samma post? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Källa: habr.com