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

Например, кэширование помогает ускорить чтение и снизить нагрузку на БД, а 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 | #практика