SQL Ready | Базы Данных

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, а не в самом индексе. PostgreSQL оптимизирует эту проверку с помощью visibility map. Если страница отмечена как 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. 🔥 Для больших таблиц это принципиально разные варианты: полный MVCC-корректный подсчёт или быстрое получение приблизительной статистической оценки. ➡️ SQL Ready | #практика

Как оплачивать зарубежные сервисы в 2026 году? Можно бегать между посредниками и бояться блокировок после оплаты, а можно выпустить международную карту Lumio Pay и пользоваться любимыми сервисами без рисков. — выпуск карты за 2 минуты — лучший курс пополнения на рынке (у конкурентов на 20% выше) — пополнение рублями или криптой — чистые BIN карт, оплата без риска блокировок Пока все ищут идеальное решение, оно у тебя перед глазами: @LumioPay

Проверяйте JSON без преобразования! Если JSON приходит как text, необязательно делать ::jsonb и ловить ошибку преобразования. IS JSON просто вернёт true или false.

SELECT '{"id": 42}' IS JSON;     -- true
SELECT '{broken}'  IS JSON;     -- false

Можно сразу потребовать конкретный тип JSON:

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;
-- false

Это можно использовать непосредственно в ограничении таблицы:

CREATE TABLE incoming_events (
    payload text NOT NULL
        CHECK (payload IS JSON OBJECT WITH UNIQUE KEYS)
);

🔥 IS JSON позволяет валидировать JSON как обычное условие SQL — без преобразований, исключений и регулярных выражений. ➡️ SQL Ready | #совет

Настройте оценку пользовательских функций! Для пользовательской функции PostgreSQL позволяет явно указать стоимость вызова через COST, а для функции, возвращающей набор строк, — ожидаемое число строк через ROWS. Причём пересоздавать функцию не обязательно:

ALTER FUNCTION find_orders(bigint)
ROWS 5;

ALTER FUNCTION expensive_check(jsonb)
COST 500;

Чем выше COST, тем дороже планировщик считает вычисление и тем сильнее старается не выполнять его лишний раз. PostgreSQL прямо предоставляет COST и ROWS как информацию для оптимизации пользовательских функций.

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, o.*
FROM users u
CROSS JOIN LATERAL find_orders(u.id) o;

🔥 Если пользовательская функция ломает оценки строк или вызывается слишком часто, проверьте COST и ROWS — иногда правильный план получается без переписывания запроса и новых индексов. ➡️ SQL Ready | #совет

🔎 Гибридный поиск в YDB: когда SQL ищет не только по словам В YDB, созданном Yandex B2B Tech, появился гибридный поиск, который объединяет полнотекстовый и векторный поиск. Полнотекстовый поиск находит точные совпадения, а векторный — данные, близкие по смыслу. Вместе они позволяют, например, найти документ по номеру и одновременно учесть описание нужной ситуации. Главное — для этого больше не обязательно собирать отдельный стек из БД, поискового движка и векторного хранилища. В YDB оба индекса работают как распределённые таблицы и обновляются в одной транзакции с основными данными. Подход можно использовать для поиска по документам, базам знаний, каталогам, тикетам и в RAG-системах. Поиск доступен в enterprise-редакции версии 26.3 для использования в on-premises и в Managed Service for YDB. 15 октября на митапе эксперты YDB объяснят, как реализовать гибридный поиск в рамках одной СУБД и повысить качество работы ИИ-агентов и рекомендательных систем. 👉 Регистрация

😍 SQL-Tutorial — бесплатный учебник по SQL с практическими заданиями! Онлайн-учебник для изучения SQL: от базовых запросов до более сложной работы с данными. Теория сопровождается примерами и упражнениями, поэтому изученные конструкции можно сразу закреплять на практике. Материал разделён на главы, что удобно для поэтапного обучения. 📌 Оставляю ссылочку: sql-tutorial.ru ➡️ SQL Ready | #ресурс

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

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

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

📂 Шпаргалка по числовым функциям! Например, ROUND() используется для округления значений, MOD() — для получения остатка от деления, а агрегатные функции AVG(), SUM(), MIN() и MAX() помогают выполнять вычисления по наборам данных. На изображении собраны основные числовые функции: назначение, синтаксис, примеры запросов и особенности использования в разных СУБД. Сохрани, чтобы не потерять! ➡️ SQL Ready | #ресурс

😎 Очень интересная статья на Хабре: «Как устроено шардирование PG в процессинге Яндекс Такси»! В этой статье: • Узнаете, зачем крупным системам переходить от одной базы к распределённому хранению данных; • Разберётесь, как работает выбор шарда и какие архитектурные решения помогают масштабировать PostgreSQL; • Посмотрите на инженерные подходы из высоконагруженного продакшена.

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

➡️ 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 | #шпора

Двойной 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 | #практика

Пачку офферов заказывали? О всех трудоустройствах через ИИ-ассистента Софи ребята узнают от пользователей. И какой же кайф, когда они делятся своими историями. Вот так в чате узнали сразу о трех новых офферах: • Кейс Сергея - 2 недели в Софи и 2 оффера. • Кейс Елены - 1 неделя в Софи и оффер спустя 10 лет перерыва в работе. Елене 52 года. • Кейс Юлии - 1 месяц в Софи, свитчер из финансового аналитика в разработчика SQL. И еще один оффер🎉 Искренние поздравления ребятам и удачи им на новой работе! Зайти на 3 бесплатных дня в Софи можно уже сейчас. Регистрируйся здесь.

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 | #практика

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

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

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

Почему 5 систем могут потребовать 10 интеграций, а 10 — уже 45? Когда в data stack появляется несколько отдельных инструментов для хранения, обработки и аналитики, между ними приходится выстраивать обмен данными. Для пяти систем таких связок может быть до 10, а для десяти — уже до 45. Yandex B2B Tech представила DataLens Platform на основе архитектуры lakehouse. Исходные и подготовленные данные хранятся в общей среде на S3 с использованием Apache Iceberg. Из технических деталей — storage и compute масштабируются независимо. То есть рост объема данных не обязательно означает пропорциональное увеличение вычислительных ресурсов, и наоборот. 👉 Подробнее об архитектуре DataLens Platform — в материале СМИ.

Как атомарно резервировать лимит без 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 для разработчиков! Например, SELECT используется для получения данных из таблиц, а CREATE, ALTER и DROP помогают управлять структурой базы данных. JOIN позволяет объединять данные из нескольких таблиц, а агрегатные функции (COUNT, SUM, AVG) — анализировать большие объёмы информации. На картинке — шпаргалка с основными категориями команд, операторами, ключевыми словами, объектами базы данных, ограничениями, функциями агрегации, типами JOIN. Сохрани, чтобы не потерять! ➡️ 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 | #совет

🤔 MentorData — материалы по SQL и аналитике данных! Авторский блог с материалами для тех, кто изучает SQL, аналитику данных и готовится к работе в этой сфере. Основной акцент сделан не только на синтаксисе, но и на решении аналитических задач и понимании бизнес-логики. В статьях разбираются оконные функции, JOIN, GROUP BY, CTE, работа с аномалиями и метриками, задачи с технических собеседований и подходы к анализу данных. 📌 Оставляю ссылочку: mentordata.ru ➡️ SQL Ready | #ресурс

Почему порядок колонок в составном индексе важен! Составной индекс часто создают, когда запрос фильтрует данные сразу по нескольким колонкам. Но просто добавить нужные поля в индекс недостаточно — их порядок влияет на то, какую часть индекса 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 | #практика