Prestaties en queryoptimalisatie in PostgreSQL · Les

HOT-updates en heap-only-tupleketens

Ontwerp schema’s en indexen zo dat updates heap-only blijven en index write amplification vermijden.

Les 2 van 413 stappen

HOT-updates en heap-only-tupleketens is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 2 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

De kosten van een update in PostgreSQL

Omdat PostgreSQL MVCC gebruikt, overschrijft een UPDATE een rij niet op dezelfde plek. In plaats daarvan schrijft het een volledig nieuwe tuple (de nieuwe rijversie) en markeert het de oude als dood. De oude versie blijft bestaan totdat VACUUM deze opruimt.

Op het eerste gezicht heeft elke nieuwe tuple een nieuwe verwijzing in elke index op de tabel nodig, zelfs in indexen waarvan de kolommen niet zijn gewijzigd. Met 8 indexen betekent één logische update 8 index-inserts plus index-bloat. Dit heet schrijfversterking door indexen.

Er wordt meer WAL geschreven (elke indexwijziging wordt gelogd)
Er ontstaat meer index-bloat (dode verwijzingen stapelen zich op)
Elke update kost meer CPU en I/O

Deze les gaat over een mechanisme waarmee PostgreSQL dat indexwerk kan overslaan: HOT-updates.

Wat is een HOT-update?

HOT staat voor Heap-Only Tuple. Een HOT-update maakt de nieuwe tupleversie op dezelfde heap-pagina als de oude en werkt helemaal geen indexen bij.

Aan beide voorwaarden moet zijn voldaan om een update als HOT te laten gelden:

Geen geïndexeerde kolom is gewijzigd. Als je een kolom wijzigt die door een index wordt gebruikt, is HOT onmogelijk.
Er is ruimte op dezelfde pagina voor de nieuwe tupleversie.

Als aan beide voorwaarden is voldaan, wordt de line pointer van de oude tuple doorgestuurd naar de nieuwe tuple, waardoor een HOT-keten ontstaat. Indexen blijven naar de oorspronkelijke line pointer verwijzen en hoeven nooit te worden aangepast.

De werking van de HOT-keten

Binnen een pagina heeft elke tuple een line pointer (item-ID). Bij een HOT-update gebeurt het volgende:

De nieuwe tuple krijgt een nieuwe line pointer en de vlag HEAP_ONLY_TUPLE.
De oude tuple krijgt de vlag HEAP_HOT_UPDATED en de t_ctid verwijst vooruit naar de nieuwe tuple.
Indexvermeldingen verwijzen nog steeds naar de oorspronkelijke line pointer, zodat een scan de keten volgt om de actieve versie te vinden.

Wanneer VACUUM later wordt uitgevoerd, kan het de keten inkorten: dode tussenversies worden verwijderd en de line pointer aan het begin wordt omgezet in een redirect-pointer die rechtstreeks naar de overgebleven tuple verwijst. Dit heet HOT-pruning en kan zelfs opportunistisch plaatsvinden tijdens het normaal lezen van een pagina (heap_page_prune).

HOT-activiteit bekijken met pg_stat

Je kunt meten hoeveel van je updates via het HOT-pad verlopen. De weergave pg_stat_user_tables bevat zowel het totale aantal bijgewerkte tuples als de subset die HOT was.

Bij een tabel met veel schrijfbewerkingen hoort n_tup_hot_upd dicht bij n_tup_upd te liggen. Een lage verhouding betekent dat updates geïndexeerde kolommen wijzigen of dat pagina's vol zijn.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM   pg_stat_user_tables
ORDER  BY n_tup_upd DESC
LIMIT  20;

Regel 1: indexeer veranderlijke kolommen niet

De meest effectieve manier om HOT mogelijk te maken is kolommen die vaak veranderen niet indexeren. Elke index op een kolom op het drukke updatepad dwingt niet-HOT-updates af zodra die kolom wordt geschreven.

Denk aan een tabel met sessies waarvan last_seen_at bij elk verzoek wordt bijgewerkt. Als je daarop een index maakt, is elke heartbeat een niet-HOT-update met volledige indexwijzigingen.

Vraag jezelf af: bedient deze index een echte query, of is hij speculatief?
Kolommen die vaak worden gewijzigd en weinig onderscheidend zijn, hebben meestal toch weinig baat bij een btree.
Zo'n index verwijderen kan een werklast direct van 0% HOT naar bijna 100% HOT laten omslaan.

-- Anti-pattern: indexing a column updated on every request
CREATE INDEX idx_sessions_last_seen ON sessions (last_seen_at);

-- Each heartbeat now forces a non-HOT update + index insert:
UPDATE sessions SET last_seen_at = now() WHERE id = 42;

Regel 2: laat vrije ruimte over met fillfactor

HOT heeft ruimte op dezelfde pagina nodig voor de nieuwe tuple. Als de pagina volgepakt is, komt de nieuwe versie op een andere pagina terecht en kan de update niet HOT zijn.

fillfactor vertelt PostgreSQL welk percentage van elke pagina tijdens het laden leeg moet blijven, zodat er ruimte is voor updates op dezelfde pagina. De standaardwaarde voor tabellen is 100 (volledig volgepakt), wat uitstekend is voor gegevens die alleen worden toegevoegd, maar ongunstig voor tabellen met veel updates.

Voor tabellen die vaak worden bijgewerkt, creëert een fillfactor van 70-90 de ruimte die HOT nodig heeft.

ALTER TABLE sessions SET (fillfactor = 85);

-- Rewrite existing pages so the new fillfactor takes effect:
VACUUM FULL sessions;  -- or CLUSTER / pg_repack for online rewrite

Beide regels combineren

De winnende aanpak voor een tabel met veel updates is beide regels combineren: houd geïndexeerde kolommen stabiel en reserveer ruimte op de pagina.

Hier wordt een tellerstabel voortdurend bijgewerkt. We indexeren alleen de stabiele opzoeksleutel, nooit de teller, en stellen een ruime fillfactor in zodat herhaalde updates op dezelfde pagina blijven.

De index staat op key, die nooit verandert → aan voorwaarde 1 is voldaan.
fillfactor = 80 laat ruimte over → aan voorwaarde 2 is voldaan.
Resultaat: verhogingen van de teller zijn HOT-updates zonder indexschrijfbewerkingen.

CREATE TABLE counters (
    key   text PRIMARY KEY,
    hits  bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 80);

-- The hot path: bumps only a non-indexed column
UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home';

Let op: expressie- en gedeeltelijke indexen tellen ook mee

Of HOT mogelijk is, wordt bepaald door de vraag of de waarde van een geïndexeerde kolom is gewijzigd, niet alleen door gewone btree-kolommen. Dit leidt vaak tot problemen met:

Expressie-indexen: een index op lower(email) betekent dat het schrijven naar email HOT blokkeert, zelfs als de waarde in kleine letters logisch identiek is.
Gedeeltelijke indexen: de geïndexeerde kolom doet nog steeds mee; het bijwerken ervan kan HOT verhinderen, ongeacht het WHERE-predicaat.
Opgenomen kolommen (INCLUDE): in PostgreSQL maken kolommen in de INCLUDE-clausule ook deel uit van de index, dus wijzigingen daarin blokkeren HOT.

PostgreSQL vergelijkt de oude en nieuwe waarden van elke kolom waarnaar een index verwijst; als ze allemaal ongewijzigd zijn, is HOT toegestaan.

-- Both of these put `email` into the indexed-column set,
-- so any UPDATE that writes email becomes non-HOT:
CREATE INDEX idx_users_email_ci ON users (lower(email));
CREATE INDEX idx_users_email_inc ON users (id) INCLUDE (email);

HOT bij een echte update controleren

De tellers van pg_stat_user_tables zijn cumulatief. Daarom kunt u vóór en na een bekende werklast een momentopname maken om aan te tonen of uw afstemming heeft gewerkt.

Voer een updatebatch uit en vergelijk daarna het verschil in n_tup_hot_upd met het verschil in n_tup_upd. Als ze gelijk oplopen, zijn uw updates volledig HOT; als alleen n_tup_upd stijgt, dwingt iets nog steeds indexupdates af.

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';

-- ... run UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home'; x1000 ...

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';
-- Expect both deltas to be ~1000 for a healthy HOT workload.

HOT en WAL-volume

Minder indexschrijfbewerkingen betekent rechtstreeks minder WAL. Bij een niet-HOT-update worden zowel de wijziging in de heap als elke indexinvoeging gelogd; bij een HOT-update wordt alleen de wijziging in de heap gelogd (plus periodiek een opruimrecord).

Op een drukke tabel met meerdere indexen kan het omzetten van updates naar HOT de WAL-productie aanzienlijk verminderen. Dat:

verkleint de replicatieachterstand op stand-byservers;
vermindert de druk op checkpoints en de achtergrondschrijver;
verlaagt de archiefopslag voor PITR.

U kunt de WAL per instructie kwantificeren met pg_stat_statements (wal_bytes) om het effect van uw wijzigingen aan fillfactor en indexen vóór en na de aanpassing te zien.

SELECT query, calls, wal_bytes,
       round(wal_bytes / NULLIF(calls, 0)) AS wal_per_call
FROM   pg_stat_statements
WHERE  query ILIKE 'UPDATE counters%'
ORDER  BY wal_bytes DESC;

Wanneer HOT u niet kan redden

HOT is krachtig, maar niet universeel. Het helpt niet wanneer:

u daadwerkelijk een geïndexeerde kolom moet bijwerken (bijvoorbeeld een statusveld dat ook als zoek sleutel dient) — de indexschrijfbewerking is onvermijdelijk, al kunt u het toegangspad soms anders ontwerpen;
pagina's ondanks fillfactor vol blijven doordat rijen groeien (variabele tekst of JSONB die groter wordt), waardoor nieuwe versies naar een andere pagina worden geduwd;
langdurige transacties de xmin-grens tegenhouden, waardoor HOT-ketens niet kunnen worden opgeschoond en er toch ketens en bloat ontstaan.

De praktische aanpak: indexeer alleen stabiele kolommen, stel fillfactor in op tabellen met veel updates, houd transacties kort zodat opruimen ruimte kan terugwinnen en meet met n_tup_hot_upd en WAL-statistieken.

Snelle controle: HOT inschakelen

Test uw begrip van wat een update geschikt maakt voor HOT.

Beantwoord dit in de interactieve les

Samenvatting: updates heap-only houden

U weet nu hoe u schema's en indexen zo ontwerpt dat updates heap-only blijven:

HOT-update = een nieuwe tuple op dezelfde pagina, geen indexschrijfbewerkingen, waarmee een HOT-keten ontstaat die VACUUM of opruimen later samenvoegt;
Twee voorwaarden: er is geen geïndexeerde kolom gewijzigd en er is vrije ruimte op de pagina;
Regel 1: indexeer geen veranderlijke kolommen of kolommen op veelgebruikte updatepaden; expressiekolommen, partiële indexen en INCLUDE-kolommen tellen allemaal als geïndexeerd;
Regel 2: stel fillfactor (70-90) in op tabellen met veel updates om ruimte op de pagina te reserveren;
Meten: streef naar een hoge verhouding n_tup_hot_upd / n_tup_upd en let erop dat wal_bytes daalt;
Beperkingen: groeiende rijen, updates van daadwerkelijk geïndexeerde kolommen en lange transacties kunnen HOT nog steeds verhinderen.

HOT maximaliseren is een van de meest effectieve verbeteringen met het laagste risico voor schrijfintensieve PostgreSQL-werklasten.

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 22
Lessen: 88

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “HOT-updates en heap-only-tupleketens” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “HOT-updates en heap-only-tupleketens”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Wat leer ik in “HOT-updates en heap-only-tupleketens”?

Ontwerp schema’s en indexen zo dat updates heap-only blijven en index write amplification vermijden. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?

Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 2 van 4.

Hoe lang duurt de les “HOT-updates en heap-only-tupleketens”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?

Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL