In PostgreSQL lijkt een SELECT-query een puur passieve leesactie. Toch kunnen deze queries fysieke schrijfoperaties veroorzaken, wat te zien is aan het oplopen van de shared blocks dirtied-statistieken.
1. MVCC (Multi-Version Concurrency Control)
- PostgreSQL gebruikt MVCC om te voorkomen dat lezers en schrijvers elkaar blokkeren.
- Bij een UPDATE wordt een bestaande rij (tuple) niet overschreven. In plaats daarvan blijft de oude tuple behouden en wordt er een nieuwe tuple aangemaakt.
- Elke tuple bevat verborgen systeemheaders: xmin (ID van de maker-transactie) en xmax (ID van de verwoestende/vervangende transactie).
2. Het Dilemma bij Committen
- Transacties kunnen falen of worden teruggerold (rollback).
- Een transactie-ID op een rij zegt niets over de vraag of die transactie daadwerkelijk succesvol is afgerond (committed).
- PostgreSQL slaat de status van elke transactie centraal op in het commit-log (pg_xact / pg_clog).
- Om COMMIT-acties razendsnel te houden, werkt de schrijver niet direct alle fysieke pagina's op de schijf bij. Het opschonen en definitief markeren van rijen wordt uitgesteld.
3. De Rol van Hint Bits
- Wanneer de eerste lezer (SELECT) een gewijzigde rij raadpleegt, moet deze in het centrale commit-log controleren of de transactie geslaagd is.
- Om te voorkomen dat latere lezers dezelfde controle moeten uitvoeren, zet de lezer een vlaggetje (hint bit) in de header van de tuple.
- Het aanpassen van dit hint bit wijzigt de geheugenpagina (shared buffer), waardoor de pagina de status dirty krijgt en uiteindelijk naar de harde schijf moet worden geschreven.
4. Ketenreacties en Performance-impact
- Page Pruning / HOT Pruning: Als een lezer merkt dat een geheugenpagina bijna vol is en er hint bits op dode tuples staan, kan de SELECT-query direct page pruning uitvoeren om dode rijen op te ruimen.
- Full Page Images (WAL): Wanneer data-checksums aanstaan (standaard in nieuwere PostgreSQL-versies), dwingt het wijzigen van een hint bit de database om een Full Page Image (8 KB) naar het Write-Ahead Log (WAL) te schrijven om torn pages bij een eventuele crash te voorkomen.
- Dit kan leiden tot onverwachte I/O-pieken wanneer analisten 's ochtends massaal queries uitvoeren op verse data die 's nachts via bulk inserts is ingeladen.
Praktische Oplossing
Om deze I/O-pieken tijdens piekuren te voorkomen, kan na een grote nachtelijke batch-import een proactieve dummy query of een handmatige VACUUM worden gedraaid. Hierdoor worden de hint bits al in het daluur gezet en verloopt het lezen overdag optimaal.
