Когда таблица в postgresql разрастается до десятков и сотен миллионов строк, два привычных инструмента перестают спасать: один процессор медленно перемалывает сканирование, а одна гигантская таблица одинаково тяжела для любого запроса. В этом уроке разберём, как заставить сервер считать запрос несколькими ядрами сразу (параллельное выполнение) и как физически разрезать большую таблицу на куски (декларативное партиционирование), чтобы планировщик читал только нужное. Это две ортогональные техники масштабирования, и они отлично работают вместе.
Как это работает
Параллельное выполнение - это когда ведущий процесс (leader) запускает несколько фоновых рабочих процессов (parallel workers), раздаёт им части работы, а потом собирает результаты. В плане запроса это видно по узлу Gather (или Gather Merge, если нужен сохранённый порядок): ниже него идёт параллельная часть, выше - последовательная сборка. Каждый воркер - это отдельный процесс ОС, поэтому параллелизм у PostgreSQL не бесплатный: на запуск воркеров и пересылку строк через очередь в разделяемой памяти тратится время.
Сколько воркеров дадут запросу, решают три ограничителя. max_worker_processes - общий лимит фоновых процессов на весь кластер. max_parallel_workers - сколько из них вообще можно отдать под параллельные запросы. max_parallel_workers_per_gather - потолок воркеров на один узел Gather. Если свободных слотов в пуле нет, запрос спокойно отработает с меньшим числом воркеров или вовсе последовательно - параллелизм всегда опционален.
Планировщик не включает параллелизм просто так. Таблица должна быть достаточно большой (порог задаёт min_parallel_table_scan_size, по умолчанию 8 МБ) и стоимость должна оправдывать накладные расходы. Параллельными бывают Seq Scan, Index Scan, агрегаты (частичная агрегация в воркерах плюс финальная сборка), хэш-джойны и nested loop. А вот изменения данных и многие конструкции параллелить нельзя.
Партиционирование - совсем про другое. Декларативное секционирование (с PARTITION BY, появилось в PG 10 и доведено до ума к 15) физически делит одну логическую таблицу на много дочерних таблиц-секций по значению ключа. Главный выигрыш - partition pruning: планировщик по условию WHERE понимает, какие секции точно не содержат нужных строк, и вообще их не трогает. Запрос по одному дню из таблицы за пять лет читает одну секцию вместо всей истории.
Есть три стратегии. RANGE - по диапазонам (типично по дате: месяц или год на секцию). LIST - по списку значений (например, регион или статус). HASH - по остатку хэша ключа, чтобы равномерно размазать строки по N секций, когда естественного диапазона нет. Pruning работает и на этапе планирования, и во время выполнения (runtime pruning) - последнее важно, когда значение приходит как параметр или из подзапроса.
SQL и примеры
Посмотрим, дают ли запросу воркеров. Тяжёлый агрегат по таблице перелётов с билетами обычно уходит в параллель:
Код: Выделить всё
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*), sum(amount)
FROM bookings.ticket_flights;
Управляем параллелизмом на лету в рамках сессии. Полезно для замеров:
Код: Выделить всё
SET max_parallel_workers_per_gather = 4;
SET min_parallel_table_scan_size = '1MB';
EXPLAIN ANALYZE
SELECT f.flight_no, count(*)
FROM bookings.flights f
JOIN bookings.ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.flight_no;
Код: Выделить всё
CREATE TABLE book_part (
book_ref char(6) NOT NULL,
book_date timestamptz NOT NULL,
total_amount numeric(10,2) NOT NULL
) PARTITION BY RANGE (book_date);
CREATE TABLE book_2017_q1 PARTITION OF book_part
FOR VALUES FROM ('2017-01-01') TO ('2017-04-01');
CREATE TABLE book_2017_q2 PARTITION OF book_part
FOR VALUES FROM ('2017-04-01') TO ('2017-07-01');
Код: Выделить всё
INSERT INTO book_part
SELECT book_ref, book_date, total_amount FROM bookings.bookings;
EXPLAIN
SELECT sum(total_amount)
FROM book_part
WHERE book_date >= '2017-05-01' AND book_date < '2017-06-01';
Удобная мелочь PG 11+: секция по умолчанию ловит всё, что не попало в диапазоны, чтобы INSERT не падал:
Код: Выделить всё
CREATE TABLE book_default PARTITION OF book_part DEFAULT;
- Ждут параллель, а её нет: таблица меньше порога, или закончились слоты max_parallel_workers, или запрос содержит непараллелизуемую конструкцию. Смотрите Workers Launched, а не Planned - они различаются при нехватке слотов.
- Считают, что больше воркеров - всегда быстрее. На пересылку строк и сборку в Gather уходит время; для лёгких запросов параллелизм только вредит. Подбирайте per_gather под реальное число ядер и нагрузку.
- Уникальный ключ или первичный ключ на секционированной таблице обязан включать столбец партиционирования - иначе СУБД не гарантирует уникальность между секциями.
- Pruning не срабатывает, если в условии WHERE по ключу стоит функция или приведение типа, мешающее планировщику. Сравнивайте ключ напрямую с константой того же типа.
- Слишком мелкие секции (тысячи штук) раздувают время планирования и память. Гонитесь за разумным числом - десятки-сотни, не тысячи.
- Забывают про default-секцию: INSERT со значением вне диапазонов падает с ошибкой. И помните - наличие default-секции замедляет добавление новых секций.
- Глобального индекса по всем секциям нет: индекс на партиционированной таблице создаётся на каждой секции отдельно.
Мини-лаба
- Шаг 1. Подключитесь к демобазе: psql -d demo. Замерьте тяжёлый агрегат: EXPLAIN ANALYZE SELECT count(*), sum(amount) FROM bookings.ticket_flights;
- Шаг 2. Запретите параллель: SET max_parallel_workers_per_gather = 0; повторите запрос. Сравните время и наличие узла Gather.
- Шаг 3. Верните 4 воркера и снизьте min_parallel_table_scan_size до 1MB. Повторите. Найдите Workers Planned и Workers Launched.
- Шаг 4. Создайте секционированную по RANGE таблицу book_part с двумя квартальными секциями и default-секцией (код из урока).
- Шаг 5. Залейте данные из bookings.bookings и выполните EXPLAIN запроса за один месяц. Убедитесь, что читается одна секция.
- Шаг 6. Добавьте условие с приведением типа (например, book_date::date = '2017-05-15') и сравните план - сохранился ли pruning.
- Шаг 7. Загляните в bookings.flights: эта таблица уже сделана партиционированной по дате вылета. Выполните \d+ bookings.flights и найдите список секций.
- Чем отличаются узлы Gather и Gather Merge в плане и когда выбирается второй?
- Какие три параметра ограничивают число параллельных воркеров и за что отвечает каждый?
- Почему параллельное выполнение не всегда ускоряет запрос и от чего это зависит?
- В чём разница между partition pruning на этапе планирования и runtime pruning?
- Когда выбрать HASH-партиционирование вместо RANGE или LIST?
- Почему первичный ключ секционированной таблицы должен включать ключ партиционирования?
