✍️ PostgreSQL Querying — практическое изучение SQL на PostgreSQL!

Здесь подробно разбираются запросы, работа с данными, фильтрация, JOIN’ы, агрегации и другие конструкции, которые постоянно используются в разработке. Материал подаётся последовательно и на примерах, поэтому намного проще понять логику запросов и научиться писать их самостоятельно.

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

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

Антиджойн в SQL — как находить отсутствующие связи!

Одна из самых частых задач в аналитике — найти строки, для которых не существует связанных данных. Например, пользователей без заказов или товары без продаж.

Таблицы:

users(id, email)
orders(id, user_id)

Многие пытаются писать через NOT IN:

id="x8d2qa"
SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Но здесь есть проблема: если подзапрос вернёт хотя бы один NULL, результат может стать пустым.

Причина — логика NULL в SQL ломает сравнение NOT IN.

Решением может служить антиджойн через NOT EXISTS:

id="m4z7pk"
SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

SQL проверяет отсутствие связанной строки и сразу останавливается при первом совпадении.

Ту же задачу можно решить через LEFT JOIN:

id="f1q9vc"
SELECT u.*
FROM users u
LEFT JOIN orders o
       ON o.user_id = u.id
WHERE o.id IS NULL;

LEFT JOIN оставляет все строки users, а WHERE o.id IS NULL отбирает только те, где совпадений не нашлось.

Этот паттерн и называется антиджойн — верни строки, для которых связи не существует.

Особенно полезно это в проверках целостности данных:

id="k6n2yb"
SELECT *
FROM orders o
WHERE NOT EXISTS (
    SELECT 1
    FROM users u
    WHERE u.id = o.user_id
);

Так можно быстро найти битые записи с отсутствующими foreign key.

🔥 Антиджойны пригодятся в аналитике, ETL, аудитах данных и поиске проблемных связей между таблицами.

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

📂 Напоминалка по шардингу баз данных!

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

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

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

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

PostgreSQL умеет пропускать заблокированные строки без ожидания!

Большинство знают SKIP LOCKED только для очередей через SELECT ... FOR UPDATE. Но мало кто использует его для параллельной пакетной обработки внутри PostgreSQL.

Если несколько воркеров одновременно обрабатывают огромную таблицу задач:

SELECT id
FROM jobs
WHERE processed = false
FOR UPDATE;

Без SKIP LOCKED процессы начинают ждать друг друга даже при наличии свободных строк.

PostgreSQL позволяет просто пропускать уже занятые записи:

SELECT id
FROM jobs
WHERE processed = false
FOR UPDATE SKIP LOCKED;

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

Это можно встроить прямо в UPDATE:

WITH cte AS (
  SELECT id
  FROM jobs
  WHERE processed = false
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET processed = true
WHERE id IN (SELECT id FROM cte);

Получается параллельная обработка на уровне PostgreSQL без внешних систем очередей.

🔥 Аналогично строят высоконагруженные фоновые обработчики, обработку писем, биллинг и массовые пакетные операции.

➡️ SQL Ready | #совет

🧐 SQL Tutorial — подробный гайд по SQL с примерами!

На сайте собрано множество обучающих материал по SQL: от базовых запросов SELECT и WHERE до JOIN, подзапросов, функций, сортировки и работы с таблицами. Всё объясняется простым языком с примерами запросов и постепенным усложнением тем, поэтому материал подойдёт как новичкам, так и тем, кто хочет систематизировать знания по бд.

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

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

Почему CHECK constraint может пропустить неправильные данные из-за NULL!

Редкая, но неприятная ловушка SQL: многие думают, что CHECK constraint требует, чтобы условие всегда было TRUE. Но это не так, CHECK запрещает только FALSE. А результат UNKNOWN — пропускается.

Именно поэтому nullable-колонки внутри CHECK могут вести себя не так, как ожидает разработчик.

Допустим, есть таблица товаров:

products(
    id,
    price,
    discount
)

Хотим запретить скидку больше цены:

ALTER TABLE products
ADD CONSTRAINT chk_discount_price
CHECK (discount <= price);

На первый взгляд всё выглядит правильно.

Теперь такой INSERT действительно не пройдёт:

INSERT INTO products(id, price, discount)
VALUES (1, 100, 150);

Потому что проверка: 150 <= 100 даёт FALSE. А CHECK constraint запрещает строки, где выражение возвращает FALSE. Но дальше начинается важный нюанс SQL и трёхзначной логики.

Вот такой INSERT уже может пройти:

INSERT INTO products(id, price, discount)
VALUES (2, 100, NULL);

И такой тоже:

INSERT INTO products(id, price, discount)
VALUES (3, NULL, 50);

Многие ожидают, что CHECK отклонит такие строки. Но SQL работает иначе, если в сравнении участвует NULL, результатом становится не TRUE и не FALSE, а: UNKNOWN

То есть:

150 <= 100     -- FALSE
NULL <= 100    -- UNKNOWN
50 <= NULL     -- UNKNOWN
NULL <= NULL   -- UNKNOWN

И вот здесь самая важная мысль: CHECK constraint считает строку валидной, если результат выражения — TRUE или UNKNOWN. Запрещается только явно FALSE.

Это поведение связано с SQL three-valued logic — логикой с тремя состояниями: TRUE, FALSE и UNKNOWN. Именно поэтому CHECK сам по себе НЕ заменяет NOT NULL. Если колонка обязательная — это нужно указывать отдельно.

Правильный вариант:

CREATE TABLE products(
    id        bigint PRIMARY KEY,
    price     numeric NOT NULL,
    discount  numeric NOT NULL,

    CONSTRAINT chk_discount_price
        CHECK (discount <= price)
);

Теперь NULL уже не сможет пройти, потому что NOT NULL сработает раньше CHECK. Но в реальных системах скидка часто может отсутствовать. То есть NULL — это нормальное состояние: скидки нет.

В таком случае constraint лучше писать явно и читаемо:

ALTER TABLE products
ADD CONSTRAINT chk_discount_price
CHECK (
    discount IS NULL
    OR discount <= price
);

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

Если price остаётся nullable: price numeric, то выражение: discount <= price снова может вернуть UNKNOWN. Например:

discount = 50
price = NULL

Результат проверки: 50 <= NULL, будет UNKNOWN, а строка снова станет валидной.

Поэтому если цена обязательна — нужен отдельный NOT NULL:

price numeric NOT NULL

Похожая ситуация встречается с датами. Например:

CHECK (end_date >= start_date)

Разработчик может думать, что constraint гарантирует корректный диапазон дат. Но если end_date nullable, такой CHECK спокойно пропускает:

end_date = NULL

потому что результат сравнения снова UNKNOWN.

И это может быть абсолютно нормальным поведением. Например, если NULL означает: период ещё не завершён. Но если обе даты обязательны, это нужно фиксировать явно:

start_date date NOT NULL,
end_date   date NOT NULL,

CHECK (end_date >= start_date)

🔥 Вывод: если в CHECK участвуют nullable-поля, constraint может пропускать строки из-за UNKNOWN, CHECK не заменяет NOT NULL. Для обязательных значений всегда нужен отдельный NOT NULL constraint.

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

🐱 SQL, MySQL и PostgreSQL — один из лучших материалов для изучения баз данных!

Это обучающий материал с теорией, объяснениями и практическими задачами. Здесь разбираются устройство баз данных, связи между таблицами, индексы, нормализация, проектирование схем и многое др. Большой акцент сделан на понимании того, как правильно проектировать бд и решать задачи, которые часто встречаются на собеседованиях и в работе.

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

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

📂 Напоминалка по проектированию систем!

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

На картинке — 8 распространённых проблем проектирования систем и практические способы их решения: кэширование, балансировка нагрузки, репликация, шардинг, централизованное логирование и другие базовые архитектурные паттерны.

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

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

PostgreSQL умеет замораживать часть запроса и запрещать оптимизатору его разворачивать!

Начиная с PostgreSQL 12 оптимизатор получил право разворачивать CTE прямо внутрь основного запроса.

WITH data AS (
  SELECT *
  FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;

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

Например, если внутри CTE дорогой расчёт, который нельзя выполнять повторно. Тут появляется малоизвестная фича:

WITH expensive AS MATERIALIZED (
  SELECT *
  FROM huge_events
  WHERE created_at >= now() - interval '1 day'
)
SELECT COUNT(*)
FROM expensive;

MATERIALIZED заставляет PostgreSQL сначала физически вычислить CTE, а потом использовать результат дальше.

А обратная фича:

WITH data AS NOT MATERIALIZED (
  SELECT *
  FROM orders
)
SELECT *
FROM data
WHERE user_id = 42;

наоборот подсказывает оптимизатору агрессивно встраивать CTE обратно в запрос.

🔥 Одна и та же CTE с MATERIALIZED и без него иногда отличается по производительности в десятки раз на больших объёмах данных.

➡️ SQL Ready | #совет

🧐 PostgreSQL в Selectel — статьи, гайды и практические кейсы по PostgreSQL!

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

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

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

N+1 проблема в SQL: почему приложение внезапно начинает делать тысячи запросов!

Одна из самых частых проблем backend-приложений — N+1 queries. Особенно часто это появляется при работе через ORM, потому что код выглядит нормально, а реальные SQL-запросы скрыты внутри слоя абстракции.

Например, есть таблицы:

users(id, name)
orders(id, user_id, amount)

Сначала приложение получает пользователей:

SELECT
    id,
    name
FROM users;

Допустим, запрос вернул 1000 пользователей. Дальше приложение начинает отдельно загружать заказы для каждого пользователя:

SELECT
    id,
    user_id,
    amount
FROM orders
WHERE user_id = ?;

И этот запрос выполняется уже 1000 раз. То есть итоговая схема выглядит так: 1 запрос на получение users, N запросов на получение orders. Это и есть классическая N+1 problem.

В ORM это обычно выглядит примерно так:

users = User.objects.all()

for user in users:
    orders = list(user.order_set.all())
    print(orders)

Внешне код выглядит абсолютно нормально, но внутри ORM может выполнять отдельный SELECT для каждого user.order_set.all().

На маленьких объемах данных проблема почти незаметна. Но на продакшене начинают быстро расти: latency, network overhead, нагрузка на connection pool, время ответа API, нагрузка на БД.

Особенно неприятно это проявляется при pagination, background jobs и high-load API. Обычно данные эффективнее загружать набором.

Например, через JOIN:

SELECT
    u.id,
    u.name,
    o.id,
    o.amount
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id;

Либо через batch loading:

SELECT
    id,
    user_id,
    amount
FROM orders
WHERE user_id IN (?, ?, ?, ...);

Во многих случаях batch loading даже эффективнее огромного JOIN, потому что JOIN может раздувать result set и создавать большое количество дублирующихся строк при one-to-many связях.

Поэтому современные ORM обычно уже имеют встроенные механизмы борьбы с N+1.

Например, в Django:

User.objects.prefetch_related("order_set")
User.objects.select_related("profile")

prefetch_related() обычно используется для reverse FK и many-to-many; select_related() — для FK и OneToOne.

В SQLAlchemy:

select(User).options(
    selectinload(User.orders)
)

Отдельная проблема — nested N+1:

users
→ orders
→ payments
→ items

И приложение внезапно начинает выполнять уже сотни, тысячи или даже десятки тысяч SQL-запросов. Самое опасное — проблема часто долго остается незаметной, пока объем данных не вырастает.

🔥 Если внутри цикла потенциально выполняется SQL-запрос — почти всегда стоит проверить код на N+1. Именно поэтому profiling SQL-запросов и понимание того, как ORM реально работает с базой, критично для продакшн backend-разработки.

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

💅 Структурированный справочник по MySQL!

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

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

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

📂 Напоминалка по MongoDB и шардированию!

Например, Query Router (mongos) принимает запросы от приложения и распределяет их по нужным шардам, а Replica Set внутри каждого shard обеспечивает отказоустойчивость и репликацию данных.

На картинке — базовая архитектура MongoDB Cluster: Client Application, Driver, Query Router, Config Server и Shards с Primary/Secondary-нодами.

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

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

🐱 SQL Window Functions — информативный гайд по оконным функциям в SQL!

Отличный ресурс для тех, кто хочет разобраться в SQL и освоить оконные функции. На сайте подробно объясняются инструменты для аналитики и сложной обработки данных. Всё сопровождается наглядными примерами и практическими кейсами.

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

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

Транзакционная блокировка без блокировки строк!

Иногда нужно запретить параллельную обработку одного объекта, но подходящей строки для FOR UPDATE ещё может не быть.

SELECT pg_advisory_xact_lock(10, 42);

pg_advisory_xact_lock создаёт транзакционную пользовательскую блокировку. Первый аргумент удобно использовать как namespace, второй — как id объекта.

SELECT pg_advisory_xact_lock(20, user_id);

Пока транзакция не завершится, другой процесс с тем же ключом будет ждать. После COMMIT или ROLLBACK блокировка снимается автоматически.

SELECT pg_try_advisory_xact_lock(20, user_id);

Если ждать нельзя, используй pg_try_advisory_xact_lock: он сразу вернёт true или false, и приложение сможет аккуратно пропустить задачу.

BEGIN;
SELECT pg_advisory_xact_lock(30, 123);
UPDATE invoices SET status = 'paid' WHERE id = 123;
COMMIT;

advisory lock не блокирует таблицу и не заменяет ограничения БД. Это кооперативная блокировка, поэтому все участники должны использовать один и тот же ключ.

🔥 Полезно для идемпотентных операций, генерации документов, биллинга, обработки вебхуков и любых мест, где один и тот же объект нельзя обрабатывать параллельно.

➡️ SQL Ready | #совет

👍 PostgreSQL Performance Essentials — практическое руководство по оптимизации PostgreSQL!

Этот репозиторий особенно полезен тем, кто уже работает с PostgreSQL и хочет больше разобраться в производительности базы данных. Здесь хорошо показано, как анализировать запросы, понимать execution plan и находить узкие места, которые замедляют работу приложения.

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

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

Lost Update: почему транзакций недостаточно без правильной модели конкурентного доступа!

Очень распространённое заблуждение: если запросы выполняются внутри транзакции — значит данные уже защищены от гонок, но это не так.

Одна из классических проблем конкурентного доступа — lost update. Например, есть таблица:

accounts(id, balance)

Текущий баланс:

id | balance
1  | 1000

Два запроса одновременно читают баланс:

SELECT balance
FROM accounts
WHERE id = 1;

Оба получают: 1000. Дальше: первый процесс хочет списать 100; второй — 200. Первый считает: 1000 - 100 = 900. Второй: 1000 - 200 = 800.

После этого выполняются:

UPDATE accounts
SET balance = 900
WHERE id = 1;

а также:

UPDATE accounts
SET balance = 800
WHERE id = 1;

В итоге финальный баланс: 800, хотя математически должен быть: 700, одно обновление потерялось. Это и есть lost update.

И самое неприятное — такое может происходить даже внутри транзакций. Всё зависит от уровня изоляции, паттерна обновления и механики блокировок конкретной СУБД.

Очень частая ошибка выглядит так:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1;

-- вычисления в приложении

UPDATE accounts
SET balance = :new_balance
WHERE id = 1;

COMMIT;

Проблема в том, что между SELECT и UPDATE другая транзакция может изменить строку.

Один из самых надёжных вариантов — атомарное обновление:

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

Теперь вычисление происходит внутри самого UPDATE.

СУБД выполняет обновление на основе актуального значения строки и использует необходимые механизмы блокировок для корректной синхронизации конкурентных изменений. Это намного безопаснее.

Ещё один вариант — pessimistic locking:

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

FOR UPDATE ставит row-level lock. Пока транзакция не завершится, другая транзакция не сможет изменить эту строку или получить несовместимую блокировку на неё.

Но здесь есть trade-off, чем больше блокировок: тем выше contention; тем ниже concurrency; тем выше риск deadlock.

Ещё один подход — optimistic locking. Например:

accounts(id, balance, version)

Чтение:

SELECT balance, version
FROM accounts
WHERE id = 1;

Обновление:

UPDATE accounts
SET
  balance = :new_balance,
  version = version + 1
WHERE
  id = 1
  AND version = 5;

Если другая транзакция уже изменила строку — UPDATE затронет 0 строк. Приложение понимает: данные устарели, нужно перечитать и повторить операцию. Ещё важно понимать: READ COMMITTED, REPEATABLE READ, SERIALIZABLE ведут себя по-разному в разных СУБД.

Например, PostgreSQL и MySQL (InnoDB) используют разные механизмы MVCC и имеют различия в поведении блокировок и уровней изоляции.

🔥 Главное правило, если логика выглядит как: прочитал значение — изменил в приложении — записал обратно, то всегда стоит проверять, не появляется ли lost update при параллельных запросах.

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