7786
Авторский канал про Базы Данных и SQL Ресурсы, гайды, задачи, шпаргалки. Информация ежедневно пополняется! Автор: @energy_it
Ускоряйте массовую загрузку через FREEZE!
При загрузке данных в новую таблицу PostgreSQL может сразу пометить строки как замороженные. Для этого у COPY есть малоизвестная опция FREEZE.
BEGIN;
CREATE TABLE events_import (
id bigint,
payload jsonb
);
COPY events_import
FROM '/data/events.csv'
WITH (FORMAT csv, FREEZE true);
COMMIT;
FREEZE позволяет избежать последующей работы по заморозке загруженных строк. Но есть условие: таблица должна быть создана или очищена через TRUNCATE в текущей подоперации транзакции.BEGIN;
TRUNCATE events_import;
COPY events_import
FROM '/data/events.csv'
WITH (FORMAT csv, FREEZE true);
COMMIT;
FREEZE не отключает обычные проверки ограничений и не гарантирует ускорения самого импорта. Его преимущество — уменьшение будущей работы VACUUM по заморозке строк.COPY FREEZE при полной загрузке новых или очищенных таблиц, чтобы сократить последующую работу по обслуживанию данных.
📂 16 паттернов программирования, которые должен знать каждый разработчик!
Например, Two Pointers помогает эффективно работать с массивами, Sliding Window — находить подмассивы по заданным условиям, а Dynamic Programming — решать сложные задачи через повторное использование результатов подзадач.
На картинке — 16 популярных алгоритмических паттернов и структур данных, которые пригодятся на технических собеседованиях.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
⚡️ В сети начали массово сливать IT-курсы и книги известных онлайн школ
Вот отсортированная база с тонной материала (постепенно пополняется):
(363 видео, 87 книги) — Python
(117 видео, 53 книги) — ВАЙБКОД
(174 видео, 91 книги) — DevOps
(415 видео, 68 книги) — Frontend
(137 видео, 93 книги) — DS/ML
(143 видео, 33 книги) — ИБ/Хакинг
(352 видео, 89 книги) — С/С++/C
(343 видео, 87 книги) — Java/QA
(176 видео, 32 книги) — Git/Linux
(167 видео, 53 книги) — PHP/1С
(227 видео, 83 книги) — SQL/БД
(114 видео, 77 книги) — Сисадмин
(107 видео, 43 книги) — BA/SA
(181 видео, 32 книги) — Go/Rust
(167 видео, 43 книги) — Kotlin/Swift
(112 видео, 24 книги) — Flutter
(113 видео, 82 книги) — GameDev
(183 видео, 37 книги) — Дизайн
(136 видео, 33 книги) — PM/HR
Скачивать ничего не нужно — все выложили в Telegram
Переносите строки без промежуточной выборки!
Обычно архивирование делают в два этапа: сначала копируют записи, затем удаляют исходные. В PostgreSQL можно связать эти действия через DELETE ... RETURNING.
-- Удаляем строки и сразу получаем их содержимое
DELETE FROM events
WHERE created_at < DATE '2025-01-01'
RETURNING id, user_id, payload, created_at;
WITH moved AS (
DELETE FROM events
WHERE created_at < DATE '2025-01-01'
RETURNING id, user_id, payload, created_at
)
INSERT INTO events_archive (
id, user_id, payload, created_at
)
SELECT id, user_id, payload, created_at
FROM moved;
WITH moved AS (
DELETE FROM events
WHERE created_at < DATE '2025-01-01'
RETURNING id, payload, created_at
)
INSERT INTO events_archive (id, payload, archived_at)
SELECT id, payload, now()
FROM moved;
DELETE ... RETURNING внутри WITH позволяет переносить строки между таблицами одним атомарным запросом, без отдельного чтения и передачи данных через приложение.
📂 Шпаргалка по Eventual Consistency в распределённых системах!
Например, Event-Driven обеспечивает асинхронное распространение изменений между сервисами, а CQRS позволяет разделять операции чтения и записи с независимым обновлением моделей данных.
На картинке — 4 архитектурных подхода, которые помогают управлять согласованностью данных, снижать связанность компонентов и масштабировать распределённые системы.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
😎 SQLinfo — русскоязычный справочник по SQL!
Сайт с учебными и справочными материалами по SQL и работе с реляционными базами данных. Подойдёт для изучения основных конструкций языка, понимания принципов построения запросов и повторения теории. Ресурс пригодится начинающим аналитикам и разработчикам, а также тем, кому нужен дополнительный справочник по SQL во время обучения или работы с базами данных.
📌 Оставляю ссылочку: sqlinfo.ru
➡️ SQL Ready | #сайт
Обновляйте несколько колонок одним подзапросом!
Вместо нескольких одинаковых подзапросов:
UPDATE orders o
SET customer_name = (
SELECT name FROM customers WHERE id = o.customer_id
),
customer_email = (
SELECT email FROM customers WHERE id = o.customer_id
);
-- Один подзапрос сразу для нескольких колонок
UPDATE orders o
SET (customer_name, customer_email) = (
SELECT c.name, c.email
FROM customers c
WHERE c.id = o.customer_id
);
UPDATE accounts a
SET (last_payment_at, total_paid) = (
SELECT max(p.created_at),
sum(p.amount)
FROM payments p
WHERE p.account_id = a.id
);
SET (a, b, ...) = (SELECT ...) позволяет одним подзапросом вычислить сразу несколько новых значений строки и заметно сокращает повторяющийся SQL.
Почему UNIQUE допускает несколько NULL в PostgreSQL!
Ограничение UNIQUE гарантирует уникальность значений, но для NULL в PostgreSQL действует отдельная семантика. По умолчанию два NULL считаются различными при проверке уникальности, поэтому ограничение не запрещает несколько строк с неопределённым значением:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT UNIQUE
);
email нарушит ограничение уникальности.INSERT INTO users (id, email)
VALUES (1, 'user@example.com');
INSERT INTO users (id, email)
VALUES (2, 'user@example.com');
NULL те же правила не применяются: несколько строк успешно проходят проверку ограничения.INSERT INTO users (id, email)
VALUES
(3, NULL),
(4, NULL),
(5, NULL);
CREATE TABLE contacts (
id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
external_id TEXT,
UNIQUE (company_id, external_id)
);
(company_id, external_id) конфликтуют, но несколько комбинаций (10, NULL) по умолчанию допустимы:INSERT INTO contacts (id, company_id, external_id)
VALUES
(1, 10, NULL),
(2, 10, NULL);
NULLS NOT DISTINCT. В таком ограничении NULL считаются неразличимыми при проверке уникальности:CREATE TABLE contacts_strict (
id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
external_id TEXT,
UNIQUE NULLS NOT DISTINCT (company_id, external_id)
);
(10, NULL) нельзя: вторая вставка завершится нарушением уникальности.INSERT INTO contacts_strict (id, company_id, external_id)
VALUES (1, 10, NULL);
INSERT INTO contacts_strict (id, company_id, external_id)
VALUES (2, 10, NULL);
CREATE UNIQUE INDEX uq_contacts_company_external
ON contacts (company_id, external_id)
NULLS NOT DISTINCT;
NULLS NOT DISTINCT не эквивалентен NOT NULL. NOT NULL полностью запрещает отсутствие значения, тогда как NULLS NOT DISTINCT разрешает NULL, но включает его в контроль уникальности.CREATE TABLE identifiers (
id BIGINT PRIMARY KEY,
external_id TEXT,
UNIQUE NULLS NOT DISTINCT (external_id)
);
external_id IS NULL, но вторая аналогичная строка уже нарушит ограничение.UNIQUE в PostgreSQL по умолчанию допускает несколько NULL. Если отсутствие значения также должно быть уникальным состоянием, начиная с PostgreSQL 15 это можно явно зафиксировать через NULLS NOT DISTINCT.
Проверяйте связи с учётом периода!
В PostgreSQL 18 связь может учитывать ещё и период его действия.WITHOUT OVERLAPS не даст создать пересекающиеся периоды для одного product_id. По сути PostgreSQL сам обеспечивает проверку пересечения диапазонов.
А теперь интересная часть:
CREATE TABLE discounts (
product_id bigint,
valid_at daterange,
FOREIGN KEY (
product_id,
PERIOD valid_at
)
REFERENCES prices (
product_id,
PERIOD valid_at
)
);
INSERT INTO discounts
VALUES (
42,
'[2026-05-01,2026-05-15)'::daterange
);
PERIOD позволяет проверять временные связи обычным FOREIGN KEY — без триггеров и ручных проверок.
👍 Устройство PostgreSQL — подробная документация на русском языке!
Материалы посвящены не написанию SQL-запросов, а архитектуре СУБД и внутренним механизмам обработки и хранения данных. Подробно рассматриваются физическая структура базы данных, системные каталоги, управление транзакциями, блокировки, WAL и др.
📌 Оставляю ссылочку: database.diasoft.ru
➡️ SQL Ready | #ресурс
Проверяйте JSON без преобразования!
Если JSON приходит как text, необязательно делать ::jsonb и ловить ошибку преобразования. IS JSON просто вернёт true или false.
SELECT '{"id": 42}' IS JSON; -- true
SELECT '{broken}' IS JSON; -- falseJSON:SELECT payload IS JSON OBJECT
FROM staging;
SELECT payload IS JSON ARRAY
FROM staging;
SELECT '{"id":1,"id":2}'
IS JSON OBJECT WITH UNIQUE KEYS;
-- falseCREATE TABLE incoming_events (
payload text NOT NULL
CHECK (payload IS JSON OBJECT WITH UNIQUE KEYS)
);
IS JSON позволяет валидировать JSON как обычное условие SQL — без преобразований, исключений и регулярных выражений.
😍 SQL-Tutorial — бесплатный учебник по SQL с практическими заданиями!
Онлайн-учебник для изучения SQL: от базовых запросов до более сложной работы с данными. Теория сопровождается примерами и упражнениями, поэтому изученные конструкции можно сразу закреплять на практике. Материал разделён на главы, что удобно для поэтапного обучения.
📌 Оставляю ссылочку: sql-tutorial.ru
➡️ SQL Ready | #ресурс
📂 Шпаргалка по числовым функциям!
Например, ROUND() используется для округления значений, MOD() — для получения остатка от деления, а агрегатные функции AVG(), SUM(), MIN() и MAX() помогают выполнять вычисления по наборам данных.
На изображении собраны основные числовые функции: назначение, синтаксис, примеры запросов и особенности использования в разных СУБД.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
🖥 PostgreSQL — разделение и сборка строк!Объединение значений с CONCAT и CONCAT_WS, агрегация строк с STRING_AGG, извлечение отдельных частей с SPLIT_PART, преобразование строк в массивы и обратно с STRING_TO_ARRAY и ARRAY_TO_STRING, а также разделение данных по регулярным выражениям с REGEXP_SPLIT_TO_ARRAY и REGEXP_SPLIT_TO_TABLE. Полезно для обработки списков, тегов, адресов и других текстовых данных.
➡️ SQL Ready | #шпора
Пачку офферов заказывали?
О всех трудоустройствах через ИИ-ассистента Софи ребята узнают от пользователей.
И какой же кайф, когда они делятся своими историями.
Вот так в чате узнали сразу о трех новых офферах:
• Кейс Сергея - 2 недели в Софи и 2 оффера.
• Кейс Елены - 1 неделя в Софи и оффер спустя 10 лет перерыва в работе. Елене 52 года.
• Кейс Юлии - 1 месяц в Софи, свитчер из финансового аналитика в разработчика SQL. И еще один оффер🎉
Искренние поздравления ребятам и удачи им на новой работе!
Зайти на 3 бесплатных дня в Софи можно уже сейчас.
Регистрируйся здесь.
Требуются парни и девушки в возрасте 19–40 лет, желающие работать в сфере IT.
Опыт в программировании не нужен.
Завтра запускаем бесплатный онлайн-интенсив по Frontend-разработке, где будем показывать, как разрабатывать сайты и веб-приложения используя ИИ.
За 7 дней обучения без опыта ты:
1. Сделаешь реальный сайт сервиса доставки с нуля;
2. Добавишь JavaScript-логику, расчёты и трекинг;
3. Подключишь карты и маршрут доставки через API;
4. Превратишь сайт в Angular-приложение;
5. Подключишь backend и выложишь проект в продакшен;
6. Научишься использовать ChatGPT и Giga во Frontend-разработке;
7. Узнаешь 9 способов найти первый заказ на фрилансе даже без опыта.
А главное, ты увидишь, что разрабатывать сайты и приложения не так сложно, как кажется. И поймёшь, как тебе развиваться в этой профессии, чтобы уже через пару месяцев зарабатывать от 1000$ на вёрстке сайтов.
👉 Проскочить на интенсив бесплатно
🔥 С 2019 стабильно помогаем с обучением, практикой, зарабатывать на фрилансе и проходить собеседования.
Почему перенос условия из ON в WHERE меняет семантику LEFT JOIN
При использовании LEFT JOIN расположение предиката определяет не только состав результата, но и возможность сохранить строки левой таблицы без соответствий. Предикаты в ON участвуют в поиске совпадений, тогда как WHERE фильтрует результат соединения.
Рассмотрим запрос, который должен возвращать всех пользователей и количество их завершённых заказов:
SELECT
u.id,
COUNT(o.id) AS completed_orders
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id;
o.status = 'completed' является null-rejecting: он не может вернуть TRUE, когда o.status содержит NULL. Поэтому строки, дополненные NULL при внешнем соединении, отбрасываются.SELECT
u.id,
COUNT(o.id) AS completed_orders
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
AND o.status = 'completed'
GROUP BY u.id;
0. Здесь принципиально использование COUNT(o.id): агрегат игнорирует NULL, тогда как COUNT(*) учитывает и строку, созданную внешним соединением при отсутствии совпадений.FILTER, сохранив соединение со всеми заказами:SELECT
u.id,
COUNT(o.id) FILTER (
WHERE o.status = 'completed'
) AS completed_orders
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
GROUP BY u.id;
FILTER позволяет применять независимые условия к разным агрегатам без повторного соединения таблиц:SELECT
u.id,
COUNT(o.id) FILTER (
WHERE o.status = 'completed'
) AS completed_orders,
COUNT(o.id) FILTER (
WHERE o.status = 'pending'
) AS pending_orders
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
GROUP BY u.id;
WHERE исключает все строки с NULL со стороны правой таблицы, внешнее соединение в некоторых случаях может быть преобразовано во внутреннее. Такое преобразование сохраняет семантику исходного запроса, но не исправляет ошибочно выбранную логику фильтрации.EXPLAIN:EXPLAIN (ANALYZE, BUFFERS)
SELECT
u.id,
COUNT(o.id)
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id;
Hash Join вместо Hash Left Join или другой тип внутреннего соединения. Конкретный алгоритм зависит от статистики, индексов и оценок стоимости, поэтому наличие LEFT JOIN в sql не гарантирует сохранение внешнего соединения в физическом плане.ON и WHERE при внешнем соединении — семантическое преобразование, а не просто изменение записи sql. Null-rejecting предикаты могут устранять строки без совпадений и создавать условия для упрощения LEFT JOIN до INNER JOIN оптимизатором.
👍 Интересная статья лежит на Хабре: «За пределами EXPLAIN: как увидеть выполнение запроса вживую в распределённой СУБД»!
В этой статье:• Узнаете, почему EXPLAIN не всегда помогает понять причины медленного выполнения SQL-запросов;• Разберёте, как pg_query_state позволяет отслеживать выполнение запросов в Apache Cloudberry в реальном времени;• Посмотрите, как архитектура с Go-агентами и сервисом YAGPCC помогает собирать метрики с множества узлов без перегрузки координатора.
🔊 Продолжай читать на Habr!
Требуются 7 человек, чтобы помогали собирать чат-ботов для бизнеса по блокам, как лего.
Стоит попробовать, если хочешь:
✅ ЗП: от 5000-9000₽ за 1-2 вечера.
✅ Занятость: 3–4 часа в день.
✅ Свободный график.
Опыт в разработке не требуется.
Всё, что нужно для старта — запустить обучение
👉@pautov_techspec_bot
И пройти инструктаж.
Почему BETWEEN может незаметно исключать данные при фильтрации по датам!
При работе с временными диапазонами часто используют BETWEEN. Но если колонка хранит дату вместе со временем, такой фильтр может возвращать не все ожидаемые записи.
Допустим, есть таблица заказов:
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
created_at TIMESTAMP NOT NULL,
amount DECIMAL(12, 2)
);
SELECT *
FROM orders
WHERE created_at BETWEEN
TIMESTAMP '2026-09-01 00:00:00'
AND TIMESTAMP '2026-09-30 00:00:00';
BETWEEN включает обе границы диапазона и эквивалентен сочетанию >= и <=. Поэтому запрос вернёт записи с 1 сентября по начало 30 сентября, но исключит все заказы, созданные позднее полуночи последнего дня месяца.2026-09-30 15:30:00 не попадёт в результат. Иногда верхнюю границу пытаются исправить вручную:WHERE created_at BETWEEN
TIMESTAMP '2026-09-01 00:00:00'
AND TIMESTAMP '2026-09-30 23:59:59';
2026-09-30 23:59:59.500 окажется за пределами диапазона.SELECT *
FROM orders
WHERE created_at >= TIMESTAMP '2026-09-01 00:00:00'
AND created_at < TIMESTAMP '2026-10-01 00:00:00';
WHERE created_at >= :date_from
AND created_at < :date_to;
date_to, каждая запись на границе интервалов попадёт ровно в один диапазон.CREATE INDEX idx_orders_created_at
ON orders (created_at);
>= и < обычно надёжнее, чем BETWEEN. Полуоткрытые диапазоны не зависят от точности TIMESTAMP, не пересекаются на границах и удобны для отчётности, пакетной обработки и инкрементальной загрузки данных.
🖥 Oracle - ограничение количества строк и выборка Top-N!Получение первых N строк с FETCH FIRST, постраничная выборка с FETCH NEXT и OFFSET, включение строк с одинаковыми значениями сортировки через WITH TIES, ограничение результата в процентах с PERCENT, использование ROWNUM, а также нумерация и ранжирование с ROW_NUMBER() и DENSE_RANK().
➡️ SQL Ready | #шпора
❤️ sqlite-postgres-cours — большой практический курс по базам данных!
Материал ведёт от первых SQL-запросов и проектирования схем до индексов, транзакций, оптимизации и работы с БД из Python через SQLAlchemy и Alembic. Особенно полезно, что SQLite и PostgreSQL изучаются параллельно: автор показывает различия между ними, типичные ошибки и ситуации, где выбор СУБД имеет значение.
Оставляю ссылочку: GitHub 📱
📂 Шпаргалка по паттернам распределённых систем!
Например, Replication повышает доступность и отказоустойчивость за счёт хранения копий данных на нескольких узлах, а Sharding позволяет горизонтально масштабировать систему, распределяя данные между независимыми шардами.
Эти паттерны помогают решать основные задачи распределённых систем: масштабирование, отказоустойчивость, распределение данных, асинхронное взаимодействие и согласованность.
Сохрани, чтобы не потерять!
➡️ SQL Ready | #ресурс
⚡️5 фундаментальных курсов по ИБ по цене одного
Это предложение для тех, кто готов войти в новую профессию прямо сейчас!
🔥Пакет курсов за 50 000 ₽ вместо 250 000 ₽:
▪️ Linux CyberPunk - обычная цена 49 500 ₽ (+ Курс идет с официальным дипломом системного администратора!)
▪️ SQL для хакера - обычная цена 15 000 ₽
▪️ HackerPoint - обычная цена ~70 000 ₽
▪️ HackerPoint (Blue vs Red Team) - обычная цена 43 500 ₽
▪️ AI-помощники на Python - обычная цена 71 500 ₽
Вы экономите 200 000 ₽ и получаете полную базу: от работы с терминалом и базами данных до разработки ИИ-агентов и взлома систем.
🔒 Квота: набор на программу ограничен по количеству мест.
🦔Отправь промокод SQLready в чат с менеджером, чтобы закрепить за собой скидку и узнать подробности:
👉@cyacademy_support
COUNT(*) в PostgreSQL: MVCC, visibility map и стоимость выполнения!
В PostgreSQL точный COUNT(*) требует определить количество строк, видимых текущему MVCC snapshot. Глобального счётчика, который можно было бы использовать для транзакционно корректного результата, у таблицы нет:
SELECT COUNT(*)
FROM orders;
Seq Scan, поскольку для точного результата всё равно требуется обработать множество видимых строк:Aggregate
-> Seq Scan on orders
PRIMARY KEY или другого подходящего индекса не гарантирует его использование. Если модель стоимости считает путь через индекс дешевле, PostgreSQL может выполнить запрос через Index Only Scan:Aggregate
-> Index Only Scan using orders_pkey on orders
Index Only Scan не означает получение готового количества записей из индекса. Информация, необходимая для определения MVCC-видимости строк, находится в heap, а не в самом индексе.all-visible, PostgreSQL может считать находящиеся на ней строки видимыми без дополнительного обращения к heap:EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders;
all-visible сбрасывается и впоследствии может быть снова установлен VACUUM.Index Only Scan может требовать дополнительных обращений к heap:Index Only Scan using orders_pkey on orders
Heap Fetches: 18427
COUNT(*) зависит не только от размера таблицы и наличия индекса, но и от состояния visibility map, характера нагрузки, работы autovacuum, выбранного плана выполнения и состояния кэша PostgreSQL.pg_class:SELECT reltuples::bigint
FROM pg_class
WHERE oid = 'orders'::regclass;
reltuples обновляется, в частности, при VACUUM и ANALYZE и остаётся приблизительной оценкой. Если статистика для таблицы ещё не собиралась, значение может быть -1.
Настройте оценку пользовательских функций!
Для пользовательской функции PostgreSQL позволяет явно указать стоимость вызова через COST, а для функции, возвращающей набор строк, — ожидаемое число строк через ROWS.
Причём пересоздавать функцию не обязательно:
ALTER FUNCTION find_orders(bigint)
ROWS 5;
ALTER FUNCTION expensive_check(jsonb)
COST 500;
COST, тем дороже планировщик считает вычисление и тем сильнее старается не выполнять его лишний раз. COST и ROWS как информацию для оптимизации пользовательских функций. EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, o.*
FROM users u
CROSS JOIN LATERAL find_orders(u.id) o;
COST и ROWS — иногда правильный план получается без переписывания запроса и новых индексов.
🐱 PostgreSQL Course RU — курс по PostgreSQL и SQL для разработчиков!
В репозитории собраны материалы для изучения работы с базами данных: от основ SQL и проектирования таблиц до функций, индексов, транзакций, оконных функций, PL/pgSQL и продвинутых возможностей PostgreSQL. Отличный вариант, чтобы разобраться с базами данных и перейти от простых запросов к пониманию работы СУБД.
Оставляю ссылочку: GitHub 📱
😎 Очень интересная статья на Хабре: «Как устроено шардирование PG в процессинге Яндекс Такси»!
В этой статье:• Узнаете, зачем крупным системам переходить от одной базы к распределённому хранению данных;• Разберётесь, как работает выбор шарда и какие архитектурные решения помогают масштабировать PostgreSQL;• Посмотрите на инженерные подходы из высоконагруженного продакшена.
🔊 Продолжай читать на Habr!
Двойной 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 полезен тем, что выражает исходную задачу напрямую: вместо подсчёта совпадений мы проверяем отсутствие хотя бы одного невыполненного требования.
PostgreSQL Advisory Locks: синхронизация конкурентных операций!
Блокировок строк недостаточно, когда нужно синхронизировать не конкретную запись, а логическую операцию. Например, два воркера одновременно начинают генерацию одного и того же отчёта для пользователя. Даже если результат в итоге сохраняется с UNIQUE(user_id), это не предотвращает двойное выполнение дорогостоящей работы.
Для таких случаев PostgreSQL предоставляет advisory locks — блокировки, ключ и семантику которых определяет приложение:
SELECT pg_advisory_lock(1001);
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;
1 можно использовать как namespace операции, а 1001 — как идентификатор ресурса:(1, 1001) — generate_report / user 1001
(2, 1001) — recalculate_stats / user 1001
SELECT pg_try_advisory_xact_lock(1, 1001);
true — lock получен, выполняем работу
false — lock уже удерживается, работу можно пропустить
ALTER TABLE reports
ADD CONSTRAINT reports_user_id_key UNIQUE (user_id);
(1, 1001). Для него это просто ключ блокировки. Поэтому все конкурирующие процессы должны одинаково формировать ключи и захватывать соответствующие locks.