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, id 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;

Добавляйте уникальный id в сортировку: одинаковые timestamps сами по себе не задают стабильную границу страницы.

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

Подробно:

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

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

⚠️ Ловушка: условие справа, отвергающее NULL, например d.title = 'IT', в WHERE удаляет строки без совпадений. Переносите такой фильтр в ON, если хотите сохранить все левые строки. Это не запрет на любые условия справа в WHERE: WHERE d.id IS NULL выше специально ищет строки без совпадений.

-- НЕПРАВИЛЬНО: фильтр в 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;

⚠️ Ловушка: выбранный столбец должен входить в группировку, агрегат или функционально зависеть от группы. PostgreSQL признаёт последний случай при группировке по первичному ключу таблицы: SELECT id, name, COUNT(*) FROM employees GROUP BY id допустим, если id — PK. Группировка только по неуникальному отделу не определяет одно имя сотрудника. MySQL отклоняет неоднозначные группы при включённом ONLY_FULL_GROUP_BY; отключение этого режима может дать произвольное значение.

Короткий ответ: 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 не может агрегировать на том же уровне запроса — для этого нужен HAVING. Фильтр по столбцам группировки оптимизатор иногда переносит вниз, поэтому HAVING не обязательно медленнее; для отбора строк пишите WHERE, чтобы намерение было ясным.

Короткий ответ: 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, сравнение без совпадения становится UNKNOWN, а не TRUE. В качестве условия WHERE это может убрать все строки. Одна лишь возможность NULL в схеме такого результата не означает. Для отсутствия совпадающей строки обычно яснее коррелированный NOT EXISTS; поведение NULL во внешней строке определите отдельно.

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

Подробно:

-- Сотрудники с зарплатой выше средней ПО ИХ отделу
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 служили оптимизационным барьером. В PG12+ нерекурсивный SELECT CTE без побочных эффектов, на который ссылаются один раз, обычно встраивается во внешний запрос. Несколько ссылок обычно ведут к материализации; NOT MATERIALIZED разрешает встраивание подходящего CTE, а MATERIALIZED запрещает его. Рекурсивные CTE и CTE с volatile-функциями не подпадают под простое правило. Смотрите план: WITH само по себе не ускорение.

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

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

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

  UNION ALL

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

⚠️ Ловушка: без проверки path рекурсия с UNION ALL зациклилась бы на цикле. Запрос начинает путь с менеджера, дополняет его на каждом шаге и пропускает уже посещённые в этом пути id. PostgreSQL также поддерживает CYCLE. Ограничение глубины — страховка, а не доказательство полного обхода иерархии.

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

Подробно: 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 строк назад; default применяется только при отсутствии строки, а не при 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 — частичные зависимости неключевых атрибутов от любого кандидатного ключа; 3NF ограничивает транзитивные зависимости; BCNF требует, чтобы любая детерминанта нетривиальной зависимости была суперключом.

Пример: один товар встречается в заказе один раз, у заказа один клиент, а id клиента определяет его имя:

order_line(order_id, product_id, quantity, customer_id, customer_name)
кандидатный ключ = (order_id, product_id)
order_id → customer_id
customer_id → customer_name

1. 1NF: товары представлены строками, а не повторяющимися столбцами product1, product2. Атомарность зависит от выбранного домена; поддержка массивов в СУБД не отменяет проектирование модели.
2. 2NF: выносим атрибуты заказа, зависящие лишь от order_id, части кандидатного ключа: order_line(order_id, product_id, quantity) и orders(order_id, customer_id, customer_name).
3. 3NF: отделяем customers(customer_id, customer_name) от orders(order_id, customer_id). Формально для любой нетривиальной зависимости X → A либо X — суперключ, либо A — ключевой атрибут, входящий хотя бы в один кандидатный ключ.
4. BCNF: в teaching(student, course, teacher) предположим (student, course) → teacher и teacher → course. Кандидатные ключи — (student, course) и (student, teacher): это 3NF, но не BCNF, поскольку teacher не суперключ. Разбиение на teacher_course(teacher, course) и student_teacher(student, teacher) не теряет данные при соединении, однако правило «один преподаватель у студента по курсу» теперь требует отдельного контроля.

⚠️ Ловушка: одностолбцовый primary key не доказывает 2NF, если есть другой составной кандидатный ключ. Проверяйте все кандидатные ключи и сначала формулируйте бизнес-зависимости.

Короткий ответ: денормализуют ради производительности чтения — дублируют данные, чтобы избежать дорогих 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
);

⚠️ Ловушка: PostgreSQL создаёт индекс для PK/UNIQUE. По умолчанию UNIQUE считает NULL различными, поэтому допускает несколько NULL; в PG15+ это меняет UNIQUE NULLS NOT DISTINCT. Это правило уникальности, а не результат NULL != NULL (он UNKNOWN). PK дополнительно запрещает NULL.

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

Подробно:

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

⚠️ Ловушка: (a, b) и (b, a) обеспечивают одинаковую уникальность пары, но дают разные пути доступа B-tree. Фильтр по первым столбцам обычно лучше сужает сканирование. Фильтр только по b всё же может использовать (a, b); в PG18 возможен skip scan, когда небольшое число различных a делает повторные поиски выгодными. Решение об отдельном индексе 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 подставляет пропущенное значение; в PostgreSQL это не ограничение целостности и не запрет явно переданного значения.

Подробно:

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 + явное удаление в коде. Также каскады усложняют отладку и могут вызвать дедлоки при конкурентных удалениях.

В PostgreSQL проверку NO ACTION можно отложить для DEFERRABLE FK; RESTRICT — нельзя. SET NULL/SET DEFAULT по-прежнему должны удовлетворять ограничениям дочерних столбцов и внешнему ключу: неверное значение по умолчанию не обходит ссылочную целостность.

Короткий ответ: 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-изоляцию и фантомы тоже исключает; попытка изменить строку, которую другая транзакция изменила после snapshot, вызывает ошибку сериализации.

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

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

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

PostgreSQL исполняет READ UNCOMMITTED как READ COMMITTED. Repeatable Read всё ещё допускает аномалии сериализации, например write skew: две транзакции меняют разные строки и вместе нарушают правило для нескольких строк. SERIALIZABLE обнаруживает опасные сочетания; при SQLSTATE 40001 повторяйте транзакцию целиком с новым snapshot.

Короткий ответ: ACID описывает гарантии транзакций, а уровень изоляции — допустимые истории конкурентного исполнения. Подбирайте уровень и протокол записи, сохраняющие инварианты операции, затем измеряйте их стоимость.

В PostgreSQL READ COMMITTED даёт новый snapshot каждому запросу. Этого может хватить для атомарного условного UPDATE, но отчёт из нескольких запросов увидит разные зафиксированные состояния. REPEATABLE READ даёт стабильный snapshot, однако допускает write skew. SERIALIZABLE делает результат зафиксированных транзакций эквивалентным некоторому последовательному порядку и может отменить транзакцию, которую нужно повторить.

Деньги не становятся защищёнными от одного повышения уровня: нужны ограничения, идемпотентность, правила для нескольких строк и обработка ретраев. И наоборот, SERIALIZABLE не обязан резко снижать throughput: цена зависит от нагрузки и конфликтов.

⚠️ Ловушка: повторяйте всю отменённую транзакцию с новым snapshot. Внешние эффекты, например отправка платежа, должны оставаться безопасными при повторе.

Короткий ответ: pessimistic — заранее блокируем строку (SELECT ... FOR UPDATE), считая конфликт вероятным; optimistic — не держим lock между ранним чтением и решением пользователя, а при записи проверяем изменение (версия/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 требует, чтобы приложение умело ретраить неудавшийся апдейт.

Оптимистичный подход не держит lock между ранним чтением и решением пользователя; условный UPDATE всё ещё блокирует строки при исполнении. Version защищает от конкурентного изменения, не от нарушения бизнес-правила: при retry снова проверяйте остаток/баланс.

Короткий ответ: 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 затрагивают все подходящие строки. До выполнения проверьте условие. В PostgreSQL TRUNCATE транзакционен и откатывается; он берёт ACCESS EXCLUSIVE, запускает ON TRUNCATE вместо ON DELETE триггеров и не MVCC-safe для старых snapshots. Правила других СУБД отличаются.

Короткий ответ: 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.

PostgreSQL тоже поддерживает MERGE (PG15+), но он не равнозначен конкурентно безопасному UPSERT: параллельная вставка может привести к ошибке уникальности. Для insert-or-update по ключу при конкуренции именно ON CONFLICT DO UPDATE даёт специальную атомарную гарантию.

Короткий ответ: обычный VIEW — сохранённый запрос (вычисляется при каждом обращении, данные берутся из snapshot текущего запроса); 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; -- разрешает SELECT; нужен подходящий полный UNIQUE-индекс

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

Обычный VIEW читает в рамках snapshot транзакции, а не обязательно самый новый commit по настенным часам. CONCURRENTLY требует уже заполненное materialized view и UNIQUE-индекс по обычным столбцам, покрывающий все строки; обновления одного представления выполняются по очереди.

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

Подробно: ускоряет 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);  -- составной

⚠️ Ловушка: обычный B-tree обычно не сужает поиск по lower(name) или шаблону с ведущим wildcard. Помочь могут индекс выражения или подходящий pg_trgm-индекс. Для большой доли строк выгоднее sequential scan; малое число разных значений само по себе не исключает индекс для редкого значения. Проверяйте EXPLAIN.

Короткий ответ: 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 транзакционен и откатывается.

Короткий ответ: Сначала уточните: нужны все сотрудники со второй различной не-NULL зарплатой или один второй сотрудник в отсортированном списке? Это разные задачи.

-- Все сотрудники со второй различной зарплатой; равенства сохраняются.
SELECT name, salary FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2;

-- Только значение зарплаты, не сами сотрудники:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- DISTINCT учитывает одинаковые зарплаты для задачи о значении:
SELECT DISTINCT salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC LIMIT 1 OFFSET 1;

Если различных не-NULL зарплат меньше двух, rank/DISTINCT не вернут строк, а MAX вернёт NULL. Для второго сотрудника используйте ORDER BY salary DESC NULLS LAST, id LIMIT 1 OFFSET 1 с уникальным id.

⚠️ Ловушка: при DESC PostgreSQL по умолчанию ставит NULL первыми. Исключайте неизвестные зарплаты или задавайте NULLS LAST; OFFSET по сотрудникам не ищет вторую различную зарплату.

Короткий ответ: 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 IS NOT DISTINCT FROM 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, чтобы убедиться в выборе «лишних» строк.

Эти примеры считают два NULL-email дублями. Если NULL означает неизвестный email, который нужно сохранять независимо, добавьте email IS NOT NULL и в поиск, и в удаление.

Короткий ответ: оконная функция 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 NULLS LAST, id) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 3;

⚠️ Ловушка: ROW_NUMBER даёт до N строк на группу. RANK включает равные значения на N-й позиции; DENSE_RANK выбирает N разных значений метрики и может дать другой результат. Для сохранения ничьих ранжируйте только по метрике: уникальный id в ORDER BY окна устраняет ничьи. Фильтруйте вычисленный ранг во внешнем запросе.

Короткий ответ: Реляционная БД моделирует таблицы и связи через декларативные constraints и SQL. NoSQL — широкое семейство key-value, document, wide-column и graph-систем. Выбирайте конкретный движок по модели и гарантиям, не ярлыку.

PostgreSQL и MySQL подходят связанным бизнес-сущностям, JOIN и меняющимся запросам. PostgreSQL поддерживает JSONB для гибких атрибутов. MongoDB имеет валидацию документов, lookup-соединения и многодокументные транзакции. Key-value и wide-column часто оптимизируют предсказуемый доступ по partition key; graph-движки — обходы связей.

Сравните границы/изоляцию транзакций, consistency чтения, гибкость запросов, распределение partition key, durability и эксплуатацию. Горизонтальное масштабирование возможно в обоих семействах с разной ценой координации и запросов.

⚠️ Ловушка: NoSQL не означает повсеместно schemaless, быстрее, non-ACID или бесплатный scale. Новый движок должен решать измеренную задачу модели/доступа без неявного ослабления инвариантов.

Короткий ответ: Индекс — это отдельная структура данных, которая хранит значения одной или нескольких колонок в упорядоченном/организованном виде вместе с указателями на физическое расположение строк (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 в PostgreSQL — сбалансированное упорядоченное дерево со связанными листьями. Поиск начального ключа требует O(log n) от числа индексных записей, затем работа пропорциональна числу найденных записей. Диапазон читается по листьям и может обойтись без отдельной сортировки; выдача множества строк не занимает O(log n).

Подробно: 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 умеет читать дерево в обратную сторону).

Для текстового LIKE по префиксу пригодность B-tree зависит от коллации; вне C locale может понадобиться text_pattern_ops. Отдельные задачи решают индексы выражений вроде upper(col) и trigram-индексы для ведущего wildcard.

Короткий ответ: 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-индексы PostgreSQL одноколоночные, поддерживают только равенство и не обеспечивают уникальность, в том числе в PG18. Они не поддерживают сортировку и index-only scan. Коллизии требуют проверки значения в heap; сравнивайте с 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 расширяем: operator class определяет хранимые данные и необходимость перепроверки. Bounding box и сигнатуры текстового поиска бывают лоссовыми; GiST не обязательно неточен. Пример бронирований требует btree_gist для равенства room_id, а географический пример — PostGIS и согласованной системы координат.

-- Диапазоны без пересечений (exclusion constraint)
CREATE EXTENSION IF NOT EXISTS btree_gist;
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';

Размер BRIN растёт с числом сводок по диапазонам блоков, а не с одной записью на строку. Он может быть на порядки меньше B-tree, но для миллиарда строк нет фиксированного размера в килобайтах. Выигрыш есть, когда физическая близость данных позволяет исключить много диапазонов.

⚠️ Ловушка: Если физический порядок строк не коррелирует со значением колонки (например, после многих 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::text = '123'            -- преобразуется столбец: обычный bigint-индекс не сужает поиск
-- WHERE user_id = '123' обычно допустим: unknown-литерал приводится к bigint

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

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

Короткий ответ: Составной B-tree упорядочен лексикографически. Равенства по первым столбцам и диапазон по первому столбцу без равенства обычно сильнее всего сужают поиск. Отсутствие первого столбца не запрещает индекс: поздние условия проверяются в нём, а PG18 может выбрать skip scan, когда повторные поиски выгодны.

Подробно:

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'                        -- нет префикса

Условия по последующим столбцам могут экономить обращения в heap; без skip scan они не обязательно уменьшают диапазон сканирования индекса. При малом числе различных ведущих значений skip scan может делать повторные переходы к полезным диапазонам. Сравнивайте реальные планы и buffers для используемой версии PostgreSQL.

⚠️ Ловушка: (a,b) часто хватает для запросов по a, но меньший индекс только по a может иметь другую стоимость. Для запросов только по b отдельный индекс может быть значительно выгоднее. По одному списку столбцов нельзя доказать ни избыточность, ни пользу: нужны нагрузка и EXPLAIN.

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

Подробно: Эвристика «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» обычно сильнее.

⚠️ Ловушка: equality-first — полезная исходная эвристика для таких условий, не закон. Нагрузка с ORDER BY/LIMIT или другими комбинациями запросов может требовать иной порядок. Сравните планы репрезентативных запросов, включая PG18 skip scan, прежде чем называть индекс оптимальным.

Короткий ответ: Покрывающий индекс содержит все колонки, нужные запросу, так что значения можно прочитать из индекса, хотя проверка видимости может обращаться к 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; Heap Fetches показывает, пришлось ли проверять видимость в таблице.

⚠️ Ловушка: index-only — тип плана, а не обещание нуля чтений heap. Метод индекса должен уметь возвращать данные и покрывать запрос; для all-visible страниц не нужна проверка видимости в heap. Остальные страницы всё равно читаются, что видно по Heap Fetches. VACUUM помогает подходящим страницам, но активные snapshots и новые UPDATE могут мешать полной видимости.

Короткий ответ: 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. PostgreSQL lower(text) — стандартный допустимый пример; явная коллация в общем случае не нужна для его индексирования. А now() и преобразования, зависящие от TimeZone/DateStyle сессии, не подходят. После изменения библиотеки коллаций соответствующие индексы может потребоваться перестроить.

Короткий ответ: Уникальный индекс гарантирует, что комбинация значений не повторяется, и одновременно ускоряет поиск. 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), иначе вся транзакция упадёт на первом дубликате.

Короткий ответ: Индексы расходуют место, кэш, WAL и работу по сопровождению. INSERT добавляет записи; многие UPDATE требуют новых записей; DELETE оставляет устаревшие записи для последующей очистки. HOT избегает новых записей в несуммирующих индексах, если индексируемые значения не изменились и новая версия помещается на ту же страницу.

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

-- Найти неиспользуемые индексы (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 блокирует запись; CREATE INDEX CONCURRENTLY допускает обычное чтение/запись, но берёт блокировки, ждёт транзакции, расходует ресурсы и при сбое может оставить невалидный индекс. Внутри транзакционного блока он запрещён. idx_scan = 0 не разрешение удалить индекс: учитывайте период наблюдения, редкие задачи и обеспечение constraints.

Короткий ответ: 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 означает попадание в буферный кэш PostgreSQL; shared read означает загрузку блока в эти буферы, в том числе из page cache ОС, а не обязательно физического диска.

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

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.

Время узла и actual rows усреднены по loops; умножение оценивает общую работу узла, но времена родителя и потомка нельзя складывать как независимые затраты. ROLLBACK отменяет обычные записи EXPLAIN ANALYZE, но не продвижение sequences или внешние эффекты триггеров; изменяющие запросы проверяйте в подходящей безопасной среде.

Короткий ответ: Seq Scan последовательно читает страницы таблицы; родительский узел, например LIMIT, может остановить его раньше. 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: читает входы, упорядоченные по совместимым ключам, и соединяет по merge-joinable равенству. Обычный Merge Join PostgreSQL не реализует произвольное соединение только по диапазону вроде a.x < b.x; при соединении по равенству возможен дополнительный фильтр-неравенство.

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) = ... не подходит.

⚠️ Ловушка: различайте преобразование индексируемого столбца и приведение литерала. В PostgreSQL bigint_col = '123' обычно приводит unknown-литерал к bigint и допускает индекс. bigint_col::text = '123' меняет индексируемое выражение; 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;
CREATE UNIQUE INDEX daily_sales_day ON daily_sales (d);
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) < ($1, $2)   -- курсор
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 будут теряться или дублироваться на границе страниц.

Стабильность предполагает не-NULL и неизменяемые ключи сортировки. UPDATE, перемещающий строку через курсор, всё ещё способен вызвать пропуск или повтор; keyset не создаёт единый snapshot для нескольких HTTP-запросов. Для точно зафиксированного результата нужна отдельная стратегия snapshot/материализации.

Короткий ответ: MVCC хранит несколько версий строки. Обычное чтение использует snapshot вместо ожидания row locks, уменьшая взаимные помехи чтений и записей. Видимость зависит от xmin/xmax, статусов транзакций, флагов tuple и snapshot; табличные locks всё ещё могут блокировать чтения.

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

• xmin — id транзакции, которая создала эту версию.
• xmax — сведения об удаляющей/обновляющей транзакции или блокировке; ноль — частный случай, а не полная проверка видимости.

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) база может остановиться, чтобы не «закольцевать» возраст транзакций.

Описание xmin/xmax упрощено: у видимой строки бывает ненулевой xmax после отменённого DELETE или блокировки строки, а xmax может представлять MultiXact. PostgreSQL также учитывает статус транзакций, snapshots и флаги tuple; xmax = 0 не является бизнес-проверкой существования строки. Обычные MVCC-чтения не ждут row locks, но могут блокироваться табличными DDL-locks.

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

Подробно:

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

Различия:

• VACUUM (обычный): делает место dead tuples переиспользуемым, обновляет visibility map и замораживает старые XID. Обычно файл остаётся выделенным, но пустые страницы в конце можно усечь и вернуть ОС; усечение может кратко требовать ACCESS EXCLUSIVE. Обычная очистка допускает конкурентное чтение/запись.
• 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 планируют, только если допустимы эксклюзивная блокировка и дополнительный диск. Инструменты вроде pg_repack уменьшают длительное блокирование, но имеют свои locks, требования и накладные расходы. Старые snapshots мешают очистке: проверяйте старые транзакции и replication slots.

Короткий ответ: PostgreSQL использует блокировки строк, таблиц и advisory locks. Обычные MVCC-чтения не ждут row locks, но блокировки таблицы могут их остановить. Бэкенд, ожидающий lock в течение deadlock_timeout, проверяет deadlock; при цикле ожиданий PostgreSQL отменяет одну транзакцию.

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

• 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

deadlock_timeout (по умолчанию 1 с) — время ожидания перед проверкой deadlock, а не интервал глобального фонового детектора. Он не ограничивает обычное ожидание блокировок; для этого отдельно служит lock_timeout.

-- Кто кого блокирует
SELECT a.pid AS waiting_pid,
       pg_blocking_pids(a.pid) AS blocking_pids, a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

⚠️ Ловушка: Главная профилактика 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.

Если UPDATE не меняет out-of-line значение, PostgreSQL может переиспользовать TOAST. Предупреждение о дорогой перезаписи относится к изменению большого значения, не любому обновлению другого поля строки.

Короткий ответ: WAL-записи должны попасть в устойчивое хранилище раньше соответствующих изменённых страниц данных. Это правило write-ahead; страницы уже могут быть изменены в памяти. WAL обеспечивает crash recovery, репликацию и point-in-time recovery.

Подробно: При обычных настройках долговечного commit PostgreSQL сбрасывает запись COMMIT в WAL до подтверждения клиенту; страницы таблиц можно записать позже. Recovery воспроизводит WAL и восстанавливает изменения, чьи страницы не успели попасть в хранилище. Отключение fsync или асинхронный commit меняют гарантию durability.

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

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

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).

Синхронность означает ожидание заданного подтверждения от выбранных standby, а не защиту от любых сбоев. remote_write, on (долговечный flush на standby) и remote_apply дают разные гарантии. Чтение на standby после commit всё ещё может отставать, пока apply не догнал. Логическая репликация не копирует автоматически любые DDL и изменения sequences.

Короткий ответ: Декларативное партиционирование разбивает одну логическую таблицу на несколько физических партиций по диапазону, списку или хешу ключа. Это ускоряет запросы (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/DETACH партиции избегает построчного DELETE, но требует блокировок и может ждать; это не безусловно мгновенная операция.
• Меньшие индексы на партицию, лучше vacuum/обслуживание.

Виды: RANGE, LIST, HASH.

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

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

Подробно:

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

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

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

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

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

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

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

⚠️ Ловушка: transaction pooling не сохраняет произвольное состояние сессии: сессионный SET, LISTEN и session advisory locks. Именованные prepared statements уровня протокола поддерживаются с PgBouncer 1.21 при ненулевом max_prepared_statements; SQL PREPARE/DEALLOCATE имеют другую поддержку. Временные таблицы с ON COMMIT DROP допустимы. Statement pooling запрещает транзакции из нескольких statements. Размер серверного пула подбирайте по измеренной ёмкости БД, а не числу клиентов приложения.

Короткий ответ: 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;

⚠️ Ловушка: PostgreSQL умеет добавлять ENUM-значения через ALTER TYPE ADD VALUE и переименовывать через RENAME VALUE. Для удаления значения или изменения существующего порядка потребуется пересоздание типа/миграция данных. У массива нет FK на каждый элемент, поэтому для поэлементной целостности и JOIN часто яснее таблица связей.

Короткий ответ: В 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.

Короткий ответ: Селективный поиск B-tree находит начало за O(log n), затем читает подходящие индексные записи и необходимые страницы heap. Полный scan может проверять O(n) строк. Выигрыш зависит от объёма реально нужных данных.

Для поиска одного email в большой таблице индекс позволяет не просматривать миллионы посторонних строк. Для агрегата почти по всем строкам sequential scan может быть дешевле. Кэши, ширина строк, физическая корреляция, LIMIT и index-only доступ меняют стоимость; индекс не обещает фиксированного перехода «с минут на миллисекунды».

⚠️ Ловушка: даже запрос множества строк может выиграть от компактного покрывающего индекса или индексного порядка. Решайте по EXPLAIN и репрезентативным замерам; ни «всегда индекс», ни «80% значит никогда» не являются правилом.

Короткий ответ: 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.

Подробно: Без очистки накапливаются dead tuples и старые transaction IDs. Статистика — отдельная задача ANALYZE (autovacuum планирует обе работы). Без freezing PostgreSQL может отказать в новых транзакциях, предотвращая повреждение от wraparound. Следите за прогрессом очистки и старыми snapshots, а не только за включённым autovacuum.

⚠️ Ловушка: Длинные транзакции и 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, и помните: иногда «медленный запрос» — это симптом блокировок или нехватки памяти, а не отсутствия индекса.

EXPLAIN ANALYZE выполняет statement: сначала изучите предполагаемый план, а изменения проверяйте на репрезентативных безопасных данных. Продакшен-эксперименты чтения ограничивайте таймаутами; глобальное увеличение work_mem умножает расход памяти по узлам плана и одновременным сессиям.

Короткий ответ: Четыре основных типа: 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);

⚠️ Ловушка: обход графа возможен в SQL, включая recursive CTE. Graph-БД стоит рассмотреть, когда измеренные обходы и её модель запросов оправдывают новую систему; сама глубина не доказывает непригодность SQL. Масштабирование wide-column также требует ограниченных и равномерно распределённых партиций.

Пример событий предполагает уникальное время внутри пользователя. Для реального потока добавьте event_id в clustering key или подходящий timeuuid: одинаковый primary key в Cassandra обновляет одну строку.

Короткий ответ: Движок выбирают по запросам, инвариантам и эксплуатации. SQL или NoSQL — не выбор между корректностью и скоростью.

Требование	Что оценить
Связанные сущности, ограничения, меняющаяся аналитика	Реляционная схема, JOIN, индексы и транзакции; PostgreSQL часто подходит как исходный вариант
Данные читаются одним ограниченным вложенным объектом	Документная модель; MongoDB поддерживает индексы, валидацию схемы и многодокументные транзакции
Предсказуемый поиск по ключу в распределённой системе	Partition key, горячие ключи, доступные consistency и границы транзакций
Основная работа — обход связей	Graph-модель, язык запросов, замер стоимости обходов

Для заказов, товаров и оплат сначала сформулируйте инварианты: уникальность оплаты, неотрицательный остаток. Для каталога сравните документы с PostgreSQL JSONB вместо предположения, что гибкие данные бывают только в NoSQL.

⚠️ Ловушка: NoSQL не означает отсутствие схемы, транзакций или автоматическое масштабирование. SQL-движки тоже распределяют данные. Сравнивайте конкретный продукт и настройки: репликацию, бэкапы, миграции и опыт команды.

Короткий ответ: Не добавляйте NoSQL только потому, что трафик когда-нибудь вырастет. Существующая реляционная БД предпочтительна, если её ограничения, JOIN, транзакции и инструменты уже подходят нагрузке.

Тревожные признаки: неизвестные запросы, меняющаяся аналитика по связанным сущностям, дорогие копии часто меняющихся фактов, плохой partition key или отсутствие опыта эксплуатации ещё одной БД. Сначала измерьте, не решают ли реальный предел индексы, запросы, pooling или партиционирование PostgreSQL.

Это не утверждение о ненадёжности NoSQL-транзакций: MongoDB и DynamoDB предлагают транзакции с описанными границами и ценой. Если документный или key-value движок лучше обеспечивает нужные инварианты и доступ, он может быть правильным выбором.

⚠️ Ловушка: прикладные JOIN и синхронизация копий могут стоить дороже упрощённой модели хранения. Оцените эту цену до миграции.

Короткий ответ: Во время сетевого разделения реплицируемый сервис не может одновременно гарантировать линеаризуемые операции и успешный ответ на каждый запрос к неупавшему узлу.

• C (consistency): операции ведут себя как одна актуальная копия и учитывают реальное время: чтение, начатое после завершённой записи, видит её или более позднюю запись.
• A (availability): каждый запрос к неупавшему узлу в итоге успешно завершается; ошибка не считается CAP-доступностью.
• P (partition tolerance): модель допускает потерю или задержку сообщений между группами узлов. Возможность разделения не исчезает от выбора двух букв.

Две реплики изолированы; одна подтвердила обновление. Другая должна либо отказаться/ждать безопасного ответа, либо отвечать, не зная об обновлении. Это выбор C/A во время partition.

⚠️ Ловушка: CAP не постоянный ярлык продукта и не процент uptime. Разные операции и read modes дают разные гарантии. Одних MongoDB majority writes недостаточно для линеаризуемости любого чтения; в Redis нет универсального переключателя в консенсусную CP-БД.

Короткий ответ: ACID описывает гарантии транзакций; BASE — неформальное описание систем с временной рассогласованностью и последующей сходимостью. Это не взаимоисключающие ярлыки SQL и NoSQL.

• Atomicity: транзакция фиксируется целиком или не фиксируется.
• Consistency: изменения сохраняют заданные правила, если приложение и constraints правильно их выражают.
• Isolation: взаимодействие конкурентных транзакций определяется уровнем изоляции.
• Durability: подтверждённый commit переживает сбои, покрытые настройками хранения и репликации.

BASE — Basically Available, Soft state, Eventually consistent. Акцент на доступности и асинхронной сходимости не доказывает 100% uptime или автоматическое разрешение конфликтов. У БД могут быть ACID-транзакции и eventually consistent реплики.

⚠️ Ловушка: Redis MULTI/EXEC не допускает вмешательства других клиентов между командами, но ошибка команды во время исполнения не откатывает предыдущие команды. Durability зависит от persistence. Называть MULTI/EXEC полностью ACID без этих оговорок — учить неверной модели rollback.

Короткий ответ: Линеаризуемые (strong) операции следуют единому порядку с учётом реального времени. Eventual consistency допускает устаревшие реплики, но обещает сходимость, если записи прекратятся и связь/согласование восстановятся. Сама по себе она не задаёт предельное время задержки.

После сохранения профиля может хватить read-your-writes: читать с подходящего primary или дождаться применения известной позиции на реплике. Для инварианта баланса по нескольким строкам одной consistency репликации мало: нужны подходящие атомарная транзакция и изоляция.

Условие кворумов R + W > N даёт пересечение при своих допущениях, но само по себе не линеаризуемость. Важны разрешение конфликтов, конкурентные и неуспешные записи, изменение состава узлов и протокол чтения.

В примерах называйте API: DynamoDB использует ConsistentRead для подходящих таблиц/local secondary indexes; global secondary indexes читаются eventually consistent. Cassandra имеет уровни ONE и QUORUM; это не настройки DynamoDB.

⚠️ Ловушка: успешная majority-запись не делает произвольную реплику актуальной. Гарантию чтения выбирают и проверяют отдельно.

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

Подробно:

• In-memory — все данные в RAM, отсюда скорость. Есть опциональная персистентность на диск.
• Не просто строки — поддерживает строки, хеши, списки, множества, sorted sets и streams.
• Большинство команд исполняется последовательно в основном потоке. Фоновые операции и, в зависимости от версии/настроек, сетевой I/O могут использовать другие потоки; Redis не является полностью однопоточным.
• Атомарные операции, 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.

Короткий ответ: Redis обычно исполняет команды в одном основном потоке. RAM, эффективные структуры и событийная сеть ускоряют многие операции без межпоточной координации каждой команды. CPU всё ещё может стать узким местом: долгая команда задерживает других клиентов.

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

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

Нюансы:

• Исполнение команд в основном последовательное; I/O threads и фоновые потоки выполняют другую работу. BGSAVE и AOF rewrite используют дочерние процессы: persistence — не просто ещё один поток исполнения.
• Для использования всех ядер запускают несколько инстансов 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 0
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 раздувают память на оверхеде ключей.

Consumer group в Stream хранит pending-доставки. Завершённую работу подтверждают XACK; зависшие pending возвращают через XAUTOCLAIM/XCLAIM по политике ретраев. Всё ещё нужны идемпотентные эффекты и persistence под требуемую долговечность.

Короткий ответ: Кэш, хранение сессий, 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 — ограничение частоты запросов.

-- KEYS[1] = rate:user:1:<UTC-minute-number>, ARGV[1] = limit
local n = redis.call('INCR', KEYS[1])
if n == 1 then redis.call('EXPIRE', KEYS[1], 120) end
return n <= tonumber(ARGV[1]) and 1 or 0

Для 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.

Код limiter выполняется атомарно через EVAL/EVALSHA. Вызывающий строит ключ текущей UTC-минуты; TTL 120 секунд нужен для очистки, а не продлевается каждым запросом. Fixed window допускает всплеск на границе. Простой pop списка удаляет задачу до обработки: надёжной очереди нужен pending/ack-паттерн или Streams; persistence и ретраи всё ещё важны.

Короткий ответ: Простая блокировка — 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 вычитаются время получения и запас на drift часов; работу нужно завершить внутри оставшегося срока аренды.
5. Если не удалось — освободить все узлы.

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

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

Подробно:

RDB (Redis Database):

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

AOF (Append Only File):

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

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

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

Выбор: настройте persistence под допустимую потерю. При включённых RDB и AOF Redis обычно загружает AOF. Multi-part AOF (Redis 7+) может иметь базовый файл формата RDB и инкрементальные AOF; это не то же самое, что просто включить независимые RDB-снапшоты. Проверяйте восстановление и учитывайте fork/CoW-память и сбои хранилища.

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

Короткий ответ: maxmemory — порог учёта памяти для eviction, а не жёсткий предел RSS процесса. На пороге Redis вытесняет подходящие ключи по политике или отклоняет команды, которым нужна дополнительная память. Buffers, фрагментация, временное превышение и fork/CoW требуют запаса.

Подробно:

Параметр maxmemory 2gb + maxmemory-policy <policy>. Распространённые политики:

Политика	Что делает
noeviction	Без вытеснения; команды с ростом памяти могут дать ошибку, чтения/удаления продолжаются
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+) — учитывает частоту использования, лучше держит реально горячие ключи. Сравнивайте с LRU на реальном паттерне доступа.

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, чтобы связанные данные жили в одном слоте.

Quorum Sentinel — число наблюдателей, согласных с отказом primary. Для разрешения failover дополнительно нужно большинство процессов Sentinel: заданного quorum недостаточно в сетевом меньшинстве. Реплики Redis Cluster нужно развернуть; само шардирование не создаёт HA.

Короткий ответ: 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 — при записи приложение пишет в кэш, а кэш синхронно пишет в БД.

• + Единый путь записи помогает обновлять кэш, если все писатели соблюдают протокол. Раздельные записи DB/cache не становятся атомарными: нужны обработка частичного сбоя и гонок заполнения.
• − Запись медленнее (две операции); кэшируются и редко читаемые данные.

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 — в кэше нет, идём в БД (медленно) и (обычно) кладём в кэш.
• При одном cache lookup на чтение и одном DB-запросе на miss hit ratio 90% уменьшает именно этот поток DB-чтений до 10%. Несколько ключей, записи и дорогие misses меняют соотношение: измеряйте сэкономленную работу БД.

В 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 на устаревших данных — это «хорошая метрика, плохой результат».

Короткий ответ: Stampede возникает, когда множество запросов промахивается по одному горячему ключу и одновременно пересчитывает его. Объединяйте работу, обновляйте заранее и ограничивайте поток к источнику.

Безопасный протокол заполнения:

1. Прочитайте кэш; отличайте отсутствие от допустимых нуля или пустого списка.
2. Объедините misses в процессе через single-flight. Между процессами можно использовать lease Redis: SET lock:key unique_token NX PX ttl.
3. После получения lease снова проверьте кэш, затем вычисляйте с deadline и ограничением конкурентных запросов к источнику.
4. Освобождайте lease атомарным compare-token-and-delete. Обычный DEL способен удалить lease нового владельца после истечения вашего.
5. Остальные ждут только до deadline, отдают допустимый stale или быстро отказывают. Не повторяйте запрос бесконечной рекурсией.

Истечение lease всё ещё допускает повторный пересчёт. Для сохранения свежести нужны проверки версии против старого писателя; бизнес-корректность не должна зависеть лишь от lease.

Jitter TTL распределяет истечение разных ключей. Вероятностное раннее и фоновое обновление уменьшают misses, но не гарантируют вечного наличия ключа. Stale-while-revalidate допустим только в рамках разрешённой продуктом устарелости.

Короткий ответ: 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 — про массовое истечение/сбой (кэш был, но «обвалился»). Лечатся по-разному.

Bloom filter не даёт false negatives для действительно добавленных элементов. Устаревший или перестраиваемый фильтр может не содержать новый id из БД: добавляйте его до публикации объекта или обращайтесь к источнику при неопределённом покрытии. False-positive rate настраивается и не всегда равен 1%.

Короткий ответ: На всём пути запроса: браузер клиента → 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 и нагрузка на бэкенд, но сложнее инвалидация (как обновить кэш в браузерах тысяч пользователей? — через expiration, валидацию или версионирование URL).

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

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

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

PostgreSQL по умолчанию не имеет общего кэша результатов запросов: shared_buffers и page cache ОС хранят страницы, а не произвольные результаты SELECT. HTTP-валидация ETag/Last-Modified позволяет обновлять браузерный entry без передачи body; версионирование URL — не единственный механизм.

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

Подробно:

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

// Документ в коллекции 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 МБ. Безграничный рост вложенного массива (например, все комментарии внутри документа поста) рано или поздно упрётся в лимит и убьёт производительность. Это сигнал выносить в отдельную коллекцию.

Гибкая схема не означает отсутствие проверок: коллекция может требовать поля и типы через validators. Лимит BSON-документа — 16 MiB.

Короткий ответ: Индексы (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 }
])

⚠️ Ловушка: порядок стадий влияет и на смысл, и на скорость. Перенос LIMIT перед GROUP или SORT меняет ответ; делайте его только при эквивалентности. Селективный MATCH ставят раньше, если это корректно. MongoDB умеет перемещать подходящие фильтры: поздний текстовый MATCH тоже может участвовать в оптимизированном индексном плане. Проверяйте explain, а не только позицию стадии.