24.11.2023

Секціонування в Postgresql

Секціонування таблиць є потужним методом оптимізації баз даних, особливо коли маємо справу з великими об'ємами даних. Цей підхід дозволяє розбити таблицю на частини, які називаються секціями чи партиціями, згідно з певним критерієм. Кожна секція може зберігати дані, що відповідають конкретному діапазону значень або іншому умовному правилу.

Переваги секціонування таблиць:

1. Підвищення швидкодії:
   • Спрощує пошук та обробку даних, оскільки операції виконуються лише над необхідними секціями.
2. Легша обслуговуваність:
   • Зменшує час необхідний для виконання операцій у великих таблицях.
3. Оптимізація роботи з пам'яттю:
   • Дозволяє ефективно використовувати ресурси системи для обробки даних.

Реалізація секціонування в PostgreSQL:

Припустимо, у нас є таблиця sales, яку ми хочемо секціонувати за роком продажу. Давайте розглянемо реалізацію цього сценарію.

-- Створення таблиці sales
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    sale_date DATE,
    amount DECIMAL
);

-- Створення партицій для секціонування за роком
CREATE TABLE sales_2019 PARTITION OF sales FOR VALUES FROM (’2019-01-01’) TO (’2020-01-01’);
CREATE TABLE sales_2020 PARTITION OF sales FOR VALUES FROM (’2020-01-01’) TO (’2021-01-01’);
CREATE TABLE sales_2021 PARTITION OF sales FOR VALUES FROM (’2021-01-01’) TO (’2022-01-01’);
-- і так далі...

-- Індекс на основі секціонування
CREATE INDEX idx_sales_date ON sales (sale_date);

-- Вставка даних в різні партиції
INSERT INTO sales_2019 VALUES (1, ’Product A’, ’2019-05-15’, 1000.00);
INSERT INTO sales_2020 VALUES (2, ’Product B’, ’2020-03-22’, 1500.00);
INSERT INTO sales_2021 VALUES (3, ’Product C’, ’2021-08-10’, 1200.00);

-- Запит для отримання даних з певної секції
SELECT * FROM sales WHERE sale_date BETWEEN ’2020-01-01’ AND ’2021-01-01’;

У цьому прикладі ми створюємо таблицю sales та її партиції на основі значень sale_date. Індекс на основі секціонування спрощує пошук та забезпечує оптимальну швидкодію запитів.

Секціонування таблиць в PostgreSQL - це потужний механізм для оптимізації роботи з великими об'ємами даних та важливий інструмент для підтримки високої продуктивності баз даних.

Динамічне створення таблиць секцій за допомогою тригерів

Зважаючи на ваш запит щодо створення таблиць у секціонований спосіб динамічно по даті документа, ось приклад програмної реалізації на мові PostgreSQL (PL/pgSQL). В цьому прикладі я використовую функцію, яка автоматично створює нову таблицю для кожного року, якщо така таблиця ще не існує.

CREATE OR REPLACE FUNCTION create_partition_table(p_year INT) RETURNS VOID AS $$
BEGIN
    -- Створення імені таблиці на основі року
    DECLARE
        table_name VARCHAR := ’sales_’ || p_year;
        sql_statement TEXT;
    BEGIN
        -- Перевірка існування таблиці
        IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = table_name) THEN
            -- Створення нової таблиці з відповідним секціонуванням
            sql_statement := ’CREATE TABLE ’ || table_name || ’ PARTITION OF sales FOR VALUES FROM (’’’ || p_year || ’-01-01’’) TO (’’’ || (p_year + 1) || ’-01-01’’)’;
            EXECUTE sql_statement;
        END IF;
    END;
END;
$$ LANGUAGE plpgsql;

Ця функція create_partition_table приймає рік як вхідний параметр і перевіряє, чи існує таблиця для цього року. Якщо таблиці не існує, то вона створюється з використанням EXECUTE для динамічного формування SQL-запиту.

Приклад виклику цієї функції:

-- Виклик функції для створення таблиці для року 2022
SELECT create_partition_table(2022);

Цей приклад взятий на основі стандартного призначення, що ім'я таблиці містить рік у своєму імені. Вам може знадобитися модифікувати цей приклад залежно від вашого конкретного використання та конвенцій іменування.

Автор: Рудюк С.А. 2023. K2 Cloud ERP.