PostgreSQL-запросы: что действительно нужно знать разработчику

mr. Cooper • 2 недели назад • Веб-разработка

#Backend #разработка #postgresql #sql #программирование #базы_данных #индексы #EXPLAIN #EXPLAIN_ANALYZE #JOIN #Seq_Scan #оптимизацияSQL #производительность

PostgreSQL-запросы: что действительно нужно знать разработчику

Когда впервые начинаешь работать с PostgreSQL, кажется, что SQL довольно быстро можно освоить.

SELECT - получить данные.

INSERT - добавить.

UPDATE - изменить.

DELETE - удалить.

На небольшой таблице этого хватает. Выбрал нужные поля, добавил WHERE, отсортировал результат - готово.

Потом база начинает расти.

Запрос, который раньше выполнялся за несколько миллисекунд, внезапно занимает заметное время. API начинает отвечать медленнее. Где-то появляется таймаут. В логах один и тот же SELECT повторяется сотни раз.

И самое неприятное - сам SQL при этом может выглядеть совершенно нормально.

Вот здесь и начинается настоящая работа с PostgreSQL.

Проблема уже не в том, умеем ли мы написать:

//sql

SELECT id, name, email
FROM users
WHERE status = 'active';

Проблема в другом: что PostgreSQL будет делать с этим запросом после его получения?

SQL выглядит проще, чем работа базы

Один и тот же запрос можно выполнить разными способами.

PostgreSQL анализирует условия, статистику таблиц, доступные индексы и выбирает план выполнения. Посмотреть его можно через EXPLAIN:

//sql

EXPLAIN
SELECT id, name, email
FROM users
WHERE status = 'active';

Это одна из тех команд, которые сначала кажутся второстепенными, а потом становятся привычным инструментом.

Допустим, мы создали индекс:

//sql

CREATE INDEX idx_users_status
ON users(status);

Логично ожидать, что поиск по status теперь будет идти через него.

Но PostgreSQL ничего такого не обещал.

Если условие подходит для большей части таблицы, последовательное чтение может оказаться дешевле. Представим десять миллионов пользователей, из которых восемь миллионов имеют статус active. Индекс не превращает такой запрос в поиск одной записи. Слишком много строк всё равно придётся получить.

Поэтому Seq Scan в EXPLAIN сам по себе не означает проблему.

Иногда это именно тот план, который нужен базе.

Мне вообще кажется полезным не воспринимать планировщик как систему, которую обязательно нужно переубеждать. Если PostgreSQL выбрал последовательное чтение, сначала стоит разобраться почему.

Индекс - это не наклейка «быстрее»

//text

orders
----------------
id
user_id
status
created_at
total

Есть запрос:

//sql

SELECT id, created_at, total
FROM orders
WHERE user_id = 125;

Индекс по user_id выглядит вполне разумно:

//sql

CREATE INDEX idx_orders_user_id
ON orders(user_id);

Но затем появляется другой запрос:

//sql

SELECT id, created_at, total
FROM orders
WHERE user_id = 125
AND status = 'paid';

Здесь уже может иметь смысл составной индекс:

//sql

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

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

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

Индексы тоже требуют ресурсов. Они занимают место и должны обновляться при изменении данных.

Поэтому индекс имеет смысл оценивать вместе с конкретными запросами.

Не «у нас есть колонка status, давайте проиндексируем её», а примерно так: какие запросы используют эту колонку, как часто они выполняются и что показывает их план?

Это уже совсем другой подход.

Сортировка может изменить всю картину

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

//sql

SELECT id, created_at, total
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC;

Теперь недостаточно просто найти записи пользователя. Их ещё нужно вернуть в определённом порядке.

Если такой запрос используется постоянно, можно рассмотреть индекс:

//sql

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Здесь структура индекса уже связана с тем, как приложение получает данные.

А затем появляется:

//sql

SELECT id, created_at, total
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC
LIMIT 20;

На первый взгляд всё элементарно: нужны двадцать строк.

Но LIMIT 20 говорит только о размере результата. Сколько данных придётся обработать до его формирования - другой вопрос.

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

Поэтому запросы вроде этого полезно смотреть вместе с EXPLAIN, а не пытаться оценивать их стоимость только по длине SQL.

`SELECT *` редко ломает приложение сразу

Вот такой запрос совершенно рабочий:

//sql

SELECT *
FROM users
WHERE id = 100;

Если нужны все поля - почему нет.

Проблема обычно появляется позже.

В таблицу добавляются телефон, настройки, аватар, служебные поля, какие-нибудь дополнительные данные. А API продолжает забирать всё, хотя ему нужны только два поля:

//sql

SELECT name, email
FROM users
WHERE id = 100;

Для одной записи разница может быть незаметной.

Но если запрос вызывается постоянно, лишние данные начинают передаваться между базой и приложением снова и снова.

Поэтому SELECT * я бы не запрещал. Просто в коде, который живёт долго и обслуживает реальные API-эндпоинты, лучше явно указывать нужные поля.

С JOIN всё интереснее

Например:

//sql

SELECT
    users.name,
    orders.created_at,
    orders.total
FROM users
JOIN orders
    ON orders.user_id = users.id;

Иногда после появления нескольких JOIN разработчик сразу начинает искать способ переписать запрос.

Но сам JOIN ничего не говорит о производительности.

PostgreSQL может выбрать Nested Loop, Hash Join или другой способ соединения. Важны количество строк, условия, индексы и конкретный план.

Поэтому вместо:

«У нас тормозит JOIN»

гораздо полезнее посмотреть:

//sql

EXPLAIN
SELECT
    users.name,
    orders.created_at,
    orders.total
FROM users
JOIN orders
    ON orders.user_id = users.id;

Если ситуация действительно подозрительная, следующий шаг:

//sql

EXPLAIN ANALYZE
SELECT
    users.name,
    orders.created_at,
    orders.total
FROM users
JOIN orders
    ON orders.user_id = users.id;

И здесь уже появляются цифры.

Сколько строк ожидал планировщик? Сколько получилось на самом деле? Какая операция занимает время? Где происходит основной объём работы?

Вместо догадки «JOIN медленный» получается конкретная проблема.

`EXPLAIN` полезнее ещё одного списка SQL-команд

Есть соблазн собирать в голове набор правил:

«Здесь нужен индекс».

SELECT * плохой».

JOIN лучше заменить».

Seq Scan надо убрать».

Но PostgreSQL не работает по настолько простому чек-листу.

Один и тот же запрос на двух базах с разным объёмом данных может получить разные планы. Даже на одной базе план может измениться после изменения статистики или распределения данных.

Поэтому EXPLAIN для меня намного полезнее очередного списка «10 способов ускорить PostgreSQL».

Он показывает, что база решила сделать с конкретным запросом.

А EXPLAIN ANALYZE позволяет посмотреть уже фактическое выполнение.

Только здесь есть один нюанс, о котором легко забыть: EXPLAIN ANALYZE действительно запускает запрос.

Например, такой UPDATE изменит данные:

//sql

EXPLAIN ANALYZE
UPDATE users
SET status = 'blocked'
WHERE last_login < '2025-01-01';

При ручной проверке можно использовать транзакцию:

//sql

BEGIN;

EXPLAIN ANALYZE
UPDATE users
SET status = 'blocked'
WHERE last_login < '2025-01-01';

ROLLBACK;

После проверки изменения откатятся.

С массовым DELETE логика та же. А перед ним я бы вообще сначала выполнил обычный SELECT:

//sql

SELECT id
FROM users
WHERE status = 'blocked';

И посмотрел хотя бы количество:

//sql

SELECT COUNT(*)
FROM users
WHERE status = 'blocked';

Это занимает несколько секунд.

Удалённые данные потом могут стоить значительно дороже.

## База не знает, что ты хотел сделать

Это особенно хорошо видно на UPDATE.

Запрос:

//sql

UPDATE users
SET status = 'blocked';

полностью корректен.

PostgreSQL не считает его ошибочным. Он просто изменит все строки.

Разработчик мог забыть WHERE, перепутать условие или неправильно собрать его в приложении. База не знает контекста задачи.

Поэтому перед массовым изменением полезно сначала получить тот же набор данных через SELECT:

//sql

SELECT id
FROM users
WHERE last_login < '2025-01-01';

Если выборка правильная:

//sql

UPDATE users
SET status = 'blocked'
WHERE last_login < '2025-01-01';

Для критичных изменений - транзакция и дополнительная проверка.

С DELETE всё ровно так же.

SQL может быть абсолютно валидным и при этом делать совсем не то, что ожидалось.

## OFFSET хорошо работает, пока не становится большим

Классическая пагинация выглядит так:

//sql

SELECT id, created_at, total
FROM orders
ORDER BY id
LIMIT 50 OFFSET 500000;

Первые страницы никто не замечает.

Проблема появляется ближе к концу большой таблицы.

Чтобы вернуть пятьдесят нужных строк, базе нужно разобраться с предыдущими пятьюстами тысячами записей. Чем больше OFFSET, тем больше работы.

Если переход на конкретную страницу пользователю не нужен, можно передавать последний полученный id:

//sql

SELECT id, created_at, total
FROM orders
WHERE id > 500000
ORDER BY id
LIMIT 50;

Это keyset pagination.

И мне нравится этот подход именно своей простотой. Мы не пытались заставить огромный OFFSET работать быстрее. Мы изменили сам способ запроса следующей страницы.

На больших лентах это имеет гораздо больше смысла.

INSERT тоже меняется вместе с объёмом

Одна запись:

//sql

INSERT INTO users (name, email)
VALUES ('Alex', 'alex@example.com');

Нормальная операция.

Несколько записей можно вставить одним запросом:

//sql

INSERT INTO users (name, email)
VALUES
    ('Alex', 'alex@example.com'),
    ('John', 'john@example.com'),
    ('Mike', 'mike@example.com');

А когда речь идёт уже о больших объёмах, у PostgreSQL есть COPY.

Смысл здесь не в том, что один синтаксис «правильный», а остальные нет. Просто одинаковая по смыслу операция ведёт себя по-разному в зависимости от масштаба.

Одна строка и сто тысяч строк - это уже две разные задачи.

`GROUP BY` быстро становится сложнее, чем кажется

Простейшая статистика:

//sql

SELECT
    user_id,
    COUNT(*) AS orders_count
FROM orders
GROUP BY user_id;

Добавим период:

//sql
SELECT
    user_id,
    COUNT(*) AS orders_count
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id;

Теперь нужны только пользователи, у которых больше десяти заказов:

//sql

SELECT
    user_id,
    COUNT(*) AS orders_count
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;

Здесь WHERE и HAVING нельзя просто поменять местами.

WHERE отбрасывает исходные строки.

GROUP BY формирует группы.

HAVING уже фильтрует эти группы.

В реальном запросе к этому добавятся JOIN, несколько агрегатов, сортировка и дополнительные условия. И вот тогда уже становится важным понимать не только синтаксис, но и порядок выполнения операций.

Не каждый медленный запрос нужно ускорять

Допустим, есть запрос, который выполняется 300 миллисекунд.

Плохой результат?

Не факт.

Если он запускается раз в сутки, вряд ли это первая проблема, за которую стоит браться.

Теперь другой запрос:

//text

20 мс × несколько тысяч вызовов в секунду

Вот здесь ситуация интереснее.

Поэтому скорость отдельного выполнения - не единственный показатель.

Нужно посмотреть, как часто запрос запускается, сколько строк читает, какой план использует и сколько данных возвращает.

И есть ещё одна вещь, которую легко пропустить.

Иногда приложение просто делает слишком много запросов.

Можно долго подбирать индекс для SELECT, который выполняется двадцать раз подряд во время одного HTTP-запроса. Хотя настоящая проблема находится в коде приложения.

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

В такой ситуации ещё один индекс не решит проблему архитектуры доступа к данным.

Что действительно стоит знать

Базовый SQL, конечно, никуда не исчезает.

Нужно уверенно работать с:

SELECT;
INSERT;
UPDATE;
DELETE;
WHERE;
JOIN;
GROUP BY;
HAVING;
ORDER BY;
LIMIT;
транзакциями.

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

Гораздо полезнее научиться отвечать на несколько практических вопросов.

Почему PostgreSQL выбрал Seq Scan?

Почему этот индекс не используется?

Сколько строк запрос реально читает?

Что происходит внутри JOIN?

Почему пагинация начинает тормозить на больших значениях OFFSET?

Почему запрос с LIMIT 20 всё равно может заставить базу обработать большой объём данных?

И главное - действительно ли этот запрос является проблемой приложения?

Вот последний вопрос особенно легко забыть.

Производительность PostgreSQL определяется не одной строкой SQL.

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

Поэтому со временем отношение к SQL меняется.

В начале хочется научиться писать запросы.

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

И вот здесь EXPLAIN становится гораздо интереснее очередного списка SQL-команд.

Потому что хороший запрос - это не тот, который просто возвращает правильные данные.

Хороший запрос ещё и не заставляет базу делать работу, которая ему вообще не нужна.

PostgreSQL-запросы: что действительно нужно знать разработчику

SQL выглядит проще, чем работа базы

Индекс - это не наклейка «быстрее»

Сортировка может изменить всю картину

`SELECT *` редко ломает приложение сразу

С JOIN всё интереснее

`EXPLAIN` полезнее ещё одного списка SQL-команд

INSERT тоже меняется вместе с объёмом

`GROUP BY` быстро становится сложнее, чем кажется

Не каждый медленный запрос нужно ускорять

Что действительно стоит знать

Комментарии

Похожие статьи

PostgreSQL-запросы: что действительно нужно знать разработчику

SQL выглядит проще, чем работа базы

Индекс - это не наклейка «быстрее»

Сортировка может изменить всю картину

SELECT * редко ломает приложение сразу

С JOIN всё интереснее

EXPLAIN полезнее ещё одного списка SQL-команд

INSERT тоже меняется вместе с объёмом

GROUP BY быстро становится сложнее, чем кажется

Не каждый медленный запрос нужно ускорять

Что действительно стоит знать

Комментарии

Похожие статьи

`SELECT *` редко ломает приложение сразу

`EXPLAIN` полезнее ещё одного списка SQL-команд

`GROUP BY` быстро становится сложнее, чем кажется