В завершение модуля — каталог типичных ошибок, которые я лично видел в продакшен-коде сотни раз. Каждая из них кажется безобидной, пока таблица маленькая. Каждая из них становится катастрофой при росте данных. И каждая из них чинится за несколько минут, если знать, что искать.

Это финальный урок модуля и финальный урок курса. Ниже — короткие истории «болезнь — диагноз — лечение».

1. SELECT * в production-коде

-- Антипаттерн
SELECT * FROM orders WHERE customer_id = 5;

-- Правильно
SELECT id, placed_at, status FROM orders WHERE customer_id = 5;

Что плохого:

• Лишний трафик. Каждая колонка передаётся с СУБД на приложение, даже если ты её не используешь. Если в таблице есть JSONB или TEXT-колонка по мегабайту — каждая лишняя строка стоит мегабайт.
• Ломает Index Only Scan. Если в индексе есть только нужные тебе колонки, Index Only Scan отвечает за миллисекунды без чтения heap. SELECT * гарантированно требует heap, потому что в индексе нет всех колонок.
• Хрупкость к изменению схемы. Завтра добавят колонку с большим bytea — все ваши запросы стали тяжелее. С явным списком — ничего не сломается.

Исключение: SELECT * FROM small_table для интерактивного исследования — норма. SELECT * в коде приложения — почти всегда смелл.

2. Функции на индексированных колонках в WHERE

-- Антипаттерн — индекс не работает
SELECT * FROM customers WHERE LOWER(email) = '[email protected]';
SELECT * FROM orders WHERE DATE(placed_at) = '2025-01-05';
SELECT * FROM products WHERE name || '!' = 'iPhone 15!';

Каждый из этих запросов не использует индекс по соответствующей колонке (мы разбирали в уроке 3). Лечение зависит от случая:

a) LOWER(email) — обычно поправимо expression index’ом:

CREATE INDEX idx_customers_email_lower ON customers(LOWER(email));

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

b) DATE(placed_at) = '2025-01-05' — переписать в диапазон:

-- Антипаттерн
WHERE DATE(placed_at) = '2025-01-05'

-- Правильно — обычный индекс работает
WHERE placed_at >= '2025-01-05' AND placed_at < '2025-01-06'

Эквивалентно по результату, sargable по форме. Это самая частая фикcа на собеседованиях.

c) name || '!' = 'iPhone 15!' — выкинуть конкатенацию:

WHERE name = 'iPhone 15'

Очевидно после того, как кто-то ткнул носом. Но в реальном коде такое встречается часто после копипасты.

3. OFFSET N пагинация на больших таблицах

Классическая пагинация:

SELECT * FROM orders ORDER BY placed_at DESC OFFSET 100000 LIMIT 20;

Проблема: чтобы пропустить 100 000 строк, PostgreSQL читает и сортирует все 100 020 строк, потом отбрасывает первые 100 000 и отдаёт 20. На странице 1 — копейки. На странице 5000 — секунды. На странице 50 000 — минуты.

Это известная как «OFFSET кладёт базу» проблема. Лечение —

-- Первая страница
SELECT id, placed_at, status FROM orders
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- Следующая страница — передаём в запрос (placed_at, id) последней строки
SELECT id, placed_at, status FROM orders
WHERE (placed_at, id) < ('2025-04-25 12:00', 17)
ORDER BY placed_at DESC, id DESC
LIMIT 20;

Тут запрос не пропускает 100 000 строк — он спускается по индексу (placed_at, id) сразу в нужное место. Стоимость одинаковая на любой странице.

Минус: нельзя «прыгнуть на страницу 5000» — только идти вперёд/назад. Для бесконечного скролла подходит идеально. Для классического «1 … 4998 4999 5000» — нужно использовать гибридный подход или признать, что глубокие страницы пользователю не нужны.

4. Type mismatch в JOIN

-- Антипаттерн
SELECT *
FROM orders o
JOIN external_payments ep ON o.id::TEXT = ep.order_id_str;

Тут orders.id — INT, а в external_payments.order_id_str — текстовое представление. CAST на колонке слева ломает индекс по o.id. Если таблицы большие, это превращается в Hash Join по двум Seq Scan или хуже.

Лечение — выровнять типы в схеме. Если внешняя система присылает текст, в которой ID — числовой, либо парсить его при импорте, либо ставить индекс на cast’нутую колонку (expression index). Самый чистый путь — починить схему: ALTER TABLE external_payments ALTER COLUMN order_id_str TYPE INT USING order_id_str::INT.

Это не всегда возможно (внешний интерфейс, легаси). Тогда:

-- Expression index на стороне external_payments
CREATE INDEX idx_ep_order_id_int ON external_payments((order_id_str::INT));

SELECT * FROM orders o
JOIN external_payments ep ON o.id = ep.order_id_str::INT;

Теперь cast — на стороне external_payments, индекс используется.

5. ORDER BY без LIMIT на больших таблицах

-- Антипаттерн на таблице в миллион строк
SELECT * FROM orders ORDER BY placed_at DESC;

Без LIMIT PostgreSQL отсортирует всю таблицу, чтобы отдать результат. На миллионе строк это десятки секунд и сотни мегабайт в work_mem (или disk-based external sort, если не помещается).

Лечение зависит от того, что ты хочешь:

• Если нужны последние N — добавь LIMIT N. С индексом по placed_at это будет Index Scan Backward + Limit, миллисекунды.
• Если правда нужны все (отчёт, экспорт) — переноси сортировку на сторону клиента или используй streaming-фреймворк (cursors).
• Если нужны все, но порядок не критичен — просто убери ORDER BY. Многие отчёты сортируют «на всякий случай», хотя UI всё равно даст возможность пересортировать.

6. WHERE x IN (длинный список)

-- Антипаттерн
SELECT * FROM products
WHERE id IN (1, 2, 3, ..., 50000);

PostgreSQL обычно справляется до ~1000 значений в IN, дальше планировщик начинает тормозить (на компиляции плана, не на исполнении). Лечение:

-- Через JOIN с временной таблицей или VALUES
WITH ids(id) AS (VALUES (1),(2),(3), ...)
SELECT p.* FROM products p JOIN ids USING (id);

-- Или через массив с ANY
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3,...]);

= ANY — конкретно та же семантика, что и IN, но в ARRAY[...] не плодит миллион параметров, и планировщик с ним лучше работает.

7. Использование DISTINCT для починки JOIN-дубликатов

-- Антипаттерн
SELECT DISTINCT c.id, c.full_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'delivered';

Здесь DISTINCT маскирует дизайнерскую ошибку: запрос плодит дубликаты (один клиент = несколько заказов), и автор «чинит» это DISTINCT. На больших таблицах DISTINCT — это Sort + Unique или HashAggregate, дополнительная работа на каждой строке.

Лечение — EXISTS:

SELECT c.id, c.full_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'delivered'
);

EXISTS останавливает поиск, как только нашёл первое совпадение — не плодит пары и не требует DISTINCT. Этот рефакторинг почти всегда даёт ускорение на крупных данных.

8. COUNT(*) на огромной таблице

-- На таблице в миллиард строк
SELECT COUNT(*) FROM events;

Точный COUNT(*) в PostgreSQL всегда читает все строки (из-за MVCC — у каждой строки своя видимость, и пересчитывать кеш на каждый коммит дорого). На миллиарде это десятки секунд.

Лечение:

• Если нужна оценка: SELECT reltuples FROM pg_class WHERE relname = 'events'; — даёт приблизительное число строк из статистики (обновляется при ANALYZE). Мгновенно.
• Если нужен COUNT с фильтром, и фильтр селективный: убедись, что есть индекс по предикату. SELECT COUNT(*) FROM events WHERE customer_id = 42 с индексом на customer_id будет быстрым.
• Если нужен точный COUNT часто — материализуй счётчик: отдельная таблица counters(table_name, value), инкрементируется триггером.

9. Длинные транзакции, удерживающие блокировки

-- Антипаттерн в коде приложения
BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
-- ... 10 секунд бизнес-логики, HTTP-запросов к внешнему API ...
UPDATE accounts SET balance = balance + 100 WHERE id = 42;
COMMIT;

Пока эта транзакция жива, никто другой не может изменить строку 42. На read-heavy системе это создаст огромную очередь ожидающих. Кроме того, autovacuum не сможет очистить старые версии строк, пока есть открытая транзакция, видящая «старый снапшот» — таблица начнёт пухнуть.

Лечение: транзакция должна быть короткой. Все внешние вызовы, HTTP, расчёты — вне транзакции. Внутри только SELECT FOR UPDATE → UPDATE → COMMIT. Иногда — оптимистические локи через UPDATE ... WHERE version = X с проверкой rows_affected.

10. IS NULL в индексируемых колонках без partial index

SELECT * FROM users WHERE deleted_at IS NULL;

На таблице, где 95% строк — deleted_at IS NULL, обычный индекс по deleted_at бесполезен: спустишься по дереву, всё равно прочитаешь 95% строк. Лечение — partial index, который индексирует только активные строки:

CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

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

Финальный чек-лист модуля

Что унести из всего модуля 13 в свою рабочую практику:

1. Без индекса — Seq Scan. С индексом — Index Scan за log(N). Trade-off: дороже запись.
2. B-tree — это упорядоченное дерево с высоким fan-out. Идеален для равенства, диапазонов, prefix-LIKE, ORDER BY.
3. Функция на колонке в WHERE ломает индекс. Лечение — переписать или expression index.
4. Composite index работает по leftmost prefix rule. Самую селективную колонку — слева.
5. EXPLAIN — дерево, читай снизу вверх. EXPLAIN ANALYZE даёт реальные числа, BUFFERS показывает RAM vs disk.
6. OFFSET-пагинация убивает производительность на глубоких страницах — keyset.

И главное: не угадывай. Замеряй. EXPLAIN ANALYZE — твой основной инструмент. Любая гипотеза о перфомансе должна быть проверена реальным планом на реальных данных.

Что дальше: SQL Internals: Middle to Senior

Это была интуиция. Мы прошли по индексам, B-tree, EXPLAIN и антипаттернам на уровне, достаточном, чтобы писать осмысленные запросы и не закапывать прод.

В следующем курсе — SQL Internals: Middle to Senior — мы пройдём по этим же темам до железа:

• Страницы B-tree — как именно устроен лист, что такое split, fill factor, page bloat.
• MVCC и видимость строк — почему UPDATE — это INSERT + flag, что такое xmin/xmax, как работает snapshot isolation, причины write amplification.
• Статистика и costing — что хранит pg_statistic, как PG оценивает selectivity для разных предикатов, когда статистика врёт.
• Parallel query plans — как PG распараллеливает Seq Scan, какие узлы parallel-safe.
• HOT updates, VACUUM, autovacuum — экономика хранилища PostgreSQL.
• Lock manager, deadlock detection, MVCC vs locking — как реально устроена конкурентность.
• WAL, replication, point-in-time recovery — что происходит при COMMIT и как PostgreSQL переживает крах.

К концу курса ты сможешь читать исходники PostgreSQL и обоснованно объяснять команде, почему план получился именно таким — а не «потому что оптимизатор так решил».

Поздравляю

Ты закончил SQL Fundamentals: Zero to Middle. За 12 модулей мы прошли от реляционной модели Кодда до индексов и EXPLAIN — это полный инструментарий middle-разработчика, который пишет SQL осознанно, а не копипастом. Дальше — только глубже.