100 вопросов по SQL, PostgreSQL и базам данных

Короткий ответ: SELECT извлекает строки из таблиц. Логический порядок выполнения НЕ совпадает с порядком написания: сначала FROM, затем WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

Подробно:

Порядок написания (синтаксический):

SELECT   DISTINCT col1, agg(col2)
FROM     t
WHERE    cond
GROUP BY col1
HAVING   agg_cond
ORDER BY col1
LIMIT    10 OFFSET 20;

Логический порядок исполнения:

1. FROM / JOIN      -- какие таблицы, как соединить
2. WHERE            -- фильтр строк ДО группировки
3. GROUP BY         -- группировка
4. HAVING           -- фильтр групп ПОСЛЕ агрегации
5. SELECT           -- вычисление выражений, алиасов
6. DISTINCT         -- удаление дублей
7. ORDER BY         -- сортировка
8. LIMIT / OFFSET   -- срез

⚠️ Ловушка: алиас из SELECT нельзя использовать в WHERE (т.к. WHERE выполняется раньше SELECT), но можно в ORDER BY и часто в GROUP BY (зависит от СУБД). Пример ошибки:

SELECT salary * 12 AS annual FROM emp WHERE annual > 100000; -- ОШИБКА
SELECT salary * 12 AS annual FROM emp ORDER BY annual;        -- ОК

Короткий ответ: WHERE фильтрует строки, ORDER BY сортирует результат, LIMIT n OFFSET m возвращает n строк, пропустив первые m.

Подробно:

-- Пагинация: страница 3 по 20 записей (пропустить 40, взять 20)
SELECT id, name
FROM users
WHERE active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

⚠️ Ловушка: LIMIT без ORDER BY возвращает НЕдетерминированный набор строк — порядок не гарантирован. Также OFFSET на больших значениях медленный (СУБД всё равно читает и отбрасывает пропущенные строки) — для глубокой пагинации используйте keyset-пагинацию:

-- keyset: быстрее, чем большой OFFSET
SELECT * FROM users WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Короткий ответ: DISTINCT убирает дублирующиеся строки из результата.

Подробно:

SELECT DISTINCT department FROM employees;        -- уникальные отделы
SELECT DISTINCT department, city FROM employees;  -- уникальные ПАРЫ (отдел, город)

⚠️ Ловушка: DISTINCT применяется ко ВСЕМ столбцам в SELECT, а не к одному. SELECT DISTINCT a, b ≠ «уникальные a». Также COUNT(DISTINCT col) считает уникальные значения, игnorируя NULL.

Для выполнения СУБД обычно сортирует результат или строит hash-set, поэтому DISTINCT может требовать много памяти и spill на диск. Не используйте его как пластырь после неверного JOIN: сначала поймите, почему строки размножились. Если нужна одна строка на группу по правилу выбора, применяйте ROW_NUMBER() или PostgreSQL DISTINCT ON (...) с явным ORDER BY.

Короткий ответ: INNER — только совпадения; LEFT — все левые + совпадения справа (иначе NULL); RIGHT — зеркало LEFT; FULL — всё с обеих сторон; CROSS — декартово произведение; SELF — таблица с самой собой.

Подробно:

Пусть есть:

employees           departments
+----+--------+----+   +----+----------+
| id | name   |dept|   | id | title    |
+----+--------+----+   +----+----------+
| 1  | Анна   | 10 |   | 10 | IT       |
| 2  | Борис  | 20 |   | 30 | Finance  |
| 3  | Вера   | NULL|  +----+----------+
+----+--------+----+

INNER JOIN — пересечение (∩). Только строки, где есть совпадение в ОБЕИХ таблицах:

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept = d.id;
-- Анна | IT      (Борис отпал: dept 20 нет в departments;
--                 Вера отпала: dept = NULL)

employees    departments
   [ ##### ]              ← только пересечение

LEFT JOIN — все строки левой таблицы + совпадения справа (нет совпадения → NULL):

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept = d.id;
-- Анна  | IT
-- Борис | NULL   ← сохранён, departments нет
-- Вера  | NULL   ← сохранён, dept = NULL

[ left ##### ]   ← вся левая + пересечение

RIGHT JOIN — зеркало LEFT (все строки правой):

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept = d.id;
-- Анна | IT
-- NULL | Finance  ← департамент 30 без сотрудников

FULL OUTER JOIN — все строки обеих таблиц:

SELECT e.name, d.title
FROM employees e
FULL OUTER JOIN departments d ON e.dept = d.id;
-- Анна  | IT
-- Борис | NULL
-- Вера  | NULL
-- NULL  | Finance

CROSS JOIN — декартово произведение (каждая с каждой), без условия:

SELECT e.name, d.title FROM employees e CROSS JOIN departments d;
-- 3 строки × 2 строки = 6 строк

SELF JOIN — таблица к самой себе (например, сотрудник → менеджер):

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

💡 Чем LEFT отличается от INNER на примере: если справа нет совпадения, INNER ВЫБРАСЫВАЕТ строку, а LEFT ОСТАВЛЯЕТ её, подставляя NULL в правые столбцы. Поэтому LEFT JOIN часто используют для поиска «осиротевших» записей:

-- Сотрудники без отдела
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.id IS NULL;

⚠️ Ловушка: условие на правую таблицу в LEFT JOIN нужно класть в ON, а не в WHERE, иначе LEFT превращается в INNER:

-- НЕПРАВИЛЬНО: фильтр в WHERE отсекает строки с NULL справа → ведёт себя как INNER
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.title = 'IT';

-- ПРАВИЛЬНО: если нужно сохранить все строки слева
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id AND d.title = 'IT';

Короткий ответ: если ключ соединения не уникален с одной из сторон, строки размножаются (одна слева × N справа).

Подробно:

-- Если у заказа несколько позиций, COUNT(*) посчитает позиции, а не заказы
SELECT o.id, COUNT(*) FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

⚠️ Ловушка: суммирование после JOIN с дублированием даёт завышенные суммы. Если соединяете несколько «один-ко-многим» таблиц, агрегируйте их в подзапросах ДО соединения.

Короткий ответ: GROUP BY группирует строки по значениям столбцов; агрегаты (COUNT, SUM, AVG, MIN, MAX) считают одно значение на группу.

Подробно:

SELECT department,
       COUNT(*)        AS headcount,
       SUM(salary)     AS payroll,
       AVG(salary)     AS avg_salary,
       MIN(salary)     AS min_salary,
       MAX(salary)     AS max_salary
FROM employees
GROUP BY department;

⚠️ Ловушка: в стандартном SQL в SELECT можно использовать только столбцы из GROUP BY или агрегаты. SELECT name, dept, COUNT(*) ... GROUP BY dept — ошибка (name не сгруппирован и не агрегирован). PostgreSQL это запрещает, MySQL (в нестрогом режиме) молча вернёт произвольное name.

Короткий ответ: WHERE фильтрует СТРОКИ до группировки (не может содержать агрегаты), HAVING фильтрует ГРУППЫ после агрегации (может).

Подробно:

-- Отделы, где средняя зарплата активных сотрудников > 100000
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = TRUE          -- фильтр строк ДО группировки
GROUP BY department
HAVING AVG(salary) > 100000; -- фильтр групп ПОСЛЕ агрегации

Правило: условие на отдельные строки → WHERE; условие на результат агрегата → HAVING. По производительности WHERE предпочтительнее (меньше строк попадает в группировку).

⚠️ Ловушка: нельзя писать WHERE AVG(salary) > 100000 — агрегаты в WHERE запрещены. И наоборот, не загоняйте простые фильтры строк в HAVING — это медленнее.

Короткий ответ: COUNT(*) — все строки; COUNT(col) — строки, где col НЕ NULL; COUNT(DISTINCT col) — уникальные не-NULL значения.

Подробно:

-- Таблица: 5 строк, в phone 2 NULL, 1 дубликат
SELECT
  COUNT(*)               AS total,        -- 5
  COUNT(phone)           AS with_phone,   -- 3 (NULL не считаются)
  COUNT(DISTINCT phone)  AS unique_phones -- 2
FROM users;

⚠️ Ловушка: COUNT(col) тихо игнорирует NULL — частая причина расхождения в отчётах. Если нужно посчитать все строки — всегда COUNT(*).

Короткий ответ: подзапрос — это SELECT внутри другого запроса. Бывают скалярные (1 значение), строчные/табличные, в WHERE/FROM/SELECT.

Подробно:

-- Скалярный (одно значение) в WHERE
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- В IN (список значений)
SELECT name FROM employees
WHERE dept IN (SELECT id FROM departments WHERE title = 'IT');

-- В FROM (производная таблица, derived table)
SELECT dept, avg_sal FROM (
  SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
) t WHERE avg_sal > 100000;

⚠️ Ловушка: NOT IN с подзапросом, который может вернуть NULL, даёт ПУСТОЙ результат (из-за трёхзначной логики). Используйте NOT EXISTS вместо NOT IN.

Короткий ответ: подзапрос, который ссылается на столбцы внешнего запроса и выполняется для КАЖДОЙ строки внешнего запроса.

Подробно:

-- Сотрудники с зарплатой выше средней ПО ИХ отделу
SELECT e.name, e.salary, e.dept
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept = e.dept   -- ссылка на внешний e.dept → корреляция
);

-- EXISTS — классический коррелированный
SELECT d.title FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept = d.id);

⚠️ Ловушка: коррелированный подзапрос концептуально выполняется построчно → может быть медленным на больших таблицах. Часто переписывается через JOIN или оконную функцию. Оптимизаторы иногда умеют его «распрямлять», но не всегда.

Короткий ответ: CTE (Common Table Expression) — именованный временный результат запроса, объявленный через WITH, улучшает читаемость и позволяет переиспользовать подзапрос.

Подробно:

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.salary, da.avg_sal
FROM employees e
JOIN dept_avg da ON da.dept = e.dept
WHERE e.salary > da.avg_sal;

Преимущества: читаемость, можно сослаться несколько раз, можно строить цепочки WITH a AS (...), b AS (...).

⚠️ Ловушка: в старых PostgreSQL (<12) CTE были «оптимизационным барьером» (материализовались всегда). С PG 12+ они инлайнятся, если не указан MATERIALIZED. CTE сам по себе НЕ ускоряет — это в первую очередь читаемость.

Короткий ответ: WITH RECURSIVE позволяет обходить иерархии и графы (дерево категорий, оргструктуру, связи) итеративно.

Подробно: структура: якорная часть (anchor) UNION ALL рекурсивная часть.

-- Вся ветка подчинённых менеджера с id = 1
WITH RECURSIVE subordinates AS (
  -- якорь: сам менеджер
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE id = 1

  UNION ALL

  -- рекурсия: те, кто подчиняется уже найденным
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level;

⚠️ Ловушка: при циклах в данных (A→B→A) рекурсия зациклится. Защита: ограничивайте глубину (WHERE level < 100) или ведите массив посещённых узлов и проверяйте через NOT = ANY(path).

Короткий ответ: оконные функции считают агрегат/ранг «по окну» строк, НЕ схлопывая строки — каждая строка остаётся в выводе, но получает значение по своей группе.

Подробно: func() OVER (PARTITION BY ... ORDER BY ...).

-- Зарплата + средняя по отделу В ТОЙ ЖЕ строке (без потери строк)
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees;

GROUP BY вернул бы по одной строке на отдел; оконная функция сохраняет все строки.

⚠️ Ловушка: оконные функции выполняются ПОСЛЕ WHERE/GROUP BY/HAVING, но ДО ORDER BY всего запроса. Нельзя фильтровать по оконной функции в WHERE — оберните в подзапрос/CTE.

Короткий ответ: ROW_NUMBER — уникальные номера 1,2,3...; RANK — при равенстве одинаковый ранг и пропуск (1,1,3); DENSE_RANK — одинаковый ранг без пропуска (1,1,2).

Подробно:

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

salary | rn | rnk | dense
 500   | 1  | 1   | 1
 500   | 2  | 1   | 1
 400   | 3  | 3   | 2     ← RANK пропустил 2, DENSE_RANK — нет
 300   | 4  | 4   | 3

⚠️ Ловушка: ROW_NUMBER при равных значениях даёт НЕдетерминированный порядок среди равных — добавляйте tiebreaker в ORDER BY (например, ORDER BY salary DESC, id).

Короткий ответ: LAG берёт значение из предыдущей строки окна, LEAD — из следующей. Удобно для расчёта дельт «месяц к месяцу».

Подробно:

-- Прирост выручки относительно предыдущего месяца
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly_sales;

LAG(col, n, default) — n строк назад, со значением по умолчанию вместо NULL.

⚠️ Ловушка: для первой строки LAG вернёт NULL (нет предыдущей) → дельта станет NULL. Используйте третий аргумент-дефолт или COALESCE.

Короткий ответ: UNION объединяет наборы и УДАЛЯЕТ дубли (делает дедупликацию = медленнее), UNION ALL просто склеивает, оставляя дубли (быстрее).

Подробно:

SELECT name FROM customers
UNION          -- уникальные имена
SELECT name FROM suppliers;

SELECT name FROM customers
UNION ALL      -- все, включая дубли (быстрее)
SELECT name FROM suppliers;

Требования: одинаковое число столбцов и совместимые типы.

⚠️ Ловушка: если дубли невозможны или не важны — используйте UNION ALL: UNION тратит ресурсы на сортировку/хеширование для дедупликации. Также ORDER BY относится ко всему результату и пишется один раз в конце.

Короткий ответ: нормализация — процесс проектирования схемы для устранения избыточности и аномалий вставки/обновления/удаления через разбиение на связанные таблицы.

Подробно: аномалии в денормализованной таблице:

• Аномалия обновления — повторяющиеся данные нужно менять во многих строках.
• Аномалия вставки — нельзя добавить факт без лишних данных.
• Аномалия удаления — удаление строки теряет несвязанный факт.

Например, если имя отдела хранится в каждой строке сотрудника, переименование требует обновить сотни строк, а удаление последнего сотрудника случайно удаляет сам факт существования отдела. Нормализованная схема выносит departments отдельно, а employees.department_id ссылается на неё внешним ключом.

Нормализация — не цель сама по себе: в OLTP обычно начинают с 3NF ради корректности, а затем осознанно денормализуют конкретные read-heavy пути. Цена денормализации — дополнительная логика синхронизации и риск расхождения копий.

Короткий ответ: 1NF — атомарные значения, нет повторяющихся групп; 2NF — 1NF + нет частичной зависимости от части составного ключа; 3NF — 2NF + нет транзитивных зависимостей; BCNF — строгая 3NF (каждая детерминанта — суперключ).

Подробно:

Исходная плохая таблица:

orders(order_id, product1, product2, customer_name, customer_city, city_zip)

1NF — атомарность. Нельзя хранить список в одной ячейке (product1, product2). Разбиваем повторяющиеся группы на строки:

CREATE TABLE order_items (
  order_id INT,
  product  VARCHAR,
  qty      INT,
  PRIMARY KEY (order_id, product)
);

2NF — нет частичной зависимости от ЧАСТИ составного ключа. Если PK = (order_id, product), а customer_name зависит только от order_id (части ключа), это нарушение. Выносим:

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT);
CREATE TABLE order_items (order_id INT, product VARCHAR, qty INT,
                          PRIMARY KEY (order_id, product));

3NF — нет транзитивных зависимостей (неключевой атрибут зависит от другого неключевого). customer_city зависит от customer_id, а city_zip от customer_city → транзитивно. Выносим:

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR, city_id INT);
CREATE TABLE cities    (city_id INT PRIMARY KEY, name VARCHAR, zip VARCHAR);

BCNF — каждая детерминанта является суперключом. Решает редкий случай 3NF, когда есть перекрывающиеся кандидатные ключи и неключевая детерминанта. Пример: таблица (student, course, teacher), где teacher → course (учитель ведёт ровно один курс), но teacher не ключ → BCNF нарушено; разбиваем на (student, teacher) и (teacher, course).

⚠️ Ловушка: «3NF достаточно для большинства» — да, но не путайте: 2NF имеет смысл только при СОСТАВНОМ ключе (при одностолбцовом PK частичных зависимостей быть не может).

Короткий ответ: денормализуют ради производительности чтения — дублируют данные, чтобы избежать дорогих JOIN, в аналитике/отчётах/кэшах.

Подробно: примеры обоснованной денормализации:

• Хранение order_total в orders, чтобы не суммировать order_items при каждом чтении.
• Звёздная схема в DWH (факты + денормализованные измерения).
• Колонка-кэш агрегата, обновляемая триггером/приложением.

⚠️ Ловушка: денормализация переносит ответственность за консистентность на приложение — появляется риск рассогласования. Денормализуйте осознанно, измерив, что нормализованная схема реально узкое место.

Короткий ответ: PRIMARY KEY — уникальный идентификатор строки (NOT NULL + UNIQUE, один на таблицу); UNIQUE — уникальность значений (может допускать NULL, их несколько); FOREIGN KEY — ссылка на PK/UNIQUE другой таблицы, обеспечивает ссылочную целостность.

Подробно:

CREATE TABLE departments (
  id    INT PRIMARY KEY,
  code  VARCHAR(10) UNIQUE          -- альтернативный уникальный ключ
);

CREATE TABLE employees (
  id      INT PRIMARY KEY,
  email   VARCHAR(255) UNIQUE,      -- может быть несколько UNIQUE
  dept_id INT REFERENCES departments(id)  -- FOREIGN KEY
);

⚠️ Ловушка: PK неявно создаёт уникальный индекс и NOT NULL. UNIQUE в большинстве СУБД допускает несколько NULL (NULL != NULL), а PK — нет.

Короткий ответ: ключ из нескольких столбцов; уникальность гарантируется их КОМБИНАЦИЕЙ.

Подробно:

CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  grade      INT,
  PRIMARY KEY (student_id, course_id)   -- пара уникальна
);

⚠️ Ловушка: порядок столбцов в составном ключе/индексе важен для использования индекса. Индекс (a, b) ускоряет фильтр по a и по (a, b), но НЕ по одному b.

Составной ключ естественен для таблиц связей: одна пара (student_id, course_id) должна существовать максимум один раз. Все внешние ключи на такую строку обязаны включать оба столбца. Если на запись часто ссылаются другие таблицы, иногда удобнее добавить surrogate id, но бизнес-уникальность пары всё равно закрепить отдельным UNIQUE (student_id, course_id) — surrogate key сам по себе дубли не запрещает.

Короткий ответ: natural — естественный бизнес-атрибут (ИНН, email); surrogate — искусственный (auto-increment id, UUID). Чаще берут surrogate.

Подробно:

Критерий	Natural	Surrogate
Источник	бизнес-данные	сгенерирован системой
Стабильность	может меняться (email сменили)	неизменен
Размер/скорость	бывает большой/строковый	компактный INT/BIGINT
Утечка смысла	да (PII в FK)	нет

⚠️ Ловушка: natural key может оказаться не таким уникальным/неизменным, как кажется (паспорт меняют, email переиспользуют). Surrogate безопаснее как PK, но natural-атрибут всё равно делайте UNIQUE-ограничением для целостности.

Короткий ответ: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT — правила, которые СУБД проверяет автоматически.

Подробно:

CREATE TABLE products (
  id     INT PRIMARY KEY,
  name   VARCHAR(255) NOT NULL,
  sku    VARCHAR(50)  UNIQUE,
  price  NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  status VARCHAR(20)  DEFAULT 'active'
                      CHECK (status IN ('active','archived')),
  cat_id INT REFERENCES categories(id)
);

⚠️ Ловушка: CHECK с NULL проходит (NULL не нарушает CHECK, т.к. условие даёт UNKNOWN, а не FALSE). Чтобы запретить NULL — нужен отдельный NOT NULL.

Короткий ответ: при удалении родительской строки CASCADE автоматически удаляет дочерние; есть также RESTRICT/NO ACTION (запретить), SET NULL, SET DEFAULT.

Подробно:

CREATE TABLE order_items (
  id       INT PRIMARY KEY,
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,  -- удалить позиции с заказом
  user_id  INT REFERENCES users(id)  ON DELETE SET NULL  -- обнулить ссылку
);

Опции FK:

• CASCADE — удалить/обновить дочерние.
• RESTRICT / NO ACTION — запретить удаление родителя, пока есть дети.
• SET NULL — поставить NULL в дочерней колонке (колонка должна допускать NULL).
• SET DEFAULT — поставить значение по умолчанию.

⚠️ Ловушка: CASCADE опасен — массовое удаление может незаметно снести гигантские поддеревья данных. Многие команды предпочитают RESTRICT + явное удаление в коде. Также каскады усложняют отладку и могут вызвать дедлоки при конкурентных удалениях.

Короткий ответ: ACID — гарантии надёжных транзакций: Atomicity (всё-или-ничего), Consistency (согласованность правил), Isolation (изоляция конкурентных транзакций), Durability (долговечность зафиксированного).

Подробно:

• Atomicity (атомарность). Транзакция выполняется целиком или не выполняется вовсе. Если на середине ошибка — ROLLBACK откатывает все изменения. Классика — перевод денег: списание и зачисление либо оба, либо никакой.

• Consistency (согласованность). Транзакция переводит БД из одного корректного состояния в другое, не нарушая constraints (FK, CHECK, UNIQUE). Это про целостность данных по объявленным правилам.

• Isolation (изоляция). Параллельные транзакции не видят промежуточные результаты друг друга так, будто выполняются последовательно (степень зависит от уровня изоляции).

• Durability (долговечность). После COMMIT данные переживут сбой/перезагрузку (за счёт журнала WAL/redo log, записанного на диск).

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- атомарно: оба апдейта или ни одного

⚠️ Ловушка: не путайте Consistency в ACID (соблюдение constraints внутри одной БД) с Consistency в CAP-теореме (согласованность реплик в распределённой системе) — это разные вещи.

Короткий ответ: транзакция — атомарная единица работы. BEGIN начинает, COMMIT фиксирует, ROLLBACK откатывает, SAVEPOINT ставит точку частичного отката.

Подробно:

BEGIN;
  INSERT INTO orders(id, customer_id) VALUES (1, 42);
  SAVEPOINT after_order;             -- точка возврата
  INSERT INTO order_items(order_id, product) VALUES (1, 'X');
  -- ошибка/передумали — откатываемся только до savepoint
  ROLLBACK TO SAVEPOINT after_order;
  INSERT INTO order_items(order_id, product) VALUES (1, 'Y');
COMMIT;

⚠️ Ловушка: «висящая» открытая транзакция держит блокировки и не даёт VACUUM очищать старые версии строк (в PostgreSQL — bloat). Всегда закрывайте транзакции. Autocommit-режим клиента фиксирует каждый statement отдельно — следите за настройкой.

Короткий ответ: dirty read (чтение незафиксированного), non-repeatable read (перечитал — другое значение), phantom read (перечитал — появились новые строки), lost update (затёртое обновление).

Подробно:

• Dirty read — T1 читает данные, которые T2 изменила, но ещё не закоммитила; T2 откатилась → T1 прочла «грязь».
• Non-repeatable read — T1 читает строку дважды, между чтениями T2 изменила и закоммитила → разные значения.
• Phantom read — T1 делает один и тот же запрос с условием дважды, между ними T2 ВСТАВИЛА подходящие строки → появились «фантомы».
• Lost update — T1 и T2 читают значение, оба прибавляют, кто записал последним — затёр изменение другого.

⚠️ Ловушка: lost update стандартом отдельно не классифицируется, но на практике критичен. Решается через SELECT ... FOR UPDATE, атомарное UPDATE ... SET x = x + 1 или optimistic locking с версией.

Короткий ответ: Read Uncommitted, Read Committed, Repeatable Read, Serializable — чем выше, тем меньше аномалий, но больше блокировок/откатов.

Подробно — таблица (по стандарту SQL):

Уровень изоляции	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	возможен	возможен	возможен
Read Committed	нет	возможен	возможен
Repeatable Read	нет	нет	возможен*
Serializable	нет	нет	нет

* По стандарту фантомы возможны на Repeatable Read, но в PostgreSQL уровень Repeatable Read реализован через snapshot-изоляцию и фантомы тоже исключает; lost update там вызывает ошибку сериализации.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  -- ...
COMMIT;

Значения по умолчанию: PostgreSQL и Oracle — Read Committed; MySQL/InnoDB — Repeatable Read.

⚠️ Ловушка: реализация отличается от стандарта. Например, в MySQL Repeatable Read через MVCC не видит вставки, но блокировки промежутков (gap locks) могут давать дедлоки. Serializable в PostgreSQL (SSI) может откатывать транзакции с ошибкой could not serialize access — приложение должно уметь ретраить.

Короткий ответ: ACID гарантирует надёжность отдельной транзакции; уровни изоляции — компромисс между корректностью при конкуренции и производительностью.

Подробно: строгий Serializable ведёт себя как последовательное выполнение (нет аномалий), но дорог (блокировки/откаты). Слабые уровни быстрее, но допускают аномалии. Вы выбираете минимальный уровень, на котором ваша логика остаётся корректной. Для денежных операций — высокий уровень или явные блокировки; для аналитики — Read Committed обычно достаточно.

⚠️ Ловушка: «поставлю Serializable везде» — резко падает throughput и растут дедлоки/ретраи. Изоляцию подбирают под конкретную операцию.

Короткий ответ: pessimistic — заранее блокируем строку (SELECT ... FOR UPDATE), считая конфликт вероятным; optimistic — не блокируем, а при записи проверяем, не изменилось ли (версия/timestamp), и при конфликте откатываем/ретраим.

Подробно:

Pessimistic:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- блокируем строку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Optimistic (через колонку версии):

-- читаем version = 5
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;     -- если 0 строк изменено → кто-то опередил → ретрай

	Pessimistic	Optimistic
Когда лучше	высокая конкуренция за строку	редкие конфликты
Цена	блокировки, риск дедлока	ретраи при конфликте
Механизм	FOR UPDATE, локи	version/timestamp

⚠️ Ловушка: FOR UPDATE держит блокировку до конца транзакции — длинные транзакции блокируют других. Optimistic требует, чтобы приложение умело ретраить неудавшийся апдейт.

Короткий ответ: deadlock — две транзакции взаимно ждут блокировки друг друга (T1 держит A, ждёт B; T2 держит B, ждёт A). СУБД обнаруживает цикл и откатывает одну из транзакций.

Подробно:

T1: LOCK A ... LOCK B   (ждёт B)
T2: LOCK B ... LOCK A   (ждёт A)   → deadlock

Меры профилактики:

• Захватывать ресурсы в ЕДИНОМ порядке (например, всегда по возрастанию id).
• Делать транзакции короткими.
• Понижать уровень изоляции, если допустимо.
• Уметь ретраить транзакцию, откатанную из-за дедлока.

⚠️ Ловушка: дедлоки на одной таблице тоже бывают — например, при обновлении нескольких строк в разном порядке разными запросами, или из-за gap-локов на уровне Repeatable Read в MySQL.

Короткий ответ: NULL означает «неизвестно», поэтому сравнение с ним даёт не TRUE/FALSE, а UNKNOWN. NULL = NULL → UNKNOWN, и строка не попадает в результат WHERE.

Подробно: SQL использует логику TRUE/FALSE/UNKNOWN.

SELECT NULL = NULL;     -- NULL (UNKNOWN), НЕ TRUE
SELECT NULL <> 5;       -- NULL
SELECT * FROM t WHERE col = NULL;     -- НЕ найдёт NULL'ы (всегда UNKNOWN)
SELECT * FROM t WHERE col IS NULL;    -- правильный способ

Таблица для AND/OR:

TRUE  AND NULL = NULL
FALSE AND NULL = FALSE
TRUE  OR  NULL = TRUE
FALSE OR  NULL = NULL

⚠️ Ловушка: WHERE col != 'x' НЕ вернёт строки, где col IS NULL (т.к. NULL != 'x' → UNKNOWN). Если нужны и NULL — пишите WHERE col != 'x' OR col IS NULL или WHERE col IS DISTINCT FROM 'x'.

Короткий ответ: COALESCE(a, b, ...) возвращает первый не-NULL аргумент; NULLIF(a, b) возвращает NULL, если a = b, иначе a.

Подробно:

-- Подставить значение по умолчанию вместо NULL
SELECT COALESCE(phone, 'не указан') FROM users;

-- Защита от деления на ноль: NULLIF(x,0) → NULL, деление даёт NULL вместо ошибки
SELECT total / NULLIF(count, 0) AS avg_safe FROM stats;

⚠️ Ловушка: COALESCE приводит все аргументы к одному типу — несовместимые типы дадут ошибку. Также при агрегации помните: SUM/AVG сами игнорируют NULL, а AVG делит на число НЕ-NULL значений (не на все строки).

Короткий ответ: INSERT добавляет строки, UPDATE меняет существующие, DELETE удаляет.

Подробно:

INSERT INTO users (id, name) VALUES (1, 'Анна'), (2, 'Борис');

UPDATE users SET name = 'Анна А.' WHERE id = 1;

DELETE FROM users WHERE id = 2;

⚠️ Ловушка: UPDATE/DELETE БЕЗ WHERE затрагивают ВСЮ таблицу. Перед выполнением проверьте условие отдельным SELECT. В отличие от DELETE, TRUNCATE чистит всю таблицу быстро, но не запускает триггеры и часто не откатывается так же гибко.

Короткий ответ: UPSERT = INSERT, а при конфликте уникальности — UPDATE (или ничего). В PostgreSQL — INSERT ... ON CONFLICT, в стандарте/Oracle/SQL Server — MERGE.

Подробно:

PostgreSQL:

INSERT INTO counters (key, value) VALUES ('hits', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + EXCLUDED.value;   -- увеличить при наличии

-- или просто игнорировать дубликат:
INSERT INTO users (email, name) VALUES ('a@b.c', 'A')
ON CONFLICT (email) DO NOTHING;

MERGE (стандарт SQL):

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

⚠️ Ловушка: ON CONFLICT требует наличия соответствующего уникального индекса/ограничения по указанным колонкам. В EXCLUDED лежат значения, которые пытались вставить. MySQL имеет своё INSERT ... ON DUPLICATE KEY UPDATE.

Короткий ответ: обычный VIEW — сохранённый запрос (вычисляется при каждом обращении, данные всегда свежие); materialized view — физически хранит результат (быстрое чтение, но данные надо обновлять).

Подробно:

-- Обычное представление: подзапрос с именем, без хранения данных
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE active = TRUE;

-- Материализованное: результат хранится на диске
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept, COUNT(*) cnt, AVG(salary) avg_sal FROM employees GROUP BY dept;

REFRESH MATERIALIZED VIEW dept_stats;             -- обновить (с блокировкой чтения)
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats; -- без блокировки (нужен UNIQUE индекс)

⚠️ Ловушка: данные в materialized view устаревают до REFRESH — не используйте для real-time. Обычный VIEW не ускоряет тяжёлый запрос (он выполняется каждый раз) — это абстракция, а не кэш. Обновляемость VIEW (INSERT/UPDATE через него) ограничена простыми представлениями.

Короткий ответ: индекс — структура (обычно B-tree), ускоряющая поиск/сортировку по столбцам ценой замедления записи и дополнительного места. (Подробно — в файле по PostgreSQL.)

Подробно: ускоряет WHERE, JOIN по ключу, ORDER BY, проверку UNIQUE. Замедляет INSERT/UPDATE/DELETE (индекс надо поддерживать) и занимает диск.

CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);  -- составной

⚠️ Ловушка: индекс не используется, если по колонке применяется функция (WHERE lower(name) = ... без функционального индекса), при LIKE '%abc' (ведущий wildcard), при низкой селективности (мало уникальных значений). Слишком много индексов вредит записи.

Короткий ответ: DDL — определение структуры; DML — работа с данными; DCL — права; TCL — управление транзакциями.

Подробно:

Категория	Расшифровка	Команды	Назначение
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	структура объектов
DML	Data Manipulation Language	SELECT*, INSERT, UPDATE, DELETE	данные
DCL	Data Control Language	GRANT, REVOKE	права доступа
TCL	Transaction Control Language	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	транзакции

* SELECT иногда выделяют в отдельную категорию DQL (Data Query Language).

⚠️ Ловушка: в большинстве СУБД DDL вызывает неявный COMMIT (например, MySQL, Oracle) — нельзя откатить CREATE TABLE через ROLLBACK. PostgreSQL — приятное исключение: DDL транзакционен и откатывается.

Короткий ответ: через DENSE_RANK, либо подзапрос с MAX, либо LIMIT 1 OFFSET 1.

Подробно:

-- Способ 1: оконная функция (надёжнее всего, учитывает дубли)
SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

-- Способ 2: подзапрос (максимум среди тех, кто меньше глобального максимума)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Способ 3: LIMIT/OFFSET (НЕ учитывает дубли зарплат!)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

⚠️ Ловушка: простой LIMIT 1 OFFSET 1 сломается при одинаковых максимальных зарплатах. Уточняйте, нужна «вторая по величине ЗАРПЛАТА» (используйте DISTINCT/DENSE_RANK) или «второй сотрудник по порядку». DENSE_RANK корректнее ROW_NUMBER для «N-й по величине значения».

Короткий ответ: GROUP BY по нужным столбцам + HAVING COUNT(*) > 1.

Подробно:

-- Найти дублирующиеся email и их количество
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Удалить дубли, оставив строку с минимальным id (PostgreSQL)
DELETE FROM users u
USING users d
WHERE u.email = d.email AND u.id > d.id;

-- Альтернатива через оконную функцию
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

⚠️ Ловушка: при поиске дублей по нескольким столбцам группируйте по всем них. Перед удалением всегда сначала сделайте SELECT, чтобы убедиться в выборе «лишних» строк.

Короткий ответ: оконная функция ROW_NUMBER/RANK с PARTITION BY группа ORDER BY метрика, затем фильтр по рангу.

Подробно:

-- Топ-3 самых оплачиваемых сотрудника в каждом отделе
WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 3;

⚠️ Ловушка: выбор функции зависит от семантики при равенстве: ROW_NUMBER даст ровно 3 строки (произвольно среди равных), RANK/DENSE_RANK могут вернуть больше при ничьих. Добавляйте tiebreaker в ORDER BY. Фильтр по rn обязан быть в подзапросе/CTE — в WHERE основного запроса оконную функцию использовать нельзя.

Короткий ответ: реляционные (PostgreSQL, MySQL) — для структурированных данных, сложных связей, транзакций и ACID; NoSQL (документные, key-value, колоночные, графовые) — для гибкой схемы, горизонтального масштаба и специфичных паттернов доступа.

Подробно:

Критерий	Реляционная (SQL)	NoSQL
Схема	строгая, заранее	гибкая/schemaless
Связи/JOIN	сильная сторона	слабо или нет
Транзакции/ACID	полноценные	ограниченные (зависит)
Масштабирование	вертикальное (+ реплики/шардинг сложнее)	горизонтальное по дизайну
Запросы	мощный SQL, агрегации	по ключу/паттерну доступа
Примеры применения	финансы, ERP, учёт	каталоги, сессии, логи, графы соцсетей

Выбор: нужны транзакции, сложные ad-hoc запросы, целостность связей → SQL. Нужна гибкая схема, огромный объём простых операций по ключу, горизонтальный скейл → подходящий NoSQL. Часто используют гибрид (полиглот-персистентность).

⚠️ Ловушка: «NoSQL = быстрее/масштабируемее всегда» — миф. Современные реляционные БД тоже масштабируются (партиционирование, реплики, расширения). NoSQL платит за скейл потерей гибких запросов и (часто) строгой консистентности. Выбирайте по паттернам доступа и требованиям к согласованности, а не по хайпу.

Короткий ответ: Индекс — это отдельная структура данных, которая хранит значения одной или нескольких колонок в упорядоченном/организованном виде вместе с указателями на физическое расположение строк (TID — tuple identifier), чтобы база могла находить строки без полного сканирования таблицы.

Подробно: Без индекса PostgreSQL вынужден читать всю таблицу (Sequential Scan) и проверять каждую строку. Индекс позволяет за O(log n) (для B-tree) найти нужные TID и потом сходить за самими строками в heap (основную область хранения таблицы).

CREATE INDEX idx_users_email ON users (email);

-- Теперь запрос ищет по дереву, а не по всей таблице
SELECT * FROM users WHERE email = 'a@b.com';

Физически таблица в Postgres хранится в виде heap — неупорядоченного набора страниц по 8 КБ. Индекс — это «оглавление» к этому heap. У одной таблицы может быть много индексов.

⚠️ Ловушка: Индекс ускоряет чтение, но замедляет запись (каждый INSERT/UPDATE/DELETE должен поддерживать индекс актуальным). Индекс на каждую колонку «на всякий случай» — антипаттерн.

Короткий ответ: B-tree (точнее, B+tree-подобная структура Lehman & Yao) — это сбалансированное дерево, где значения отсортированы. Листья связаны в двусвязный список. Поиск, диапазонные запросы и сортировка работают за логарифм высоты дерева.

Подробно: B-tree поддерживает операторы =, <, <=, >, >=, BETWEEN, IN, IS NULL, а также сортировку (ORDER BY) и LIKE 'prefix%'. Дерево состоит из:

• корневой страницы,
• внутренних (branch) страниц с разделителями,
• листовых (leaf) страниц с самими значениями и TID.

Высота дерева обычно 3–4 уровня даже для сотен миллионов строк, поэтому поиск — это 3–4 чтения страниц.

CREATE INDEX idx_orders_created ON orders (created_at); -- B-tree по умолчанию

-- Эффективно: диапазон + сортировка прямо из индекса
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
ORDER BY created_at
LIMIT 100;

Связанные листья позволяют делать range scan: нашли начало диапазона, идём по листьям до конца. Та же упорядоченность даёт «бесплатную» сортировку.

⚠️ Ловушка: B-tree бесполезен для условий вида WHERE col LIKE '%suffix' или WHERE upper(col) = '...' — порядок значений не помогает. И помните: индекс по created_at ASC отлично обслуживает ORDER BY created_at DESC (Postgres умеет читать дерево в обратную сторону).

Короткий ответ: Hash индекс хранит хеш значения и поддерживает только оператор равенства =. Может быть чуть компактнее/быстрее B-tree для точечного поиска, но не поддерживает диапазоны и сортировку.

Подробно: До PostgreSQL 10 hash-индексы не писались в WAL (не были crash-safe и не реплицировались), поэтому их не рекомендовали. С версии 10 они WAL-логируются и пригодны для продакшена.

CREATE INDEX idx_sessions_token ON sessions USING hash (token);

SELECT * FROM sessions WHERE token = '...'; -- только равенство

На практике B-tree почти всегда предпочитают, потому что он универсальнее, а выигрыш hash невелик. Hash имеет смысл для очень длинных значений, где сравнение хешей дешевле сравнения полных ключей.

⚠️ Ловушка: Hash индекс не обслуживает ORDER BY, <, >, LIKE, не может быть уникальным до новых версий и не используется в составных индексах в полной мере. По умолчанию выбирайте B-tree.

Короткий ответ: GIN (Generalized Inverted Index) — обратный индекс «значение → список строк, где оно встречается». Идеален для составных значений: массивов, jsonb, full-text search (tsvector), где в одной колонке много элементов.

Подробно: GIN хранит каждый элемент (ключ jsonb, элемент массива, лексему) как отдельную запись, указывающую на все строки, содержащие его. Поэтому он отлично отвечает на вопросы «какие строки содержат элемент X».

-- Массивы: поиск по вхождению
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

-- jsonb: поиск по ключам/значениям
CREATE INDEX idx_docs_data ON docs USING gin (data);
SELECT * FROM docs WHERE data @> '{"status": "active"}';

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('russian', body));
SELECT * FROM articles
WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'база данных');

Операторы для GIN: @> (содержит), <@, ?, ?|, ?&, @@.

⚠️ Ловушка: GIN дорог в обновлении (вставка обновляет много записей). Для смягчения есть fastupdate (отложенная вставка через pending list) и обязательно нужен autovacuum. Для jsonb с поиском только по @> используйте оператор-класс jsonb_path_ops — он меньше и быстрее, но поддерживает меньше операторов.

Короткий ответ: GiST (Generalized Search Tree) — каркас для индексов «по близости/пересечению»: геоданные (PostGIS), диапазоны (range types), геометрия, ближайшие соседи (KNN), частично full-text. Это сбалансированное дерево с настраиваемыми предикатами.

Подробно: GiST не точная, а «лоссовая» структура — внутренние узлы хранят приблизительные предикаты (bounding box), что позволяет искать по пересечению, содержанию, расстоянию.

-- Диапазоны без пересечений (exclusion constraint)
CREATE TABLE bookings (
  room_id int,
  during  tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- Гео: ближайшие точки (KNN)
CREATE INDEX idx_places_geom ON places USING gist (geom);
SELECT * FROM places ORDER BY geom <-> ST_Point(30.3, 59.9) LIMIT 5;

Есть вариант SP-GiST для несбалансированных структур (quad-tree, radix-tree) — для текста-префиксов, точек.

⚠️ Ловушка: GiST медленнее B-tree для обычного равенства/диапазона по скалярам — используйте его только когда нужна семантика пересечения/расстояния, иначе B-tree.

Короткий ответ: BRIN (Block Range Index) хранит минимальное и максимальное значение для диапазона блоков (по умолчанию 128 страниц). Крошечный по размеру, эффективен для очень больших таблиц, где данные физически коррелируют с колонкой (например, монотонно растущий created_at).

Подробно: Вместо записи на каждую строку BRIN хранит сводку (min/max) на группу блоков. При запросе он отбрасывает блоки, чьи диапазоны не пересекаются с условием, и сканирует только оставшиеся.

CREATE INDEX idx_events_ts_brin ON events USING brin (created_at);

SELECT * FROM events WHERE created_at BETWEEN '2026-06-01' AND '2026-06-02';

Индекс на миллиарды строк может занимать килобайты. Идеален для append-only таблиц (логи, метрики, time-series), где новые строки пишутся в конец и значение растёт.

⚠️ Ловушка: Если физический порядок строк не коррелирует со значением колонки (например, после многих UPDATE/перемешивания), BRIN бесполезен — min/max диапазонов перекрываются и приходится читать всё. Корреляцию можно восстановить через CLUSTER или физическую сортировку при загрузке.

Короткий ответ: Когда условие не SARGable (функция/преобразование над колонкой), при LIKE '%x', при низкой селективности, на малых таблицах, при несовпадении типов (implicit cast), и когда планировщик решает, что Seq Scan дешевле.

Подробно: Частые причины:

-- 1) Функция над колонкой убивает индекс по самой колонке
WHERE lower(email) = 'a@b.com'        -- индекс по email не используется
-- Решение: индекс по выражению
CREATE INDEX ON users (lower(email));

-- 2) Leading wildcard
WHERE name LIKE '%son'                 -- B-tree не помогает
WHERE name LIKE 'john%'                -- а так помогает (префикс)

-- 3) Низкая селективность: вернётся 60% таблицы
WHERE status = 'active'                -- Seq Scan дешевле, чем index + random heap fetches

-- 4) Малая таблица — вся помещается в пару страниц, Seq Scan быстрее

-- 5) Несовпадение типов
WHERE user_id = '123'                  -- user_id bigint, '123' text → возможный cast
-- индекс может не подойти; приводите литерал к типу колонки

-- 6) Арифметика над колонкой
WHERE price * 1.2 > 100                 -- не SARGable
WHERE price > 100 / 1.2                 -- SARGable

⚠️ Ловушка: Не делайте SET enable_seqscan = off, чтобы «заставить» индекс — это маскирует проблему. Если индекс игнорируется на «правильном» запросе — почти всегда виноваты устаревшая статистика (ANALYZE), несовпадение типов или то, что выборка реально большая. Сначала смотрите EXPLAIN ANALYZE.

Короткий ответ: Составной (multicolumn) индекс строится по нескольким колонкам. Правило «левого префикса»: индекс эффективно используется для условий на ведущую колонку и непрерывный префикс колонок слева направо. По колонке из середины без ведущих — нет.

Подробно:

CREATE INDEX idx ON orders (customer_id, status, created_at);

-- Используется хорошо:
WHERE customer_id = 5                                  -- префикс (1 колонка)
WHERE customer_id = 5 AND status = 'paid'              -- префикс (2 колонки)
WHERE customer_id = 5 AND status = 'paid'
      AND created_at > '2026-01-01'                    -- весь индекс
WHERE customer_id = 5 ORDER BY status, created_at      -- сортировка из индекса

-- Используется плохо / не используется:
WHERE status = 'paid'                                  -- нет ведущей customer_id
WHERE created_at > '2026-01-01'                        -- нет префикса

При равенстве на ведущих колонках диапазон/сортировку можно эффективно применить к следующей. Если на ведущей колонке стоит диапазон (>), то колонки правее уже не используются для дальнейшего сужения дерева (но могут для index filter).

⚠️ Ловушка: Один составной индекс (a, b) не заменяет потребность в индексе по b отдельно. Но индекс (a, b) покрывает запросы по a — поэтому отдельный индекс только по a обычно избыточен.

Короткий ответ: Сначала колонки, по которым идёт равенство (=), потом колонка для диапазона/сортировки. Среди равенств — учитывайте, какие комбинации запросов нужны (ведущая должна встречаться чаще всего).

Подробно: Эвристика «equality first, range last»:

-- Запрос:
SELECT * FROM events
WHERE tenant_id = 7 AND type = 'click' AND ts > now() - interval '1 day'
ORDER BY ts;

-- Оптимально: равенства слева, диапазон+сортировка справа
CREATE INDEX ON events (tenant_id, type, ts);

Поставив ts первой, вы бы получили лишь грубый диапазон без точного попадания по tenant_id/type. С равенствами слева дерево сразу сужается до нужной ветки, а ts внутри неё уже отсортирован — это даёт и фильтр, и ORDER BY без отдельной сортировки.

Селективность тоже важна, но при равенствах правило «equality first» обычно сильнее.

⚠️ Ловушка: Не всегда «самая селективная колонка первой». Если по самой селективной колонке вы ищете диапазоном, а по менее селективной — равенством, ведущей должна быть колонка равенства.

Короткий ответ: Покрывающий индекс содержит все колонки, нужные запросу, так что данные берутся прямо из индекса без обращения к heap — это Index-Only Scan. INCLUDE добавляет «полезную нагрузку» в листья индекса, не участвующую в сортировке/уникальности.

Подробно:

-- Запрос читает только user_id и email
SELECT email FROM users WHERE user_id = 42;

-- Вариант 1: всё в ключе
CREATE INDEX ON users (user_id, email);

-- Вариант 2: INCLUDE (email не нужен для поиска, только для возврата)
CREATE INDEX ON users (user_id) INCLUDE (email);

В плане увидите Index Only Scan. Преимущество — нет random доступа к heap.

⚠️ Ловушка: Index-Only Scan возможен, только если visibility map говорит, что страница «полностью видима». На свежеобновлённых данных Postgres всё равно ходит в heap проверять видимость (Heap Fetches в плане > 0). Если Heap Fetches большой — нужен VACUUM, чтобы обновить visibility map.

Короткий ответ: Partial-индекс индексирует только строки, удовлетворяющие условию WHERE. Он меньше, быстрее и обновляется реже, когда запросы всегда обращаются к подмножеству данных.

Подробно:

-- Индексируем только активные заказы (их 1%, а не все 100M)
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'active';

-- Используется автоматически, если запрос совместим с предикатом:
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-06-01';

-- Частый кейс: уникальность только среди не-удалённых
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;

Это классика для soft-delete, флагов, очередей задач (WHERE processed = false).

⚠️ Ловушка: Планировщик использует partial-индекс, только если может доказать, что предикат запроса включён в предикат индекса. WHERE status = 'active' подойдёт, а WHERE status = $1 с параметром во время планирования — может не подойти, если значение неизвестно (зависит от prepared statement / generic plan).

Короткий ответ: Индекс не по колонке, а по результату выражения/функции. Используется, когда в WHERE/ORDER BY стоит то же выражение.

Подробно:

-- Регистронезависимый поиск
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

-- Индекс по полю jsonb
CREATE INDEX idx_docs_status ON docs ((data->>'status'));
SELECT * FROM docs WHERE data->>'status' = 'active';

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

⚠️ Ловушка: Функция должна быть IMMUTABLE. Нельзя индексировать по now() или функциям, зависящим от настроек сессии (например, lower() без указанной коллации в нестабильных случаях). Индекс по выражению чуть дороже в поддержке — выражение вычисляется при каждой записи.

Короткий ответ: Уникальный индекс гарантирует, что комбинация значений не повторяется, и одновременно ускоряет поиск. PRIMARY KEY и UNIQUE constraint реализованы поверх уникального индекса.

Подробно:

CREATE UNIQUE INDEX ON users (email);

-- эквивалент ограничения
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

NULL-ы по умолчанию считаются различными (несколько NULL допускаются). С PostgreSQL 15 есть UNIQUE NULLS NOT DISTINCT, чтобы запрещать повторяющиеся NULL.

⚠️ Ловушка: Уникальность проверяется немедленно при вставке (если не DEFERRABLE). Массовые вставки с конфликтами лучше делать через INSERT ... ON CONFLICT DO NOTHING/UPDATE (upsert), иначе вся транзакция упадёт на первом дубликате.

Короткий ответ: Индексы стоят: (1) замедление записи — каждый INSERT/UPDATE/DELETE поддерживает все индексы; (2) дисковое место; (3) накладные расходы на сопровождение (VACUUM, bloat, реиндексация); (4) нагрузка на планировщик при выборе плана.

Подробно: UPDATE особенно дорог: из-за MVCC он создаёт новую версию строки и может потребовать вставку во все индексы (кроме случаев HOT-update — Heap-Only Tuple, когда изменённые колонки не входят ни в один индекс и новая версия помещается на ту же страницу).

-- Найти неиспользуемые индексы (idx_scan = 0)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

⚠️ Ловушка: «Просто добавлю индекс» на горячую большую таблицу под нагрузкой блокирует запись. Используйте CREATE INDEX CONCURRENTLY (не в транзакции, медленнее, но не блокирует). Дублирующиеся и неиспользуемые индексы — частый источник деградации записи.

Короткий ответ: EXPLAIN показывает предполагаемый план и оценки (cost, rows). EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время и количество строк. План читается снизу вверх / изнутри наружу.

Подробно:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer on orders
  (cost=0.43..8.45 rows=3 width=64)
  (actual time=0.018..0.022 rows=2 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.1 ms
Execution Time: 0.05 ms

Что значит:

• cost=startup..total — условные единицы (не миллисекунды): стоимость до первой строки и до последней.
• rows — оценка планировщика; actual ... rows — реальность.
• loops — сколько раз узел выполнялся (важно во вложенных циклах: реальное время = actual time * loops).
• BUFFERS — сколько страниц прочитано из кэша (shared hit) и с диска (read).

Что искать при медленном запросе:

1. Большое расхождение rows (оценка) vs actual rows — устаревшая статистика → ANALYZE. Из-за неверной оценки выбирается плохой план.
2. Seq Scan на большой таблице с селективным фильтром → не хватает индекса.
3. Rows Removed by Filter большое → индекс не сужает, фильтрация в heap.
4. Heap Fetches большое в Index Only Scan → нужен VACUUM.
5. Sort с external merge Disk → не хватает work_mem, сортировка ушла на диск.
6. Nested Loop с большим числом loops и большой внутренней стороной → плохой join.

⚠️ Ловушка: EXPLAIN ANALYZE действительно выполняет запрос — для UPDATE/DELETE/INSERT он изменит данные! Оборачивайте в транзакцию с ROLLBACK. И помните: cost — не время; сравнивать планы по cost можно, но мерить производительность нужно по actual time и Execution Time.

Короткий ответ: Seq Scan читает всю таблицу подряд. Index Scan ходит по индексу и за каждой строкой случайно в heap. Bitmap Heap Scan — гибрид: собирает все нужные TID в битовую карту, сортирует их по физическому расположению и читает heap последовательно; хорош при средней селективности.

Подробно:

-- Index Scan: мало строк, точечный доступ
Index Scan using idx_users_email on users
  Index Cond: (email = 'a@b.com')

-- Bitmap: средняя выборка, много строк, но не вся таблица
Bitmap Heap Scan on orders
  Recheck Cond: (status = 'paid')
  ->  Bitmap Index Scan on idx_orders_status
        Index Cond: (status = 'paid')

-- Seq Scan: выборка большой доли таблицы или нет индекса
Seq Scan on small_table
  Filter: (active = true)

Логика выбора:

• Очень мало строк → Index Scan (random доступ оправдан).
• Средняя доля → Bitmap (избегаем повторных random чтений одной страницы, читаем по порядку).
• Большая доля / маленькая таблица → Seq Scan (последовательное чтение дешевле random).

Bitmap может объединять несколько индексов через BitmapAnd / BitmapOr.

⚠️ Ловушка: Recheck Cond в Bitmap Heap Scan — нормально: если битмап стал «lossy» (не хватило памяти на точные TID, хранит страницы целиком), Postgres перепроверяет условие на строках. Много lossy → увеличьте work_mem.

Короткий ответ: Nested Loop — для каждой строки внешней таблицы ищем совпадения во внутренней (хорош, когда внешняя мала и есть индекс на внутренней). Hash Join — строит хеш-таблицу из меньшей стороны, прогоняет большую (хорош для крупных несортированных наборов, равенство). Merge Join — сливает две отсортированные стороны (хорош, когда обе уже отсортированы по ключу).

Подробно:

EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id;

• Nested Loop: cost ≈ outer_rows * cost_inner_lookup. Отлично, когда внешняя сторона возвращает мало строк и по внутренней есть индекс. Плохо, когда обе большие (квадратично).
• Hash Join: O(n + m), требует памяти на хеш меньшей стороны. Только для эквисоединений (=). Если хеш не влезает в work_mem — «batches» на диск.
• Merge Join: требует сортировки обеих сторон (или индексов, дающих порядок). Хорош для очень больших сортированных наборов и для неравенств в диапазонах.

Hash Join  (cost=...) 
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on customers c

⚠️ Ловушка: Если видите Nested Loop с loops=1000000 и Seq Scan внутри — это катастрофа, обычно из-за недооценки строк внешней стороны (плохая статистика) или отсутствия индекса по ключу join. Проверьте ANALYZE и индекс на FK-колонке.

Короткий ответ: SARGable (Search ARGument able) — условие, которое позволяет использовать индекс, потому что индексированная колонка стоит «голой» по одну сторону оператора, без функций и вычислений над ней.

Подробно:

-- НЕ SARGable (функция/арифметика над колонкой):
WHERE date_trunc('day', created_at) = '2026-06-24'
WHERE created_at + interval '1 day' > now()
WHERE extract(year from created_at) = 2026
WHERE price * 1.2 > 120

-- SARGable (переписать так, чтобы колонка была чистой):
WHERE created_at >= '2026-06-24' AND created_at < '2026-06-25'
WHERE created_at > now() - interval '1 day'
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE price > 100

Если переписать условие нельзя, создают индекс по тому же выражению, например CREATE INDEX ON users (lower(email)); обычный индекс по email для WHERE lower(email) = ... не подходит.

⚠️ Ловушка: Implicit cast тоже ломает SARGability. WHERE varchar_col = 123 (число) может привести к приведению типов и игнорированию индекса. Приводите литерал к типу колонки, а не наоборот.

Короткий ответ: Делать условия SARGable, не тащить SELECT *, избегать N+1 (джойнить вместо запросов в цикле), использовать пагинацию по ключу, при необходимости — денормализацию и материализованные представления.

Подробно:

-- 1) SELECT * мешает index-only scan и тащит TOAST/большие поля
SELECT id, name FROM products WHERE category_id = 5;   -- только нужное

-- 2) N+1: вместо запроса в цикле приложения — один JOIN/IN
SELECT o.*, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.id = ANY($1);

-- 3) Агрегаты/часто читаемое — денормализация или materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) d, sum(amount) FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

N+1 «на уровне БД» — это когда ORM вместо одного запроса делает 1 запрос за списком + N запросов за связанными сущностями. Решается eager loading (JOIN) или батч-выборкой через WHERE id IN (...).

⚠️ Ловушка: Денормализация ускоряет чтение, но создаёт риск рассогласования данных и усложняет запись (нужны триггеры/логика синхронизации). Materialized view не обновляется автоматически — данные устаревают до REFRESH.

Короткий ответ: OFFSET N заставляет Postgres прочитать и отбросить первые N строк, прежде чем вернуть нужную страницу — стоимость растёт линейно с N. Keyset (seek/cursor) пагинация использует условие по последнему виденному ключу и попадает прямо в нужное место по индексу.

Подробно:

-- Медленно на глубине: читает 100020 строк, отдаёт 20
SELECT * FROM events ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 20;

-- Keyset: O(log n) переход к нужной позиции по индексу
SELECT * FROM events
WHERE (created_at, id) < ($last_created_at, $last_id)   -- курсор
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Нужен индекс ON events (created_at DESC, id DESC)

Keyset стабилен при вставках/удалениях (не «съезжает», как OFFSET) и не деградирует на больших глубинах.

⚠️ Ловушка: Keyset не умеет «перейти на страницу 500» (только вперёд/назад от курсора) и требует строгого, уникального порядка сортировки — поэтому в ключ обычно добавляют id как tie-breaker, иначе строки с одинаковым created_at будут теряться или дублироваться на границе страниц.

Короткий ответ: MVCC (Multiversion Concurrency Control) хранит несколько версий строки. Читатели видят согласованный снимок (snapshot) данных и не блокируют писателей, а писатели не блокируют читателей. Видимость версии определяется системными колонками xmin/xmax.

Подробно: У каждой строки есть скрытые поля:

• xmin — id транзакции, которая создала эту версию.
• xmax — id транзакции, которая удалила/обновила её (0, если жива).

SELECT xmin, xmax, * FROM accounts WHERE id = 1;

Правила видимости (упрощённо): строка видна транзакции, если её xmin уже зафиксирован и виден в снимке, а xmax либо пуст, либо принадлежит ещё не зафиксированной/невидимой транзакции.

Почему UPDATE = DELETE + INSERT: Postgres не меняет строку на месте. Он проставляет xmax старой версии и вставляет новую версию с новым xmin. Старая версия (dead tuple) остаётся, пока её не уберёт VACUUM.

Снимок (snapshot) определяет, какие транзакции считаются «уже завершёнными». В READ COMMITTED новый снимок берётся на каждый оператор; в REPEATABLE READ/SERIALIZABLE — один на всю транзакцию.

⚠️ Ловушка: Из-за «UPDATE = новая версия» частые обновления порождают bloat (раздувание таблицы мёртвыми строками) и заставляют обновлять индексы. Кроме того, MVCC требует борьбы с transaction ID wraparound: счётчик XID 32-битный, и без VACUUM (freeze) база может остановиться, чтобы не «закольцевать» возраст транзакций.

Короткий ответ: VACUUM удаляет мёртвые версии строк (dead tuples), освобождая место для повторного использования и обновляя visibility map. ANALYZE собирает статистику распределения данных для планировщика. autovacuum делает и то, и другое автоматически в фоне.

Подробно:

VACUUM (VERBOSE, ANALYZE) orders;   -- очистка + статистика
ANALYZE orders;                     -- только статистика
VACUUM FULL orders;                 -- переписывает таблицу, возвращает место ОС

Различия:

• VACUUM (обычный): помечает место от dead tuples как переиспользуемое внутри таблицы, не отдаёт его ОС, не блокирует читателей/писателей (берёт лёгкую блокировку). Обновляет visibility map (нужно для Index-Only Scan) и freeze старых XID.
• VACUUM FULL: переписывает всю таблицу в новый файл, физически сжимая её и возвращая место ОС, но берёт ACCESS EXCLUSIVE lock (полная блокировка таблицы!).
• ANALYZE: считает статистику (гистограммы, n_distinct, most common values), от которой зависит выбор плана.

-- Сколько мёртвых строк и когда последний autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

⚠️ Ловушка: VACUUM FULL нельзя гонять на проде на горячих таблицах — он блокирует таблицу целиком. Для онлайн-устранения bloat используйте pg_repack. Длинные открытые транзакции (idle in transaction) мешают VACUUM убирать dead tuples (они ещё «могут понадобиться» старому снимку) — отсюда внезапный рост bloat.

Короткий ответ: Postgres использует блокировки на уровне строк и таблиц. Чтение благодаря MVCC не блокируется. Взаимоблокировки (deadlock) обнаруживаются автоматически фоновым детектором, который прерывает одну из транзакций с ошибкой.

Подробно: Уровни/типы (упрощённо):

• Row-level: FOR UPDATE, FOR SHARE (SELECT ... FOR UPDATE), а также UPDATE/DELETE берут блокировку строки.
• Table-level: ACCESS SHARE (SELECT) ... ACCESS EXCLUSIVE (DDL, VACUUM FULL).
• Advisory locks — прикладные блокировки по ключу.

-- Транзакция A
BEGIN; UPDATE accounts SET bal = bal - 100 WHERE id = 1; -- lock row 1
-- Транзакция B
BEGIN; UPDATE accounts SET bal = bal - 50  WHERE id = 2; -- lock row 2
-- A: UPDATE ... WHERE id = 2  (ждёт B)
-- B: UPDATE ... WHERE id = 1  (ждёт A) → DEADLOCK

Detector найдёт цикл ожиданий и убьёт одну транзакцию: ERROR: deadlock detected. Интервал проверки — deadlock_timeout (по умолчанию 1s).

-- Кто кого блокирует
SELECT * FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT granted;

⚠️ Ловушка: Главная профилактика deadlock — всегда брать блокировки в одном и том же порядке (например, обновлять строки в порядке возрастания id). Используйте SELECT ... FOR UPDATE SKIP LOCKED для очередей задач, чтобы воркеры не конфликтовали. И избегайте lock_timeout-зависаний — ставьте таймауты.

Короткий ответ: TOAST (The Oversized-Attribute Storage Technique) — механизм хранения больших значений (текст, jsonb, bytea), которые не влезают в страницу 8 КБ. Большие поля сжимаются и/или выносятся в отдельную TOAST-таблицу, а в основной строке остаётся указатель.

Подробно: Если строка превышает ~2 КБ (TOAST_TUPLE_THRESHOLD), Postgres сжимает и/или выносит крупные атрибуты «out-of-line». Стратегии хранения колонки: PLAIN, EXTENDED (сжать + вынести, по умолчанию для text/jsonb), EXTERNAL (вынести без сжатия), MAIN (сжать, но стараться держать inline).

ALTER TABLE docs ALTER COLUMN payload SET STORAGE EXTERNAL;

Это прозрачно для запросов, но влияет на производительность: чтение TOAST-значения — дополнительный доступ.

⚠️ Ловушка: SELECT * по таблице с большими TOAST-полями тянет и распаковывает их, даже если они не нужны — ещё одна причина перечислять только нужные колонки. Частые UPDATE больших jsonb дороги: меняется вся версия строки + TOAST.

Короткий ответ: WAL (Write-Ahead Log) — журнал, куда изменения записываются ДО применения к страницам данных. Это даёт durability (восстановление после сбоя) и служит основой репликации и PITR (point-in-time recovery).

Подробно: Принцип write-ahead logging: прежде чем изменить страницу в данных, изменение фиксируется в WAL и сбрасывается на диск (fsync) при COMMIT. Если сервер падает, при старте он «проигрывает» WAL и доводит данные до согласованного состояния (crash recovery).

Преимущества:

• Не нужно синхронно писать сами страницы данных при каждом коммите — достаточно последовательной записи в WAL (быстро).
• Записи WAL передаются на реплики → streaming replication.
• Архивируя WAL, можно восстановиться на любой момент (PITR).

SELECT pg_current_wal_lsn();           -- текущая позиция в WAL
SHOW wal_level;                        -- replica / logical

⚠️ Ловушка: При большом объёме записи WAL может расти быстрее, чем архивируется/реплицируется, и заполнить диск. Следите за pg_wal, лагом репликации и max_slot_wal_keep_size. Зависший replication slot (мёртвая реплика) удерживает WAL и забивает диск primary.

Короткий ответ: Физическая (streaming) репликация копирует WAL побайтно на standby — реплика идентична primary, годится для read-replica и failover. Логическая репликация передаёт изменения на уровне строк по publish/subscribe — выборочно по таблицам, между разными версиями. Бывает синхронной и асинхронной.

Подробно:

• Streaming (physical): standby непрерывно получает WAL и применяет его. Реплики только для чтения (hot standby). Один primary — много реплик. Используется для масштабирования чтения и HA.
• Синхронная: COMMIT на primary ждёт подтверждения от standby (нет потери данных при падении primary, но выше latency).
• Асинхронная: primary не ждёт реплику (быстрее, но возможна потеря последних транзакций при сбое). По умолчанию.
• Логическая: декодирует WAL в логические изменения и реплицирует выбранные таблицы. Гибко: разные мажорные версии, частичная репликация, разные схемы, multi-master через расширения.

-- Логическая репликация
CREATE PUBLICATION pub_orders FOR TABLE orders;          -- на источнике
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary dbname=app' PUBLICATION pub_orders;  -- на приёмнике

⚠️ Ловушка: Read-replica при асинхронной репликации отстаёт (replication lag) — «прочитал свою же только что записанную запись и не нашёл». Это replication lag / read-your-writes проблема: критичные чтения после записи направляйте на primary. Синхронная реплика повышает надёжность, но если она зависнет — коммиты на primary встанут (нужен кворум/несколько синхронных standby).

Короткий ответ: Декларативное партиционирование разбивает одну логическую таблицу на несколько физических партиций по диапазону, списку или хешу ключа. Это ускоряет запросы (partition pruning), упрощает удаление старых данных (DROP партиции) и обслуживание.

Подробно:

CREATE TABLE events (id bigserial, created_at date, payload jsonb)
PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Преимущества:

• Partition pruning: запрос с WHERE created_at >= '2026-07-01' сканирует только нужные партиции.
• Удаление данных: DROP TABLE events_2026_06 мгновенно вместо медленного DELETE + VACUUM.
• Меньшие индексы на партицию, лучше vacuum/обслуживание.

Виды: RANGE, LIST, HASH.

⚠️ Ловушка: Ключ партиционирования должен входить в первичный/уникальный ключ. Запросы без условия на ключ партиционирования сканируют все партиции (хуже, чем одна таблица). Слишком много партиций (тысячи) замедляют планирование. Нужен процесс автоматического создания будущих партиций (pg_partman или cron).

Короткий ответ: Партиционирование делит таблицу на части в пределах одного сервера. Шардирование распределяет данные по нескольким серверам (горизонтальное масштабирование записи и объёма). Репликация копирует одни и те же данные на несколько серверов (отказоустойчивость и масштабирование чтения).

Подробно:

Аспект	Партиционирование	Шардирование	Репликация
Где данные	один сервер, разные таблицы	разные серверы, разные данные	разные серверы, одинаковые данные
Цель	ускорение запросов, обслуживание	масштаб записи и объёма	HA + масштаб чтения
Запись	один primary	распределена по шардам	один primary
Сложность	низкая (нативно)	высокая (маршрутизация, кросс-шард join)	средняя

Шардирование в Postgre обычно требует внешнего решения (Citus, app-level routing, postgres_fdw). Часто комбинируют: шардирование + репликация каждого шарда + партиционирование внутри шарда.

⚠️ Ловушка: Шардирование ломает кросс-шардовые JOIN, транзакции и уникальность глобальных ключей — это серьёзный архитектурный шаг. Не шардируйте «на всякий случай»: сначала индексы, реплики чтения, партиционирование и connection pooling.

Короткий ответ: Каждое соединение в Postgres — это отдельный процесс ОС с заметным расходом памяти; их число ограничено (max_connections). Пул соединений (pgbouncer) переиспользует небольшое число реальных соединений для множества клиентов, снижая накладные расходы и защищая БД от перегрузки.

Подробно: Postgres использует модель «процесс на соединение», а не потоки. 10 000 клиентских соединений напрямую = 10 000 процессов = крах по памяти и context switching. pgbouncer держит, скажем, 50 реальных соединений к БД и мультиплексирует на них тысячи клиентов.

Режимы пулинга:

• session — соединение к БД закреплено за клиентом на всю сессию (безопасно, но меньше экономия).
• transaction — соединение возвращается в пул после каждой транзакции (самый популярный, лучшая утилизация).
• statement — после каждого запроса (агрессивно).

⚠️ Ловушка: В transaction режиме нельзя полагаться на серверные сессионные фичи: prepared statements (без поддержки), SET на сессию, advisory session locks, LISTEN/NOTIFY, временные таблицы — они могут «утечь» между разными клиентами или не работать. Также не выставляйте размер пула больше, чем БД может реально обслужить (ориентир: ~ (ядра*2 + диски)).

Короткий ответ: json хранит текст «как есть» (сохраняет пробелы, порядок ключей, дубликаты), парсится при каждом обращении. jsonb хранит разобранное бинарное представление: быстрее операции, поддерживает индексацию (GIN), но не сохраняет форматирование и порядок ключей. В 99% случаев нужен jsonb.

Подробно:

-- GIN по всему документу: операторы @>, ?, ?|, ?&
CREATE INDEX idx_docs_data ON docs USING gin (data);
SELECT * FROM docs WHERE data @> '{"status":"active"}';

-- jsonb_path_ops: меньше и быстрее, но только @>
CREATE INDEX idx_docs_data2 ON docs USING gin (data jsonb_path_ops);

-- B-tree по конкретному пути (равенство/диапазон по одному полю)
CREATE INDEX idx_docs_status ON docs ((data->>'status'));
SELECT * FROM docs WHERE data->>'status' = 'active';

⚠️ Ловушка: json/jsonb соблазняют «свалить всё в одну колонку», теряя реляционные гарантии (типы, FK, нормализацию). Используйте jsonb для действительно полуструктурированных/динамических данных, а не вместо нормальной схемы. GIN-индекс по всему jsonb большой и дорог в обновлении — для поиска по одному полю дешевле B-tree по выражению.

Короткий ответ: PostgreSQL поддерживает массивы любого типа (int[], text[]) с операторами вхождения и GIN-индексом. ENUM — перечислимый тип с фиксированным набором значений, хранится компактно и с заданным порядком.

Подробно:

-- ARRAY
CREATE TABLE posts (id int, tags text[]);
INSERT INTO posts VALUES (1, ARRAY['sql','db']);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];     -- содержит
CREATE INDEX ON posts USING gin (tags);

-- ENUM
CREATE TYPE order_status AS ENUM ('new','paid','shipped','cancelled');
CREATE TABLE orders (id int, status order_status);
-- сортировка по объявленному порядку, не алфавиту
SELECT * FROM orders ORDER BY status;

⚠️ Ловушка: ENUM трудно менять: добавить значение можно (ALTER TYPE ... ADD VALUE), но удалить/переименовать в середине — больно (часто проще text + CHECK или справочная таблица). Массивы удобны, но поиск/join по элементам и поддержание целостности (нет FK на элементы массива) хуже, чем нормализованная связь many-to-many.

Короткий ответ: В PostgreSQL операторы DDL (CREATE/ALTER/DROP) транзакционны: их можно выполнять внутри BEGIN ... COMMIT и откатывать через ROLLBACK. Изменения схемы атомарны.

Подробно:

BEGIN;
ALTER TABLE users ADD COLUMN age int;
CREATE INDEX idx_users_age ON users (age);
-- если что-то пошло не так:
ROLLBACK;   -- схема возвращается в исходное состояние, как будто ничего не было

Это огромный плюс для миграций: целая миграция либо применяется полностью, либо не применяется вовсе — нет «полусломанной» схемы.

⚠️ Ловушка: Не всё можно/нужно гонять в транзакции. CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE (в старых версиях) НЕ работают внутри транзакционного блока. Кроме того, ALTER TABLE, берущий ACCESS EXCLUSIVE lock в длинной транзакции, блокирует таблицу на всё время транзакции — держите DDL-транзакции короткими и ставьте lock_timeout.

Короткий ответ: Полное чтение — это O(n): на таблице в 100M строк каждый поиск читал бы гигабайты с диска. Индекс даёт O(log n) и читает буквально несколько страниц. Без индексов любая выборка масштабируется линейно и убивает базу под нагрузкой.

Подробно: Прочитать всё имеет смысл, только когда вы реально берёте большую долю строк (тогда Seq Scan действительно быстрее random-доступа), или таблица крошечная. Для точечных/селективных запросов разница — миллисекунды против минут.

⚠️ Ловушка: Обратное тоже верно — индекс на запрос, возвращающий 80% строк, лишь замедлит (random heap fetches). Индекс окупается на селективных условиях.

Короткий ответ: Postgres не умеет «перепрыгнуть» к 100000-й строке — он обязан физически прочитать и отбросить все 100000 строк перед нужной страницей. Стоимость линейна по OFFSET.

Подробно: Решение — keyset pagination: клиент хранит курсор из значений последней строки, например created_at и уникальный id. Следующая страница использует WHERE (created_at, id) < (:created_at, :id) ORDER BY created_at DESC, id DESC LIMIT 20, поэтому БД сразу находит позицию по составному индексу вместо сканирования и отбрасывания всех предыдущих строк.

⚠️ Ловушка: Глубокий OFFSET ещё и нестабилен: если между запросами строки добавились/удалились, страницы «съезжают» — пользователь видит дубли или пропуски.

Короткий ответ: Из-за MVCC обновления и удаления оставляют мёртвые версии строк. VACUUM освобождает это место под переиспользование, предотвращает bloat, обновляет visibility map (для index-only scan) и защищает от transaction ID wraparound.

Подробно: Без VACUUM таблица и индексы раздуваются, запросы замедляются, статистика устаревает, а в худшем случае база уходит в защитный shutdown из-за исчерпания XID. autovacuum обычно справляется, но требует тюнинга на write-heavy таблицах.

⚠️ Ловушка: Длинные транзакции и idle in transaction мешают VACUUM убирать dead tuples — bloat растёт даже при работающем autovacuum.

Короткий ответ: Найти запрос → снять EXPLAIN (ANALYZE, BUFFERS) → искать узкое место (Seq Scan, расхождение rows, Nested Loop с большими loops, сортировка на диске, Heap Fetches) → устранить причину (индекс, ANALYZE, переписать запрос, увеличить work_mem) → проверить эффект.

Подробно: Пошагово:

-- 1) Найти тяжёлые запросы
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- 2) Что происходит прямо сейчас (блокировки, долгие запросы)
SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY dur DESC;

-- 3) План реального запроса
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <запрос>;

На что смотреть в плане: оценка vs факт по rows (статистика), наличие Seq Scan на больших таблицах, Rows Removed by Filter, тип join и loops, Sort Method: external merge Disk, Heap Fetches. Затем гипотеза → исправление (добавить/исправить индекс, ANALYZE, сделать условие SARGable, переписать пагинацию, поднять work_mem) → повторный замер.

⚠️ Ловушка: Не оптимизируйте вслепую и не добавляйте индексы наугад. Воспроизводите проблему с реалистичными данными (на пустой таблице план будет другим), используйте pg_stat_statements, и помните: иногда «медленный запрос» — это симптом блокировок или нехватки памяти, а не отсутствия индекса.

Короткий ответ: Четыре основных типа: key-value (Redis, DynamoDB), document (MongoDB, CouchDB), column-family / wide-column (Cassandra, HBase, ScyllaDB), graph (Neo4j, JanusGraph). Выбор зависит от модели данных и паттернов доступа.

Подробно:

Тип	Модель	Когда использовать	Примеры
Key-value	Ключ → значение (blob)	Кэш, сессии, простой lookup по ключу, счётчики	Redis, Memcached, DynamoDB
Document	Ключ → JSON/BSON документ	Гибкая схема, вложенные объекты, каталоги, профили	MongoDB, CouchDB, Firestore
Column-family	Строка → колонки (разреженные), партиционирование по ключу	Огромные объёмы записи, time-series, логи, IoT, write-heavy	Cassandra, HBase, ScyllaDB
Graph	Узлы + рёбра (отношения как граждане первого класса)	Соцсети, рекомендации, обнаружение мошенничества, графы связей	Neo4j, ArangoDB, JanusGraph

Как выбирать на практике:

• Нужен просто быстрый кэш / распределённое хранилище ключ-значение → key-value (Redis).
• Данные — это «документы» с переменными полями, агрегаты читаются целиком → document (MongoDB).
• Гигантский поток записи, нужна линейная горизонтальная масштабируемость, нет сложных join → column-family (Cassandra).
• Главное в данных — связи и обход графа (друзья друзей, кратчайший путь) → graph (Neo4j).

// Neo4j — найти друзей друзей (то, что в SQL потребует многократных JOIN)
MATCH (me:User {name: 'Alice'})-[:FRIEND]->()-[:FRIEND]->(fof)
WHERE NOT (me)-[:FRIEND]->(fof) AND me <> fof
RETURN DISTINCT fof.name

-- Cassandra — таблица проектируется под конкретный запрос
CREATE TABLE events_by_user (
  user_id uuid,
  event_time timestamp,
  event_type text,
  payload text,
  PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

⚠️ Ловушка: Граф можно сэмулировать в реляционной БД, но обход на много «прыжков» (depth N) убивает SQL рекурсивными join — это классический сигнал к graph-БД. И наоборот: не берите Neo4j там, где связей мало, — теряете в простоте.

Короткий ответ: SQL — когда нужны строгая схема, сложные join'ы, ACID-транзакции и данные сильно связаны. NoSQL — когда нужна гибкая/меняющаяся схема, огромный объём, горизонтальное масштабирование и понятны паттерны доступа.

Подробно:

Критерий	SQL (реляционные)	NoSQL
Схема	Жёсткая, задаётся заранее (schema-on-write)	Гибкая / schemaless (schema-on-read)
Масштабирование	В основном вертикальное (мощнее сервер)	Горизонтальное (шардинг по узлам)
Join	Нативные, мощные	Обычно нет (денормализация или app-side join)
Транзакции	Полноценные ACID	Часто ограниченные / eventual
Консистентность	Strong	Часто eventual (BASE)
Запросы	Декларативный SQL, гибкие ad-hoc	Оптимизированы под известные паттерны

Schema-on-write vs schema-on-read:

• SQL валидирует структуру при записи — данные всегда «чистые», но миграции дороги.
• NoSQL принимает любые документы — гибко на старте, но логика валидации/совместимости переезжает в приложение.

Масштабирование:

• Вертикальное (scale up) — добавить CPU/RAM одному серверу. Просто, но есть потолок и дорого.
• Горизонтальное (scale out) — добавить узлы. NoSQL спроектированы под это (шардинг + репликация). В SQL шардинг возможен, но болезненный (теряются join'ы и кросс-шардовые транзакции).

⚠️ Ловушка: «NoSQL = нет схемы» — миф. Схема есть всегда, просто она неявная и живёт в коде приложения. Игнорирование этого приводит к «свалке» из несовместимых версий документов, которую невозможно запросить.

Короткий ответ: Когда данные сильно связаны, нужны транзакции и сложные ad-hoc запросы/аналитика, а объём умещается на одном-двух серверах. То есть — в большинстве типичных бизнес-приложений.

Подробно: Не берите NoSQL, если:

• Нужны транзакции через несколько сущностей (деньги, заказы, складские остатки) — классический ACID реляционок здесь надёжнее.
• Данные реляционные по природе — много связей «многие-ко-многим», нужны join'ы.
• Запросы заранее не известны — аналитика, BI, ad-hoc отчёты. SQL гибче; в NoSQL вы платите за каждый незапланированный паттерн денормализацией.
• Объём данных скромный — Postgres легко тянет терабайты и миллионы строк. Гнаться за «web scale» преждевременно — это over-engineering.
• Команда знает SQL — операционная зрелость и инструментарий реляционок огромны.

Современный Postgres к тому же умеет JSONB (документы), массивы, full-text search — часто закрывает «NoSQL-потребности» без отдельной БД.

⚠️ Ловушка: Выбор NoSQL «потому что модно» или «вдруг вырастем до Google» — самая частая ошибка. Сначала докажите, что реляционка не справляется, и только потом меняйте.

Короткий ответ: В распределённой системе из трёх свойств — Consistency, Availability, Partition tolerance — при возникновении сетевого разделения (P) можно гарантировать только одно из двух: либо консистентность (CP), либо доступность (AP).

Подробно:

• C (Consistency) — каждый чтение видит последнюю запись (или ошибку). Все узлы согласованы.
• A (Availability) — каждый запрос получает (не-ошибочный) ответ, без гарантии что он самый свежий.
• P (Partition tolerance) — система продолжает работать при потере/задержке сообщений между узлами.

Почему «выбираешь 2 из 3» — упрощение. В реальной распределённой системе сеть всегда может разделиться, поэтому P обязательно. Реальный выбор — между C и A в момент партиции:

• CP-системы — при разделении жертвуют доступностью ради консистентности (отказывают в записи на «отрезанной» части). Примеры: MongoDB (с majority writes), HBase, Redis (в режиме строгой консистентности), etcd/ZooKeeper, реляционки с синхронной репликацией.
• AP-системы — при разделении остаются доступными, но могут вернуть устаревшие данные (сходятся позже). Примеры: Cassandra, DynamoDB, CouchDB, Riak.

        Партиция случилась
              |
      +-------+-------+
      |               |
   CP: откажу       AP: отвечу,
   в ответе,        но возможно
   но данные        устаревшими
   консистентны     данными

💡 Важно: Когда партиции нет, можно иметь и C, и A одновременно. CAP описывает поведение именно во время сбоя сети. Более точная модель — PACELC: если есть Partition — выбор A/C; Else (в норме) — выбор Latency/Consistency.

⚠️ Ловушка: Сказать «CA-система» (Consistency + Availability без P) для распределённой БД — почти всегда неверно. CA — это одиночный сервер. Любая система с сетью между узлами обязана быть P-tolerant.

Короткий ответ: ACID — строгие гарантии транзакций (классика SQL), фокус на консистентности. BASE — мягкие гарантии (NoSQL), фокус на доступности и масштабируемости, консистентность достигается со временем.

Подробно:

ACID:

• Atomicity — транзакция целиком или никак.
• Consistency — переход из одного валидного состояния в другое (соблюдение ограничений).
• Isolation — параллельные транзакции не мешают друг другу.
• Durability — закоммиченное переживёт сбой.

BASE:

• Basically Available — система отвечает всегда (возможно, частично/устаревше).
• Soft state — состояние может меняться со временем без новых записей (из-за репликации).
• Eventual consistency — со временем все реплики сойдутся.

BASE — это, по сути, философия AP-систем: пожертвовать строгой консистентностью ради доступности и горизонтального масштаба.

⚠️ Ловушка: «NoSQL не умеет ACID» — устаревший миф. MongoDB с 4.0 поддерживает многодокументные ACID-транзакции, Redis имеет MULTI/EXEC. Но эти возможности часто ограничены (производительность, область действия), и злоупотреблять ими в распределённом сеттинге не стоит.

Короткий ответ: Strong consistency — после записи любое чтение сразу видит новое значение. Eventual consistency — реплики синхронизируются с задержкой, поэтому чтение может временно вернуть устаревшие данные, но «в итоге» все увидят актуальное.

Подробно:

• Strong consistency — линейная история, кажется будто есть единственная копия данных. Цена: выше latency, ниже доступность при сбоях (нужно подтверждение от кворума/всех реплик).
• Eventual consistency — запись подтверждается быстро, распространяется асинхронно. Окно несогласованности — обычно миллисекунды-секунды. Цена: приложение должно уметь жить с устаревшими данными.

Между ними есть промежуточные модели: read-your-writes, monotonic reads, causal consistency.

Пример eventual consistency: вы поставили лайк, но друг на другом континенте видит счётчик старым ещё секунду — это нормально для соцсети, недопустимо для банковского баланса.

Многие системы настраиваемы: Cassandra/DynamoDB позволяют задавать уровень консистентности на каждый запрос (ONE, QUORUM, ALL). Правило кворума: если R + W > N (read + write реплики > всего реплик), получаем строгую консистентность.

⚠️ Ловушка: Eventual consistency коварна в read-after-write сценариях: пользователь сохранил профиль, тут же открыл его и увидел старое — баг с точки зрения UX. Решение — читать с primary/leader или использовать read-your-writes.

Короткий ответ: Redis (REmote DIctionary Server) — это in-memory key-value хранилище данных, используемое как кэш, БД, брокер сообщений и очередь. Хранит данные в оперативной памяти, поэтому очень быстрый (sub-millisecond latency).

Подробно:

• In-memory — все данные в RAM, отсюда скорость. Есть опциональная персистентность на диск.
• Не просто строки — поддерживает богатый набор структур данных (см. ниже).
• Однопоточный для команд (event loop), что упрощает модель и убирает блокировки.
• Атомарные операции, TTL на ключи, pub/sub, скрипты на Lua, транзакции.

redis-cli SET user:1:name "Alice"
redis-cli GET user:1:name        # "Alice"
redis-cli SET session:abc "data" EX 3600   # с TTL 1 час
redis-cli TTL session:abc        # 3600

⚠️ Ловушка: Redis по умолчанию держит весь датасет в RAM. Это не «бесконечное» хранилище — нужно следить за maxmemory и политикой вытеснения, иначе OOM.

Короткий ответ: Потому что узкое место — не CPU, а память и сеть. Однопоточность убирает накладные расходы на блокировки, переключение контекста и race conditions, а данные в RAM + эффективный event loop (epoll/kqueue) дают sub-ms скорость.

Подробно: Причины скорости при одном потоке:

1. Данные в RAM — нет дисковых I/O при операциях (доступ к памяти на порядки быстрее диска).
2. Нет блокировок и контеншена — один поток = нет мьютексов, нет cache-line bouncing между ядрами, нет дедлоков.
3. I/O-мультиплексирование — epoll/kqueue обрабатывает тысячи соединений в одном цикле без потоков на соединение.
4. Эффективные структуры данных — оптимизированные реализации (skip lists для ZSet, специальные кодировки для малых коллекций).
5. Простая модель — каждая команда атомарна «бесплатно», нет сложной синхронизации.

Нюансы:

• «Однопоточный» относится к выполнению команд. С Redis 6+ есть threaded I/O (чтение/запись в сокеты в несколько потоков), а фоновые задачи (persistence, удаление больших ключей через UNLINK) и так в отдельных потоках.
• Для использования всех ядер запускают несколько инстансов Redis (или Cluster).

⚠️ Ловушка: Одна «тяжёлая» команда блокирует весь сервер. KEYS * на большой базе, SMEMBERS на огромном Set, или сортировка большого списка остановят обработку всех остальных клиентов. Используйте SCAN, избегайте O(N) команд на больших структурах.

Короткий ответ: String, List, Hash, Set, Sorted Set (ZSet), Stream, плюс «вероятностные»/специальные — HyperLogLog, Bitmap, Geo. Каждая закрывает свой класс задач.

Подробно:

String — простейший тип (текст, число, бинарь до 512 МБ). Кэш, счётчики, флаги.

SET counter 0
INCR counter            # атомарный инкремент → 1
INCRBY counter 10       # → 11
APPEND log "line\n"

List — связный список строк. Очереди, стек, последние N элементов.

LPUSH queue "job1"      # добавить слева
RPUSH queue "job2"      # добавить справа
RPOP queue              # забрать справа (FIFO с LPUSH)
LRANGE queue 0 -1       # все элементы
BLPOP queue 5           # блокирующее извлечение (для воркеров)

Hash — словарь поле→значение внутри ключа. Объекты/сущности.

HSET user:1 name "Alice" age 30
HGET user:1 name        # "Alice"
HGETALL user:1
HINCRBY user:1 age 1    # → 31

Set — неупорядоченное множество уникальных строк. Теги, уникальные посетители, операции над множествами.

SADD tags:post:1 "redis" "nosql"
SISMEMBER tags:post:1 "redis"   # 1
SINTER tags:post:1 tags:post:2  # пересечение
SCARD tags:post:1               # размер

Sorted Set (ZSet) — множество с числовым score, отсортировано. Лидерборды, очереди с приоритетом, time-series, rate limiting.

ZADD leaderboard 100 "player1" 250 "player2"
ZINCRBY leaderboard 50 "player1"     # → 150
ZREVRANGE leaderboard 0 9 WITHSCORES # топ-10
ZRANK leaderboard "player1"          # ранг игрока

Stream — append-only лог записей (как Kafka-lite). Event sourcing, очереди с consumer groups, надёжная доставка.

XADD events * type "click" user "1"          # * = авто-ID
XREAD COUNT 10 STREAMS events 0
XGROUP CREATE events workers $
XREADGROUP GROUP workers w1 COUNT 1 STREAMS events >

HyperLogLog — вероятностный подсчёт уникальных элементов с ~0.81% погрешностью на ~12 КБ. Уникальные посетители за день при миллионах значений.

PFADD visitors "user1" "user2" "user3"
PFCOUNT visitors        # приблизительное кол-во уникальных

Bitmap — биты на ключе. Флаги активности (был ли юзер онлайн в день X), компактные множества по id.

SETBIT active:2026-06-24 1001 1   # юзер 1001 был активен
GETBIT active:2026-06-24 1001     # 1
BITCOUNT active:2026-06-24        # сколько активных

⚠️ Ловушка: Хранить большие JSON в String вместо Hash — теряете частичные обновления (придётся читать/парсить/писать весь объект). И наоборот, очень много мелких ключей вместо Hash раздувают память на оверхеде ключей.

Короткий ответ: Кэш, хранение сессий, rate limiting, лидерборды (ZSet), очереди задач (List/Stream), pub/sub, распределённые блокировки.

Подробно:

1. Кэш — самое частое. Кладём результаты запросов/вычислений с TTL.

SET cache:user:1 "{...json...}" EX 300

2. Сессии — серверные сессии в распределённом приложении (несколько инстансов делят одно хранилище).

SETEX session:token123 1800 "{userId: 1, role: admin}"

3. Rate limiting — ограничение частоты запросов.

# Fixed window: счётчик на окно
INCR rate:user:1:minute
EXPIRE rate:user:1:minute 60   # если >limit — блокируем

Для sliding window используют ZSet с timestamp'ами.

4. Leaderboard — рейтинги в реальном времени через ZSet (см. выше ZADD/ZREVRANGE).

5. Очереди — LPUSH + BRPOP для простых очередей, Streams + consumer groups для надёжных.

6. Pub/Sub — рассылка сообщений подписчикам (чаты, нотификации, инвалидация кэша).

SUBSCRIBE news        # подписчик
PUBLISH news "hello"  # издатель

⚠️ Pub/Sub в Redis — fire-and-forget: если подписчика нет в момент публикации, сообщение теряется. Нужна гарантия доставки — Streams.

7. Распределённые блокировки — SET key token NX PX ttl даёт ограниченную по времени аренду. Освобождать её нужно Lua-скриптом только при совпадении token; для операций, где просроченный владелец опасен, дополнительно нужны монотонные fencing tokens.

⚠️ Ловушка: Использовать Redis Pub/Sub как полноценную очередь сообщений — ошибка. Нет персистентности, нет подтверждений, нет повторов. Для очередей — List или Streams.

Короткий ответ: Простая блокировка — SET key value NX PX ttl (атомарно: установить если не существует, с TTL). Снятие — через Lua-скрипт, проверяющий владельца. Redlock — алгоритм для блокировки поверх нескольких независимых Redis-узлов для повышенной надёжности.

Подробно:

Простая блокировка (один Redis):

# Захват: только если ключа нет, с уникальным токеном и TTL
SET lock:resource <random-token> NX PX 30000

-- Освобождение: удалить, только если мы владелец (атомарно)
if redis.call("get", KEYS[1]) == ARGV[1] then
    return redis.call("del", KEYS[1])
else
    return 0
end

• NX — не перезаписать чужую блокировку.
• PX ttl — авто-снятие, чтобы не было вечной блокировки при падении клиента.
• Уникальный токен — чтобы не снять блокировку, которую перехватил другой после истечения TTL.

Redlock (несколько узлов): алгоритм Антиреза для N независимых мастеров (без репликации между ними):

1. Получить текущее время.
2. Последовательно попытаться захватить блокировку на всех N узлах с одним токеном и малым таймаутом.
3. Блокировка считается захваченной, если получена на большинстве (N/2+1) узлов И суммарное время < TTL.
4. Эффективный TTL = исходный TTL минус потраченное время.
5. Если не удалось — освободить все узлы.

⚠️ Ловушка: Redlock спорен (критика Мартина Клеппманна): из-за GC-пауз, рассинхрона часов и сетевых задержек двое могут одновременно считать, что владеют блокировкой. Для корректности (а не оптимизации) нужен fencing token — монотонный счётчик, который проверяет защищаемый ресурс. Redis-блокировки хороши для эффективности, но не как единственная гарантия mutual exclusion в критичных системах.

Короткий ответ: Два механизма: RDB — периодические снапшоты всего датасета (компактно, быстрый рестарт, но теряются данные между снапшотами). AOF — лог всех команд записи (durability выше, но файл больше и рестарт медленнее). Можно использовать оба.

Подробно:

RDB (Redis Database):

• Бинарный снапшот в момент времени (SAVE/BGSAVE, по расписанию save 900 1).
• BGSAVE форкает процесс — снапшот делается в фоне (copy-on-write).
• + Компактный, быстрая загрузка при рестарте, минимальное влияние на производительность.
• − При падении теряются данные с момента последнего снапшота (минуты).

AOF (Append Only File):

• Логирует каждую команду, изменяющую данные.
• Политики fsync: always (каждая запись, самый durable, медленно), everysec (раз в секунду — компромисс, потеря ≤1 сек), no (на усмотрение ОС).
• Периодически делается rewrite — компактизация лога.
• + Выше durability, лог человекочитаем.
• − Файл крупнее RDB, рестарт медленнее, выше нагрузка на диск.

Что теряется:

• Только RDB → данные с последнего снапшота (например, до 15 минут).
• AOF everysec → до ~1 секунды.
• AOF always → практически ничего, но медленно.

Рекомендация: включить оба — AOF для durability + RDB для быстрых бэкапов/рестарта. Современный Redis (7+) умеет multi-part AOF (RDB-preamble + инкрементальный лог).

⚠️ Ловушка: Считать Redis надёжным как основная БД «из коробки» опасно. Даже с AOF everysec можно потерять секунду данных, а при always — просесть в производительности. Для критичных данных Redis — кэш/ускоритель, а source of truth — durable БД.

Короткий ответ: maxmemory задаёт лимит RAM. Когда он достигнут, Redis применяет политику вытеснения (eviction policy) — какие ключи удалять: по LRU, LFU, TTL, случайно или вообще не удалять (возвращать ошибку).

Подробно:

Параметр maxmemory 2gb + maxmemory-policy <policy>. Политики:

Политика	Что делает
noeviction	Не удаляет, новые записи → ошибка (дефолт). Хорошо для «Redis как БД»
allkeys-lru	Вытесняет наименее недавно используемые из всех ключей. Классика для кэша
volatile-lru	LRU только среди ключей с TTL
allkeys-lfu	Наименее часто используемые из всех (Least Frequently Used)
volatile-lfu	LFU среди ключей с TTL
allkeys-random	Случайные из всех
volatile-random	Случайные среди ключей с TTL
volatile-ttl	Ключи с ближайшим истечением TTL

LRU vs LFU:

• LRU — выкидывает давно не использованные. Уязвим к «всплескам» (один редкий скан вытеснит горячие данные).
• LFU (Redis 4+) — учитывает частоту использования, лучше держит реально горячие ключи. Обычно предпочтительнее для кэша.

Redis использует приближённый LRU/LFU (сэмплирует несколько ключей, не сканирует все) — компромисс точность/скорость, настраивается maxmemory-samples.

⚠️ Ловушка: Дефолтная политика — noeviction. Если используете Redis как кэш и не сменили её, при заполнении памяти записи начнут падать с ошибкой OOM command not allowed, а не «само-очищаться». Для кэша явно ставьте allkeys-lru/allkeys-lfu.

Короткий ответ: Репликация — копии master→replica (масштаб чтения, бэкап). Sentinel — система мониторинга и автоматического failover (HA для одного шарда). Cluster — горизонтальное шардирование данных по узлам + встроенный HA.

Подробно:

Репликация (master-replica):

• Один master принимает записи, реплики копируют данные асинхронно.
• Реплики обслуживают чтения → масштаб чтения.
• Асинхронность → возможна потеря записей при падении master до репликации (eventual consistency).
• Сама по себе не делает автоматический failover.

Sentinel:

• Набор процессов-наблюдателей, которые мониторят master и реплики.
• При падении master по кворуму sentinel'ов выбирают новую реплику мастером (automatic failover).
• Клиенты узнают новый адрес master через Sentinel.
• Подходит для HA, но данные не шардируются — весь датасет должен помещаться на одном узле.

Cluster:

• Данные шардируются по 16384 hash slots, распределённым между master-узлами.
• Ключ → CRC16(key) mod 16384 → слот → узел.
• Каждый master имеет свои реплики (встроенный HA + failover).
• Горизонтальное масштабирование и записи, и объёма данных.
• Ограничения: мультиключевые операции работают только если ключи в одном слоте (используют hash tags {user1}:profile, {user1}:settings).

Standalone + Replication:  [master] → [replica] [replica]   (масштаб чтения)
Sentinel:                  [sentinel x3] следят, failover    (HA, 1 шард)
Cluster:                   [m1+r][m2+r][m3+r], 16384 слотов   (шардинг + HA)

⚠️ Ловушка: В Cluster нельзя просто так делать операции над несколькими ключами (MGET, транзакции, Lua с разными ключами), если они в разных слотах — будет ошибка CROSSSLOT. Нужно проектировать ключи с hash tags, чтобы связанные данные жили в одном слоте.

Короткий ответ: Cache-aside (lazy loading) — приложение само управляет кэшем. Read-through/write-through — кэш сам читает/пишет в БД синхронно. Write-behind (write-back) — кэш пишет в БД асинхронно.

Подробно:

Cache-aside (lazy loading) — самый распространённый:

def get_user(user_id):
    data = redis.get(f"user:{user_id}")
    if data is None:                      # cache miss
        data = db.query_user(user_id)     # читаем из БД
        redis.set(f"user:{user_id}", data, ex=300)  # кладём в кэш
    return data

• Приложение отвечает за загрузку и инвалидацию.
• В кэш попадает только запрашиваемое (lazy).
• − Первый запрос всегда miss; возможна несогласованность кэша и БД.

Read-through — кэш сам подгружает данные из БД при miss (логика в слое кэша/библиотеке). Приложение всегда обращается только к кэшу.

Write-through — при записи приложение пишет в кэш, а кэш синхронно пишет в БД.

• + Кэш всегда консистентен с БД.
• − Запись медленнее (две операции); кэшируются и редко читаемые данные.

Write-behind (write-back) — запись в кэш, а в БД — асинхронно (батчами/с задержкой).

• + Очень быстрые записи, можно агрегировать.
• − Риск потери данных при падении кэша до flush; сложнее.

Паттерн	Чтение	Запись	Риск
Cache-aside	App ↔ Cache ↔ DB	App → DB (+ инвалидация)	Stale data, miss penalty
Read-through	App ↔ Cache → DB	—	—
Write-through	—	App → Cache → DB (sync)	Медленнее запись
Write-behind	—	App → Cache → DB (async)	Потеря данных

⚠️ Ловушка: В cache-aside при записи частая ошибка — обновлять кэш вместо его удаления. При гонке (два конкурентных апдейта) кэш может остаться со старым значением навсегда. Безопаснее инвалидировать (удалить) ключ при записи в БД, а не перезаписывать.

Короткий ответ: Сложность в том, чтобы вовремя и точно убрать/обновить устаревшие данные во всех местах, где они закэшированы, не словив гонок, не оставив stale-данных и не обрушив БД массовыми промахами. Это фундаментально про консистентность в распределённой системе.

Подробно: Известная цитата Фила Карлтона: «There are only two hard things in Computer Science: cache invalidation and naming things.»

Почему трудно:

1. Гонки (race conditions): между чтением из БД, записью в кэш и обновлением БД другими потоками легко закэшировать устаревшее значение. Классическая проблема: read miss загружает старое значение в кэш сразу после того, как другой поток уже обновил БД и инвалидировал кэш.
2. Множество копий: одни данные могут лежать в кэше приложения, Redis, CDN, браузере — инвалидировать нужно везде.
3. Зависимости: изменение одной сущности может затронуть много производных кэшей (агрегаты, списки, денормализованные представления). Трудно отследить «что протухло».
4. Баланс TTL: короткий TTL → много промахов и нагрузка на БД; длинный → дольше stale-данные.

Стратегии инвалидации:

• TTL (expiration) — простейшая: ключ живёт N секунд. «Eventual consistency» для кэша. Просто, но допускает временную устарелость.
• Explicit invalidation — удалять ключ при записи (delete-on-write). Точнее, но сложнее (нужно знать все ключи).
• Write-through — кэш всегда синхронизирован, но медленнее.
• Versioning / key namespacing — менять часть ключа при изменении (user:1:v2), старые сами вытеснятся.
• Event-based — инвалидация через события/pub-sub (например, CDC из БД).

⚠️ Ловушка: «Просто поставлю TTL» работает, пока бизнес не потребует «сразу видеть изменения». А explicit invalidation легко забыть в каком-то из путей записи (особенно при батчах, миграциях, прямых апдейтах в БД). Лучший дефолт — TTL + явная инвалидация на горячих путях.

Короткий ответ: Hit ratio = доля запросов, обслуженных из кэша, от общего числа. hit_ratio = hits / (hits + misses). Чем выше — тем эффективнее кэш разгружает источник данных.

Подробно:

• Hit — данные нашлись в кэше (быстро, без обращения к БД).
• Miss — в кэше нет, идём в БД (медленно) и (обычно) кладём в кэш.
• Высокий hit ratio (например, 90%+) означает, что БД получает лишь 10% запросов.

В Redis метрики смотрят через INFO stats:

redis-cli INFO stats | grep keyspace
# keyspace_hits:100000
# keyspace_misses:5000
# hit_ratio = 100000 / 105000 ≈ 95.2%

Низкий hit ratio сигнализирует: слишком короткий TTL, неправильные ключи, слишком маленький кэш (частые вытеснения), или плохая локальность доступа (данные слабо повторяются).

⚠️ Ловушка: Гнаться за hit ratio = 100% бессмысленно: для холодных/редких данных кэш бесполезен и лишь тратит память. Важнее кэшировать «горячие» данные (Парето: 20% данных дают 80% запросов). Также высокий hit ratio на устаревших данных — это «хорошая метрика, плохой результат».

Короткий ответ: Cache stampede (он же thundering herd, dogpile) — когда популярный ключ истекает, и множество запросов одновременно промахиваются и разом бьют в БД, перегружая её. Борются locking, early recomputation, фоновым обновлением.

Подробно: Сценарий: горячий ключ с TTL истёк. В этот момент тысяча параллельных запросов все видят miss → все идут в БД пересчитывать одно и то же → всплеск нагрузки, возможно падение БД.

Методы борьбы:

1. Locking / mutex (single-flight): первый промахнувшийся берёт блокировку и пересчитывает, остальные ждут результат или отдают старое значение.

def get(key):
    val = redis.get(key)
    if val: return val
    if redis.set(f"lock:{key}", 1, nx=True, ex=10):  # только один пересчитывает
        val = recompute()
        redis.set(key, val, ex=300)
        redis.delete(f"lock:{key}")
        return val
    else:
        time.sleep(0.05)            # подождать и перечитать
        return get(key)

2. Probabilistic early expiration — обновлять ключ до истечения с вероятностью, растущей по мере приближения TTL (алгоритм XFetch). Размазывает пересчёт.
3. Background refresh — фоновый воркер обновляет горячие ключи по расписанию, кэш «никогда не пустеет».
4. Stale-while-revalidate — отдавать устаревшее значение, пока обновление идёт в фоне.

⚠️ Ловушка: Одинаковый TTL для пачки связанных ключей приводит к синхронному истечению и stampede. Добавляйте jitter (случайный разброс TTL, например 300 ± 30 сек).

Короткий ответ: Penetration — запросы к несуществующим данным проходят кэш насквозь в БД. Avalanche — массовое одновременное истечение/падение кэша обрушивает БД. Hot key — один ключ получает непропорционально много трафика, перегружая узел.

Подробно:

Cache penetration (проникновение): Запрашивают данные, которых нет ни в кэше, ни в БД (например, несуществующий id, или атака перебором). Каждый раз miss → удар по БД, кэш не помогает.

• Решение 1: кэшировать «пустой» результат (null/sentinel) с коротким TTL.
• Решение 2: Bloom filter — проверять существование ключа до обращения к БД.

SET cache:user:99999 "__NULL__" EX 60   # негативное кэширование

Cache avalanche (лавина): Большое количество ключей истекает одновременно, ИЛИ кэш-сервер падает → весь трафик разом идёт в БД → каскадный отказ.

• Решение 1: jitter в TTL (разброс времени истечения).
• Решение 2: многоуровневый кэш (L1 локальный + L2 Redis).
• Решение 3: circuit breaker / rate limiting к БД, graceful degradation.
• Решение 4: HA-кластер Redis, чтобы падение одного узла не убирало весь кэш.

Hot key (горячий ключ): Один ключ (например, товар на распродаже, знаменитость) собирает огромную долю запросов → один узел/слот кластера перегружен.

• Решение 1: локальный кэш на стороне приложения для топ-ключей.
• Решение 2: реплицировать ключ с суффиксами (hot:1#1, hot:1#2) и читать случайную реплику.
• Решение 3: реплики для чтения.

Проблема	Суть	Лечение
Penetration	Запрос к несуществующим данным	Null-кэш, Bloom filter
Avalanche	Массовое одновременное истечение/падение	TTL jitter, HA, circuit breaker
Stampede	Гонка за пересчёт одного горячего ключа	Lock, background refresh
Hot key	Перекос трафика на один ключ	Локальный кэш, репликация ключа

⚠️ Ловушка: Penetration и avalanche часто путают. Penetration — про несуществующие данные (кэш бесполезен по сути), avalanche — про массовое истечение/сбой (кэш был, но «обвалился»). Лечатся по-разному.

Короткий ответ: На всём пути запроса: браузер клиента → CDN → reverse proxy / API gateway → кэш приложения (in-process / Redis) → кэш БД (query/buffer cache). Чем ближе к пользователю — тем быстрее и дешевле, но тяжелее инвалидировать.

Подробно:

Пользователь
   │
   ▼
[1] Браузер (HTTP cache, localStorage)      — самый быстрый, у клиента
   │
   ▼
[2] CDN (CloudFront, Cloudflare)            — статика, edge, географически близко
   │
   ▼
[3] Reverse proxy / API gateway (Nginx, Varnish)  — кэш ответов
   │
   ▼
[4] Кэш приложения:
      - In-process (локальная память: Caffeine, LRU map)  — L1, наносекунды
      - Распределённый (Redis, Memcached)                 — L2, разделяемый
   │
   ▼
[5] База данных (buffer pool, query cache)   — встроенное кэширование БД
   │
   ▼
   Диск

Trade-off: чем выше уровень (ближе к пользователю), тем ниже latency и нагрузка на бэкенд, но сложнее инвалидация (как обновить кэш в браузерах тысяч пользователей? — только через TTL/версионирование URL).

HTTP-кэширование (уровни 1-3) управляется заголовками: Cache-Control, ETag, Last-Modified, max-age.

Многоуровневый кэш приложения (L1+L2): локальный in-process кэш для самых горячих данных (минимум сети) + Redis как общий L2. Защищает от avalanche и hot key.

⚠️ Ловушка: Кэширование на нескольких уровнях умножает проблему инвалидации. Изменили данные — а они закэшированы в Redis, в локальной памяти 10 инстансов и в CDN. Нужна продуманная стратегия (события/версии), иначе пользователи видят рассогласованные данные с разных уровней.

Короткий ответ: MongoDB — документная NoSQL БД. Данные хранятся как документы (JSON-подобные) в формате BSON, документы группируются в коллекции (аналог таблиц), коллекции — в базах. Схема гибкая.

Подробно:

• Документ — набор пар ключ-значение, может иметь вложенные объекты и массивы. Аналог строки, но богаче.
• Коллекция — группа документов. Документы в одной коллекции могут иметь разные поля (schemaless).
• BSON (Binary JSON) — бинарный формат хранения: компактнее и быстрее JSON, поддерживает доп. типы (ObjectId, Date, Decimal128, binary).

// Документ в коллекции users
{
  "_id": ObjectId("..."),
  "name": "Alice",
  "age": 30,
  "addresses": [                    // вложенный массив
    { "city": "Moscow", "zip": "101000" }
  ],
  "tags": ["admin", "premium"]
}

db.users.insertOne({ name: "Bob", age: 25 })
db.users.find({ age: { $gt: 20 } })

Когда подходит MongoDB:

• Данные естественно «документные» (профили, каталоги товаров, CMS-контент).
• Меняющаяся/гибкая схема, быстрая итерация.
• Агрегаты читаются/пишутся целиком (документ = единица доступа).
• Нужно горизонтальное масштабирование с шардингом.

⚠️ Ловушка: Каждый документ ограничен 16 МБ. Безграничный рост вложенного массива (например, все комментарии внутри документа поста) рано или поздно упрётся в лимит и убьёт производительность. Это сигнал выносить в отдельную коллекцию.

Короткий ответ: Индексы (B-tree, как в SQL) ускоряют поиск/сортировку; без них — полный скан коллекции. Aggregation pipeline — конвейер стадий ($match, $group, $sort, $lookup...) для трансформации и агрегации данных, аналог GROUP BY/JOIN в SQL.

Подробно:

Индексы:

db.users.createIndex({ email: 1 }, { unique: true })   // одиночный, уникальный
db.users.createIndex({ age: 1, name: -1 })             // составной
db.posts.createIndex({ title: "text" })                // текстовый
db.places.createIndex({ location: "2dsphere" })        // гео

Без индекса запрос делает COLLSCAN (полный скан). explain() показывает план.

Aggregation pipeline — последовательность стадий, данные «текут» через них:

db.orders.aggregate([
  { $match: { status: "completed" } },          // фильтр (как WHERE)
  { $group: {                                    // группировка (как GROUP BY)
      _id: "$customerId",
      total: { $sum: "$amount" },
      count: { $sum: 1 }
  }},
  { $sort: { total: -1 } },                      // сортировка
  { $limit: 10 }
])

⚠️ Ловушка: Стадии порядок важен для производительности. $match и $limit должны идти как можно раньше в pipeline, чтобы сократить объём данных до тяжёлых стадий ($group, $lookup). $match в начале использует индексы, в середине — уже нет.