Particionirovanie-tablic-v-PostgreSQL (1)

1.

Партиционирование
таблиц в PostgreSQL
Углубленное понимание и практическое применение для
разработчиков и DBA.

2.

Глава 1: Основы
Что такое Партиционирование?
Партиционирование (Partitioning) — это механизм разделения одной логически большой таблицы на несколько меньших,
физически независимых частей, называемых партициями.
Родительская Таблица
Партиции
Логическая сущность, через которую пользователь
Отдельные физические таблицы, хранящие
взаимодействует со всеми данными.
подмножество данных родительской таблицы.

3.

Ключевые Цели Партиционирования (The "Почему")
Применение партиционирования позволяет решить ряд критических проблем, связанных с работой с очень большими таблицами (миллионы и
миллиарды строк).
Ускорение Запросов
Упрощение Управления
Удобный Жизненный Цикл
PostgreSQL использует отсечение партиций
Операции обслуживания (например, очистка,
Старые данные (архивы по годам или
(partition pruning), чтобы сканировать только
резервное копирование, удаление) могут
месяцам) можно легко удалить или
те части таблицы, которые содержат нужные
выполняться для отдельных партиций,
архивировать, просто отсоединив и удалив
данные.
снижая общую нагрузку.
партицию (операция занимает
миллисекунды).

4.

Глава 2: Типы партиционирования
Три Основных Метода Разделения Данных в
PostgreSQL
PostgreSQL поддерживает декларативное партиционирование с тремя основными стратегиями, подходящими для различных типов
данных и сценариев использования.
RANGE (по диапазону)
LIST (по списку)
HASH (по хэшу)
Деление таблицы на основе диапазонов
Разделение данных о продажах или логах по
значений (например, по датам или числовым ID).
годам/месяцам.
Деление таблицы по конкретным, дискретным
Разделение клиентов по странам, регионам или
значениям (например, по строковым полям).
статусам.
Деление данных равномерно между заданным
Автоматическое равное распределение записей
числом партиций с помощью хэш-функции.
для балансировки нагрузки, когда нет
естественного диапазона или списка.

5.

RANGE Партиционирование: Разделение по Времени
Этот тип идеален для временных рядов и архивных данных, где данные организованы последовательно.
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount NUMERIC) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2024 PARTITION OF salesFOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
Как это работает:
Каждая запись с датой продажи в пределах 2024 года будет автоматически помещена в таблицу sales_2024. При запросе
данных за 2024 год, PostgreSQL полностью игнорирует партиции за другие годы.

6.

LIST Партиционирование: Разделение по
Категориям
LIST-партиционирование используется, когда нужно разделить данные по конечному набору значений ключа.
CREATE TABLE customers (
id SERIAL,
name TEXT,
city TEXT) PARTITION BY LIST (city);CREATE
TABLE customers_astana PARTITION OF customers FOR VALUES IN ('Astana');CREATE TABLE customers_other
PARTITION OF customers DEFAULT;
Astana
Конкретное значение города для отдельной партиции.
Almaty
Каждое значение списка имеет свою физическую таблицу.
DEFAULT
Специальная партиция для всех значений, не упомянутых в списке.

7.

HASH Партиционирование: Равномерное Распределение
Если естественные диапазоны отсутствуют, HASH-партиционирование гарантирует равное распределение строк по всем партициям, что идеально для балансировки нагрузки.
CREATE TABLE logs (id SERIAL, message TEXT) PARTITION BY HASH (id);CREATE TABLE logs_p1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
Система использует формулу (hash(key) % MODULUS = REMAINDER), чтобы определить, в какую из 4-х партиций (0, 1, 2, 3) попадет строка.
MODULUS & REMAINDER
Используется для определения индекса партиции (например,
MODULUS 4).
Вычисление Хэша
PostgreSQL вычисляет хэш-значение ключа
партиционирования (например, ID).
Балансировка
Обеспечивает максимально равномерное распределение,
минимизируя "горячие" партиции.

8.

Подтверждение Эффектив ности: Отсечение Партиций
(Pruning)
Основной механизм ускорения запросов — это Partition Pruning, который исключает ненужные партиции из плана выполнения.
EXPLAIN SELECT * FROM logs WHERE id = 2;
Если запрос содержит условие по ключу партиционирования (например, WHERE id = 2), команда EXPLAIN покажет, что планировщик обращается только к
одной, нужной партиции (logs_p2 или любой другой), а не сканирует всю родительскую таблицу или все партиции.
Этот механизм значительно сокращает время выполнения запроса, так как уменьшает объем данных, которые необходимо прочитать с диска.

9.

Управление Жизненным Циклом
Партиций
Партиционирование упрощает такие задачи, как добавление новых периодов данных или
удаление устаревших архивов.
Добавление (Attach)
Создание новой партиции для будущего периода происходит без простоя
родительской таблицы. Это мгновенная операция.
CREATE TABLE sales_2025 PARTITION OF salesFOR VALUES FROM ('2025-0101') TO ('2026-01-01');
Удаление (Detach/Drop)
Удаление старых данных (например, 2023 года) выполняется через удаление
партиции целиком (DROP TABLE sales_2023), что занимает миллисекунды, вместо
многочасового DELETE.
DROP TABLE sales_2023;

10.

Ограничения и Лучшие Практики
Хотя партиционирование мощно, оно имеет ряд нюансов, которые необходимо учитывать при проектировании схемы.
Индексы
Индексы должны быть созданы отдельно для каждой партиции.
Индекс на родительской таблице не наследуется автоматически
(кроме случая использования Primary Key).
Внешние Ключи (FOREIGN KEY)
PostgreSQL не поддерживает прямые FK-связи между отдельными
партициями, но поддерживает FK с родительской таблицы на внешнюю
Обновление Ключа
таблицу.
Обновление поля, по которому происходит партиционирование
(например, sale_date), требует перемещения строки между партициями,
что является дорогостоящей операцией.
Правило Вставки
При вставке данных PostgreSQL строго требует, чтобы строка
соответствовала ровно одной партиции. Иначе вставка завершится
ошибкой, если нет партиции DEFAULT.
Итог
Партиционирование — это обязательный инструмент для оптимизации работы с таблицами, содержащими миллионы и миллиарды записей, обеспечивающий
высокую производительность и удобство управления хранилищем.