SQL и индексы

Индекс — это вспомогательная структура, отображающая значения столбца на расположение строк, чтобы движок находил строки без полного сканирования таблицы. Индекс по умолчанию в PostgreSQL — это B-tree, хранящий ключи отсортированными и поддерживающий поиск по равенству и диапазону.

N+1 — это когда один запрос достаёт список из N строк, а затем код выполняет ещё по запросу на каждую строку, чтобы подгрузить связанные данные — 1 + N обращений, и каждое платит за сеть и планирование. Исправляется выборкой всего одним запросом: JOIN либо один батч WHERE id IN (...) / = ANY($1) по идентификаторам родителей.

LEFT JOIN Orders к Customers по id клиента, затем фильтр WHERE o.customer_id IS NULL — анти-join оставляет клиентов без совпавшего заказа. NOT EXISTS эквивалентен; NOT IN вернёт пусто, если в подзапросе есть NULL, поэтому предпочтительнее LEFT JOIN ... IS NULL или NOT EXISTS.

B-tree — это сбалансированное отсортированное многопутевое дерево поиска с высоким ветвлением, поэтому оно остаётся неглубоким для миллионов строк и является индексом по умолчанию в PostgreSQL. Отсортированные ключи дают равенство, диапазоны, ORDER BY и поиск по префиксу. Поиск, вставка и удаление — все O(log n), ведь оно балансируется разбиением и слиянием узлов.

Составной индекс на (a, b, c) — одно B-tree, отсортированное по a, затем b, затем c. По правилу leftmost-prefix он обслуживает фильтры по ведущему префиксу — a, a,b, a,b,c — но не по b или c отдельно. Столбцы равенства ставьте первыми, диапазонный — последним.

LEFT JOIN Employee к Departments, чтобы выжил каждый отдел, GROUP BY d.id, d.name и COUNT(e.id) — счёт по столбцу сотрудника даёт 0 для пустых отделов, тогда как COUNT(*) посчитал бы единственную NULL-строку соединения как 1. INNER JOIN отбросил бы пустые отделы.

EXPLAIN печатает выбранный планировщиком план выполнения — порядок соединений, типы сканирований и оценки стоимости — не выполняя запрос. EXPLAIN ANALYZE запрос выполняет и добавляет фактическое число строк и время, поэтому сравнение оценки с реальностью находит плохие оценки и лишние сканирования.

Возьмите MAX(salary) там, где salary < (SELECT MAX(salary) FROM Employee): внутренний максимум — топ-зарплата, поэтому внешний максимум — вторая. Если второй зарплаты нет, эта форма аккуратно возвращает одну строку NULL. Вариант ORDER BY salary DESC LIMIT 1 OFFSET 1 вместо этого не возвращает строк.

Соедините Employee саму с собой: один экземпляр под алиасом e (сотрудник), другой — m (менеджер), по e.manager_id = m.id, затем оставьте строки, где e.salary > m.salary. Одна таблица фигурирует дважды под разными алиасами, проходя связь manager_id → id внутри одной таблицы.

Отфильтруйте RU-строки в WHERE country = 'ru', сделайте GROUP BY customer.id, email, затем суммируйте каждую корзину через SUM(amount * price). HAVING отбирает группы с итогом >= 1000 (он работает после агрегации, в отличие от WHERE), ORDER BY по этой сумме DESC и LIMIT 10 оставляет верхние строки.

Логический порядок — FROM/JOIN → WHERE → GROUP BY → агрегаты → HAVING → SELECT → ORDER BY. WHERE работает до группировки и не может ссылаться на SUM(...); HAVING — после. INNER JOIN теряет клиентов без строк корзины, и они не образуют группу; LEFT JOIN оставляет их с NULL-агрегатом.

Ранжируйте строки через DENSE_RANK() OVER (ORDER BY salary DESC) в подзапросе, затем фильтруйте WHERE rnk = N. DENSE_RANK учитывает совпадения — равные зарплаты делят ранг, не пропуская следующий, поэтому N=2 — это вторая различная зарплата. ROW_NUMBER даёт одну строку, RANK пропускает ранги.

Партиционирование разбивает одну логическую таблицу на меньшие физические дочерние таблицы по ключу (range, list или hash); планировщик отсекает лишние партиции, уменьшая сканы и облегчая обслуживание вроде VACUUM по партициям. Это остаётся в пределах одного сервера. PostgreSQL не шардирует по серверам из коробки — для этого нужно расширение вроде Citus или маршрутизация на уровне приложения.

B-tree — по умолчанию: равенство, диапазоны, сортировка. Hash обслуживает только равенство. GIN индексирует составные значения: массивы, jsonb, полнотекстовый поиск. GiST покрывает геометрические и диапазонные поиски. BRIN подходит огромным физически упорядоченным таблицам.

MVCC в Postgres оставляет мёртвые версии строк после каждого UPDATE/DELETE. VACUUM возвращает это место для повторного использования внутри таблицы и обновляет visibility map и статистику планировщика; autovacuum запускает его в фоне. Обычный VACUUM не отдаёт диск ОС и не блокирует таблицу — это делает только VACUUM FULL, беря эксклюзивную блокировку и переписывая всю таблицу.

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

Используйте оконную SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). OVER ... ORDER BY делает агрегат нарастающим, а явная рамка суммирует каждую строку вплоть до текущей. Добавьте PARTITION BY dept_id для нарастающего итога по отделу.

Пронумеруйте строки по отделу через ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) в подзапросе, затем оставьте WHERE rn = 1. PARTITION BY сбрасывает ранжирование для каждого отдела, поэтому строка 1 — топ отдела. Альтернатива — коррелированный подзапрос по MAX(salary) на отдел.

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

Индекс всё ещё выбран, но возвращает 12 строк, трогая 4120 буферов — признак bloat: мёртвые строки MVCC от тяжёлого трафика UPDATE/DELETE копились быстрее, чем autovacuum успевал их вернуть, поэтому heap и индекс полны мёртвых страниц, через которые скан вынужден продираться. Исправление: сделать autovacuum агрессивнее на этой таблице, выполнить VACUUM и REINDEX (или pg_repack), чтобы перестроить раздутый индекс.

Пять ошибок. sql.Open вызывается на каждый вызов, утекая новым пулом каждый раз — откройте один *sql.DB в NewStore и переиспользуйте. Результат fmt.Errorf отбрасывается, а метод не возвращает ошибку — верните обёрнутую ошибку. Запрос через fmt.Sprintf уязвим к SQL-инъекции — используйте параметризованный запрос $1..$4, передавая аргументы в Exec. go db.Exec — это fire-and-forget, теряющий ошибку и порядок — вызывайте синхронно. И нет context — принимайте ctx и используйте ExecContext.

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