Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

Migrera en enorm tabell till partitioner online

Konvertera en befintlig monolitisk tabell till en partitionerad tabell med minimal låsning och utan dataförlust.

Lektion 4 av 413 steg

Migrera en enorm tabell till partitioner online är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 4 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Varför migrera en enorm tabell?

Föreställ Er en events-tabell med 800 miljoner rader i form av tilläggsbaserad loggdata. Varje fråga genomsöker ett enormt index, VACUUM körs i timmar och borttagning av gamla data med DELETE gör tabellen onödigt stor.

Partitionering delar upp en logisk tabell i många fysiska barntabeller baserat på en nyckel (till exempel created_at per månad). Fördelarna omfattar:

Partition pruning — planeraren hoppar helt över irrelevanta partitioner.
Omedelbar dataretention — DROP eller DETACH en hel månad på millisekunder, utan borttagning rad för rad.
Billigare underhåll — VACUUM och omindexering körs per partition.

Utmaningen är att göra detta på en aktiv tabell med många skrivningar utan långa lås eller dataförlust.

Den naiva metoden och dess fallgrop

PostgreSQL kan inte omvandla en befintlig vanlig tabell till en partitionerad tabell med ett enda ALTER TABLE. En partitionerad föräldratabell är en annan typ av objekt som skapas med PARTITION BY.

Den frestande engångsplanen är att skapa den partitionerade tabellen och sedan flytta alla rader i en enda transaktion.

Detta blockerar tabellen med tunga lås under hela kopieringen och håller en enorm transaktion öppen. För 800 miljoner rader innebär det timmar av driftstopp och enorma mängder WAL. Vi behöver i stället en strategi online.

-- This single INSERT...SELECT locks and runs for hours.
-- Holds one transaction open across the whole 800M-row copy.
INSERT INTO events_partitioned
SELECT * FROM events_old;  -- DON'T do this on a live huge table

Strategiöversikt: skuggningstabell + efterinläsning + byte

Den beprövade metoden online har fyra faser:

Skapa en ny partitionerad skuggningstabell med matchande kolumner och en partitioneringsnyckel.
Skriv till båda — låt nya rader fortsätta flöda till både den gamla och den nya tabellen via en trigger (eller skriv till den nya när den finns).
Efterinläs historiska rader i små, bekräftade batcher så att låsen förblir kortvariga.
Byt namn i en enda kort transaktion och ta sedan bort den gamla tabellen.

Varje fas är säker och kan återupptas separat. Inga enskilda långvariga lås och inga förlorade skrivningar.

Steg 1: Skapa skuggningstabellen som partitionerad

Skapa en ny föräldratabell som deklareras med PARTITION BY RANGE på den valda nyckeln. Kolumnen som används som partitioneringsnyckel måste ingå i primärnyckeln vid deklarativ partitionering.

Vi definierar månadsvisa range-partitioner. Observera att föräldratabellen själv inte lagrar några rader; varje barn äger en delmängd.

CREATE TABLE events_new (
    id          bigint        GENERATED ALWAYS AS IDENTITY,
    user_id     bigint        NOT NULL,
    event_type  text          NOT NULL,
    payload     jsonb,
    created_at  timestamptz   NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)   -- partition key must be in PK
) PARTITION BY RANGE (created_at);

CREATE TABLE events_new_2024_01 PARTITION OF events_new
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_new_2024_02 PARTITION OF events_new
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Steg 2: En standardpartition som skyddsnät

Om en rads nyckel hamnar utanför alla definierade intervall misslyckas INSERT. Under migreringen kanske Ni ännu inte har skapat alla månader, så lägg till en standardpartition som fångar upp eftersläntrare.

Var uppmärksam: när Ni senare ansluter en ny partition måste PostgreSQL genomsöka standardpartitionen för att bevisa att det inte finns några motstridiga rader. Håll standardpartitionen tom i normalt läge genom att skapa de månader Ni faktiskt behöver i förväg.

CREATE TABLE events_new_default
    PARTITION OF events_new DEFAULT;

-- Later, when you add a real partition, PostgreSQL scans
-- the default for conflicting rows before attaching.
CREATE TABLE events_new_2024_03 PARTITION OF events_new
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Steg 3: Håll nya skrivningar synkroniserade

Medan vi efterinläser historiken fortsätter applikationen att göra INSERT. Vi får inte förlora de aktiva raderna. Ett robust mönster är en trigger på den gamla tabellen som speglar varje skrivning till den nya partitionerade tabellen.

När efterinläsningen är klar och bytet närmar sig säkerställer triggern att båda tabellerna förblir identiska för nya data.

CREATE OR REPLACE FUNCTION mirror_to_new()
RETURNS trigger AS $$
BEGIN
    INSERT INTO events_new (user_id, event_type, payload, created_at)
    VALUES (NEW.user_id, NEW.event_type, NEW.payload, NEW.created_at);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_mirror_events
    AFTER INSERT ON events_old
    FOR EACH ROW EXECUTE FUNCTION mirror_to_new();

Steg 4: Efterinläsning i små batcher

Kopiera nu historiska rader i avgränsade, bekräftade delar. Varje batch är en egen transaktion, så låsen släpps omedelbart och Ni kan pausa eller återuppta när som helst.

Styr loopen med primärnyckeln eller ett tidsintervall. Använd ON CONFLICT DO NOTHING så att rader som redan speglats av triggern inte orsakar dubbletter.

-- Run repeatedly (from a script) until 0 rows are moved.
INSERT INTO events_new (id, user_id, event_type, payload, created_at)
SELECT id, user_id, event_type, payload, created_at
FROM   events_old
WHERE  id > :last_id
ORDER  BY id
LIMIT  10000
ON CONFLICT (id, created_at) DO NOTHING;

-- Capture MAX(id) of this batch as the next :last_id, then COMMIT.

Varför batchar slår en enda stor kopiering

Små batcher spelar roll av konkreta skäl:

Låsets varaktighet — varje batch håller radlås i millisekunder, inte timmar.
WAL och bloat — committade batcher gör att VACUUM och checkpoints hinner med; en enda enorm transaktion får WAL att svälla.
Replikfördröjning — repliker kan tillämpa små delar jämnt i stället för att stanna upp på grund av en enorm transaktion.
Återupptagning — vid en krasch under migreringen går bara den aktuella batchen förlorad.

Begränsa hastigheten med en kort pg_sleep mellan batcherna om Ni ser I/O-belastning eller replikfördröjning.

-- Optional throttle between batches to ease I/O / replica lag.
SELECT pg_sleep(0.2);

Steg 5: Stäm av och lägg till index

Innan bytet ska Ni verifiera att tabellerna överensstämmer och skapa de index som den nya tabellen behöver.

I en partitionerad tabell skapar ett index på den överordnade tabellen automatiskt motsvarande index på varje partition. Använd CREATE INDEX (det sprids till partitionerna) och skapa indexet när backfill är klar för att undvika att kopieringen går långsammare.

-- Sanity check: counts should match (allow for in-flight writes).
SELECT (SELECT count(*) FROM events_old)  AS old_count,
       (SELECT count(*) FROM events_new)  AS new_count;

-- Cascades to all current and future partitions.
CREATE INDEX idx_events_new_user_id
    ON events_new (user_id);

CREATE INDEX idx_events_new_created_at
    ON events_new (created_at);

Steg 6: Det atomära bytet

Övergången är en enda kort transaktion som byter namn på tabellerna. Eftersom RENAME endast ändrar katalogposter kräver det ett ACCESS EXCLUSIVE-lås under ett mycket kort ögonblick.

Ta först bort speglings-triggern (den nya tabellen ska snart bli den riktiga), kör en sista catch-up-batch och byt sedan namn. Håll den här transaktionen så liten som möjligt.

BEGIN;

DROP TRIGGER trg_mirror_events ON events_old;

-- Final tiny catch-up for any rows written since last batch.
INSERT INTO events_new (id, user_id, event_type, payload, created_at)
SELECT id, user_id, event_type, payload, created_at
FROM   events_old
ON CONFLICT (id, created_at) DO NOTHING;

ALTER TABLE events_old RENAME TO events_retired;
ALTER TABLE events_new RENAME TO events;

COMMIT;

Steg 7: Verifiera och städa sedan upp

Efter bytet ska Ni bekräfta att den aktiva tabellen är partitionerad och att partition pruning fungerar. Kör EXPLAIN på en fråga som avgränsas till ett datumintervall: endast de relevanta partitionerna ska visas.

Behåll events_retired under en kort säkerhetsperiod och ta sedan bort den för att återta utrymme. Automatisera framöver skapandet av nästa månads partition i förväg.

-- Should touch only Jan/Feb partitions, not the whole table.
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE created_at >= '2024-01-10'
  AND created_at <  '2024-02-05';

-- After a safe verification window:
DROP TABLE events_retired;

Snabbkontroll: Välj strategi för övergången

Ni migrerar en skrivintensiv tabell med 1 miljard rader till månadsvisa intervallpartitioner och vill orsaka så lite störning som möjligt. Vilken slutlig övergångsmetod är korrekt?

Besvara frågan i den interaktiva lektionen

Sammanfattning: Online-migrering till partitioner

Ni konverterade en monolitisk tabell till partitioner utan driftstopp genom att:

Skapa en partitionerad skuggtabell med partitionsnyckeln i primärnyckeln.
Lägga till en standardpartition som skyddsnät och skapa nödvändiga intervall i förväg.
Installera en speglings-trigger så att skrivningar i den aktiva tabellen går till båda tabellerna.
Göra backfill i små committade batcher med ON CONFLICT DO NOTHING för korta lås och möjlighet att återuppta arbetet.
Skapa index på den överordnade tabellen (de sprids till partitionerna) och stämma av antalen.
Genomföra ett atomärt RENAME-byte efter en sista catch-up och sedan ta bort den gamla tabellen.

Den vägledande principen är: håll aldrig ett enda långt lås eller en enda enorm transaktion — dela upp arbetet så att det aktiva systemet kan fortsätta betjäna trafik hela tiden.

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Migrera en enorm tabell till partitioner online” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Migrera en enorm tabell till partitioner online”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”Migrera en enorm tabell till partitioner online”?

Konvertera en befintlig monolitisk tabell till en partitionerad tabell med minimal låsning och utan dataförlust. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 4 av 4.

Hur lång tid tar lektionen ”Migrera en enorm tabell till partitioner online”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Välja partitionsnyckel och strategi
Partitionsbeskärning vid planering och körning
Automatisera skapande och kvarhållning av partitioner
Migrera en enorm tabell till partitioner online

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL