Почему порядок колонок в составном индексе важен!

Составной индекс часто создают, когда запрос фильтрует данные сразу по нескольким колонкам. Но просто добавить нужные поля в индекс недостаточно — их порядок влияет на то, какую часть индекса PostgreSQL сможет эффективно использовать.

Есть таблица:

CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    status VARCHAR(20),
    created_at TIMESTAMPTZ,
    amount NUMERIC(12,2)
);

Допустим, часто выполняется запрос:

SELECT
    id,
    created_at,
    amount
FROM orders
WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
ORDER BY created_at;

Для него можно создать составной индекс:

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

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

B-tree позволяет сначала ограничить сканируемую часть индекса конкретным user_id:

user_id = 1500

После этого created_at задаёт диапазон уже внутри записей этого пользователя:

created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'

Такой порядок также соответствует ORDER BY created_at: при фиксированном user_id PostgreSQL может получить строки из индекса уже в нужном порядке и при подходящем плане обойтись без отдельной сортировки.

Теперь поменяем порядок колонок:

CREATE INDEX idx_orders_created_user
ON orders (created_at, user_id);

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

created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'

Условие по user_id всё ещё может участвовать в индексном сканировании, но уже не сокращает начальный диапазон B-tree так же эффективно, как в варианте (user_id, created_at). Разница особенно заметна, если за выбранный период накопились миллионы заказов разных пользователей.

Индекс (user_id, created_at) хорошо подходит и для запроса только по ведущей колонке:

WHERE user_id = 1500;

А также для сочетания равенства и диапазона:

WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00';

Но запрос только по created_at обычно не получает от этого индекса того же преимущества, поскольку условие на ведущую колонку отсутствует:

WHERE created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00';

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

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    created_at,
    amount
FROM orders
WHERE user_id = 1500
  AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
ORDER BY created_at;

Важно, чтобы практика была показательной, таблица должна содержать достаточно данных, а статистика должна быть актуальной (ANALYZE orders;). На маленькой или пустой таблице PostgreSQL вполне может выбрать Seq Scan, и это будет нормальным поведением оптимизатора.

🔥 Вывод такой: для B-tree индекса (a, b) особенно эффективен сценарий, когда сначала ограничивается ведущая колонка a, а затем используется диапазон по b. Если запрос содержит равенство и диапазон, колонку с равенством часто имеет смысл поставить перед колонкой с диапазоном. Но окончательный выбор индекса должен подтверждаться реальным execution plan.

➡️ SQL Ready | #практика

🤔 MentorData — материалы по SQL и аналитике данных!

Авторский блог с материалами для тех, кто изучает SQL, аналитику данных и готовится к работе в этой сфере. Основной акцент сделан не только на синтаксисе, но и на решении аналитических задач и понимании бизнес-логики. В статьях разбираются оконные функции, JOIN, GROUP BY, CTE, работа с аномалиями и метриками, задачи с технических собеседований и подходы к анализу данных.

📌 Оставляю ссылочку: mentordata.ru

➡️ SQL Ready | #ресурс

Разворачивайте несколько массивов вместе!

Когда приложение передаёт несколько связанных массивов, не нужно отдельно делать unnest(), нумеровать элементы и потом соединять их по позиции.

SELECT *
FROM unnest(
    ARRAY[101, 102, 103],
    ARRAY['book', 'mouse', 'keyboard'],
    ARRAY[2, 1, 4]
) AS x(id, name, qty);

PostgreSQL сопоставит элементы по позиции и сразу вернёт строки 101/book/2, 102/mouse/1, 103/keyboard/4.

Это удобно и для bulk-операций:

INSERT INTO order_items (product_id, quantity)
SELECT *
FROM unnest(
    :product_ids::bigint[],
    :quantities::integer[]
);

Можно обновлять пачку строк тем же способом:

UPDATE products p
SET price = x.price
FROM unnest(
    :ids::bigint[],
    :prices::numeric[]
) AS x(id, price)
WHERE p.id = x.id;

🔥 unnest(array1, array2, ...) объединяет связанные массивы в строки и позволяет массово добавлять или обновлять данные одним запросом.

➡️ SQL Ready | #совет

📂 Шпаргалка по SQL для разработчиков!

Например, SELECT используется для получения данных из таблиц, а CREATE, ALTER и DROP помогают управлять структурой базы данных. JOIN позволяет объединять данные из нескольких таблиц, а агрегатные функции (COUNT, SUM, AVG) — анализировать большие объёмы информации.

На картинке — шпаргалка с основными категориями команд, операторами, ключевыми словами, объектами базы данных, ограничениями, функциями агрегации, типами JOIN.

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

Как атомарно резервировать лимит без SELECT FOR UPDATE!

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

SELECT used, quota
FROM accounts
WHERE id = 42;

Если два процесса одновременно получили used = 80 при quota = 100 и каждый собирается зарезервировать ещё 15, обе проверки в приложении могут успешно пройти.

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

UPDATE accounts
SET used = used + 15
WHERE id = 42
  AND used + 15 <= quota
RETURNING used;

Такой запрос одновременно проверяет условие и изменяет строку. В PostgreSQL конкурентный UPDATE одной строки сериализуется через row-level locking. Если другая транзакция успела изменить строку, условие WHERE для ожидавшего UPDATE будет повторно проверено относительно актуальной версии строки в READ COMMITTED:

UPDATE accounts
SET used = used + :amount
WHERE id = :account_id
  AND used <= quota - :amount
RETURNING id, used, quota;

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

Предполагается, что amount >= 0: это стоит валидировать в приложении или закрепить ограничением на уровне БД:

UPDATE inventory
SET reserved = reserved + :qty
WHERE product_id = :product_id
  AND reserved <= stock - :qty
RETURNING product_id, reserved;

Тот же паттерн применяется к резервированию товара, квотам API, счётчикам использования и другим инвариантам, которые выражаются условием над одной изменяемой строкой. Отдельный SELECT FOR UPDATE здесь нужен не всегда: если проверку можно выразить непосредственно в WHERE, сам UPDATE становится точкой синхронизации:

UPDATE limits
SET consumed = consumed + :delta
WHERE id = :id
  AND consumed <= maximum - :delta
RETURNING consumed;

🔥 Делаем вывод: инвариант вида consumed + delta <= maximum над одной строкой лучше проверять внутри атомарного UPDATE, а не между SELECT и UPDATE в приложении. Это сокращает критическую секцию и корректно работает при конкурентном изменении строки.

➡️ SQL Ready | #практика

👨‍💻 SQL PostgreSQL Patterns Library — большая коллекция готовых SQL-решений для PostgreSQL!

Здесь собраны практические запросы для работы со строками, JSON и массивами, поиска и оптимизации, массового обновления данных, индексов, миграций и администрирования БД. Есть решения для реальных задач: от UPSERT и поиска дубликатов до EXPLAIN, работы с миллионами записей, мониторинга запросов и обслуживания PostgreSQL.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

PostgreSQL Advisory Locks: синхронизация конкурентных операций!

Блокировок строк недостаточно, когда нужно синхронизировать не конкретную запись, а логическую операцию. Например, два воркера одновременно начинают генерацию одного и того же отчёта для пользователя. Даже если результат в итоге сохраняется с UNIQUE(user_id), это не предотвращает двойное выполнение дорогостоящей работы.

Для таких случаев PostgreSQL предоставляет advisory locks — блокировки, ключ и семантику которых определяет приложение:

SELECT pg_advisory_lock(1001);

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

pg_advisory_lock() работает на уровне сессии: блокировка сохраняется до явного pg_advisory_unlock() или завершения сессии:

SELECT pg_advisory_unlock(1001);

Если критическая секция укладывается в транзакцию, обычно удобнее pg_advisory_xact_lock(): такая блокировка автоматически освобождается при COMMIT или ROLLBACK:

BEGIN;

SELECT pg_advisory_xact_lock(1, 1001);

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

INSERT INTO reports(user_id, created_at)
SELECT 1001, now()
WHERE NOT EXISTS (
    SELECT 1
    FROM reports
    WHERE user_id = 1001
);

COMMIT;

Важно то, что операция, которую нужно защитить от конкурентного выполнения, должна происходить после получения lock и до его освобождения.

Если дорогостоящая работа выполняется приложением вне транзакции и может занимать значительное время, держать ради неё долгую транзакцию обычно нежелательно. В таком случае можно использовать session-level advisory lock и гарантированно освобождать его после завершения критической секции.

Здесь 1 можно использовать как namespace операции, а 1001 — как идентификатор ресурса:

(1, 1001) — generate_report / user 1001
(2, 1001) — recalculate_stats / user 1001

Так независимые операции над одним ресурсом не будут случайно блокировать друг друга.

Если воркеру не нужно ждать освобождения lock, есть неблокирующий вариант:

SELECT pg_try_advisory_xact_lock(1, 1001);

Он сразу вернёт:

true  — lock получен, выполняем работу
false — lock уже удерживается, работу можно пропустить

Это удобно для cron-задач и фоновых воркеров, где второй экземпляр работы не должен ждать завершения первого.

advisory lock не гарантирует уникальность данных. Он координирует только процессы, которые используют одинаковый протокол блокировок. Инварианты данных по-прежнему должны обеспечиваться самой БД:

ALTER TABLE reports
ADD CONSTRAINT reports_user_id_key UNIQUE (user_id);

PostgreSQL не знает, что означает (1, 1001). Для него это просто ключ блокировки. Поэтому все конкурирующие процессы должны одинаково формировать ключи и захватывать соответствующие locks.

🔥 Advisory locks полезны для генерации артефактов, фоновых задач, пересчётов, cron jobs и других операций, где критическая секция существует на уровне бизнес-логики, а не отдельной строки таблицы.

➡️ SQL Ready | #практика

Двойной NOT EXISTS: реляционное деление без подсчёта строк!

Задачи вида «найти сущности, удовлетворяющие всем условиям из некоторого набора» встречаются регулярно: все разрешения роли, все обязательные атрибуты товара, все зависимости конфигурации или все компетенции проекта. В реляционной алгебре такой класс задач связан с операцией деления.

Предположим, требования проекта и навыки сотрудников представлены отношениями:

project_requirements(project_id, skill_id)

employee_skills(employee_id, skill_id)

Требуется получить сотрудников, обладающих всеми навыками проекта 42. Один из распространённых вариантов — подсчитать совпадения:

SELECT e.id
FROM employees e
JOIN employee_skills s
    ON s.employee_id = e.id
JOIN project_requirements r
    ON r.project_id = 42
   AND r.skill_id = s.skill_id
GROUP BY e.id
HAVING COUNT(DISTINCT r.skill_id) = (
    SELECT COUNT(DISTINCT skill_id)
    FROM project_requirements
    WHERE project_id = 42
);

Запрос корректен, если skill_id не допускает NULL. DISTINCT необходим, если уникальность пар (project_id, skill_id) и (employee_id, skill_id) не гарантирована схемой.

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

SELECT e.id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM project_requirements r
    WHERE r.project_id = 42
      AND NOT EXISTS (
          SELECT 1
          FROM employee_skills s
          WHERE s.employee_id = e.id
            AND s.skill_id = r.skill_id
      )
);

Внешний NOT EXISTS ищет отсутствие невыполненных требований, а внутренний проверяет отсутствие соответствующего навыка. Важное свойство — дубли не влияют на результат:

INSERT INTO employee_skills (employee_id, skill_id)
VALUES
    (7, 10),
    (7, 10),
    (7, 20);

Если проект требует навыки 10 и 20, сотрудник 7 удовлетворяет требованиям независимо от числа повторений (7, 10). EXISTS проверяет наличие строки, а не их количество.

На практике такие дубли лучше запрещать ограничениями:

ALTER TABLE project_requirements
    ADD CONSTRAINT uq_project_requirement
    UNIQUE (project_id, skill_id);

ALTER TABLE employee_skills
    ADD CONSTRAINT uq_employee_skill
    UNIQUE (employee_id, skill_id);

Также skill_id в такой модели обычно следует объявлять NOT NULL. Иначе COUNT(DISTINCT skill_id) игнорирует NULL, а сравнение s.skill_id = r.skill_id с NULL не даст совпадения, что может привести к различию результатов двух подходов.

Если у проекта 42 вообще нет требований, двойной NOT EXISTS вернёт всех сотрудников: нет ни одного требования, которое сотрудник не выполняет. Если по правилам предметной области проект без требований не должен возвращать кандидатов, это нужно указать отдельно:

SELECT e.id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM project_requirements r
    WHERE r.project_id = 42
)
AND NOT EXISTS (
    SELECT 1
    FROM project_requirements r
    WHERE r.project_id = 42
      AND NOT EXISTS (
          SELECT 1
          FROM employee_skills s
          WHERE s.employee_id = e.id
            AND s.skill_id = r.skill_id
      )
);

Двойной NOT EXISTS полезен тем, что выражает исходную задачу напрямую: вместо подсчёта совпадений мы проверяем отсутствие хотя бы одного невыполненного требования.

🔥 Для условий вида «выполнены все требования», «присутствуют все зависимости» или «есть соответствие каждому элементу набора» это одна из наиболее естественных форм реляционного деления в SQL.

➡️ SQL Ready | #практика

😎 Очень интересная статья на Хабре: «Как устроено шардирование PG в процессинге Яндекс Такси»!

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

🔊 Продолжай читать на Habr!

➡️ SQL Ready | #статья

📂 Шпаргалка по числовым функциям!

Например, ROUND() используется для округления значений, MOD() — для получения остатка от деления, а агрегатные функции AVG(), SUM(), MIN() и MAX() помогают выполнять вычисления по наборам данных.

На изображении собраны основные числовые функции: назначение, синтаксис, примеры запросов и особенности использования в разных СУБД.

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

🐱 PostgreSQL Course RU — курс по PostgreSQL и SQL для разработчиков!

В репозитории собраны материалы для изучения работы с базами данных: от основ SQL и проектирования таблиц до функций, индексов, транзакций, оконных функций, PL/pgSQL и продвинутых возможностей PostgreSQL. Отличный вариант, чтобы разобраться с базами данных и перейти от простых запросов к пониманию работы СУБД.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

😍 SQL-Tutorial — бесплатный учебник по SQL с практическими заданиями!

Онлайн-учебник для изучения SQL: от базовых запросов до более сложной работы с данными. Теория сопровождается примерами и упражнениями, поэтому изученные конструкции можно сразу закреплять на практике. Материал разделён на главы, что удобно для поэтапного обучения.

📌 Оставляю ссылочку: sql-tutorial.ru

➡️ SQL Ready | #ресурс