0Pricing
PostgreSQL Performance & Query Optimization · Lekcja

Migracja ogromnej tabeli do partycji online

Przekształć istniejącą monolityczną tabelę w tabelę partycjonowaną przy minimalnym blokowaniu i bez utraty danych.

Migracja ogromnej tabeli do partycji online to bezpłatna lekcja PostgreSQL Performance & Query Optimization na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej PostgreSQL Performance & Query Optimization, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

Części tej lekcji nie zostały jeszcze przetłumaczone i są wyświetlane po angielsku.

Why Migrate a Huge Table?

Imagine an events table holding 800 million rows of append-only log data. Every query scans a monstrous index, VACUUM runs for hours, and dropping old data with DELETE bloats the table.

Partitioning splits one logical table into many physical child tables based on a key (for example, created_at by month). Benefits include:

  • Partition pruning — the planner skips irrelevant partitions entirely.
  • Instant data retention — DROP or DETACH a whole month in milliseconds, no row-by-row delete.
  • Cheaper maintenance — VACUUM and reindex run per partition.

The challenge: doing this on a live, write-heavy table without long locks or data loss.

The Naive Approach and Its Trap

PostgreSQL cannot turn an existing plain table into a partitioned one with a single ALTER TABLE. A partitioned parent is a different kind of object created with PARTITION BY.

The tempting one-shot plan is: create the partitioned table, then move all rows in a single transaction.

This blocks the table with heavy locks for the entire copy and holds a giant transaction open. On 800M rows that means hours of downtime and enormous WAL. We need an online strategy instead.

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

Strategy Overview: Shadow Table + Backfill + Swap

The proven online recipe has four phases:

  • Create a new partitioned shadow table with matching columns and a partition key.
  • Dual-write — keep new rows flowing into both old and new tables via a trigger (or write to the new one once it exists).
  • Backfill historical rows in small, committed batches so locks stay short.
  • Swap names inside one short transaction, then drop the old table.

Each phase is individually safe and resumable. No single long-running lock, no lost writes.

Step 1: Create the Partitioned Shadow

Create a new parent declared with PARTITION BY RANGE on the chosen key. The partition key column must be part of the primary key in declarative partitioning.

We define monthly range partitions. Note that the parent itself stores no rows; each child owns a slice.

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

Step 2: A Default Partition as a Safety Net

If a row's key falls outside every defined range, the INSERT fails. While migrating you may not have created every month yet, so add a default partition to catch stragglers.

Watch out: attaching a new partition later requires PostgreSQL to scan the default to prove no conflicting rows exist. Keep the default empty in steady state by pre-creating the months you actually need.

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

Step 3: Keep New Writes in Sync

While we backfill the past, the application keeps inserting. We must not lose those live rows. A robust pattern is a trigger on the old table that mirrors every write into the new partitioned table.

Once backfill is done and swap is near, the trigger guarantees both tables stay identical for fresh 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();

Step 4: Backfill in Small Batches

Now copy historical rows in bounded, committed chunks. Each batch is its own transaction, so locks release immediately and you can pause or resume anytime.

Drive the loop by the primary key or a timestamp window. Use ON CONFLICT DO NOTHING so rows already mirrored by the trigger don't cause duplicates.

-- 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.

Why Batching Beats One Big Copy

Small batches matter for concrete reasons:

  • Lock duration — each batch holds row locks for milliseconds, not hours.
  • WAL and bloat — committed batches let VACUUM and checkpoints keep up; one giant transaction balloons WAL.
  • Replication lag — replicas apply small chunks smoothly instead of stalling on a huge transaction.
  • Resumability — a crash mid-migration loses only the current batch.

Throttle with a short pg_sleep between batches if you see I/O pressure or replica lag.

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

Step 5: Reconcile and Add Indexes

Before the swap, verify both tables agree and build the indexes the new table needs.

On a partitioned table, creating an index on the parent automatically creates matching indexes on every partition. Use CREATE INDEX (it cascades) and build it once backfill is complete to avoid slowing the copy.

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

Step 6: The Atomic Swap

The cutover is a single short transaction that renames tables. Because RENAME only changes catalog entries, it takes an ACCESS EXCLUSIVE lock for a tiny moment.

Drop the mirror trigger first (the new table is about to become the real one), do a final catch-up batch, then rename. Keep this transaction minimal.

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;

Step 7: Verify, Then Clean Up

After the swap, confirm the live table is partitioned and pruning works. EXPLAIN a date-bounded query: only the relevant partitions should appear.

Keep events_retired around for a short safety window, then drop it to reclaim space. Going forward, automate creating next month's partition ahead of time.

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

Quick Check: Choosing the Cutover Strategy

You are migrating a 1-billion-row, write-heavy table to monthly range partitions with the smallest possible disruption. Which final cutover approach is correct?

Recap: Online Partition Migration

You converted a monolithic table into partitions with no downtime by:

  • Creating a partitioned shadow table with the partition key in the primary key.
  • Adding a default partition as a safety net and pre-creating needed ranges.
  • Installing a mirror trigger so live writes hit both tables.
  • Backfilling in small committed batches with ON CONFLICT DO NOTHING for short locks and resumability.
  • Building indexes on the parent (they cascade to partitions) and reconciling counts.
  • Performing an atomic RENAME swap after a final catch-up, then dropping the retired table.

The guiding principle: never hold one long lock or one giant transaction — split the work so the live system keeps serving traffic throughout.

Często zadawane pytania

Czy lekcja „Migracja ogromnej tabeli do partycji online” jest bezpłatna?

Tak — pełny tekst „Migracja ogromnej tabeli do partycji online” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu PostgreSQL Performance & Query Optimization, przejdź na CoddyKit PRO. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

Co nauczysz się w „Migracja ogromnej tabeli do partycji online”?

Przekształć istniejącą monolityczną tabelę w tabelę partycjonowaną przy minimalnym blokowaniu i bez utraty danych. Ćwiczysz PostgreSQL Performance & Query Optimization z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć PostgreSQL Performance & Query Optimization?

Nie wymagamy żadnego doświadczenia. PostgreSQL Performance & Query Optimization w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Migracja ogromnej tabeli do partycji online”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji PostgreSQL Performance & Query Optimization?

Tak. Każda lekcja PostgreSQL Performance & Query Optimization zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Wybór klucza i strategii partycjonowania
  2. Zawężanie partycji podczas planowania i wykonywania
  3. Automatyzacja tworzenia i retencji partycji
  4. Migracja ogromnej tabeli do partycji online
← Powrót do PostgreSQL Performance & Query Optimization