DEV Community

Andrzej Klusiewicz
Andrzej Klusiewicz

Posted on

pg_partman - automatyczne partycjonowanie tabel w PostgreSQL

#postgres #database #partitioning #devops

Reczne zarzadzanie partycjami w PostgreSQL - dodawanie nowych, usuwanie starych - szybko robi sie uciazliwe przy tabelach rosnacych codziennie. pg_partman automatyzuje ten cykl w calosci. Pokazujemy jak go skonfigurowac.

Z tego artykułu dowiesz się:

  • Czym jest pg_partman i kiedy warto go użyć
  • Jak zainstalować i skonfigurować rozszerzenie
  • Jak tworzyć partycje czasowe (dzień, miesiąc, rok) i zakresowe (po ID)
  • Jak zarządzać retencją - automatyczne usuwanie starych partycji
  • Jak migrować dane z niepatycjonowanej tabeli
  • Jak skonfigurować background worker do automatycznej konserwacji

Partycjonowanie tabel to jeden z najskuteczniejszych sposobów na przyspieszenie zapytań i zarządzanie rozrastającymi się danymi. Logi zdarzeń, dane IoT, tabele zamówień z wieloma milionami wierszy - bez partycjonowania pełne skany i indeksy stają się wąskim gardłem.

Problem polega na tym, że natywne partycjonowanie PostgreSQL (PARTITION BY RANGE) jest świetne strukturalnie, ale wymaga ręcznego tworzenia każdej partycji. Przyszłe partycje nie powstaną same z siebie. Stare nie usuną się automatycznie. Wchodzi pg_partman.

Czym jest pg_partman?

pg_partman (Partition Manager) to rozszerzenie PostgreSQL, które automatyzuje cały cykl życia partycji:

  • Premake - tworzy partycje z wyprzedzeniem (domyślnie 4 naprzód)
  • Retention - usuwa stare partycje po zadanym czasie
  • Migracja danych - przeprowadza dane z istniejącej tabeli do struktury partycjonowanej
  • Background worker - odpala konserwację automatycznie, bez crona
  • Monitorowanie - widok part_config ze stanem wszystkich partycjonowanych tabel

pg_partman współpracuje z deklaratywnym partycjonowaniem PostgreSQL (dostępnym od wersji 10). Obsługuje PostgreSQL 13-17 i jest aktywnie rozwijany na GitHubie.

Instalacja

Debian / Ubuntu 

# PostgreSQL 16
sudo apt install postgresql-16-partman

# PostgreSQL 17
sudo apt install postgresql-17-partman
Enter fullscreen mode Exit fullscreen mode

Red Hat / Rocky Linux 

sudo dnf install pg_partman_17
Enter fullscreen mode Exit fullscreen mode

Aktywacja w bazie

pg_partman wymaga własnego schematu. Dobrą praktyką jest dedykowany schemat partman:

-- Utwórz dedykowany schemat
CREATE SCHEMA partman;

-- Zainstaluj rozszerzenie w tym schemacie
CREATE EXTENSION pg_partman SCHEMA partman;

-- Weryfikacja
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_partman';
--   extname   | extversion
-- ------------+------------
--  pg_partman | 5.2.4
Enter fullscreen mode Exit fullscreen mode

Partycjonowanie czasowe - tabela logów

Najpopularniejszy przypadek użycia: tabela zdarzeń lub logów partycjonowana po dacie. Zamiast jednej ogromnej tabeli z indeksem na created_at, mamy osobną partycję na każdy miesiąc (lub dzień, rok).

Krok 1 - utwórz tabelę główną (parent) 

-- Tabela główna: musi mieć PARTITION BY RANGE
CREATE TABLE app_events (
    id          BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id     INTEGER,
    event_type  VARCHAR(64),
    payload     JSONB
) PARTITION BY RANGE (created_at);

-- Indeks na tabeli głównej jest automatycznie kopiowany na partycje
CREATE INDEX ON app_events (user_id, created_at);
CREATE INDEX ON app_events (event_type, created_at);
Enter fullscreen mode Exit fullscreen mode

Krok 2 - zarejestruj tabelę w pg_partman 

SELECT partman.create_parent(
    p_parent_table  => 'public.app_events',
    p_control       => 'created_at',         -- kolumna partycjonująca
    p_interval      => '1 month',            -- wielkość partycji
    p_premake       => 3                       -- ile partycji naprzód tworzyć
);
-- Wynik: t (true = sukces)
Enter fullscreen mode Exit fullscreen mode

Po wywołaniu create_parent pg_partman automatycznie tworzy partycje: bieżący miesiąc + 3 naprzód. Sprawdź efekt:

-- Lista partycji tabeli app_events
SELECT
    child.relname        AS partycja,
    pg_get_expr(child.relpartbound, child.oid) AS zakres
FROM pg_inherits i
JOIN pg_class parent ON i.inhparent = parent.oid
JOIN pg_class child  ON i.inhrelid  = child.oid
WHERE parent.relname = 'app_events'
ORDER BY child.relname;

--         partycja          |                    zakres
-- --------------------------+----------------------------------------------
--  app_events_p20260601     | FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')
--  app_events_p20260701     | FOR VALUES FROM ('2026-07-01') TO ('2026-08-01')
--  app_events_p20260801     | FOR VALUES FROM ('2026-08-01') TO ('2026-09-01')
--  app_events_p20260901     | FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
--  app_events_default       | DEFAULT
Enter fullscreen mode Exit fullscreen mode

Partycja DEFAULT - pg_partman tworzy partycję _default, która przechowuje wiersze nienależące do żadnej istniejącej partycji. Gdy tworzysz nową partycję, dane z DEFAULT są do niej migrowane automatycznie.

Dostępne interwały czasowe

Interwał Przykład nazwy partycji Kiedy stosować
'1 hour' events_p2026060914 Logi o bardzo wysokim wolumenie (miliony/dzień)
'1 day' events_p20260609 Logi aplikacji, dane IoT
'1 week' events_p2026w23 Dane transakcyjne, sesje
'1 month' events_p20260601 Zamówienia, faktury - najczęstszy wybór
'3 months' events_p20260401 Dane archiwalne, rzadkie zapytania
'1 year' events_p20260101 Długoterminowe dane historyczne

Retencja - automatyczne usuwanie starych partycji

Retencja to jedna z najważniejszych cech pg_partman. Zamiast pisać cron z DROP TABLE, konfigurujesz politykę raz:

-- Ustaw retencję: zachowaj dane z ostatnich 6 miesięcy
UPDATE partman.part_config
SET
    retention               = '6 months',   -- usuń partycje starsze niż 6 mies.
    retention_keep_table    = false,          -- DROP TABLE (nie tylko DETACH)
    retention_keep_index    = false,          -- usuń też indeksy
    infinite_time_partitions = true           -- zezwól na dane z dalekiej przyszłości
WHERE parent_table = 'public.app_events';

-- Alternatywnie: tylko DETACH bez DROP (bezpieczniej na start)
UPDATE partman.part_config
SET
    retention            = '6 months',
    retention_keep_table = true   -- tabela zostaje, tylko odłączona od parenta
WHERE parent_table = 'public.app_events';
Enter fullscreen mode Exit fullscreen mode

Uwaga: retention_keep_table = false trwale usuwa dane. Zanim włączysz DROP, przetestuj z retention_keep_table = true (DETACH) i sprawdź, czy odłączone tabele mają właściwe dane.

run_maintenance() - ręczna konserwacja

Konserwacja (tworzenie przyszłych partycji + retencja) uruchamia się przez:

-- Uruchom konserwację dla wszystkich tabel zarządzanych przez pg_partman
CALL partman.run_maintenance_proc();

-- Lub tylko dla konkretnej tabeli
CALL partman.run_maintenance_proc(p_parent_table => 'public.app_events');

-- Starsza forma (PostgreSQL < 14, bez CALL)
SELECT partman.run_maintenance();
Enter fullscreen mode Exit fullscreen mode

Po wywołaniu sprawdź logi i stan partycji:

-- Stan konfiguracji partycjonowania
SELECT
    parent_table,
    control,
    partition_interval,
    premake,
    retention,
    retention_keep_table,
    last_partition
FROM partman.part_config;

--     parent_table    | control    | partition_interval | premake | retention | ...
-- --------------------+------------+--------------------+---------+-----------+----
--  public.app_events  | created_at | 1 month            |       3 | 6 months  | ...
Enter fullscreen mode Exit fullscreen mode

Partycjonowanie zakresowe po ID

Partycjonowanie po kluczu całkowitym (np. ID) sprawdza się przy ogromnych tabelach, gdzie zapytania często filtrują po zakresie ID:

-- Tabela zamówień partycjonowana co 1 000 000 rekordów
CREATE TABLE zamowienia (
    id          BIGINT NOT NULL,
    klient_id   INTEGER NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    kwota       NUMERIC(12,2),
    status      VARCHAR(32)
) PARTITION BY RANGE (id);

SELECT partman.create_parent(
    p_parent_table => 'public.zamowienia',
    p_control      => 'id',
    p_interval     => '1000000',    -- partycja co milion wierszy
    p_start_partition => '1'        -- zacznij od ID=1
);

-- Partycje: zamowienia_p1, zamowienia_p1000001, zamowienia_p2000001, ...
Enter fullscreen mode Exit fullscreen mode

Partycjonowanie po ID kontra po czasie - partycjonowanie po ID ma sens gdy zapytania filtrują po zakresie ID lub gdy chcesz archiwizować starsze partie danych. Dla danych czasowych (logi, zdarzenia) zdecydowanie lepsze jest partycjonowanie po TIMESTAMPTZ.

Migracja danych z istniejącej tabeli

Jeśli masz już tabelę z danymi i chcesz ją przepartycjonować, pg_partman udostępnia funkcję do migracji wsadowej - bez blokowania tabeli na czas całej operacji:

-- Stara tabela (bez partycjonowania)
CREATE TABLE logi_stare (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMPTZ NOT NULL,
    tresc      TEXT
);
-- ... miliony istniejących wierszy ...

-- 1. Utwórz nową tabelę partycjonowaną (z tą samą strukturą)
CREATE TABLE logi (
    id         BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    tresc      TEXT
) PARTITION BY RANGE (created_at);

SELECT partman.create_parent(
    p_parent_table => 'public.logi',
    p_control      => 'created_at',
    p_interval     => '1 month'
);

-- 2. Migruj dane wsadami (batch_interval = rozmiar wsadu)
-- Ta funkcja może być wywoływana wielokrotnie, aż zwróci 0
CALL partman.partition_data_proc(
    p_parent_table  => 'public.logi',
    p_source_table  => 'public.logi_stare',   -- skąd przenosić
    p_batch_count   => 10,                      -- ile wsadów na raz
    p_batch_interval => '1 month'              -- przenoś po miesiącu danych
);
-- Wywołuj w pętli, aż wszystkie dane zostaną przeniesione
Enter fullscreen mode Exit fullscreen mode

Wsadowa migracja zajmuje krótkie blokady na małe partie danych, więc tabela źródłowa jest dostępna podczas całego procesu. W praktyce uruchamia się to przez prostą pętlę w shellu:

# Bash: migruj dane, aż partition_data_proc zwróci 0 rowcount
while true; do
  result=$(psql -d mydb -c "CALL partman.partition_data_proc(
    p_parent_table  => 'public.logi',
    p_source_table  => 'public.logi_stare',
    p_batch_count   => 20
  );" 2>&1)
  echo "$result"
  sleep 1
done
Enter fullscreen mode Exit fullscreen mode

Background worker - bez crona

Zamiast konfigurować cron do wywołania run_maintenance(), pg_partman oferuje wbudowany background worker, który robi to automatycznie co N sekund:

-- postgresql.conf lub przez ALTER SYSTEM
ALTER SYSTEM SET shared_preload_libraries = 'pg_partman_bgw';

-- Interwał konserwacji (sekundy): domyślnie 3600 (1h)
ALTER SYSTEM SET pg_partman_bgw.interval = '3600';

-- Bazy, na których bgw ma działać (przecinkami)
ALTER SYSTEM SET pg_partman_bgw.dbname = 'mydb';

-- Rola, jako która bgw się łączy (musi mieć uprawnienia do partman)
ALTER SYSTEM SET pg_partman_bgw.role = 'postgres';

-- Załaduj konfigurację (wymaga restartu PostgreSQL)
Enter fullscreen mode Exit fullscreen mode 
-- Restart serwisu
sudo systemctl restart postgresql

-- Weryfikacja - bgw pojawi się w pg_stat_activity
SELECT pid, backend_type, state, query
FROM pg_stat_activity
WHERE backend_type = 'pg_partman_bgw';
--   pid  |   backend_type   | state |  query
-- -------+------------------+-------+---------
--  12345 | pg_partman_bgw   | idle  |
Enter fullscreen mode Exit fullscreen mode

bgw vs cron: Background worker to prostsze rozwiązanie dla typowych przypadków. Jeśli potrzebujesz różnych interwałów dla różnych baz lub bardziej szczegółowego harmonogramu, użyj pg_cron lub systemowego crona wywołującego CALL partman.run_maintenance_proc().

Praktyczny przykład - tabela zamówień e-commerce

Zestawmy wszystko razem: tabela zamówień partycjonowana miesięcznie, z retencją 2 lat i background workerem:

-- ============================================================
-- Tabela główna
-- ============================================================
CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    customer_id INTEGER NOT NULL,
    total_netto NUMERIC(12,2) NOT NULL,
    status      VARCHAR(32) NOT NULL DEFAULT 'NEW'
) PARTITION BY RANGE (created_at);

CREATE INDEX orders_customer_idx  ON orders (customer_id, created_at);
CREATE INDEX orders_status_idx    ON orders (status, created_at);

-- ============================================================
-- Rejestracja w pg_partman
-- ============================================================
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control      => 'created_at',
    p_interval     => '1 month',
    p_premake      => 4    -- twórz 4 partycje do przodu
);

-- ============================================================
-- Konfiguracja retencji: zachowaj 24 miesiące
-- ============================================================
UPDATE partman.part_config
SET
    retention            = '24 months',
    retention_keep_table = false,
    retention_keep_index = false,
    premake              = 4
WHERE parent_table = 'public.orders';

-- ============================================================
-- Sprawdź co zostało utworzone
-- ============================================================
SELECT
    child.relname    AS partycja,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS rozmiar,
    pg_get_expr(child.relpartbound, child.oid) AS zakres
FROM pg_inherits i
JOIN pg_class parent ON i.inhparent = parent.oid
JOIN pg_class child  ON i.inhrelid  = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- ============================================================
-- Partition pruning - PostgreSQL automatycznie pomija partycje
-- ============================================================
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*), SUM(total_netto)
FROM orders
WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31';
-- Planner czyta tylko partycję orders_p20260501 - reszta pomijana!
Enter fullscreen mode Exit fullscreen mode

Monitorowanie partycji 

-- Wszystkie tabele pod kontrolą pg_partman
SELECT
    parent_table,
    partition_interval,
    premake,
    retention,
    last_partition,
    sub_partition_set_full
FROM partman.part_config
ORDER BY parent_table;

-- Liczba wierszy per partycja (z katalogu systemowego)
SELECT
    child.relname AS partycja,
    child.reltuples::bigint AS wiersze_est,
    pg_size_pretty(pg_total_relation_size(child.oid)) AS rozmiar
FROM pg_inherits i
JOIN pg_class parent ON i.inhparent = parent.oid
JOIN pg_class child  ON i.inhrelid  = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- Historia konserwacji (logi pg_partman)
SELECT *
FROM partman.part_config_sub
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Na co uważać

Problem Rozwiązanie
Klucz główny musi zawierać kolumnę partycjonującą PRIMARY KEY (order_id, created_at) - dodaj created_at do PK
Indeksy UNIQUE muszą zawierać kolumnę partycjonującą W PostgreSQL 10+ każdy UNIQUE obejmuje tylko partycję, nie całą tabelę
INSERT do nieistniejącej partycji Dane trafiają do partycji DEFAULT; po run_maintenance() są migrowane
pg_dump partycjonowanej tabeli pg_dump domyślnie dumping tabeli parent + wszystkich partycji - działa poprawnie
Zmiana interwału partycjonowania Niemożliwa bez odtworzenia struktury - zaplanuj to przed wdrożeniem
Wysoka liczba partycji spowalnia planowanie Powyżej ~1000 partycji czas planowania rośnie; użyj większych interwałów

Chcesz opanować PostgreSQL w praktyce?

Szkolenie Administracja, replikacja i tuning baz danych PostgreSQL

Termin gwarantowany  |  5 dni

Partycjonowanie, pg_partman, replikacja strumieniowa, HA z Patroni, EXPLAIN ANALYZE, vacuuming

Sprawdź termin i zapisz się

Top comments (0)