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

👍 Годная статья вышла на Хабре: «Шардированный не значит распределённый, что важно знать, когда PostgreSQL становится мало»!

В этой статье:
• Объясняется разница между шардированным PostgreSQL и настоящими распределёнными СУБД;
• Разбирается, почему Citus и похожие решения не дают полноценные ACID-гарантии для multi-shard транзакций;
• Показывается, как работают 2PC, distributed snapshot и уровни изоляции транзакций;
• На примерах объясняются проблемы consistency и eventual consistency в распределённых системах;

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

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

DEFERRABLE constraints — проверяй связи в конце транзакции, а не после каждой строки!

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

BEGIN;

INSERT INTO payments (id, order_id)
VALUES (1, 100);

INSERT INTO orders (id)
VALUES (100);

COMMIT;

Обычный FOREIGN KEY проверяется сразу, поэтому первый INSERT упадёт, если заказа 100 ещё нет.

DEFERRABLE INITIALLY DEFERRED переносит проверку ограничения на момент COMMIT, поэтому база смотрит не на промежуточное состояние, а на итог транзакции.

SET CONSTRAINTS ALL IMMEDIATE;

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

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

🔥 Фишка в том, что ты не отключаешь целостность данных, а просто переносишь момент проверки.

➡️ SQL Ready | #совет

🐱 PostgreSQL Cheat Sheet — удобная шпаргалка по PostgreSQL!

Этот репозиторий собрал самые важные команды и конструкции PostgreSQL в одном месте. Здесь можно быстро вспомнить синтаксис запросов, работу с таблицами, индексами, представлениями, функциями, транзакциями и др. Такая шпаргалка особенно полезна во время работы или подготовки к собеседованию.

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

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

В PostgreSQL уникальность можно проверять не для всей таблицы, а только для части строк!

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

SELECT email, deleted_at
FROM users;

После удаления запись остаётся в таблице, но появляется дата удаления.

Обычный уникальный индекс создаст проблему:

CREATE UNIQUE INDEX users_email_uniq
ON users (email);

Теперь пользователь не сможет зарегистрироваться повторно с тем же email даже после удаления старого аккаунта.

Решение — частичный уникальный индекс:

CREATE UNIQUE INDEX users_active_email_uniq
ON users (email)
WHERE deleted_at IS NULL;

Теперь PostgreSQL будет проверять уникальность только среди активных пользователей.

Удалённые записи перестают участвовать в проверке.

Точно так же можно хранить:

CREATE UNIQUE INDEX one_draft_per_user
ON posts (user_id)
WHERE status = 'draft';

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

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

➡️ SQL Ready | #совет

📂 Напоминалка по SQL Roadmap!

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

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

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

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

Почему оконные функции могут давать неверный результат из-за RANGE вместо ROWS!

Одна из самых неприятных особенностей window functions — frame по умолчанию. Запрос выглядит корректно, проходит проверку глазами, но накопительная сумма может считаться совсем не так, как ожидает разработчик.

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

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

payments(id, created_at, amount)

Допустим, нужно получить обычный running total — сумму всех предыдущих платежей до текущей строки.

Запрос выглядит очевидно:

SELECT
    id,
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at
    ) AS running_total
FROM payments;

Логика кажется простой: первая строка берёт свой amount, вторая прибавляет своё значение к предыдущей сумме, третья делает то же самое. То есть ожидается: 100 — 300 — 600. Но у оконных функций есть скрытая настройка — frame.

Если после ORDER BY не указать его явно, SQL использует значение по умолчанию:

RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW

И именно здесь появляется разница между ожиданием и реальным результатом. RANGE работает не по физическому положению строк, а по значениям сортировки. То есть база смотрит на поле из ORDER BY и объединяет строки с одинаковым значением в одну логическую группу.

Например:

id=1, created_at='2026-01-01 10:00:00', amount=100
id=2, created_at='2026-01-01 10:00:00', amount=200
id=3, created_at='2026-01-01 11:00:00', amount=300

Здесь первые две записи имеют одинаковое время. Для человека это две разные операции. Но для RANGE они являются одной группой, потому что значение сортировки совпадает.

При таком запросе:

SUM(amount) OVER (
    ORDER BY created_at
)

результат будет:

300
300
600

Почему? Потому что при расчёте первой строки RANGE уже включает обе записи с одинаковым created_at. Получается: 100 + 200 = 300. Поэтому первая строка сразу показывает итог двух операций. Это не ошибка SQL, это стандартное поведение RANGE.

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

Он не смотрит, одинаковые ли значения сортировки у соседних строк. Для него важен порядок строк после сортировки. Например:

SELECT
    id,
    created_at,
    amount,
    SUM(amount) OVER (
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS running_total
FROM payments;

Теперь база получает явную инструкцию: сначала отсортировать записи, затем считать накопление строка за строкой.

Результат становится ожидаемым:

100
300
600

Ещё один важный момент — сама сортировка. Даже если используется ROWS, одного created_at может быть недостаточно.

Если несколько событий произошли в одну секунду, SQL не обязан выбирать между ними определённый порядок. Например:

id=1, created_at='10:00:00', amount=100
id=2, created_at='10:00:00', amount=200

Какая операция должна идти первой? Без дополнительного поля ответа нет. Поэтому обычно добавляют уникальный идентификатор:

ORDER BY created_at, id

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

RANGE удобен, когда нужно работать с группами одинаковых значений сортировки. ROWS нужен, когда важен порядок конкретных строк. Для обычного running total почти всегда стоит явно указывать ROWS.

🔥 Вывод: если cumulative sum показывает одинаковые значения на нескольких строках подряд — проверьте ORDER BY и frame окна. Чаще всего причина в том, что ожидали ROWS, а получили поведение RANGE.

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

Разбираем почему ORDER BY RANDOM() на больших таблицах не лучшая идея!

Если нужно вытащить случайные строки из таблицы, многие пишут так, например в PostgreSQL:

SELECT *
FROM products
ORDER BY RANDOM()
LIMIT 10;

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

То есть даже если нужна всего одна строка:

SELECT *
FROM products
ORDER BY RANDOM()
LIMIT 1;

на большой таблице это всё равно может оказаться тяжёлым запросом. Если строк миллионы, начинаются проблемы с CPU, памятью, сортировками и latency. Индексы здесь обычно не помогают.

Один из более дешёвых вариантов — случайный выбор через id:

SELECT *
FROM products
WHERE id >= 1 + FLOOR(RANDOM() * (SELECT MAX(id) FROM products))::int
ORDER BY id
LIMIT 1;

Этот вариант уже может использовать индекс по id. Но есть нюанс: если после удалений в id много дырок, распределение будет неидеальным.

Например:

id
1
2
3
10000
10001

В таком случае чаще будет выпадать первая существующая строка после большого разрыва — здесь это 10000. То есть выборка получается смещённой.

Ещё один вариант — случайный OFFSET:

SELECT *
FROM products
OFFSET FLOOR(RANDOM() * (SELECT COUNT(*) FROM products))::int
LIMIT 1;

Звучит неплохо. Но у OFFSET тоже есть проблема: чем больше offset, тем больше строк базе придётся пропустить. Плюс в PostgreSQL COUNT(*) на большой таблице тоже может быть дорогой операцией.

В PostgreSQL есть ещё TABLESAMPLE:

SELECT *
FROM products TABLESAMPLE SYSTEM (1);

Или:

SELECT *
FROM products TABLESAMPLE BERNOULLI (1);

На больших таблицах это часто работает заметно быстрее. Но важно понимать: TABLESAMPLE выбирает примерный процент таблицы, а не конкретное количество строк.

Если нужно, например, 10 строк, обычно добавляют LIMIT:

SELECT *
FROM products TABLESAMPLE SYSTEM (1)
LIMIT 10;

Но и тут есть нюанс: если sample слишком маленький, запрос может вернуть меньше 10 строк.

И здесь тоже есть компромисс между скоростью и качеством случайной выборки. SYSTEM работает быстрее, но выбирает данные блоками, поэтому распределение менее равномерное. BERNOULLI ближе к построчной случайной выборке, но обычно дороже. В общем, мысль простая:

ORDER BY RANDOM()

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

🔥 Именно такие простые запросы часто становятся причиной деградации производительности в продакшн.

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

Разбор JSON в строки на стороне PostgreSQL!

Когда из API прилетает массив объектов, не обязательно разбирать его в коде и делать много отдельных INSERT или UPDATE. PostgreSQL умеет превратить JSON-массив в обычную табличную выборку.

SELECT *
FROM jsonb_to_recordset($1::jsonb)
AS x(id bigint, amount numeric);

На вход можно передать JSON вида [{"id":1,"amount":500},{"id":2,"amount":900}], а на выходе получить нормальные типизированные строки.

INSERT INTO payments (id, amount)
SELECT id, amount
FROM jsonb_to_recordset($1::jsonb)
AS x(id bigint, amount numeric);

Это особенно удобно для пакетных загрузок, импорта данных, обработки вебхуков и синхронизации с внешними сервисами.

UPDATE payments p
SET amount = x.amount
FROM jsonb_to_recordset($1::jsonb)
AS x(id bigint, amount numeric)
WHERE p.id = x.id;

Главный плюс в том, что приложение передаёт один JSON-параметр, а вся пакетная обработка происходит внутри базы одним запросом.

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

➡️ SQL Ready | #совет

👍 ClickHouse + PostgreSQL — интеграция OLTP и OLAP для аналитики!

На странице собрана официальная документация ClickHouse по работе с PostgreSQL. Здесь подробно разбираются импорт данных, репликация таблиц, синхронизация и выполнение запросов к PostgreSQL напрямую из ClickHouse. Также есть примеры настройки и практические сценарии использования.

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

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

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

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

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

➡️ 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 | #совет

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

➡️ 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 | #совет

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

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

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

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

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

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

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

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

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