Partycjonowanie - SQL

Lista praktycznych SQL do zarządzania partycjami przy założeniu, że partycje są twórzone po kolumnie typu timestamp na okres 1 miesiąca.

TWORZENIE PARTYCJI

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('2023-01-01 00:00:00'::timestamp, '2023-06-01 00:00:00'::timestamp, '1 month'::interval) i
),
t AS (
   SELECT 'transaction' AS table - tu wpisz listę rodziców
    UNION SELECT 'email'
    UNION SELECT 'transaction_withsettlement'
    UNION SELECT 'balancelog'
    UNION SELECT 'emailstatus'
    UNION SELECT 'fees'
    UNION SELECT 'notification'
    UNION SELECT 'notificationid'
    UNION SELECT 'params'
    UNION SELECT 'parserdata'
    UNION SELECT 'product'
    UNION SELECT 'itnparserdata'
    UNION SELECT 'transactionanalyticdata'
)
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
ORDER BY
    1;

SPRAWDZANIE POTENCJALNIE BRAKUJĄCYCH INDEKSÓW

WITH t AS (
    SELECT
        '2022_12' AS old_partition_period, -- tu partycja z wzorcowym indexopwaniem
        '2023_01' AS new_partition_period -- tu partycja, której indeksy chcesz sprawdzić
),
p AS (
   SELECT 'transaction' AS table - tu wpisz listę rodziców
    UNION SELECT 'email'
    UNION SELECT 'transaction_withsettlement'
    UNION SELECT 'balancelog'
    UNION SELECT 'emailstatus'
    UNION SELECT 'fees'
    UNION SELECT 'notification'
    UNION SELECT 'notificationid'
    UNION SELECT 'params'
    UNION SELECT 'parserdata'
    UNION SELECT 'product'
    UNION SELECT 'itnparserdata'
    UNION SELECT 'transactionanalyticdata'
)
SELECT
    replace(
      replace(
        pg_get_indexdef(indexrelid),
        old_partition_period,
        new_partition_period),
      'INDEX',
      'INDEX CONCURRENTLY') || ';'
FROM
    pg_stat_user_indexes,
    t,
    p
WHERE
    relname = p.table || '_' || old_partition_period
    AND replace(indexrelname, old_partition_period, new_partition_period)
    NOT IN (
        SELECT
            indexrelname
        FROM
            pg_stat_user_indexes
        WHERE
            relname = p.table || '_' || new_partition_period);

USUWANIE PARTYCJI

WITH s AS (
    SELECT
        CASE WHEN m < 10 THEN
            y::text || '_0'::text || m::text
        ELSE
            y::text || '_' || m::text
        END AS s
    FROM
        generate_series(2020, 2020) y,
        generate_series(1, 12) m
)
SELECT
    'ALTER TABLE ' || inhparent::regclass || ' DETACH PARTITION ' || inhrelid::regclass || '; ' -- || 'ALTER TABLE ' || inhrelid::regclass || ' SET SCHEMA archive;'
FROM
    pg_inherits,
    s
WHERE
    inhparent IN (
        SELECT
            oid
        FROM
            pg_class
        WHERE
            relkind = 'p')
    AND inhrelid::regclass::text LIKE '%' || s || '%'
ORDER BY
    1;

Last modified: 30 May 2024