Три основных способа оптимизировать ваши запросы в PostgreSQL

Привет, Хабр!

Представьте себе, вы приходите домой после долгого рабочего дня, и вас встречает пушистый котик. Вы знаете, что он ждет вас, чтобы вы рассказали ему о своих подвигах на работе. И вот, вы начинаете свой рассказ, а он, этот милый мурлыка, вдруг спрашивает: "А как там у тебя с оптимизацией запросов в PostgreSQL?"

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

А твои запросы... ну, сам понимаешь, как иногда выглядит код, который пишется в пятницу вечером, когда уже мыслями в выходных."

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

Начнем с базы - индексирование.

Индексирование

Индекс – это специальная структура данных, которая улучшает скорость поиска строк в таблице. Он работает подобно алфавитному указателю в книге.

В PostgreSQL существует несколько видов индексов.

1. B-Tree индексы

Самый распространенный тип индексов в PostgreSQL. Подходит для большинства операций поиска и сортировки. Используется по умолчанию при создании индекса.

CREATE INDEX idx_name ON table_name (column_name);

Когда использовать:

• Поиск по равенству и диапазону, например, WHERE age = 30 или WHERE age BETWEEN 20 AND 30.

• Часто применим для уникальных ключей и первичных ключей.

2. Hash индексы

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

CREATE INDEX idx_name ON table_name USING hash (column_name);

Когда использовать:

• Поиск по точному соответствию, к примеру по эмаилу: WHERE email = '[e-mail]'.

• Не поддерживают сортировку и операции диапазона.

3. GIN и GiST индексы

Используются для индексации сложных типов данных, типо как JSONB, массивы и полнотекстовый поиск.

CREATE INDEX idx_name ON table_name USING gin (column_name);

Когда использовать:

• Поиск по JSONB полям, массивам, полнотекстовый поиск.

4. SP-GiST и BRIN индексы

Подходят для гео-пространственных данных и больших, разреженных таблиц соответственно.

CREATE INDEX idx_name ON table_name USING spgist (geom);

Когда использовать:

• Гео-пространственные запросы.

• Большие таблицы с редкими данными, например, временные ряды.

Мультиколоночные индексы позволяют индексировать несколько колонок одновременно:

CREATE INDEX idx_name ON table_name (column1, column2);

К примеру, если есть таблица заказов с колонками customer_id и order_date, и нужно часто выполнять запросы, которые фильтруют по обеим колонкам, создание мультиколоночного индекса может значительно ускорить такие запросы:

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

Порядок колонок в индексе имеет значение. Колонки, по которым чаще всего происходит фильтрация или сортировка, должны стоять первыми.

Например, если чаще всего запросы фильтруются по customer_id, а затем сортируются по order_date, правильный порядок будет таким:

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

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

Переходим к следующему - оптимизация конфигурации сервера PostgreSQL

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

Рассмотрим три важных параметра конфигурации: shared_buffers ,work_mem, и effective_cache_size и также заденем connection pooling.

Shared Buffers

shared_buffers определяет количество памяти, выделенной PostgreSQL для хранения часто используемых данных в памяти. Параметр напрямую влияет на производительность БД, т.к позволяет уменьшить количество операций чтения с диска.

Общепринятое правило — устанавливать shared_buffers на уровне 25% от доступной оперативной памяти сервера. Например, для сервера с 32 ГБ RAM, значение будет таким:

effective_cache_size = 24GB

Важно корректировать значение shared_buffers в зависимости от нагрузки и профиля запросов. Это можно сделать с помощью pg_stat_activity.

Work Memory

work_mem определяет количество памяти, которое может быть использовано для выполнения операций сортировки и хеширования в отдельных запросах. Параметр имеет значение для производительности сложных запросов, включающих операции сортировки, объединения и агрегации.

Значение work_mem следует выбирать в зависимости от количества одновременных запросов и сложности операций. Начальное значение может быть установлено на уровне 32 МБ:

effective_cache_size = 24GB

Важно учитывать, что при выполнении большого количества параллельных запросов суммарное использование памяти для work_mem может существенно возрасти. Для сложных запросов можно временно увеличить значение work_mem:

SET work_mem = '64MB';

effective_cache_size

Параметр effective_cache_size используется PostgreSQL для оценки объема доступной оперативной памяти, что помогает оптимизировать планы выполнения запросов. Этот параметр не влияет на фактическое использование памяти, но позволяет более точно планировать использование ресурсов.

Общепринятое значение effective_cache_size — это 50-75% от общей оперативной памяти системы. Для сервера с 32 ГБ RAM настройка будет такой:

effective_cache_size = 24GB

connection pooling

Connection pooling или пул соединений позволяет управлять подключениями к БД, уменьшая накладные расходы на установку и разрыв соединений.

Один из наиболее лучших инструментов для PostgreSQL — это PgBouncer. Он позволяет устанавливать и управлять пулами соединений, значительно снижая нагрузку на сервер.

Установим PgBouncer:

sudo apt-get install pgbouncer

Пример конфигурации:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 100
default_pool_size = 20

Подготовленные запросы и партиционирование таблиц

Подготовленные запросы - это серверные объекты, которые позволяют оптимизировать выполнение SQL-запросов. Основная идея заключается в разделении процесса выполнения запроса на два этапа: подготовку и исполнение. Сначала запрос анализируется, планируется и компилируется, а затем может выполняться множество раз с различными параметрами без повторного парсинга и планирования. Это снижает нагрузку на сервер и ускоряет выполнение запросов. Например, подготовленный запрос для вставки данных может выглядеть так:

PREPARE insert_data (int, text) AS
INSERT INTO my_table (id, description) VALUES ($1, $2);

Затем он выполняется с конкретными значениями:

EXECUTE insert_data (1, 'Sample description');

Фича подготовленных запросов заключаются в том, что они позволяют избежать повторных затрат на парсинг и планирование для каждого выполнения.

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

CREATE TABLE sales (
  sale_id serial,
  sale_date date,
  amount numeric
) PARTITION BY RANGE (sale_date);

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

Партиционирование по списку разделяет данные на основе списка значений:

CREATE TABLE sales (
  sale_id serial,
  region text,
  amount numeric
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
FOR VALUES IN ('North');

Партиционирование позволяет ускорить выполнение запросов, так как запросы могут использовать конкретные партиции, что уменьшает объем данных для обработки.

---

Успешная оптимизация запросов — это постоянный процесс. Не стоит останавливаться на достигнутом!

Больше практических навыков вы сможете получить в рамках практических онлайн-курсов от ведущих экспертов рынка. Переходите в каталог и выбирайте подходящее направление.