PostgreSQL Antipatterns: vechten tegen de hordes ‘doden’

De eigenaardigheden van de interne mechanismen van PostgreSQL zorgen ervoor dat het in sommige situaties erg snel is en in andere “niet erg snel”. Vandaag zullen we ons concentreren op een klassiek voorbeeld van een conflict tussen hoe een DBMS werkt en wat de ontwikkelaar ermee doet: UPDATE versus MVCC-principes.

Kort verhaal van geweldig artikel:

Wanneer een rij wordt gewijzigd door een UPDATE-opdracht, worden er feitelijk twee bewerkingen uitgevoerd: DELETE en INSERT. IN huidige versie van de tekenreeks xmax is gelijk aan het nummer van de transactie die de UPDATE heeft uitgevoerd. Vervolgens wordt het aangemaakt nieuwe versie dezelfde lijn; de xmin-waarde valt samen met de xmax-waarde van de vorige versie.

Enige tijd nadat deze transactie is voltooid, is de oude of nieuwe versie afhankelijk van COMMIT/ROOLBACK, zal worden erkend "dood" (dode tupels) bij het passeren VACUUM volgens de tabel en gewist.

Maar dit zal niet meteen gebeuren, maar problemen met de "doden" kunnen zeer snel worden verworven - met herhaalde of massale update van records in een grote tafel, en even later zul je dezelfde situatie tegenkomen VACUUM zal niet kunnen helpen.

#1: Ik vind het leuk om het te verplaatsen

Laten we zeggen dat uw methode werkt aan bedrijfslogica, en plotseling beseft dat het nodig zou zijn om het X-veld in een bepaald record bij te werken:

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

Naarmate de uitvoering vordert, blijkt dat ook het Y-veld moet worden bijgewerkt:

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

... en dan ook Z - waarom tijd verspillen aan kleinigheden?

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

Hoeveel versies van dit record hebben we nu in de database? Ja, 4 stuks! Hiervan is er één relevant, en drie zullen na u moeten worden opgeruimd met behulp van [auto]VACUUM.

Doe het niet op deze manier! Gebruik alle velden in één verzoek bijwerken — bijna altijd kan de logica van de methode als volgt worden gewijzigd:

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

#2: Gebruik IS VERSCHILLEND VAN, Luke!

Dus je wilde nog steeds update heel veel records in een tabel (bijvoorbeeld tijdens het gebruik van een script of converter). En zoiets als dit komt in het script terecht:

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

Een verzoek in ongeveer dit formulier komt vrij vaak en vrijwel altijd voor om niet een leeg nieuw veld in te vullen, maar om enkele fouten in de gegevens te corrigeren. Tegelijkertijd zijzelf er wordt helemaal geen rekening gehouden met de juistheid van bestaande gegevens - maar tevergeefs! Dat wil zeggen, de plaat wordt herschreven, ook al bevatte deze precies wat we wilden - maar waarom? Laten we het oplossen:

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

Veel mensen zijn zich niet bewust van het bestaan ​​van zo'n geweldige operator, dus hier is een spiekbriefje over IS DISTINCT FROM en andere logische operatoren om te helpen:

... en een beetje over operaties op complex ROW()-uitdrukkingen:

#3: Ik herken mijn liefje aan... blokkeren

worden gelanceerd twee identieke parallelle processen, die elk proberen het item te markeren als "in uitvoering":

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

Zelfs als deze processen daadwerkelijk dingen onafhankelijk van elkaar doen, maar binnen dezelfde ID, zal de tweede cliënt op dit verzoek worden “vergrendeld” totdat de eerste transactie is voltooid.

Oplossing # 1: de taak wordt teruggebracht tot de vorige

Laten we het gewoon nog een keer toevoegen IS DISTINCT FROM:

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

In deze vorm zal het tweede verzoek eenvoudigweg niets in de database veranderen, alles is al zoals het zou moeten zijn - daarom zal er geen blokkering plaatsvinden. Vervolgens verwerken we het feit dat het record ‘niet wordt gevonden’ in het toegepaste algoritme.

Oplossing # 2: adviessloten

Een groot onderwerp voor een apart artikel, waar je meer over kunt lezen toepassingsmethoden en “hark” van aanbevolen blokkering.

Oplossing # 3: domme telefoontjes

Maar dit is precies wat er met je zou moeten gebeuren gelijktijdig werken met hetzelfde record? Of heb je bijvoorbeeld de algoritmen voor het aanroepen van bedrijfslogica aan de clientzijde verprutst? En als je erover nadenkt?..

Bron: www.habr.com