Секционирование таблиц в PostgreSQL

Введение

В этой лабораторной работе вы научитесь реализовывать секционирование таблиц в PostgreSQL. Цель состоит в том, чтобы разделить большую таблицу на более мелкие, управляемые части, что может значительно улучшить производительность запросов и упростить задачи управления данными, такие как резервное копирование или архивирование.

Вы начнете с создания основной "родительской" таблицы, предназначенной для секционирования. Затем вы определите несколько "дочерних" таблиц, или секций, каждая из которых будет содержать данные для определенного диапазона дат. Наконец, вы вставите данные в родительскую таблицу и увидите, как PostgreSQL автоматически направляет их в правильную секцию. Вы также научитесь запрашивать секционированную таблицу и увидите, как PostgreSQL оптимизирует эти запросы, обращаясь только к соответствующим секциям.

Создание родительской секционированной таблицы

На этом шаге вы создадите основную таблицу sales, которая будет служить родительской таблицей для наших секций. Эта таблица определяет структуру для всех своих секций, но сама по себе не будет хранить никаких данных.

Сначала вам нужно подключиться к базе данных PostgreSQL. Откройте терминал и используйте следующую команду, чтобы запустить интерактивную оболочку psql от имени пользователя postgres:

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL, которое выглядит как postgres=#. Все последующие SQL-команды в этой лабораторной работе будут выполняться из этого приглашения.

Далее создайте таблицу sales. Эта таблица будет секционирована по диапазону (range) на основе столбца sale_date.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

Разберем эту команду:

• CREATE TABLE sales (...): Определяет столбцы для наших данных о продажах.
• PRIMARY KEY (sale_id, sale_date): В секционированной таблице первичный ключ должен включать столбец секционирования (sale_date).
• PARTITION BY RANGE (sale_date): Это ключевая часть. Она объявляет, что таблица секционируется с использованием метода RANGE по столбцу sale_date.

После выполнения команды вы должны увидеть сообщение о подтверждении создания таблицы (CREATE TABLE).

Чтобы проверить, была ли таблица создана, вы можете использовать команду \d в psql для описания структуры таблицы.

\d sales

Вывод покажет столбцы таблицы, а внизу подтвердит, что это "Partitioned table" (Секционированная таблица) и перечислит "Partition key" (Ключ секционирования).

                                       Table "public.sales"
   Column    |     Type      | Collation | Nullable |                    Default
-------------+---------------+-----------+----------+------------------------------------------------
 sale_id     | integer       |           | not null | nextval('sales_sale_id_seq'::regclass)
 sale_date   | date          |           | not null |
 product_id  | integer       |           |          |
 sale_amount | numeric(10,2) |           |          |
Partition key: RANGE (sale_date)
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0

Обратите внимание, что "Number of partitions" (Количество секций) равно 0. Фактические секции вы создадите на следующем шаге.

Определение секций для диапазонов дат

Теперь, когда у вас есть родительская таблица sales, вам нужно создать фактические секции, где будут храниться данные. Каждая секция будет содержать данные для определенного диапазона дат. На этом шаге вы создадите квартальные секции для 2023 и 2024 годов.

Вы все еще должны находиться в интерактивном терминале psql.

Сначала создайте четыре секции для 2023 года. Каждая команда определяет новую таблицу как секцию таблицы sales и указывает диапазон дат, который она будет охватывать.

CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

Предложение FOR VALUES FROM ... TO ... определяет диапазон для каждой секции. Нижняя граница включается, а верхняя граница исключается. Например, sales_2023_q1 будет хранить записи, где sale_date находится в диапазоне от 2023-01-01 до 2023-04-01 (не включая 2023-04-01).

Далее создайте секции для 2024 года, используя ту же квартальную схему:

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

После выполнения каждой команды CREATE TABLE вы увидите сообщение о подтверждении.

Чтобы убедиться, что все секции были созданы, вы можете снова вывести список таблиц в базе данных.

\dt

Теперь в выводе вы должны увидеть родительскую таблицу sales и все восемь секций, которые вы только что создали (sales_2023_q1, sales_2023_q2 и т. д.).

Вставка и маршрутизация данных

На этом шаге вы вставите пример данных. Ключевая особенность секционирования заключается в том, что вы вставляете данные непосредственно в родительскую таблицу (sales), а PostgreSQL автоматически маршрутизирует каждую строку в соответствующую секцию на основе значения ключа секционирования (sale_date).

Вы все еще должны находиться в интерактивном терминале psql.

Выполните следующую инструкцию INSERT, чтобы добавить 16 записей о продажах, охватывающих 2023 и 2024 годы:

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);

После завершения команды вы увидите вывод INSERT 0 16, который указывает на то, что 16 строк были успешно вставлены.

Чтобы проверить, были ли данные правильно маршрутизированы, вы можете выполнить запрос к отдельным секциям. Например, давайте проверим количество записей в первом квартале 2023 года:

SELECT COUNT(*) FROM sales_2023_q1;

Вывод должен быть следующим:

 count
-------
     2
(1 row)

Теперь проверьте количество записей в четвертом квартале 2024 года:

SELECT COUNT(*) FROM sales_2024_q4;

Вывод также должен быть 2. Это подтверждает, что PostgreSQL поместил данные в правильные нижележащие секционные таблицы.

Запрос данных и анализ производительности

На заключительном этапе вы выполните запрос к секционированной таблице sales. Основное преимущество секционирования, известное как "отсечение секций" (partition pruning), заключается в том, что планировщик запросов PostgreSQL достаточно умен, чтобы сканировать только необходимые секции, избегая полного сканирования всего набора данных.

Вы все еще должны находиться в интерактивном терминале psql.

Сначала выполните запрос для получения всех продаж за первый квартал 2023 года.

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Вы увидите две записи, попадающие в этот диапазон дат. Чтобы увидеть, как PostgreSQL оптимизирует это, вы можете использовать команду EXPLAIN, которая показывает план выполнения запроса.

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Вывод будет выглядеть примерно так:

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on sales_2023_q1 sales  (cost=0.00..31.75 rows=7 width=28)
   Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)

Обратите внимание на строку Seq Scan on sales_2023_q1. Это доказывает, что PostgreSQL сканировал только секцию sales_2023_q1 и проигнорировал остальные семь, что делает запрос намного быстрее на большом наборе данных.

Теперь выполним более сложный запрос для расчета общей суммы продаж по каждому продукту за 2024 год.

SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;

Этот запрос будет эффективно сканировать только четыре секции за 2024 год для расчета результата. Вывод покажет общие продажи для каждого продукта от 109 до 116.

Наконец, вы можете выйти из интерактивного терминала PostgreSQL, набрав:

\q

Вы вернетесь к обычному приглашению командной строки.

Резюме

В этой лабораторной работе вы изучили основы секционирования таблиц в PostgreSQL. Вы успешно создали родительскую таблицу, секционированную по диапазону дат, определили конкретные секции для различных периодов времени и вставили данные, которые были автоматически маршрутизированы в соответствующую секцию. Самое главное, вы использовали команду EXPLAIN, чтобы увидеть отсечение секций в действии, демонстрируя, как секционирование может значительно улучшить производительность запросов, позволяя базе данных сканировать только подмножество данных. Это мощный метод для эффективного управления большими наборами данных.