Ydelsesoptimering og optimering af forespørgsler i PostgreSQL · Lektion

HOT-opdateringer og heap-only tuple-kæder

Udform schemaer og indeks, så opdateringer forbliver heap-only og undgår write amplification i indeks.

Lektion 2 af 413 trin

HOT-opdateringer og heap-only tuple-kæder er en gratis Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Prisen for en opdatering i PostgreSQL

Fordi PostgreSQL bruger MVCC, overskriver en UPDATE ikke en række på stedet. I stedet skriver den en helt ny tupel (den nye rækkeversion) og markerer den gamle som død. Den gamle version bliver liggende, indtil VACUUM frigiver den.

Naivt set kræver hver ny tupel en ny peger i hvert indeks på tabellen, også indeks hvis kolonner ikke blev ændret. Med 8 indeks betyder én logisk opdatering 8 indeksindsættelser plus indekssvulst. Dette er forstærkning af indeksskrivninger.

Der skrives mere WAL (hver indeksændring logges)
Der opstår mere indekssvulst (døde pegere hober sig op)
Der bruges mere CPU og I/O pr. opdatering

Denne lektion handler om en mekanisme, der lader PostgreSQL springe dette indeksarbejde over: HOT-opdateringer.

Hvad er en HOT-opdatering?

HOT står for tupel kun i heapen. En HOT-opdatering opretter den nye tupelversion på samme heap-side som den gamle og opdaterer ingen indeks overhovedet.

Begge betingelser skal være opfyldt, for at en opdatering kan være HOT:

Ingen indekseret kolonne blev ændret. Hvis du ændrer en kolonne, der bruges af et indeks, er HOT umulig.
Der er plads på den samme side til den nye tupelversion.

Når begge betingelser er opfyldt, omdirigeres den gamle tupels linjepeger, så den peger på den nye tupel, og der dannes en HOT-kæde. Indeksene bliver ved med at pege på den oprindelige linjepeger og behøver aldrig at blive ændret.

HOT-kædens mekanik

På en side har hver tupel en linjepeger (element-id). Ved en HOT-opdatering:

Den nye tupel får en ny linjepeger og flaget HEAP_ONLY_TUPLE.
Den gamle tupel får flaget HEAP_HOT_UPDATED, og dens t_ctid peger videre til den nye tupel.
Indeksposter refererer stadig til den oprindelige linjepeger, så en scanning følger kæden fremad for at finde den levende version.

Når VACUUM senere kører, kan den beskære kæden: Døde mellemliggende versioner fjernes, og rodens linjepeger omdannes til en omdirigeringspeger direkte til den overlevende tupel. Dette kaldes HOT-beskæring og kan endda ske lejlighedsvist under en normal sidelæsning (heap_page_prune).

Undersøgelse af HOT-aktivitet med pg_stat

Du kan måle, hvor mange af dine opdateringer der følger HOT-stien. Visningen pg_stat_user_tables viser både det samlede antal opdaterede tupler og den delmængde, der var HOT.

En sund, skriveintensiv tabel bør have n_tup_hot_upd tæt på n_tup_upd. En lav andel tyder på, at opdateringer ændrer indekserede kolonner, eller at siderne er fulde.

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 nr. 1: Undlad at indeksere flygtige kolonner

Den mest effektive måde at aktivere HOT på er at undgå at indeksere kolonner, der ændres ofte. Hvert indeks over en kolonne i den travle opdateringssti tvinger ikke-HOT-opdateringer igennem, når kolonnen skrives.

Overvej en sessionstabel, hvor last_seen_at opdateres ved hver forespørgsel. Hvis du indekserer den, bliver hvert heartbeat til en ikke-HOT-opdatering med fuld indeksaktivitet.

Spørg: bruges dette indeks af en reel forespørgsel, eller er det spekulativt?
Kolonner, der ændres ofte og har lav selektivitet, har sjældent gavn af et btree alligevel.
Hvis du fjerner et sådant indeks, kan en arbejdsbelastning øjeblikkeligt skifte fra 0 % HOT til næsten 100 %.

-- 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 nr. 2: Efterlad ledig plads med fillfactor

HOT kræver plads på den samme side til den nye tupel. Hvis siden er pakket tæt, havner den nye version på en anden side, og opdateringen kan ikke være HOT.

fillfactor fortæller PostgreSQL, hvor stor en procentdel af hver side der skal være tom ved indlæsning, så der reserveres plads til opdateringer på siden. Standardværdien for tabeller er 100 (tæt pakning), hvilket er fremragende til data, der kun tilføjes, men dårligt for HOT på tabeller med mange opdateringer.

For tabeller, der opdateres meget, skaber en fillfactor på 70-90 den plads, HOT har brug for.

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

Samling af begge regler

Den bedste opskrift til en tabel med mange opdateringer er at kombinere begge regler: hold indekserede kolonner stabile, og reserver plads på siden.

Her opdateres en tællertabel konstant. Vi indekserer kun den stabile opslagsnøgle, aldrig tælleren, og sætter en generøs fillfactor, så de gentagne opdateringer bliver på siden.

Indekset er på key, som aldrig ændres → betingelse 1 er opfyldt.
fillfactor = 80 efterlader plads → betingelse 2 er opfyldt.
Resultat: Tællerforøgelser er HOT-opdateringer uden indeksskrivninger.

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';

Pas på: Udtryksindeks og delvise indeks tæller stadig med

HOT-egnethed afgøres af, om værdien af en indekseret kolonne er ændret, ikke kun af almindelige btree-kolonner. Det giver problemer med:

Udtryksindeks: Et indeks på lower(email) betyder, at skrivning til email blokerer HOT, selv om værdien med små bogstaver logisk set er identisk.
Delvise indeks: Den indekserede kolonne deltager stadig; en opdatering af den kan forhindre HOT uanset WHERE-prædikatet.
Inkluderede kolonner (INCLUDE): I PostgreSQL er kolonner i INCLUDE-udtrykket også en del af indekset, så ændringer i dem blokerer HOT.

PostgreSQL sammenligner de gamle og nye værdier for hver kolonne, der refereres af hvert indeks. Hvis alle er uændrede, er HOT tilladt.

-- 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);

Kontrol af HOT ved en virkelig opdatering

Tællerne i pg_stat_user_tables er kumulative, så du kan tage et øjebliksbillede før og efter en kendt arbejdsbelastning for at bevise, om din optimering virkede.

Kør en opdateringsbatch, og sammenlign derefter ændringen i n_tup_hot_upd med ændringen i n_tup_upd. Hvis de bevæger sig sammen, er opdateringerne fuldt ud HOT; hvis kun n_tup_upd stiger, er der stadig noget, der tvinger indeksopdateringer igennem.

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- og WAL-mængde

Færre indeksskrivninger reducerer direkte mængden af WAL. En ikke-HOT-opdatering logger ændringen i heapen og hver indeksindsættelse; en HOT-opdatering logger kun ændringen i heapen (samt lejlighedsvis en oprydningspost).

På en travl tabel med flere indekser kan ændringer til HOT reducere WAL-genereringen betydeligt, hvilket igen:

mindsker replikeringsforsinkelsen på standbyservere.
reducerer belastningen på checkpoints og baggrundsskriveren.
reducerer arkivlageret til PITR.

Du kan kvantificere WAL pr. sætning med pg_stat_statements (wal_bytes) for at se effekten før og efter ændringer af fillfactor og indekser.

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;

Når HOT ikke kan redde dig

HOT er effektivt, men ikke universelt. Det hjælper ikke, når:

Du reelt har brug for at opdatere en indekseret kolonne (f.eks. et statusfelt, der også bruges som søgenøgle) — indeksskrivningen kan ikke undgås, selv om du nogle gange kan redesigne adgangsvejen.
Siderne forbliver fulde trods fillfactor, fordi rækker vokser (tekst med variabel længde eller JSONB, der udvides), så nye versioner skubbes ud af siden.
Langvarige transaktioner holder xmin-horisonten tilbage, så HOT-oprydning forhindres, og kæder og oppustning alligevel ophobes.

Den praktiske fremgangsmåde er at indeksere kun stabile kolonner, angive fillfactor på tabeller med mange opdateringer, holde transaktioner korte, så oprydningen kan frigive plads, og måle med n_tup_hot_upd og WAL-statistikker.

Hurtigt tjek: Aktivering af HOT

Test din forståelse af, hvad der gør en opdatering egnet til HOT.

Besvar den i den interaktive lektion

Opsummering: Sådan holdes opdateringer kun i heapen

Du ved nu, hvordan du designer skemaer og indekser, så opdateringer forbliver kun i heapen:

HOT-opdatering = en ny tupel på samme side, nul indeksskrivninger, hvilket danner en HOT-kæde, som VACUUM/oprydning senere samler.
To betingelser: ingen indekseret kolonne må være ændret, og der skal være ledig plads på siden.
Regel 1: indeksér ikke flygtige kolonner eller kolonner på den varme sti; kolonner i udtryk, delvise indekser og INCLUDE-kolonner tæller alle som indekserede.
Regel 2: angiv fillfactor (70-90) på tabeller med mange opdateringer for at reservere plads på siden.
Mål: gå efter en høj værdi for n_tup_hot_upd / n_tup_upd, og hold øje med, at wal_bytes falder.
Begrænsninger: voksende rækker, reelt nødvendige opdateringer af indekserede kolonner og lange transaktioner kan stadig besejre HOT.

At maksimere HOT er en af de gevinster med størst effekt og lavest risiko for arbejdsbelastninger i PostgreSQL med mange skrivninger.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 22
Lektioner: 88

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “HOT-opdateringer og heap-only tuple-kæder” gratis?

Ja — alle 3 lektioner i læringssporet Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, inklusive “HOT-opdateringer og heap-only tuple-kæder”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “HOT-opdateringer og heap-only tuple-kæder”?

Udform schemaer og indeks, så opdateringer forbliver heap-only og undgår write amplification i indeks. Du øver dig i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Ydelsesoptimering og optimering af forespørgsler i PostgreSQL?

Der kræves ingen tidligere erfaring. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “HOT-opdateringer og heap-only tuple-kæder”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion?

Ja. Alle Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Tuple-synlighed, xmin og xmax
HOT-opdateringer og heap-only tuple-kæder
Visibility map og index-only scans
WAL-generering og write amplification

← Tilbage til Ydelsesoptimering og optimering af forespørgsler i PostgreSQL