Оптимизация PostgreSQL

Оптимизация СУБД, в частности, PostgreSQL – это комплексная задача по нескольким направлениям. Лучший подход - итеративный процесс: идентифицировать узкое место, произвести оптимизацию, протестировать, измерить результат, повторить несколько раз и применить на продуктивном контуре.
Ниже представлен достаточно полный список рекомендаций.

Оптимизация SQL-запросов

Оптимизация SQL-запросов требует постоянного внимания. Почти все перечисленные ниже приёмы тюнинга SQL-запросов справедливы как для PostgreSQL, так и для СУБД Oracle, MS MSQL, Teradata.

• Использование плана выполнения запроса запросов - команды EXPLAIN [options] query.
ANALYZE выполняет запрос, предоставляет статистику реального выполнения, показывает фактическое время, затраченное на каждый шаг плана, количество возвращённых строк и общее время выполнения.
BUFFERS показывает информацию об операциях ввода-вывода (I/O) в ходе выполнения запроса.
VERBOSE выдаёт сообщения, отмечая текущую обрабатываемую таблицу, печатает различную статистику.
• В запросах следует выбирать только необходимые столбцы, поскольку это уменьшает объем данных, передаваемых по сети объем данных и обрабатываемых в памяти. Инструкция SELECT * FROM ... подходит для быстрого интерактивного просмотра данных. Кроме того, такая команда чревата последствиями при использовании в программных приложениях / интеграциях.
• Использование LIMIT для выборки только части данных (например, для пагинации), особенно с ORDER BY.
• Необходимо фильтровать данные, применяя WHERE-условия, как можно раньше, как можно ближе к источнику данных, чтобы уменьшить объем данных для последующих операций JOIN, GROUP BY, ORDER BY.
• Следует избегать использования функций в WHERE-условиях, если они не индексированы:
WHERE LOWER(column) = 'value' не использует обычный B-tree индекс по column. Или необходимо создать индекс выражения: CREATE INDEX ... ON table (LOWER(column)).
WHERE TO_CHAR(date_column, 'YYYY-MM-DD') = '...' тоже не использует индекс.
• Надлежит избегать неявного преобразования типов данных, например, WHERE text_column = 456 потребует PostgreSQL преобразовать text_column в число для каждой строки, что опрокинет использование индекса. Лучше всегда явно приводить типы: WHERE text_column = '456'.
• Оптимизация LIKE / ILIKE:
LIKE 'prefix%' может использовать B-tree индекс;
LIKE '%substring%' или '%suffix' не использует B-tree индекс. Для таких случаев используйте полнотекстовый поиск (GIN / GiST индексы) или pg_trgm расширение;
ILIKE (без учёта регистра) по умолчанию не использует B-tree индекс, используйте выражение LOWER(column) или операторный класс text_pattern_ops в индексе.
• Оптимизация JOIN-ов:
Избегать ненужных JOIN-ов;
Использование LEFT JOIN / RIGHT JOIN / FULL JOIN только тогда, когда это действительно необходимо;
Наличие подходящих индексов по полям таблиц, участвующих в соединении.
• По возможности UNION ALL вместо UNION: UNION выполняет дополнительную операцию по удалению дубликатов, что значительно медленнее.
• Для подзапросов с большим количеством возвращаемых строк EXISTS часто более эффективен чем IN, так как EXISTS прекращает сканирование, как только находит первое совпадение.
• В случае больших таблиц надо убедиться, что для столбцов в ORDER BY и GROUP BY имеются подходящие индексы.
• ORDER BY - дорогостоящая операция, по возможности надо исполнять сортировку в клиентском приложении.
• Использование с умом Common Table Expressions (CTE): конструкции WITH улучшают восприятие человеком запроса, но не всегда обеспечивают производительность. Иногда CTE могут материализовываться (сохранять промежуточные результаты на диск), что может быть медленнее, чем прямой JOIN.
• Каким бы продвинутым ни был анализатор запросов, но большой сложный длинный SQL-запрос иногда лучше разбить на отдельные SQL-запросы: создать явным образом таблицу с указанной структурой полей, заполнить её результатом исполнения одного из SQL-запроса (потом даже, возможно, проиндексировать по подходящим полям), а затем использовать эту техническую таблицу в итоговом SQL-запросе, после чего промежуточную таблицу можно удалить. Кстати, такой приём удобен для анализа бизнес-логики, поскольку при отладке запросов можно просмотреть данные на промежуточных шагах.
• Транзакции (BEGIN ... COMMIT / ROLLBACK) должны быть как можно короче.
• При массовой вставке (INSERT INTO table_name (...)) миллионов строк в таблицу может быть быстрее прежде удалить индексы, а после вставки снова воссоздать индексы.

Оптимизация схемы базы данных и таблиц

Производительность баз данных и качество данных закладывается на этапе проектирования моделей данных.

• Выбор оптимальных типов данных для полей таблиц - минимально достаточный размер, например, SMALLINT вместо INTEGER, INTEGER вместо BIGINT, DATE вместо TIMESTAMP, TEXT вместо VARCHAR(N) (если нет строгого ограничения по длине строк, то VARCHAR(N) может быть менее эффективен при больших значениях N), NUMERIC - для точных денежных значений, FLOAT - для приблизительных числовых значений. Для UUID-идентификаторов следует утилизировать тип UUID, а не VARCHAR(36), т.к. он компактнее и эффективнее. Для хранения JSON-документов - JSONB вместо JSON.
Чем меньше размер полей, тем меньше расходуется оперативной памяти, кроме того, также экономится место на диске.
• Указание для внешних ключей FOREIGN KEY опций ON DELETE CASCADE или ON UPDATE CASCADE может быть удобно, но может вызывать блокировки.
• Для очень больших таблиц в десятки миллионов или миллиардов строк секционирование (Table Partitioning) может значительно улучшить производительность запросов и обслуживания (vacuum, index rebuild). Декларативное партиционирование поддерживается в PostgreSQL с версии 10.
Range Partitioning (по диапазону) - таблица делится на «диапазоны», определяемые по одному или нескольким ключевым столбцам. Диапазоны значений разных разделов не пересекаются.
List Partitioning (по списку значений) - таблица партиционируется по списку, который явно задаёт, какие значения первичного ключа должны попадать в каждую партицию.
Hash Partitioning (по хэшу) - таблица делится на разделы по результатам хеширования ключа. Для этого задаются модуль и остаток, при котором хеш-значение ключа, делённое на модуль, даёт нужный остаток.
PostgreSQL игнорирует партиции, которые не содержат запрашиваемые данные. Упрощается обслуживание таблиц: TRUNCATE или DETACH старых партиций.
• Обновление таблиц методом подмены партиций достигается комбинацией команд DETACH PARTITION и ATTACH PARTITION.
• Процент свободного места на странице (FILLFACTOR), оставляемый для обновлений таблицы. По умолчанию - 100%, что значит нет свободного места. Для часто обновляемых таблиц необходимо установить меньшее значение, например, 80%-90%, чтобы уменьшить bloat и избежать Heap Only Tuples (HOT) updates.
• Денормализация таблиц подходит для аналитических задач.

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

Хорошая индексация таблиц – это ключевой элемент производительности SQL-запросов.
Типы индексов:
B-tree - стандартный, для большинства случаев (равенство, диапазоны, сортировка);
GIN - для полнотекстового поиска, массивов, JSONB-документов;
GiST / SP-GiST - для географических данных (PostGIS), полнотекстового поиска, сложных структур данных (деревья, списки);
BRIN (Block Range Index) - для очень больших таблиц, где данные логически отсортированы (например, по дате вставки). Компактен, но менее селективен.

• Следует создавать B-tree индексы для таблиц(ы), когда:
столбцы, используются в условиях JOIN;
столбцы, используются в WHERE-условиях (особенно в AND и OR конструкциях);
столбцы, используются в ORDER BY и GROUP BY (индекс может обеспечить предварительно отсортированные данные). Если строится индекс по нескольким полям, то первым слева должно быть поле с меньшим количеством уникальных значений (очень низкой кардинальностью);
столбцы, по которым создаются первичные или альтернативные ключи;
столбцы внешних ключей (рекомендуется индексировать для предотвращения блокировок при изменении родительских таблиц).
• Когда не следует индексировать таблицы:
очень маленькие (меньше нескольких тысяч строк) таблицы;
столбцы с очень низкой кардинальностью, например, столбец типа Boolean или столбец с 2-3 уникальными значениями;
очень часто обновляются / вставляются столбцы (индексы создают накладные расходы на запись).
• Парциальный индекс (Partial Indexes) индексирует только часть таблицы, что делает индекс меньше и быстрее, если часто запрашивается эта часть, например, WHERE status = 'active'. Команда CREATE INDEX ... WHERE condition.
• Индекс выражений (Expression Indexes) индексирует результат функции или выражения, полезен для запросов типа WHERE LOWER(column) = 'value'
CREATE INDEX ... ON table (LOWER(column)).
• Покрывающий индекс (Covering Indexes - с INCLUDE) содержит дополнительные столбцы, не являющимися частью ключа, но могут быть прочитаны без обращения к таблице (сканирование только индекса): CREATE INDEX ... ON table (column1) INCLUDE (column2, column3).
• CREATE INDEX CONCURRENTLY - использование этой опции для создания индексов на рабочих серверах, чтобы избежать блокировки таблицы на время создания индекса. Работает дольше, но без простоя.
• Мониторинг неиспользуемых индексов позволяет выявить индексы, которые никогда не используются и могут быть удалены, так как они увеличивают накладные расходы на запись и занимают место. Используйте pg_stat_user_indexes.

Обслуживание базы данных

Регулярное обслуживание базы данных критично для её производительности.

• Необходимо контролировать эффективность работы Autovacuum, его активность и настройки. Плохой Autovacuum приводит к раздуванию таблиц и замедлению запросов. Для таблиц, которые сильно раздулись, может потребоваться VACUUM FULL или pg_repack (расширение) для восстановления места. VACUUM FULL блокирует таблицу, pg_repack работает онлайн.
• Регулярный ANALYZE (вручную или через Autovacuum) собирает статистику о распределении данных в таблицах и индексах. Эта статистика критична для планировщика запросов, чтобы тот мог выбрать наиболее эффективный план выполнения SQL-запросов.
• В версиях PostgreSQL 12+ индексы раздуваются очень редко, но если индекс сильно раздут или повреждён, то исполнение REINDEX может помочь. REINDEX TABLE / INDEX блокирует, REINDEX CONCURRENTLY (для индексов) не блокирует.
• Мониторинг свободного дискового пространства для таблиц и индексов на постоянной основе.

Мониторинг и диагностика

Без перманентного мониторинга невозможно понять, что и где нужно оптимизировать.

• Обязательно установить и использовать расширение pg_stat_statement, которое предоставляет агрегированную статистику по всем выполненным запросам: общее время выполнения, среднее время, количество вызовов, I/O и т.д. Идеально для поиска самых медленных и часто выполняемых SQL-запросов.
Системные представления pg_stat_*:
pg_stat_activity: Текущая активность сессий.
pg_stat_database - статистика по базам данных (хиты кэша);
pg_stat_all_tables / pg_stat_user_tables - статистика по таблицам (количество сканирований, вставок, обновлений, удалений, активность autovacuum);
pg_stat_all_indexes / pg_stat_user_indexes - статистика по индексам (количество сканирований, неиспользуемые индексы);
pg_locks - информация о текущих блокировках;
pg_stat_replication - статистика репликации.
• Использование инструментов мониторинга:
Prometheus / Grafana - популярная связка для сбора и визуализации метрик;
pg_activity - интерактивный монитор активности PostgreSQL;
pghero, pgbadger - удобные инструменты для анализа логов и статистики PostgreSQL.

Оптимизация конфигурации сервера

postgresql.conf — основной файл конфигурации сервера СУБД PostgreSQL. В нём содержатся параметры, влияющие на производительность сервера баз данных. Расположение файла postgresql.conf зависит от операционной системы и метода установки:
Linux (установка с помощью apt/yum) — по умолчанию:
/etc/postgresql/<версия>/main/
или
/var/lib/pgsql/<версия>/data/
Windows — по умолчанию:
C:\Program Files\PostgreSQL\<версия>\data/
Для пользовательских установок путь к каталогу данных может отличаться в зависимости от выбранного во время настройки.

• Настройки памяти:
shared_buffers: кэш данных PostgreSQL. Устанавливайте в 25-40% от доступной RAM сервера. Слишком много может привести к нехватке памяти для операционной системы или конфликтам с файловым кешем операционной системы.
work_mem: память для сортировок, хеширований и других операций, не использующих индексы. Устанавливается на одну операцию в запросе, может быть от нескольких Мб до Гб. Слишком низкое значение приводит к сбросу данных на диск (затормаживание). Начинать надо с 16-64 Мб, увеличивая по мере необходимости, анализируя EXPLAIN ANALYZE.
maintenance_work_mem: память для VACUUM, CREATE INDEX, ALTER TABLE, необходимо устанавливать выше work_mem (128-512 Мб или 1-2 Гб для больших баз данных).
effective_cache_size: подсказка для планировщика о том, сколько памяти доступно для кеширования (PostgreSQL + операционная система). Надлежит задавать в 50-75% от общей RAM сервера. Данная подсказка не выделяет память, просто информирует планировщик.
• Настройки Write-Ahead Log (WAL):
wal_buffers: размер памяти для буферизации WAL-записей. Обычно 16 Мб. Увеличение может помочь, но часто не является узким местом.
min_wal_size, max_wal_size: управляют размером WAL-сегментов. Увеличение уменьшает частоту создания / удаления файлов, но может потреблять больше места. Рекомендуется установить max_wal_size в 4-16 Гб или больше для активных систем.
checkpoint_timeout: частота контрольных точек. Увеличение (например, до 15-30 минут) уменьшает нагрузку на I/O, но увеличивает время восстановления после сбоя.
synchronous_commit: определяет, когда подтверждение о коммите возвращается клиенту:
on (по умолчанию): максимальная надежность (запись в WAL на диск);
off: самая высокая производительность, но данные могут быть потеряны при сбое сервера (крайне редко);
local: надежность "почти on", но без гарантии, что запись попала на диск (компромисс);
remote_write / remote_apply: для репликации.
• Настройки Autovacuum:
autovacuum: on - обязательно! Не отключать! Это жизненно важно для MVCC.
autovacuum_max_workers: количество одновременно работающих Autovacuum-процессов (обычно 3-10).
autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor: процент от таблицы, который должен измениться, чтобы запустить VACUUM/ANALYZE. Снижать для активных таблиц, например, 0.05 или 0.1.
autovacuum_vacuum_threshold, autovacuum_analyze_threshold: минимальное количество измененных строк, чтобы запустить VACUUM/ANALYZE. Снижать для небольших, но активных таблиц.
autovacuum_vacuum_cost_delay, autovacuum_analyze_cost_delay: задержка между итерациями VACUUM/ANALYZE. Уменьшение ускоряет Autovacuum, но увеличивает нагрузку.
• Параметры соединения:
max_connections: максимальное количество одновременных подключений. Не надо устанавливать слишком высоко без необходимости. Используйте пулы соединений (PgBouncer) для высоконагруженных систем.
listen_addresses: с каких IP-адресов будут приниматься соединения.
• Настройки логирования:
log_min_duration_statement: логировать запросы, выполнение которых занимает больше указанного времени (например, 100ms). Критически важно для поиска медленных запросов.
log_statement: что логировать (none, ddl, mod, all). all - очень шумно, используется для отладки.
log_destination, logging_collector, log_filename, log_rotation_age, log_rotation_size: настройка места и ротации логов.
• Настройки планировщика:
default_statistics_target: увеличивает количество собираемой статистики для планировщика (по умолчанию 100). Для сложных запросов и больших таблиц можно увеличить до 500-1000.
random_page_cost, seq_page_cost: оценка стоимости случайного и последовательного доступа к странице на диске. Настраивается в соответствии с вашим I/O (SSD имеют более низкий random_page_cost).
cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost: стоимость обработки CPU.
• Параллельные запросы:
max_worker_processes: общее количество фоновых процессов, которые может порождать PostgreSQL.
max_parallel_workers_per_gather: максимальное количество параллельных исполнителей, которое может быть запущено одним узлом Gather или Gather Merge.
max_parallel_workers: максимальное количество параллельных рабочих процессов, которые система может поддерживать одновременно.
min_parallel_table_scan_size, min_parallel_index_scan_size: минимальный размер таблицы / индекса для использования параллельных сканирований.

Дополнительные методы

• Материализованное представление (Materialized View) хранит результат запроса и может быть обновлено по расписанию (REFRESH MATERIALIZED VIEW). Идеально подходит для предварительной агрегации данных для отчётов.
• Внешние данные (Foreign Data Wrappers, FDW) - postgres_fdw: позволяет обращаться к таблицам на других PostgreSQL-серверах, как если бы они были локальными.
• Пул соединений (Connection Pooling). PgBouncer / Pgpool-II - можно использовать пулеры соединений для высоконагруженных систем. Они уменьшают накладные расходы на установление новых подключений к базе данных, переиспользуя существующие.
• Можно настроить потоковую репликацию (Streaming Replication) для создания реплик, которые могут обрабатывать запросы на чтение, разгружая мастер-сервер.
• Для экстремально больших объемов данных или высокой нагрузки, когда один сервер не справляется, можно распределить данные по нескольким PostgreSQL-серверам (шардам). Это сложная архитектура, требующая тщательного проектирования.
• Использование расширений (Extensions):
pg_trgm: для эффективного поиска подстрок и похожих строк.
PostGIS: для геопространственных данных.
hstore: для хранения пар ключ-значение, если JSONB слишком тяжёлый.
pg_cron: для планирования задач в PostgreSQL.

Оптимизация аппаратного обеспечения и инфраструктуры

Нередко вопросы производительности решается "в лоб" за счёт наращивания аппаратных мощностей.

• Быстрое дисковое хранилище (Disk I/O):
SSD / NVMe: абсолютно критично. Чем быстрее диски, тем лучше. NVMe-накопители значительно превосходят SATA SSD.
RAID: используйте RAID-10 для баланса между производительностью и отказоустойчивостью или RAID-0 для максимальной производительности, если отказоустойчивость обрабатывается на другом уровне, например, репликацией). Избегайте RAID-5, RAID-6 для высоконагруженных систем.
• Отдельные диски для Write-Ahead Log (WAL): размещение журнала WAL на отдельном, очень быстром диске, например, небольшой NVMe может значительно улучшить производительность записи, особенно для OLTP-систем.
• Локальное хранилище: для максимальной производительности данных лучше использовать локальные диски, а не сетевые хранилища (SAN, NAS), которые могут вносить задержки.
• Достаточный объем оперативной памяти (RAM): PostgreSQL активно использует RAM для кэширования данных и индексов, что значительно уменьшает количество дисковых операций. Чем больше RAM, тем больше данных может храниться в кэше.
• Мощный CPU: большее количество ядер и высокая тактовая частота процессора способствуют более быстрому выполнению сложных запросов, вычислительных операций, параллельным операциям и фоновым задачам (Autovacuum, Checkpointing).
• Быстрая сеть: для распределённых систем, репликации и клиентских подключений необходима высокоскоростная сеть (10 Gb или выше), особенно для больших объёмов передаваемых данных.

Ну и...

Уточнение, понимание решаемой бизнес-задачи важно, поскольку автоматизация может быть "в стол". Выяснение истинных причин(ы) может выявить совсем иное решение, не обязательно в плоскости ИТ.