Partycjonowanie natywne

Wprowadzenie

O co chodzi?

Partycjowanie to podzielenie jednej dużje tabelki na fizyczne, mniejsze cześci - partycje. Nad nimi czuwa

Po co stosujemy partycjonowanie?

Podstawową korzyścią jakie daje

Po czym należy partycjonowac?

po kolumnie która będzie najcześciej używana w selectach i która wraz z odpowiednim trbem partycjonowania (list, range, hash) da w miarę równe rozłożenie rekordów po partycjach.

Schemat najczęściej stosowany w BM - partition by range +createtime

Mamy tu:

tabelę spartycjonowana o nazwie parent po kolumnie createtime, nazywaną dalej rodzicem

klucz główny - zwróć uwagę, że musi zawierać kolumne, po której partycjonujemy, bywa to uciążliwe szczególnie w wypadku partycjonowania po timestampie utworzenia rekordu

kolumna timestamp z datą utworzenia rekordu
korzyści
podkreślić, że nie zawsze to jest najlepszy model partycjonowania

Zarządzanie partycjami

W planach jest zbudowanie automatu który będzie w stanie

Listowanie partycjonowanych tabel

select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;

Dodawanie partycji

Utworzenie nowych tabel i jednoczesne dodanie ich jako partycje

WITH s AS (
    SELECT
        i AS start,
        i + '1 month'::interval AS stop,
        extract(YEAR FROM i)::text || '_' || lpad(extract(MONTH FROM i)::text, 2, '0') AS sufix
    FROM
        generate_series('2022-10-01 00:00:00'::timestamp, '2022-12-01 00:00:00'::timestamp, '1 month'::interval) i
),
t AS (
    SELECT
        'parent' AS p
        -- UNION SELECT 'parent2'
)
SELECT
    'CREATE TABLE partitions.' || t.p || '_' || sufix || E' PARTITION OF public.' || t.p || E'  FOR VALUES FROM (\'' || START || E'\') TO (\'' || stop || E'\');'
FROM
    s,
    t;

W linii:

4. Ustawiasz interwał, jakiego ma dotyczyć partycja

7. wskazujesz dla jakiego przedziału czasu maja być partycje utworzone

11. wskazujesz nazwę rodzica

12. zakomentowano przykład, jak dla utworzyć partycje dla kolejnych rodziców w jednym zapytaniu

Wyrównanie indeksacji

Poniższy sql wylsituje sqle do tworzenia brakujących indexów. Działa na zasadzie porównania dwóch partycji.

Do zmiany:

linia 3 - parent

linia 4,5 - suffix z datą partycji - np. tylko '2022_12'

WITH t AS (
	SELECT
	'transfer' AS table,
	'2022_12' AS old_partition_period,
	'2023_02' AS new_partition_period
)
SELECT
	replace(pg_get_indexdef(indexrelid), old_partition_period, new_partition_period) || ';'
FROM
	pg_stat_user_indexes, t
WHERE relname = t.table || '_' || old_partition_period
AND replace(indexrelname, old_partition_period, new_partition_period)
    NOT IN (
    	SELECT indexrelname FROM pg_stat_user_indexes WHERE relname = t.table || '_' || new_partition_period
    );

Dodanie istniejącej tabeli jako partycji

Usuwanie partycji

Indeksowanie

Ogólne zasady indeksowania

CREATE INDEX parent_tdata_idx ON ONLY parent (tdata);
CREATE INDEX CONCURRENTLY parent_2022_10_tdata_idx ON partitions.parent_2022_10 (tdata);
ALTER INDEX parent_tdata_idx ATTACH PARTITION partitions.parent_2022_10_tdata_idx;
ANALYZE parent;

W linii:

Tworzymy indeks na rodzicu - dzięki temu każda partycja, która powstanie będzie miała ten indeks
1. używamy klauzuli ONLY - indeks na tym moment powstanie tylko na rodzicu, ale ponieważ na tabeli spartycjonowanej nie możemy użyć CONCURRENTLY, znacząco skróci nam to locka
Tworzymy indeks na partycji / partycjach
1. ważne jest to, że niekoniecznie zawsze będziemy chcieli, żeby wszystkie partycje miały ten indeks
2. Tu już możemy użyć CONCURRENTLY
Dodajemy indeks partycji do indeksu głównego na rodzicu
1. dzięki temu do każdego
Koniecznie wykonujemy ANALYZE, optymalnie możemy wykonać VACUUM ANALYZE;

When CREATE INDEX is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the ONLY option is specified, no recursion is done, and the index is marked invalid. (ALTER INDEX ... ATTACH PARTITION marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using CREATE TABLE ... PARTITION OF will automatically have a matching index, regardless of whether ONLY is specified.

Last modified: 30 May 2024