SQL #

1. Уровни изоляции транзакций в БД #

Статья на хабре про уровни изоляции

От менее строгого к самому строгому:

• Read uncommited (незащищённое чтение) - транзакции могут читать данные, которые еще не зафиксированы другими транзакциями (использовать, когда все транзакции на чтение)
• Read commited (защищённое чтение) - транзакции могут читать только те данные, которые уже зафиксированы другими данными (решает потерянное обновление и грязное чтение)
• Repeatable read (повторяющееся чтение) - в рамках одной транзакции многократный запрос данных всегда возвращает одинаковый набор данных, независимо от изменений, сделанных другими транзакциями (решает потерянное обновление, грязное чтение и неповторяющееся чтение)
• Serializable - самый строгий уровень изоляции. Транзакции выполняются так, как будто происходят последовательно одна за другой (решает всё, но бьёт по производительности)

Какой уровень изолированности стоит по умолчанию в MySQL? В MySQL - Repeatable read, в PostgreSQL - Read commited

Зависимость скорости работы БД и уровня изоляции

2. Что такое реляционные базы данных? #

Что такое реляционные базы данных #

Реляционная база данных — это база данных, в которой данные хранятся в виде связанных таблиц.

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

Пример:

Таблица users:

Таблица orders:

Здесь orders.user_id ссылается на users.id. То есть заказ связан с конкретным пользователем.

Основная идея #

Реляционная БД строится на отношениях между таблицами.

Связи обычно задаются через:

Primary Key — первичный ключ, уникально идентифицирует запись в таблице.

Foreign Key — внешний ключ, указывает на запись в другой таблице.

Пример:

users.id        -- primary key
orders.user_id  -- foreign key на users.id

Так база понимает, как данные связаны между собой.

Зачем нужны реляционные БД #

Они позволяют:

хранить структурированные данные;

избегать дублирования данных;

связывать сущности между собой;

делать сложные выборки через SQL;

обеспечивать целостность данных;

использовать транзакции и ACID.

Примеры реляционных СУБД #

Самые известные:

PostgreSQL;

MySQL;

MariaDB;

SQLite;

Oracle Database;

Microsoft SQL Server.

Кратко #

Реляционная база данных — это база данных, где информация хранится в таблицах, а связи между таблицами задаются через ключи. Основная работа с такими базами выполняется с помощью SQL. Реляционные БД хорошо подходят для структурированных данных, где важны целостность, связи между сущностями и транзакционность.

3. Как повысить эффективность выборки записей из большой таблицы? | Опыт оптимизации запросов. #

Как повысить эффективность выборки из большой таблицы #

Главная идея: база должна читать как можно меньше строк и страниц с диска/памяти.

1. Добавить правильные индексы #

Индекс нужен на поля, которые часто используются в:

WHERE
JOIN ON
ORDER BY
GROUP BY

Пример:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Запрос:

SELECT *
FROM orders
WHERE user_id = 10;

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

2. Использовать составные индексы #

Если часто фильтруешь по нескольким полям:

SELECT *
FROM orders
WHERE user_id = 10 AND status = 'paid';

лучше создать составной индекс:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

Важно: порядок колонок в индексе имеет значение. Обычно первым ставят поле, которое чаще используется в фильтрации и сильнее сужает выборку.

3. Не выбирать лишние колонки #

Плохо:

SELECT *
FROM users;

Лучше:

SELECT id, username, email
FROM users;

Чем меньше данных нужно прочитать и передать, тем быстрее работает запрос.

4. Использовать пагинацию #

Для больших таблиц нельзя без ограничения вытаскивать все записи:

SELECT *
FROM logs
ORDER BY created_at DESC
LIMIT 100;

Для глубоких страниц OFFSET может становиться дорогим:

LIMIT 100 OFFSET 100000;

Часто лучше использовать keyset pagination:

SELECT *
FROM logs
WHERE created_at < '2026-06-29 10:00:00'
ORDER BY created_at DESC
LIMIT 100;

Индекс:

CREATE INDEX idx_logs_created_at ON logs(created_at);

5. Проверять запрос через EXPLAIN / EXPLAIN ANALYZE #

Нельзя оптимизировать вслепую. Нужно смотреть реальный план выполнения:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 10;

EXPLAIN показывает, какой план выполнения выбрала БД, а EXPLAIN ANALYZE фактически выполняет запрос и показывает реальные времена выполнения.

Особенно важно смотреть:

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan

Если на большой таблице виден Seq Scan, возможно, не хватает индекса или запрос написан так, что индекс не используется.

6. Использовать частичные индексы #

Если часто выбирается только часть таблицы, можно индексировать не всю таблицу, а только нужные строки.

Пример:

CREATE INDEX idx_orders_unpaid
ON orders(created_at)
WHERE status = 'unpaid';

Это полезно, когда, например, 95% заказов уже оплачены, а запросы часто ищут только неоплаченные. PostgreSQL описывает partial index как индекс только по подмножеству таблицы, заданному условием.

7. Использовать партиционирование #

Если таблица очень большая, например логи за несколько лет, её можно разделить на части:

logs_2024
logs_2025
logs_2026

Или декларативно:

PARTITION BY RANGE (created_at)

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

8. Следить за статистикой таблиц #

Оптимизатор выбирает план запроса на основе статистики. Если статистика устарела, БД может выбрать плохой план.

В PostgreSQL используют:

ANALYZE table_name;

или:

VACUUM ANALYZE table_name;

9. Избегать функций над индексируемыми колонками #

Плохо:

SELECT *
FROM users
WHERE LOWER(email) = 'test@mail.com';

Обычный индекс по email может не помочь, потому что применяется функция LOWER.

Варианты решения:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

или хранить email в нормализованном виде.

10. Оптимизировать JOIN #

Для больших таблиц важно, чтобы поля соединения были индексированы:

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id;

Обычно нужны индексы:

users.id
orders.user_id

Первичный ключ обычно индексируется автоматически, но внешний ключ не всегда создаёт индекс автоматически — зависит от СУБД.

Кратко #

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

4. Виды JOIN. Как работает каждый JOIN? #

Оператор, позволяющий связать данные из двух таблиц. Бывают:

• (INNER) JOIN - выдаст общее для левой и правой таблицы (может быть заменён WHERE)
• LEFT (OUTER) JOIN - выдаст все значения с левой таблицы и все подходящие с правой
• RIGHT (OUTER) JOIN - выдаст все значения с правой таблицы и все подходящие с левой
• FULL (OUTER) JOIN - выдаст все записи, которые присутствуют в таблицах
• CROSS JOIN - создаст декартово произведение двух таблиц (если в первой таблице 5 строк, а во второй 3, то вернет 5 x 3 = 15)
• NATURAL JOIN - объединяет таблицы по столбцам с одинаковыми именами, если значения в этих столбцах совпадают
• SELF JOIN - соединяет таблицу с самой собой

5. Нормализация и денормализация. Перечислите формы #

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

• 1NF (1-я нормальная форма). Одна ячейка - одно значение.
• 2NF (2-я нормальная форма). Все не ключевые колонки зависят от всего ключа.
• 3NF (3-я нормальная форма). Все не ключевые колонки не зависят друг от друга.

Денормализация - процесс обратный нормализации. Обычно это необходимо для повышения производительности и скорости извлечения данных, за счет увеличения избыточности данных (влечёт уменьшение сложности запросов)

6. Индексы в SQL. Что это и для чего используются? #

Индексы в БД - это инструмент оптимизации/ускорения запросов путём создания упорядоченного набора указателей на строки таблицы. Ускорение происходит за счёт того, что структура индекса оптимизирована под поиск (например, b-tree). При этом, чем больше дубликатов в столбце - тем хуже работает индекс

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

• указателей на строки в таблице, где хранятся данные
• идентификаторов записей, которые позволяют найти данные в таблице

В Postgres в индексе хранятся ссылки на реальные данные, которые находятся в таблице базы данных. Например, в B-tree:

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

Однако, можно создать покрывающий индекс - индекс, который включает не только ключи, но и значение колонки(ок). Такие индексы позволяют извлекать данные не обращаясь к таблице. Пример:

CREATE INDEX idx_price ON products(price) INCLUDE (name);
-- Теперь индекс хранит значения price + колонку name, что позволяет выполнить запрос
SELECT price, name FROM products WHERE price > 50;
-- не обращаясь к таблице

В MySQL (InnoDB) поведение индексов отличается в зависимости от их типа:

1. PRIMARY KEY (кластерный индекс) - индекс содержит сами данные
2. Обычный (SECONDARY INDEX) индекс хранит ссылки (ключи PRIMARY KEY) на данные

Какие индексы по умолчанию в разных СУБД? По-умолчанию в большинстве CУБД стоит B-tree

Сравнение сложности поиска

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

• PostgreSQL - CREATE INDEX CONCURRENTLY / pg_repack / gh-ost
• MySQL - ALTER TABLE … ALGORITHM=INPLACE, LOCK=NONE / pt-online-schema-change

Как определить, какой индекс будет использован в запросе с фильтрацией по столбцу с низкой селективностью (например, пол), и как селективность влияет на эффективность индексов?

1. EXPLAIN ANALYZE покажет, будет ли Index Scan или Seq Scan
2. pg_stats.n_distinct даёт оценку числа уникальных значений

7. Какие основные типы/виды индексов существуют и в каких случаях они применяются? #

8. В каких случаях целесообразно использовать hash-индекс в базе данных? #

Когда целесообразно использовать hash-индекс #

Hash-индекс имеет смысл использовать, когда запросы ищут строки только по точному совпадению значения.

Типичный пример:

SELECT *
FROM users
WHERE email = 'test@example.com';

Или:

SELECT *
FROM sessions
WHERE token = 'abc123';

В PostgreSQL hash-индексы хранят хеш-код значения и могут использоваться только для простых сравнений на равенство через оператор =.

Хорошие случаи для hash-индекса #

Hash-индекс может быть уместен, если:

1. Запросы почти всегда используют =

WHERE user_id = 100
WHERE email = 'a@mail.com'
WHERE token = '...'
WHERE uuid = '...'

2. Не нужны диапазонные условия

Hash-индекс не подходит для:

WHERE created_at > '2026-01-01'
WHERE price BETWEEN 100 AND 500
WHERE id >= 1000

Для таких запросов обычно нужен B-tree индекс.

3. Не нужна сортировка по этому индексу

Hash-индекс не помогает для:

ORDER BY created_at

Потому что hash-индекс не хранит данные в отсортированном порядке.

4. Колонка имеет высокую селективность

Хорошие кандидаты:

id
uuid
email
username
token
session_id
api_key

Плохие кандидаты:

status
is_active
gender
type

Например, если в колонке status всего 3 значения, hash-индекс может быть бесполезен: база всё равно найдёт слишком много строк.

Пример в PostgreSQL #

CREATE INDEX idx_users_email_hash
ON users USING hash (email);

Запрос:

SELECT *
FROM users
WHERE email = 'test@example.com';

Но важно: в PostgreSQL обычный B-tree индекс тоже умеет работать с =, а также дополнительно поддерживает диапазоны, сортировку и больше сценариев. Поэтому hash-индекс не является универсальной заменой B-tree. PostgreSQL указывает, что B-tree поддерживает операторы <, <=, =, >=, >, а hash-индекс — только equality-сравнения.

Когда hash-индекс не подходит #

Hash-индекс не стоит использовать, если запросы выглядят так:

WHERE age > 18

WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'

ORDER BY created_at DESC

WHERE name LIKE 'Alex%'

WHERE status IN ('new', 'pending')

Для этих случаев чаще подходит B-tree индекс.

В MySQL #

В MySQL hash-индексы особенно связаны с MEMORY storage engine. Документация MySQL указывает, что hash-индексы используются только для equality-сравнений через = или <=>, но не подходят для range-поиска.

То есть хорошо:

WHERE id = 10

Плохо:

WHERE id > 10

Кратко #

Hash-индекс целесообразно использовать для точечного поиска по точному совпадению, когда запросы используют только = по высокоселективной колонке: id, uuid, email, token, session_id. Он не подходит для диапазонов, сортировки, ORDER BY, LIKE 'prefix%' и условий больше/меньше. На практике чаще сначала используют B-tree индекс, а hash-индекс рассматривают только после проверки через EXPLAIN ANALYZE, если workload действительно состоит из equality-запросов.

9. Как организован B-tree индекс и по какому принципу он работает? #

Что такое B-tree индекс #

B-tree индекс — это сбалансированное многопутевое дерево, где ключи хранятся в отсортированном порядке. “Многопутевое” означает, что один узел содержит не один ключ, как в бинарном дереве, а много ключей и много ссылок на дочерние узлы. PostgreSQL прямо описывает B-tree как стандартную структуру “multi-way balanced tree” для данных, которые можно отсортировать в линейный порядок.

Упрощённо структура такая:

                 [ 30 | 60 ]
                /     |      \
        < 30         30-60        > 60

       [10|20]     [35|45|55]    [70|80|90]

Корневой узел говорит:
ключи меньше 30 ищи слева, от 30 до 60 — посередине, больше 60 — справа.

Как он организован #

B-tree индекс состоит из нескольких уровней:

root node
  └── internal nodes
        └── leaf nodes

Внутренние узлы хранят ключи-разделители и ссылки на следующие узлы. Листовые узлы хранят реальные индексные записи: значение ключа и указатель на строку таблицы либо на первичный ключ, в зависимости от СУБД и типа индекса. Например, в InnoDB первичный ключ является clustered index, то есть данные строки хранятся вместе с кластерным индексом; вторичные индексы содержат свои ключи и значения первичного ключа строки.

Главная идея: индекс не хранит строки “как попало”. Он хранит ключи в отсортированной древовидной структуре, чтобы СУБД не читала всю таблицу подряд.

Как выполняется поиск #

Допустим, есть индекс по колонке age, и запрос:

SELECT *
FROM users
WHERE age = 35;

СУБД идёт сверху вниз:

1. Смотрит в корневой узел.

2. Определяет, в какой диапазон попадает 35.

3. Переходит в нужный дочерний узел.

4. Повторяет процесс, пока не дойдёт до листа.

5. В листе находит 35.

6. По ссылке из индекса получает нужную строку таблицы.

То есть вместо полного сканирования таблицы:

проверь строку 1
проверь строку 2
проверь строку 3
...
проверь строку 10 000 000

СУБД делает короткий путь по дереву:

root → internal node → leaf node → row

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

Почему B-tree хорошо подходит для диапазонов #

B-tree сохраняет порядок ключей. Поэтому он хорошо работает не только с =, но и с диапазонными условиями:

WHERE age > 30
WHERE age BETWEEN 20 AND 40
WHERE created_at >= '2026-01-01'
ORDER BY created_at

PostgreSQL использует B-tree для сравнений <, <=, =, >=, >, а также может использовать такой индекс для получения данных в отсортированном порядке. ( PostgreSQL) MySQL также указывает, что B-tree индекс применим для =, >, >=, <, <=, BETWEEN и некоторых LIKE 'prefix%' условий.

Например:

SELECT *
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

СУБД сначала быстро находит начало диапазона в дереве, а потом последовательно проходит по соседним ключам, пока диапазон не закончится.

Как происходит вставка #

При вставке новой строки СУБД должна добавить новый ключ в индекс.

Например, вставляется age = 42.

1. СУБД ищет листовой узел, куда должен попасть ключ 42.

2. Если в узле есть место — просто вставляет ключ в правильную позицию.

3. Если узел переполнен — он делится на два узла.

4. Средний ключ поднимается выше, чтобы родительский узел знал о новом разделении.

5. Если переполнен родитель — процесс может подняться выше, вплоть до корня.

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

Почему индекс ускоряет чтение, но замедляет запись #

B-tree ускоряет SELECT, потому что позволяет быстро найти нужные строки. Но при INSERT, UPDATE, DELETE СУБД должна обновлять не только таблицу, но и все связанные индексы. PostgreSQL отдельно подчёркивает, что индексы ускоряют поиск, но добавляют накладные расходы, поэтому их нужно использовать разумно.

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

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_users_created_at ON users(created_at);

То при вставке пользователя СУБД обновит:

таблицу users
индекс по email
индекс по age
индекс по created_at

Чем больше индексов, тем тяжелее операции записи.

Главное #

B-tree индекс — это отсортированное сбалансированное дерево, которое позволяет быстро сузить область поиска. Он особенно полезен для:

WHERE column = value
WHERE column > value
WHERE column BETWEEN a AND b
ORDER BY column
LIKE 'abc%'

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

LIKE '%abc'

В таком случае порядок B-tree почти бесполезен, потому что неизвестно, с какого места дерева начинать поиск.

10. Что хранится в узлах B-дерева и как по нему выполняется поиск? #

Что хранится в узлах B-дерева #

В B-tree/B+tree индексе узел — это страница индекса. В PostgreSQL B-tree индекс состоит из внутренних страниц и листовых страниц; листовые страницы находятся на нижнем уровне дерева, а все уровни выше — внутренние. В листовых страницах PostgreSQL хранятся индексные кортежи, которые указывают на строки таблицы.

Упрощённо:

B-tree индекс

                 [ 30 | 60 ]
                /     |      \
        [10|20]   [35|45|55]   [70|80|90]

Внутренний узел хранит:

ключи-разделители
ссылки на дочерние узлы

Например:

[ 30 | 60 ]

означает:

меньше 30        → идти в левый дочерний узел
от 30 до 60      → идти в средний дочерний узел
больше 60        → идти в правый дочерний узел

Листовой узел хранит уже сами индексные записи:

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

Например, для индекса по email лист может логически выглядеть так:

["a@mail.com"] → ссылка на строку #101
["b@mail.com"] → ссылка на строку #205
["c@mail.com"] → ссылка на строку #317

В реальной СУБД детали зависят от движка. Например, в MySQL InnoDB вторичные индексы хранят ключ вторичного индекса и значение первичного ключа, по которому затем можно найти строку в clustered index.

Как выполняется поиск #

Допустим, есть индекс:

CREATE INDEX idx_users_age ON users(age);

И запрос:

SELECT *
FROM users
WHERE age = 45;

СУБД не перебирает всю таблицу. Она идёт по дереву сверху вниз.

                 [ 30 | 60 ]
                /     |      \
        [10|20]   [35|45|55]   [70|80|90]

Поиск 45:

1. Смотрим корневой узел: [30 | 60]
2. 45 больше 30, но меньше 60
3. Значит, идём в средний дочерний узел
4. Попадаем в лист: [35 | 45 | 55]
5. Находим ключ 45
6. По ссылке из индекса получаем строку таблицы

То есть путь такой:

root node → internal node → leaf node → table row

Если дерево выше, процесс тот же самый: на каждом уровне СУБД сравнивает искомое значение с ключами-разделителями и выбирает нужную ветку.

Почему поиск быстрый #

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

Условно, вместо такого поиска:

проверить 1-ю строку
проверить 2-ю строку
проверить 3-ю строку
...
проверить миллионную строку

получается такой:

прочитать корень
прочитать нужный внутренний узел
прочитать нужный лист
найти ссылку на строку

Поэтому B-tree хорошо подходит для точечного поиска:

WHERE id = 100
WHERE email = 'test@mail.com'

и для диапазонов:

WHERE age BETWEEN 20 AND 40
WHERE created_at >= '2026-01-01'

MySQL указывает, что B-tree индекс может использоваться для сравнений =, >, >=, <, <=, BETWEEN, а также для LIKE, если шаблон не начинается с wildcard, например LIKE 'abc%'. ( MySQL)

Как выполняется диапазонный поиск #

Для диапазона принцип немного другой.

Запрос:

SELECT *
FROM users
WHERE age BETWEEN 30 AND 50;

СУБД:

1. Через B-tree быстро находит первое значение >= 30
2. Попадает в нужный листовой узел
3. Дальше идёт по соседним индексным записям в отсортированном порядке
4. Останавливается, когда значение стало больше 50

Именно поэтому B-tree полезен для ORDER BY, BETWEEN, <, >, >=, <=: данные внутри индекса упорядочены.

Коротко #

В узлах B-дерева хранятся не сами “объекты” таблицы, а индексная структура:

внутренние узлы:
  ключи-разделители + ссылки на дочерние страницы

листовые узлы:
  индексируемые значения + ссылки на строки таблицы

Поиск выполняется так:

сравнить ключ в корне
выбрать нужную ветку
спуститься ниже
дойти до листа
найти ключ
получить строку таблицы

Главный смысл B-tree индекса — быстро сузить область поиска за счёт отсортированной и сбалансированной структуры.

11. Недостатки индексов | В каких ситуациях использование индексов в базе данных снижает эффективность системы или приводит к ухудшению производительности? | Почему много индексов замедляет INSERT, UPDATE, DELETE? #

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

12. Что такое SQL-инъекция? #

Что такое SQL-инъекция #

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

Иными словами: приложение ожидало данные, но из-за неправильной сборки SQL-запроса пользователь может передать кусок SQL-кода, который база данных выполнит. OWASP относит SQL Injection к распространённым и опасным уязвимостям, потому что база часто содержит чувствительные данные.

Простой пример плохого кода:

username = request.GET["username"]

sql = f"SELECT * FROM users WHERE username = '{username}'"

Ожидался обычный ввод:

alex

Тогда получится запрос:

SELECT * FROM users WHERE username = 'alex'

Но атакующий может передать:

' OR '1'='1

И итоговый запрос станет:

SELECT * FROM users WHERE username = '' OR '1'='1'

Условие '1'='1' всегда истинно, поэтому база может вернуть не одного пользователя, а все подходящие строки.

В чём суть проблемы #

Проблема не в самой базе данных, а в том, что приложение смешивает:

данные пользователя
SQL-команду

То есть пользовательский ввод становится частью SQL-синтаксиса.

Плохо:

SELECT * FROM users WHERE username = '<ввод пользователя>'

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

Правильно — передавать значение отдельно от SQL-команды:

cursor.execute(
    "SELECT * FROM users WHERE username = %s",
    [username]
)

В этом случае база понимает: username — это значение, а не SQL-код. OWASP прямо указывает parameterized queries / prepared statements как основной способ защиты от SQL-инъекций.

Что может сделать SQL-инъекция #

В зависимости от прав приложения и типа запроса SQL-инъекция может позволить:

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

PortSwigger описывает SQL injection как уязвимость, позволяющую вмешиваться в запросы приложения к базе; часто это даёт доступ к данным, которые пользователь не должен видеть, а иногда позволяет изменять или удалять данные.

Пример с авторизацией #

Плохой вариант:

sql = f"""
SELECT *
FROM users
WHERE username = '{username}'
AND password = '{password}'
"""

Обычный запрос:

SELECT *
FROM users
WHERE username = 'admin'
AND password = '12345'

Атакующий может ввести в поле username:

admin' --

Тогда запрос станет примерно таким:

SELECT *
FROM users
WHERE username = 'admin' --'
AND password = 'anything'

-- в SQL означает комментарий. Всё после него игнорируется. В итоге проверка пароля может быть выключена.

Почему экранирования недостаточно #

Иногда пытаются защищаться так: удалить кавычки, заменить спецсимволы, запретить слова SELECT, DROP, OR.

Это слабый подход, потому что:

у разных СУБД разные правила синтаксиса
можно обойти фильтр альтернативной записью
можно ошибиться в обработке кодировок
легко забыть обработать одно из мест

OWASP указывает, что allow-list валидация может быть полезна, но параметризованные SQL-запросы обычно требуют меньше сопровождения и дают более надёжные гарантии безопасности.

Как выглядит правильная защита #

Основной способ — параметризованные запросы:

cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password_hash = %s",
    [username, password_hash]
)

Или через ORM:

User.objects.get(username=username)

ORM обычно сам формирует параметризованный запрос, если не использовать опасные ручные вставки SQL.

Также важно:

не собирать SQL через f-string / конкатенацию
использовать prepared statements / bind parameters
ограничивать права пользователя БД
валидировать входные данные
не показывать пользователю подробные SQL-ошибки
логировать подозрительные запросы

Коротко #

SQL-инъекция — это когда ввод пользователя попадает в SQL-запрос как исполняемый код.

Плохая идея:

sql = f"SELECT * FROM users WHERE id = {user_id}"

Нормальная идея:

cursor.execute(
    "SELECT * FROM users WHERE id = %s",
    [user_id]
)

Главное правило: SQL-команда и пользовательские данные должны передаваться отдельно.

13. Как защититься от SQL-инъекций? #

Главная защита — параметризованные запросы #

Нельзя собирать SQL через строки:

username = request.GET["username"]

sql = f"SELECT * FROM users WHERE username = '{username}'"

Так пользовательский ввод становится частью SQL-кода.

Правильно — передавать SQL и значения отдельно:

cursor.execute(
    "SELECT * FROM users WHERE username = %s",
    [username]
)

В этом случае база воспринимает username как значение, а не как SQL-команду. OWASP называет prepared statements / parameterized queries основным способом защиты: SQL-код задаётся отдельно, а пользовательские параметры передаются позже как данные.

Что делать на практике #

1. Использовать ORM вместо ручного SQL, когда это возможно.

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

User.objects.get(username=username)

Вместо:

User.objects.raw(
    f"SELECT * FROM users WHERE username = '{username}'"
)

Django прямо рекомендует сначала использовать ORM, а при raw SQL обязательно передавать пользовательские значения через params, чтобы защищаться от SQL-инъекций.

2. Если нужен raw SQL — использовать параметры.

Django:

User.objects.raw(
    "SELECT * FROM users WHERE username = %s",
    [username]
)

psycopg:

cur.execute(
    "SELECT * FROM users WHERE username = %s",
    (username,)
)

В psycopg параметры передаются вторым аргументом в execute(). Документация отдельно предупреждает не склеивать значения с SQL-строкой вручную.

SQLAlchemy:

from sqlalchemy import text

stmt = text("SELECT * FROM users WHERE id = :user_id")
result = session.execute(stmt, {"user_id": user_id})

В SQLAlchemy для текстового SQL есть text() с bind parameters через :name; документация также предупреждает не подставлять недоверенный ввод прямо внутрь SQL-строки.

Чего нельзя делать #

Опасно:

sql = "SELECT * FROM users WHERE id = " + user_id

Опасно:

sql = f"SELECT * FROM users WHERE email = '{email}'"

Опасно:

sql = "SELECT * FROM users WHERE name = '%s'" % name

Даже если кажется, что значение безопасное. Например, id пришёл как число, но из HTTP-запроса всё изначально приходит как внешние данные.

Правильно:

cursor.execute(
    "SELECT * FROM users WHERE id = %s",
    [user_id]
)

Валидация не заменяет параметризацию #

Валидация нужна, но она не должна быть основной защитой от SQL-инъекции.

Например, для id можно проверять:

user_id = int(user_id)

Для сортировки можно использовать allow-list:

allowed_sort_fields = {
    "created_at": "created_at",
    "username": "username",
    "email": "email",
}

sort_by = allowed_sort_fields.get(request.GET.get("sort"))

Но это дополнение. Главное — не вставлять пользовательские значения прямо в SQL.

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

Например, так делать нельзя:

cursor.execute(
    "SELECT * FROM users ORDER BY %s",
    [sort_by]
)

Для таких частей запроса лучше использовать allow-list:

allowed_sort = {
    "name": "username",
    "date": "created_at",
}

column = allowed_sort.get(sort_by, "created_at")

sql = f"SELECT * FROM users ORDER BY {column}"

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

Ограничить права пользователя БД #

Даже если SQL-инъекция где-то появится, ущерб можно уменьшить правами.

Не стоит подключать приложение к БД под суперпользователем.

Для обычного backend-приложения лучше отдельный пользователь БД с минимальными правами:

GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;

А не:

GRANT ALL PRIVILEGES ON DATABASE main_db TO app_user;

И тем более не использовать postgres, root или владельца всей БД в приложении.

Не показывать SQL-ошибки наружу #

Плохо, когда пользователь видит:

syntax error at or near ...
relation users does not exist
column password_hash does not exist

Такие ошибки помогают понять структуру базы.

Нормально:

{
  "detail": "Invalid request"
}

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

Проверять опасные места в коде #

Искать нужно всё, где SQL собирается вручную:

f"SELECT ..."
"SELECT ..." + value
"SELECT ... %s" % value
.format(...)
raw()
RawSQL()
extra()
text()
execute()

ORM-запросы обычно безопаснее, но raw SQL, кастомные выражения и динамические ORDER BY, WHERE, LIMIT, имена таблиц и колонок нужно проверять отдельно.

Коротко #

Нормальный набор защиты:

1. Не собирать SQL через f-string, +, %, .format().
2. Использовать ORM.
3. Для raw SQL — только параметризованные запросы.
4. Для имён колонок/таблиц/sort — allow-list.
5. Валидировать входные данные.
6. Ограничивать права пользователя БД.
7. Не показывать SQL-ошибки пользователю.
8. Логировать подозрительные запросы и ошибки.

Главное правило: пользовательский ввод никогда не должен становиться частью SQL-синтаксиса. Он должен передаваться отдельно как значение.

14. Что такое транзакция в базе данных и какими свойствами ACID она обладает? #

Транзакция - это набор логически связанных запросов, выполняемых атомарно (либо выполняются все - commit транзакции, либо откат всего - rollback)

15. Расскажите про ACID #

ACID - это набор свойств, гарантирующих надёжность транзакции:

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

16. Какими инструментами вы пользовались для анализа плана выполнения (Профилирования) запроса (EXPLAIN/EXPLAIN ANALYZE)? #

• EXPLAIN - показывает теоретический план выполнения запроса, что позволяет увидеть, как СУБД планирует извлекать данные
• EXPLAIN ANALYZE - выполняет запрос и показывает реальный план выполнения, включая затраченные ресурсы (время, память)
• Indexes - индексы ускоряют доступ к данным, особенно при использовании в условиях WHERE
• pg_stat_statements (для PostgreSQL) - модуль для сбора статистики выполнения запросов

SQL-запрос у тебя в норме, но выполнение всё равно долгое. Твои действия?

• Оптимизация плана выполнения - воспользоваться EXPLAIN и EXPLAIN ANALYZE для анализа плана выполнения запроса и поиска проблемных мест
• Использование индексов - проверить, правильно ли используются существующие индексы. При необходимости создать или изменить текущие
• Рефакторинг запроса - изменить структуру запроса, если есть более эффективный способ получить те же данные. Например, объединение нескольких подзапросов или использование более простых JOIN
• Ресурсные ограничения - проверить отсутствие ограничений по железу (нехватка CPU, памяти, диска)

17. Какую информацию о выполнении SQL-запроса позволяет получить EXPLAIN/EXPLAIN ANALYZE? #

EXPLAIN и EXPLAIN ANALYZE показывают, как СУБД планирует выполнить SQL-запрос и где могут быть узкие места.

Главное отличие #

EXPLAIN показывает предполагаемый план выполнения, но сам запрос не выполняет.

EXPLAIN ANALYZE реально выполняет запрос и показывает не только план, но и фактические метрики: реальное время, реальное количество строк, количество повторов узлов плана и т.д. В PostgreSQL EXPLAIN ANALYZE выполняет запрос полностью, поэтому для INSERT, UPDATE, DELETE, MERGE изменения действительно произойдут, если не обернуть запрос в транзакцию и не сделать ROLLBACK.

Какой план выполнения выбран #

Можно увидеть, какие операции использует база:

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan
Bitmap Heap Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate
Limit

Например, по плану видно, читает ли PostgreSQL всю таблицу через Seq Scan или использует индекс через Index Scan. План представлен как дерево узлов: нижние узлы обычно читают данные из таблиц или индексов, а верхние узлы выполняют join, sort, aggregate и другие операции.

Оценочная стоимость выполнения #

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

(cost=0.00..445.00 rows=10000 width=244)

Где:

cost=0.00..445.00 — примерная стоимость выполнения узла плана. Это не миллисекунды, а внутренние условные единицы PostgreSQL.

rows=10000 — сколько строк планировщик ожидает получить из этого узла.

width=244 — средний ожидаемый размер строки в байтах.

Важно: cost — это не реальное время. Это оценка, по которой планировщик сравнивает разные варианты выполнения запроса.

Реальное время выполнения #

В EXPLAIN ANALYZE появляются фактические данные:

(actual time=0.017..0.051 rows=10 loops=1)

Где:

actual time=0.017..0.051 — реальное время начала и завершения работы узла, в миллисекундах.

rows=10 — сколько строк реально вернул этот узел.

loops=1 — сколько раз этот узел был выполнен.

Если loops больше 1, например при Nested Loop, то actual time и rows показываются как средние значения за один запуск узла. Чтобы примерно понять общее время узла, нужно умножать на loops. ( PostgreSQL)

Насколько планировщик ошибся #

Одна из самых важных вещей — сравнить:

rows=100

из оценки планировщика

с

actual rows=10000

из реального выполнения.

Если планировщик ожидал 100 строк, а реально получил 10000, это может означать:

данные плохо проанализированы;

устарела статистика;

нужен ANALYZE;

условие плохо оценивается;

индекс не помогает так, как ожидалось;

запрос написан неоптимально.

PostgreSQL прямо указывает, что при EXPLAIN ANALYZE особенно важно смотреть, насколько оценочное количество строк близко к реальному.

Используются ли индексы #

По плану видно:

используется ли индекс;

какой именно индекс используется;

применяется ли условие как Index Cond;

какие условия применяются уже после чтения строк через Filter;

сколько строк было отброшено фильтром.

Пример:

Index Cond: (unique1 < 100)
Filter: (stringu1 = 'xxx')
Rows Removed by Filter: 3000

Index Cond означает, что условие помогло при поиске по индексу.

Filter означает, что строки сначала были прочитаны, а потом отфильтрованы.

Rows Removed by Filter показывает, сколько строк было отброшено после чтения.

Затраты на сортировку, хеширование и память #

Для некоторых операций PostgreSQL показывает дополнительные детали:

Sort Method: quicksort  Memory: 74kB
Hash Cond: ...
Buckets: 1024  Batches: 1  Memory Usage: 35kB

Это помогает понять:

сортировка была в памяти или ушла на диск;

сколько памяти заняла сортировка;

как строился hash join;

не было ли слишком большого hash/sort этапа.

Буферы и I/O #

С опцией BUFFERS, а в актуальных версиях PostgreSQL при ANALYZE она может включаться автоматически, можно увидеть работу с буферами:

Buffers: shared hit=36 read=6

Это показывает:

hit — данные были найдены в shared buffers, то есть уже в памяти PostgreSQL;

read — данные пришлось читать с диска/ОС;

dirtied — страницы были изменены;

written — страницы были записаны.

Это полезно, когда запрос медленный не из-за CPU, а из-за чтения большого объёма данных.

Время планирования и выполнения #

В конце EXPLAIN ANALYZE обычно есть:

Planning Time: 0.485 ms
Execution Time: 0.073 ms

Planning Time — сколько времени ушло на построение плана.

Execution Time — сколько времени ушло на выполнение запроса.

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

Что можно диагностировать через EXPLAIN/EXPLAIN ANALYZE #

С помощью этих команд можно понять:

почему запрос медленный;

используется ли индекс;

почему база выбрала Seq Scan;

какой join используется: Nested Loop, Hash Join, Merge Join;

сколько строк реально проходит через каждый этап;

где планировщик ошибается в оценках;

есть ли лишняя сортировка;

есть ли тяжелая агрегация;

читает ли запрос много данных с диска;

какой узел плана занимает больше всего времени;

насколько LIMIT, ORDER BY, WHERE, JOIN, GROUP BY влияют на выполнение.

Пример чтения #

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'test@example.com';

Возможный результат:

Seq Scan on users
  (cost=0.00..1200.00 rows=1 width=80)
  (actual time=0.030..45.000 rows=1 loops=1)
  Filter: (email = 'test@example.com')
  Rows Removed by Filter: 999999
Execution Time: 45.100 ms

Это значит: PostgreSQL просканировал таблицу целиком, нашёл 1 строку, но отбросил почти миллион строк. Вероятная проблема — нет индекса по email.

Лучше было бы увидеть:

Index Scan using users_email_idx on users
  Index Cond: (email = 'test@example.com')

Тогда база ищет строку через индекс, а не перебирает всю таблицу.

Итог #

EXPLAIN показывает что PostgreSQL собирается сделать.

EXPLAIN ANALYZE показывает что PostgreSQL реально сделал.

Самое важное в выводе:

Seq Scan / Index Scan — как читаются данные;

cost — оценка планировщика;

rows — ожидаемые строки;

actual rows — реальные строки;

actual time — реальное время;

loops — сколько раз выполнялся узел;

Filter / Index Cond — где применяются условия;

Rows Removed by Filter — сколько строк зря прочитано;

Buffers — работа с памятью и диском;

Planning Time / Execution Time — время планирования и выполнения.

18. EXPLAIN vs EXPLAIN ANALYZE #

EXPLAIN показывает план выполнения запроса до его фактического выполнения. Он выводит, как СУБД собирается получить данные. Это включает:

• какой тип сканирования будет применяться (Seq Scan, Index Scan)
• сортировки и объединения (Nested Loop, Hash Join)
• предполагаемое количество строк и стоимость выполнения на каждом этапе

Explain analyze. Как устроен? На что обращать внимание? EXPLAIN ANALYZE не только показывает план выполнения, но и выполняет запрос, выводя фактическое время выполнения каждой операции. Полезные моменты:

• фактическое время (Actual Time) - сколько времени заняла операция
• ROWS - реальное количество строк, прошедших через каждый этап
• LOOPS - количество раз, когда этот шаг был выполнен (важно для вложенных циклов)

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 100000;

19. Что такое сбор статистики в PostgreSQL? | Для чего нужен ANALYZE в PostgreSQL? #

Что такое сбор статистики в PostgreSQL #

В контексте ANALYZE сбор статистики — это процесс, при котором PostgreSQL изучает содержимое таблицы и собирает примерные сведения о данных. Эти сведения потом использует планировщик запросов, чтобы выбрать более выгодный план выполнения. ANALYZE сохраняет результат в системном каталоге pg_statistic; официальная документация PostgreSQL прямо указывает, что эти данные использует query planner.

Важно: это не статистика “сколько запросов было выполнено”. Это статистика о содержимом таблиц и колонок.

Например, PostgreSQL собирает информацию примерно такого типа:

сколько строк в таблице
сколько страниц занимает таблица
какие значения встречаются чаще всего
примерное распределение значений
сколько уникальных значений в колонке
сколько NULL-значений
корреляция физического порядка строк и порядка значений

Эти данные нужны не пользователю напрямую, а оптимизатору.

Для чего нужен ANALYZE #

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

Например, есть запрос:

SELECT *
FROM users
WHERE city = 'Baku';

PostgreSQL должен решить, что выгоднее:

прочитать всю таблицу последовательно
или использовать индекс по city

Для этого ему нужно примерно понимать:

сколько всего строк в users
сколько строк имеют city = 'Baku'
насколько избирательное это условие

Если city = 'Baku' встречается у 1% строк — индекс может быть выгоден.

Если city = 'Baku' встречается у 80% строк — индекс может быть хуже, потому что придётся сходить по индексу и потом всё равно прочитать большую часть таблицы.

Пример #

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

CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    status text,
    created_at timestamp
);

CREATE INDEX idx_orders_status ON orders(status);

В таблице 10 миллионов заказов.

Распределение такое:

paid      — 9 000 000 строк
new       —   900 000 строк
failed    —   100 000 строк

Запрос:

SELECT *
FROM orders
WHERE status = 'failed';

Для failed индекс может быть полезен, потому что таких строк мало.

А для:

SELECT *
FROM orders
WHERE status = 'paid';

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

Чтобы принять такое решение, PostgreSQL использует статистику, собранную через ANALYZE.

Что будет, если статистика устарела #

Если в таблице сильно изменились данные, а статистика старая, PostgreSQL может выбрать плохой план.

Например, раньше в таблице было мало строк:

orders: 10 000 строк

Потом стало много:

orders: 10 000 000 строк

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

Типичные последствия устаревшей статистики:

выбирается Seq Scan вместо Index Scan
выбирается Index Scan вместо Seq Scan
неудачный порядок JOIN
неподходящий алгоритм JOIN: Nested Loop / Hash Join / Merge Join
неверная оценка количества строк

Главная проблема обычно видна в EXPLAIN ANALYZE: PostgreSQL ожидал одно количество строк, а реально получил совсем другое.

Например:

estimated rows: 100
actual rows: 500000

Это признак, что планировщик сильно ошибся в оценке.

Как запускается ANALYZE #

Можно запустить вручную:

ANALYZE;

Это обновит статистику по всем таблицам текущей базы данных.

Можно по конкретной таблице:

ANALYZE users;

Можно по конкретным колонкам:

ANALYZE users(email, city);

Также можно совместить с VACUUM:

VACUUM ANALYZE users;

VACUUM ANALYZE выполняет VACUUM, а затем ANALYZE; PostgreSQL описывает это как обновление статистики, которую планировщик использует для выбора эффективного плана запроса.

Автоматический ANALYZE #

Обычно вручную постоянно запускать ANALYZE не нужно. В PostgreSQL этим занимается autovacuum daemon: он не только чистит мёртвые версии строк через VACUUM, но и автоматически запускает ANALYZE для таблиц, где данные заметно изменились. Документация указывает, что в стандартной конфигурации autovacuum автоматически анализирует таблицы при загрузке данных и при изменениях в процессе работы.

Проверить, когда таблица последний раз анализировалась, можно так:

SELECT
    schemaname,
    relname,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autoanalyze DESC NULLS LAST;

Где посмотреть собранную статистику #

Удобное представление:

SELECT *
FROM pg_stats
WHERE tablename = 'users';

Там можно увидеть статистику по колонкам: долю NULL, частые значения, гистограммы и другие данные.

Системный каталог низкого уровня — pg_statistic, но напрямую с ним обычно работают редко. PostgreSQL указывает, что pg_statistic хранит статистические данные о содержимом базы, а записи создаются командой ANALYZE; при этом статистика является приблизительной.

ANALYZE не выполняет запрос #

Важно не путать:

ANALYZE users;

и

EXPLAIN ANALYZE
SELECT * FROM users;

Это разные вещи.

ANALYZE users; — собирает статистику по таблице.

EXPLAIN ANALYZE SELECT ... — реально выполняет запрос и показывает фактический план выполнения с реальным временем и количеством строк.

Коротко #

ANALYZE в PostgreSQL нужен, чтобы обновить статистику о данных в таблицах.

Эта статистика помогает планировщику решать:

использовать индекс или читать таблицу целиком
в каком порядке соединять таблицы
какой тип JOIN выбрать
какой план будет дешевле
сколько строк примерно вернёт условие

Без актуальной статистики PostgreSQL может выбрать плохой план, даже если индексы созданы правильно.

20. Что делает команда VACUUM в PostgreSQL и зачем она нужна? #

Что делает VACUUM #

VACUUM в PostgreSQL очищает таблицы от “мёртвых” версий строк, которые появились после UPDATE и DELETE.

PostgreSQL работает через MVCC: когда строку обновляют, старая версия не удаляется физически сразу. Вместо этого появляется новая версия строки, а старая остаётся в таблице, пока она ещё может быть нужна старым транзакциям. После того как старая версия уже никому не нужна, её можно убрать. Именно этим занимается VACUUM: он освобождает место, занятое dead tuples — мёртвыми строковыми версиями. В официальной документации PostgreSQL указано, что удалённые или устаревшие после UPDATE строки физически не удаляются из таблицы до выполнения VACUUM.

Пример:

UPDATE users
SET email = 'new@mail.com'
WHERE id = 10;

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

старая версия строки: id=10, email='old@mail.com'  ← стала мёртвой
новая версия строки:  id=10, email='new@mail.com'  ← актуальная

VACUUM потом приходит и очищает старую версию, когда она уже не нужна активным транзакциям.

Зачем он нужен #

Без VACUUM таблицы и индексы постепенно раздуваются.

Это называется bloat — лишнее место внутри таблиц и индексов. Запросы начинают читать больше страниц, чем реально нужно, из-за чего падает производительность.

VACUUM нужен для нескольких вещей:

1. Убирать мёртвые версии строк после UPDATE и DELETE.
2. Освобождать место внутри таблицы для повторного использования.
3. Чистить мёртвые записи в индексах.
4. Поддерживать нормальную производительность запросов.
5. Помогать предотвращать transaction ID wraparound.

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

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

Простая аналогия #

Таблица после множества UPDATE и DELETE похожа на файл с пометками:

строка 1 — актуальная
строка 2 — удалена, но физически ещё лежит
строка 3 — старая версия после UPDATE
строка 4 — актуальная
строка 5 — удалена, но физически ещё лежит

VACUUM проходит и говорит:

строка 2 больше никому не нужна → место можно переиспользовать
строка 3 больше никому не нужна → место можно переиспользовать
строка 5 больше никому не нужна → место можно переиспользовать

Но он не обязательно “сжимает файл” на диске. Он скорее наводит порядок внутри таблицы.

VACUUM и VACUUM FULL #

Есть обычный VACUUM:

VACUUM users;

Он очищает мёртвые строки и освобождает место для повторного использования внутри таблицы. Обычно он не блокирует обычные SELECT, INSERT, UPDATE, DELETE так жёстко.

Есть VACUUM FULL:

VACUUM FULL users;

Он физически переписывает таблицу в новый файл без лишнего свободного места. Поэтому после него размер таблицы на диске может реально уменьшиться. Но он намного тяжелее: PostgreSQL указывает, что VACUUM FULL медленнее и требует ACCESS EXCLUSIVE lock на таблицу, то есть блокирует нормальную работу с ней на время выполнения.

Разница:

VACUUM:
  очищает мёртвые строки
  место остаётся внутри таблицы для переиспользования
  обычно не требует полной блокировки таблицы

VACUUM FULL:
  переписывает таблицу заново
  реально уменьшает размер файла на диске
  требует сильную блокировку
  работает дольше

VACUUM FULL не стоит запускать просто “для профилактики” на живой большой таблице. Его используют, когда таблица сильно раздулась и нужно именно вернуть место операционной системе.

Что такое autovacuum #

Обычно вручную постоянно запускать VACUUM не нужно. В PostgreSQL есть autovacuum — фоновый механизм, который сам запускает VACUUM и ANALYZE, когда таблицы достаточно изменились. В документации PostgreSQL указано, что autovacuum daemon в каждом цикле проверяет базу и выполняет VACUUM и ANALYZE для таблиц, где это нужно.

Проверить, когда autovacuum последний раз работал по таблицам:

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Особенно полезно смотреть на n_dead_tup — примерное количество мёртвых строк.

VACUUM и ANALYZE — не одно и то же #

VACUUM чистит мёртвые строки.

ANALYZE собирает статистику для планировщика запросов.

Можно запустить вместе:

VACUUM ANALYZE users;

Это сначала выполняет VACUUM, а потом ANALYZE. В PostgreSQL документации указано, что VACUUM ANALYZE выполняет обе операции для выбранных таблиц, а ANALYZE обновляет статистику, которую планировщик использует для выбора эффективного плана запроса.

То есть:

VACUUM        → чистит физический мусор после UPDATE/DELETE
ANALYZE       → обновляет статистику о данных
VACUUM ANALYZE → делает и то, и другое

Почему VACUUM важен для индексов #

После UPDATE и DELETE мёртвые записи могут оставаться не только в таблице, но и в индексах. Если их долго не чистить, индексы тоже раздуваются, становятся тяжелее, и индексные сканы могут работать хуже. PostgreSQL указывает, что если index cleanup не выполняется регулярно, производительность может ухудшаться, потому что индексы накапливают мёртвые записи.

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

Когда вручную запускать VACUUM #

Обычно autovacuum достаточно. Но вручную VACUUM может быть полезен после массовых операций:

DELETE FROM logs
WHERE created_at < now() - interval '1 year';

или:

UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '2 years';

После таких операций появляется много мёртвых строк. Тогда можно выполнить:

VACUUM logs;

или, если нужно ещё обновить статистику:

VACUUM ANALYZE logs;

А если после огромного удаления нужно именно уменьшить размер файла на диске:

VACUUM FULL logs;

Но VACUUM FULL надо применять осторожно из-за блокировки.

Коротко #

VACUUM — это механизм уборки в PostgreSQL.

Он нужен, потому что PostgreSQL не удаляет старые версии строк сразу после UPDATE и DELETE. Сначала они остаются в таблице из-за MVCC, а потом VACUUM освобождает занятое ими место.

Главное:

VACUUM чистит мёртвые строки.
VACUUM уменьшает bloat.
VACUUM помогает таблицам и индексам не раздуваться.
Обычный VACUUM обычно не возвращает место ОС, а делает его доступным внутри таблицы.
VACUUM FULL реально сжимает таблицу на диске, но блокирует её.
Autovacuum обычно делает эту работу автоматически.
VACUUM ANALYZE = очистка + обновление статистики.

21. Что делает команда TRUNCATE и чем она отличается от DELETE? #

• DELETE - оператор DML, удаляет записи из таблицы, которые удовлетворяют условиям WHERE. Ведёт журналирование. Не сбрасывает последовательности. Медленнее. Есть возможность восстановить данные

• TRUNCATE - DDL оператор, удаляет все строки из таблицы, быстро высвобождая занятую память. Не ведёт журналирование, не сбрасывает последовательности. Нет возможности восстановить данные

22. Какие решения по конфигурации базы данных обычно принимает разработчик на проекте? #

Что обычно решает backend-разработчик #

Разработчик обычно не “настраивает всю PostgreSQL как DBA”, но принимает решения, которые напрямую влияют на работу приложения с БД: схема данных, индексы, транзакции, пул соединений, таймауты, миграции и настройки ORM.

1. Как приложение подключается к БД #

Разработчик задаёт конфигурацию подключения:

DB_HOST=postgres
DB_PORT=5432
DB_NAME=app_db
DB_USER=app_user
DB_PASSWORD=...
DB_SSLMODE=require

Обычно это выносится в .env, Kubernetes secrets, Docker Compose env или настройки CI/CD.

Также решается, будут ли разные подключения для:

локальной разработки
тестов
staging
production
read replica

В Django это обычно настраивается через DATABASES, а время жизни persistent database connection — через CONN_MAX_AGE; документация Django описывает его как максимальное время жизни подключения к БД.

2. Размер пула соединений #

Если приложение использует SQLAlchemy/FastAPI, разработчик часто настраивает пул:

engine = create_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=1800,
)

Здесь решается:

сколько постоянных соединений держать
сколько временных соединений можно открыть сверх лимита
проверять ли соединение перед использованием
когда пересоздавать старые соединения

SQLAlchemy описывает pool_size как количество соединений, поддерживаемых в пуле, а max_overflow — как дополнительные соединения сверх pool_size, которые можно открыть при нехватке свободных подключений.

Это важно, потому что если каждый backend-инстанс открывает слишком много соединений, можно упереться в лимит PostgreSQL. max_connections в PostgreSQL определяет максимальное количество одновременных подключений к серверу.

3. Структура таблиц и типы данных #

Разработчик решает, как будут выглядеть таблицы:

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    email text NOT NULL,
    username text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Здесь принимаются решения:

какой primary key использовать: serial/bigserial/uuid
какие поля обязательные
какие типы данных выбрать
использовать ли jsonb, uuid, numeric, timestamptz
как хранить даты и время
как нормализовать связи между сущностями

Например, для времени в backend-проектах чаще безопаснее использовать timestamptz, если дата связана с реальным моментом времени, а не просто календарной датой.

4. Ограничения целостности #

Разработчик определяет ограничения:

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

или:

ALTER TABLE orders
ADD CONSTRAINT orders_user_id_fk
FOREIGN KEY (user_id) REFERENCES users(id);

Обычно решаются такие вещи:

PRIMARY KEY
UNIQUE
FOREIGN KEY
NOT NULL
CHECK
ON DELETE CASCADE / RESTRICT / SET NULL

Это не просто “красота схемы”. Это защита данных на уровне БД. Например, если email должен быть уникальным, лучше закрепить это UNIQUE-ограничением, а не только проверкой в коде.

PostgreSQL автоматически создаёт уникальный индекс при создании UNIQUE constraint или PRIMARY KEY.

5. Индексы #

Разработчик обычно решает, какие индексы нужны под реальные запросы приложения.

Например:

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);

Индекс имеет смысл, если по колонке часто выполняются:

WHERE user_id = ?
WHERE status = ?
ORDER BY created_at DESC
JOIN orders ON orders.user_id = users.id

PostgreSQL описывает индексы как способ повысить производительность поиска, но также предупреждает, что неправильное использование индексов может ухудшать производительность.

Отдельно важный момент: PostgreSQL не всегда автоматически создаёт индекс на колонке, где объявлен foreign key. Документация указывает, что при DELETE или UPDATE родительской строки БД должна проверять дочернюю таблицу, поэтому часто имеет смысл индексировать referencing columns — то есть колонки, где лежит внешний ключ.

6. Миграции #

Разработчик решает, как изменения схемы будут доставляться в БД:

Django migrations
Alembic
ручные SQL-миграции
миграции через CI/CD

Обычно нужно решить:

как именовать миграции
кто создаёт миграции
как решать конфликты миграций
можно ли делать destructive changes
как откатывать миграции
как безопасно менять большие таблицы

Например, опасная миграция:

ALTER TABLE users ADD COLUMN age integer NOT NULL;

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

ALTER TABLE users ADD COLUMN age integer;
-- заполнить данные
-- потом добавить NOT NULL

7. Границы транзакций #

Разработчик решает, какие операции должны выполняться атомарно.

Например:

with transaction.atomic():
    order = Order.objects.create(user=user)
    Payment.objects.create(order=order, amount=amount)
    StockReservation.objects.create(order=order, item=item)

Здесь важно определить:

что должно быть в одной транзакции
где транзакция должна начинаться и заканчиваться
можно ли держать транзакцию во время внешнего HTTP-запроса
какой уровень изоляции нужен
нужны ли SELECT FOR UPDATE / advisory locks

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

8. Таймауты #

Разработчик часто задаёт таймауты на уровне подключения, роли или сессии:

SET statement_timeout = '30s';
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '60s';

Смысл:

statement_timeout — ограничить длительность запроса
lock_timeout — не ждать блокировку бесконечно
idle_in_transaction_session_timeout — прибить зависшую открытую транзакцию

В PostgreSQL statement_timeout ограничивает максимальное время выполнения SQL-команды, а idle_in_transaction_session_timeout завершает сессию, которая слишком долго простаивает внутри открытой транзакции.

Для backend-приложения это важно: один зависший запрос или забытая транзакция не должны держать БД в плохом состоянии бесконечно.

9. Настройки read/write split и реплик #

Если есть реплики, разработчик может решать:

какие запросы идут в master
какие запросы идут в read replica
можно ли читать устаревшие данные
что делать после записи, если нужно сразу прочитать свежие данные

Например:

POST /orders       → primary database
GET /orders/list   → read replica

Но тут есть проблема replica lag. После записи данные на реплике могут появиться не мгновенно. Поэтому нельзя бездумно отправлять все SELECT на реплики.

10. Настройки тестовой БД #

Разработчик обычно решает:

использовать SQLite или тот же PostgreSQL
создавать отдельную test database
откатывать тесты транзакциями или пересоздавать схему
запускать ли миграции в тестах
чем наполнять тестовые данные

Для серьёзного backend-проекта лучше тестировать на той же СУБД, что и production. SQLite и PostgreSQL по-разному работают с типами, блокировками, JSON, индексами, ограничениями и SQL-синтаксисом.

11. Расширения PostgreSQL #

Иногда разработчик решает, какие расширения нужны:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS postgis;

Примеры:

uuid-ossp / pgcrypto — генерация UUID
pg_trgm — ускорение поиска по похожести / ILIKE
citext — case-insensitive text
postgis — геоданные

Это уже влияет не только на код, но и на инфраструктуру: расширения должны быть доступны в окружении БД.

12. Логирование и диагностика запросов #

Разработчик обычно настраивает или просит включить:

логирование медленных запросов
EXPLAIN / EXPLAIN ANALYZE для проблемных запросов
метрики количества соединений
метрики длительности запросов
контроль deadlocks

На уровне приложения это может быть логирование SQL-запросов ORM, middleware для slow queries, OpenTelemetry, APM.

Что чаще не решает обычный разработчик один #

Обычно это зона DBA/DevOps/SRE, но разработчик может участвовать:

shared_buffers
work_mem
maintenance_work_mem
wal_level
checkpoint_timeout
max_wal_size
autovacuum thresholds
replication slots
backup policy
HA/failover
physical storage
partitioning strategy для очень больших таблиц

Например, shared_buffers и другие параметры памяти относятся к resource consumption settings PostgreSQL и обычно требуют понимания нагрузки и ресурсов сервера.

Коротко #

Обычно backend-разработчик принимает решения по таким вещам:

подключение к БД
пул соединений
структура таблиц
типы данных
constraints
индексы
миграции
границы транзакций
таймауты запросов
настройки ORM
тестовая БД
read/write routing
расширения PostgreSQL
логирование медленных запросов

А низкоуровневый тюнинг PostgreSQL, репликация, бэкапы, WAL, память и autovacuum обычно решаются вместе с DBA/DevOps, потому что там ошибка может повлиять на всю БД, а не только на один сервис.

23. Как в PostgreSQL работает блокировка строки (row-level lock)? #

Что такое row-level lock #

Row-level lock — это блокировка конкретной строки, а не всей таблицы.

Она нужна, чтобы две транзакции не могли одновременно конфликтно изменить одну и ту же строку. В PostgreSQL row-level lock не мешает обычному чтению данных: он блокирует только другие операции записи или другие попытки взять конфликтующую блокировку на ту же строку. Официальная документация PostgreSQL прямо указывает: row-level locks не влияют на обычный запрос данных, они блокируют только writers and lockers для той же строки.

То есть обычный SELECT обычно не ждёт row-level lock:

SELECT *
FROM accounts
WHERE id = 1;

Но UPDATE, DELETE или SELECT ... FOR UPDATE по той же строке могут ждать.

Простой пример #

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

CREATE TABLE accounts (
    id bigint PRIMARY KEY,
    balance numeric NOT NULL
);

Первая транзакция:

BEGIN;

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

-- транзакция пока не завершена

Когда PostgreSQL выполняет этот UPDATE, он берёт блокировку на строку accounts.id = 1.

Вторая транзакция в это время пытается изменить ту же строку:

BEGIN;

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

Вторая транзакция не сможет сразу выполнить изменение. Она будет ждать, пока первая сделает:

COMMIT;

или:

ROLLBACK;

После этого PostgreSQL продолжит выполнение второй транзакции.

Что именно блокируется #

Блокируется не вся таблица accounts, а конкретная строка:

accounts.id = 1

Другая строка той же таблицы может обновляться параллельно:

UPDATE accounts
SET balance = balance - 50
WHERE id = 2;

Если первая транзакция заблокировала только id = 1, то id = 2 не конфликтует.

Обычный SELECT не блокируется #

В PostgreSQL работает MVCC. Поэтому читающий запрос обычно видит согласованную версию данных, а не ждёт, пока завершится чужой UPDATE.

Пример:

-- Transaction 1
BEGIN;

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

Пока первая транзакция не сделала COMMIT, другой запрос:

-- Transaction 2
SELECT balance
FROM accounts
WHERE id = 1;

обычно прочитает старую зафиксированную версию строки.

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

SELECT FOR UPDATE #

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

Пример:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

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

COMMIT;

FOR UPDATE блокирует выбранные строки так, как будто они будут обновляться. Другие транзакции не смогут изменить, удалить или взять конфликтующую блокировку на эти строки до завершения текущей транзакции.

Это нужно, например, при работе с балансом:

1. прочитать текущий баланс
2. проверить, хватает ли денег
3. списать деньги

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

Что происходит при конфликте #

Допустим:

-- Transaction 1
BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

Теперь строка id = 1 заблокирована.

Вторая транзакция:

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

Она будет ждать завершения первой транзакции.

Если первая транзакция сделает:

COMMIT;

вторая продолжит работу уже с учётом актуального состояния строки.

Если первая сделает:

ROLLBACK;

блокировка освободится, и вторая транзакция сможет выполнить свой UPDATE.

Блокировка держится до конца транзакции #

Row-level lock обычно удерживается до завершения транзакции:

COMMIT;

или:

ROLLBACK;

PostgreSQL указывает, что row-level locks освобождаются в конце транзакции или при rollback к savepoint, если блокировка была взята после этого savepoint.

Поэтому опасно держать транзакцию открытой долго:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

-- здесь приложение ждёт внешний API, ввод пользователя или долгую операцию

COMMIT;

Пока транзакция открыта, другие операции по этой строке могут стоять в ожидании.

Основные режимы row-level lock #

В PostgreSQL есть несколько режимов строковых блокировок:

FOR UPDATE
FOR NO KEY UPDATE
FOR SHARE
FOR KEY SHARE

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

FOR NO KEY UPDATE слабее: PostgreSQL обычно использует его для UPDATE, который не меняет ключевые колонки, важные для foreign key.

FOR SHARE позволяет другим транзакциям тоже брать shared lock, но мешает изменять или удалять строку.

FOR KEY SHARE ещё слабее: он защищает ключевые значения строки, например когда нужно не дать удалить родительскую строку, пока на неё ссылается foreign key. PostgreSQL описывает эти режимы и их конфликты в разделе Row-Level Lock Modes.

На практике чаще всего вручную используют:

SELECT ... FOR UPDATE;

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

NOWAIT и SKIP LOCKED #

По умолчанию, если строка уже заблокирована, PostgreSQL будет ждать.

Иногда ждать не нужно. Тогда используют NOWAIT:

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;

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

Для очередей задач часто используют SKIP LOCKED:

SELECT *
FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

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

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

worker 1 забрал jobs 1-10
worker 2 пропустил заблокированные 1-10 и забрал 11-20
worker 3 забрал 21-30

Как увидеть блокировки #

Для диагностики используют pg_locks.

Пример:

SELECT
    locktype,
    relation::regclass,
    page,
    tuple,
    transactionid,
    pid,
    mode,
    granted,
    waitstart
FROM pg_locks
WHERE NOT granted;

pg_locks показывает активные блокировки и ожидания блокировок. В документации PostgreSQL указано, что одна строка в pg_locks соответствует lockable object, режиму блокировки и процессу; поле granted = false означает, что процесс ждёт блокировку.

Важный нюанс: row-level locks обычно хранятся на диске, а не в памяти, поэтому сами строковые блокировки не всегда видны в pg_locks как tuple. Если процесс ждёт строковую блокировку, он часто отображается как ожидание transaction ID владельца этой блокировки.

Deadlock на строках #

Row-level locks могут привести к deadlock.

Пример:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Параллельно:

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Что происходит:

Transaction 1 заблокировала строку id = 1
Transaction 2 заблокировала строку id = 2

Transaction 1 ждёт id = 2
Transaction 2 ждёт id = 1

Получается deadlock. PostgreSQL умеет автоматически обнаруживать deadlock и завершает одну из транзакций, чтобы вторая могла продолжить. В документации также указано, что deadlock может возникать из-за row-level locks, и лучшая защита — брать блокировки в одинаковом порядке во всех транзакциях.

Правильнее:

-- всегда блокировать счета по возрастанию id
SELECT *
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

Типичный backend-сценарий #

Например, нужно безопасно списать деньги:

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

-- приложение проверяет balance

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

COMMIT;

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

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

Коротко #

Row-level lock в PostgreSQL — это блокировка конкретной строки.

Главное:

UPDATE и DELETE автоматически блокируют изменяемые строки.
SELECT FOR UPDATE блокирует выбранные строки явно.
Обычный SELECT обычно не ждёт row-level lock из-за MVCC.
Другой UPDATE/DELETE по той же строке будет ждать.
Блокировка держится до COMMIT или ROLLBACK.
Долгие транзакции усиливают проблему блокировок.
Deadlock возможен, если разные транзакции блокируют строки в разном порядке.

Практический смысл: row-level lock нужен, чтобы безопасно выполнять конкурентные изменения одной и той же строки без потери данных и гонок.

24. Что такое блокировки в базе данных? Как они работают и какие бывают? #

Блокировки (или локи) в базе данных - это механизм, управляющий конкурентным доступом к данным, чтобы избежать конфликтов и ошибок. Есть два типа блокировок

Оптимистическая блокировка - при завершении транзакции сверяется текущая версия данных и версия, полученная в начале транзакции. Если они не совпадают, то транзакция откатывается. Подразумевает, что конфликты будут происходить нечасто

SELECT version  
FROM orders  
WHERE id = 1;  
  
-- Обновляем, если версия не изменилась  
UPDATE orders  
SET status = 'processed', version = version + 1  
WHERE id = 1 AND version = 5;  

Пессимистическая блокировка - при начале транзакции изменяемые данные блокируются для всех остальных, пока текущая транзакция не закончится

SELECT *  
FROM orders  
WHERE id = 1  
FOR UPDATE;  

Сравнение

25. Что обычно используют в качестве Primary Key в реляционной базе данных (Какие типы данных чаще всего выбирают)? #

В реляционных БД чаще всего используют суррогатный Primary Key — отдельное поле id, которое не несёт бизнес-смысла.

Самые частые варианты #

Что обычно выбирают на практике #

Для большинства проектов:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Это хороший дефолт: компактный, быстрый для индексов, хорошо работает с B-tree, автоматически генерируется базой.

В PostgreSQL IDENTITY — современный способ автоинкрементного ключа через неявную sequence; документация прямо показывает пример с id bigint GENERATED ... AS IDENTITY. При этом сама IDENTITY-колонка не гарантирует уникальность без PRIMARY KEY или UNIQUE. ( PostgreSQL)

Почему часто берут BIGINT, а не INTEGER #

INTEGER занимает 4 байта, BIGINT — 8 байт. Но INTEGER ограничен примерно 2.1 млрд положительных значений, а для долгоживущих таблиц, логов, заказов, событий, уведомлений и аудита это может стать проблемой. В PostgreSQL serial создаёт integer, а bigserial создаёт bigint; документация рекомендует bigserial, если ожидается больше 2^31 идентификаторов за жизнь таблицы.

UUID как Primary Key #

UUID используют, когда id нужно генерировать независимо от БД или не хочется раскрывать последовательные номера наружу:

id UUID PRIMARY KEY

В PostgreSQL есть нативный тип uuid, то есть его не нужно хранить как varchar или text.

Минус UUID: он больше по размеру, хуже для локальности вставок в B-tree, особенно если используется случайный UUID v4. Поэтому для внутренних таблиц обычный BIGINT IDENTITY часто эффективнее.

AUTO_INCREMENT в MySQL #

В MySQL типичный вариант:

id BIGINT AUTO_INCREMENT PRIMARY KEY

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

Итог #

Лучший стандартный выбор:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Для PostgreSQL это предпочтительнее старого SERIAL, потому что IDENTITY явно описывает авто生成ируемую колонку.
UUID стоит брать, когда нужен распределённый id, внешний публичный id или генерация идентификаторов вне базы.
Составной Primary Key уместен в связующих таблицах.
Natural key лучше использовать осторожно, потому что бизнес-значения могут меняться.

26. Как работает автоинкрементный id через sequence в PostgreSQL? #

Суть #

В PostgreSQL автоинкрементный id обычно работает не как специальное свойство самой колонки, а через отдельный объект — sequence.

Sequence — это генератор чисел. PostgreSQL хранит его отдельно от таблицы и при вставке новой строки берёт следующее значение через функцию nextval().

Пример:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL
);

Когда выполняется:

INSERT INTO users (username) VALUES ('alex');

PostgreSQL фактически получает новый id из внутренней sequence, связанной с колонкой users.id. В документации PostgreSQL identity column прямо описана как колонка, которая автоматически генерируется из неявной sequence.

Что происходит внутри #

Упрощённо:

CREATE SEQUENCE users_id_seq;

CREATE TABLE users (
    id BIGINT NOT NULL DEFAULT nextval('users_id_seq'),
    username TEXT NOT NULL
);

ALTER SEQUENCE users_id_seq OWNED BY users.id;

То есть у колонки id стоит значение по умолчанию:

DEFAULT nextval('users_id_seq')

Когда ты вставляешь строку и не указываешь id, PostgreSQL вызывает nextval(). Эта функция атомарно увеличивает sequence и возвращает новое значение. Даже если несколько транзакций одновременно делают INSERT, каждая получит отдельное значение.

Пример по шагам #

INSERT INTO users (username) VALUES ('A');
-- nextval() вернул 1

INSERT INTO users (username) VALUES ('B');
-- nextval() вернул 2

INSERT INTO users (username) VALUES ('C');
-- nextval() вернул 3

В таблице получится:

id | username
---+---------
1  | A
2  | B
3  | C

Важный момент: sequence не откатывается при rollback #

Например:

BEGIN;

INSERT INTO users (username) VALUES ('bad_user');
-- id = 4

ROLLBACK;

Строка откатится, но значение 4 уже было выдано sequence. Следующая вставка получит уже 5, а не 4.

Поэтому пропуски в id — это нормально:

1, 2, 3, 5, 6

id через sequence гарантирует не «идеальную нумерацию без дыр», а безопасную выдачу уникальных значений при конкурентных вставках. nextval() в PostgreSQL атомарен и безопасен для одновременных вызовов из разных сессий.

Чем отличаются SERIAL и IDENTITY #

Старый способ:

id BIGSERIAL PRIMARY KEY

Современный способ:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

SERIAL / BIGSERIAL — это псевдотипы. Они создают sequence и ставят nextval() как DEFAULT для колонки. А IDENTITY — более современный SQL-стандартный синтаксис, где PostgreSQL сам создаёт связанную sequence для identity-колонки. Связанную sequence можно получить через pg_get_serial_sequence(), причём функция работает и для serial, и для identity-колонок.

Как посмотреть sequence у колонки #

SELECT pg_get_serial_sequence('users', 'id');

Результат будет примерно:

public.users_id_seq

Потом можно посмотреть текущее состояние:

SELECT last_value, is_called
FROM users_id_seq;

Но last_value — это не всегда «следующий id». Это последнее значение, уже выделенное sequence. PostgreSQL отдельно отмечает, что last_value может устареть к моменту просмотра, если другие сессии параллельно вызывают nextval().

Как вручную получить следующий id #

SELECT nextval('users_id_seq');

Это сразу сдвинет sequence вперёд.

SELECT currval('users_id_seq');

currval() вернёт последнее значение sequence, полученное через nextval() в текущей сессии. То есть сначала в этой же сессии должен быть вызван nextval().

Как сбросить или синхронизировать sequence #

После ручной загрузки данных может возникнуть проблема:

INSERT INTO users (id, username) VALUES (100, 'manual');

Если sequence всё ещё думает, что последний id был 5, следующая авто-вставка может попытаться вставить 6, а не 101.

Обычно sequence синхронизируют так:

SELECT setval(
    pg_get_serial_sequence('users', 'id'),
    COALESCE(MAX(id), 1)
)
FROM users;

После этого следующий nextval() пойдёт дальше от текущего максимального id.

Главное #

Автоинкрементный id в PostgreSQL — это связка:

колонка id
  ↓
DEFAULT nextval(...)
  ↓
sequence
  ↓
следующее число

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

27. Можно ли создать таблицу без Primary Key? #

Да, таблицу можно создать без Primary Key. В PostgreSQL это разрешено.

Пример:

CREATE TABLE logs (
    message TEXT,
    created_at TIMESTAMP
);

Такая таблица будет работать: в неё можно делать INSERT, SELECT, UPDATE, DELETE.

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

Что теряется без Primary Key #

Primary Key даёт сразу несколько вещей:

1. Уникальную идентификацию строки

Например, в таблице пользователей каждая строка должна иметь однозначный идентификатор:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Без него сложнее точно обратиться к конкретной записи.

2. NOT NULL + UNIQUE

В PostgreSQL добавление PRIMARY KEY автоматически создаёт уникальный B-tree индекс и делает колонку или набор колонок NOT NULL. То есть Primary Key не может быть NULL и не может повторяться.

3. Связи через Foreign Key

Обычно другие таблицы ссылаются именно на Primary Key:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT REFERENCES users(id)
);

Без ключа таблицу сложнее нормально использовать в связях.

4. Удобную работу ORM

Django ORM, SQLAlchemy ORM и большинство других ORM ожидают, что у модели есть стабильный уникальный идентификатор. Без Primary Key начинаются ограничения: сложнее обновлять, удалять и однозначно отслеживать объект.

Когда таблица без Primary Key допустима #

Так делают в отдельных случаях:

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

Например:

CREATE TABLE raw_import_events (
    payload JSONB,
    imported_at TIMESTAMP DEFAULT now()
);

Если это просто временная таблица для загрузки данных, Primary Key может быть не нужен.

Когда Primary Key лучше обязательно делать #

Для обычных таблиц приложения:

users,
orders,
products,
notifications,
files,
payments,
comments,
posts

Primary Key лучше делать всегда.

Обычный вариант:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL
);

Важный нюанс про MySQL #

В MySQL InnoDB таблица тоже может быть создана без явного Primary Key, но в новых версиях есть механизм Generated Invisible Primary Key: при включённой настройке sql_generate_invisible_primary_key MySQL может автоматически добавить скрытый первичный ключ для InnoDB-таблицы без явного PK.

То есть в некоторых СУБД или конфигурациях отсутствие явного Primary Key может обрабатываться особым образом.

Итог #

Технически — да, таблицу можно создать без Primary Key.

Но для нормальной прикладной таблицы лучше почти всегда добавлять:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

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

28. Чем отличается суррогатный ключ от натурального ключа? #

Суррогатный ключ — искусственный технический идентификатор строки.

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

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

email TEXT UNIQUE NOT NULL

PostgreSQL определяет PRIMARY KEY как колонку или набор колонок, которые уникально идентифицируют строки; значения Primary Key должны быть UNIQUE и NOT NULL.

Пример #

Таблица пользователей:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username TEXT NOT NULL
);

Здесь:

id    -> суррогатный ключ
email -> натуральный ключ-кандидат

id не имеет смысла вне базы. Это просто технический номер строки.

email имеет бизнес-смысл: пользователь реально использует этот адрес, по нему его можно идентифицировать.

Главное отличие #

IBM описывает натуральные ключи как осмысленные значения, идентифицирующие записи, например номера соцстрахования, календарные даты или SKU товара. Суррогатные ключи, по IBM и Kimball Group, используются как искусственные/замещающие ключи, особенно когда натуральные ключи могут меняться, переиспользоваться или быть неудобными для связей.

Почему чаще используют суррогатный ключ #

Потому что он стабильный.

Например, если сделать email Primary Key:

CREATE TABLE users (
    email TEXT PRIMARY KEY,
    username TEXT NOT NULL
);

И на него ссылается другая таблица:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_email TEXT REFERENCES users(email)
);

Если пользователь сменит email, придётся обновлять связи. Это можно сделать через ON UPDATE CASCADE, но сама модель становится более хрупкой.

С суррогатным ключом проще:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT REFERENCES users(id)
);

Теперь email можно менять, а связь остаётся через стабильный user_id.

Натуральный ключ всё равно часто нужен #

Суррогатный id не отменяет бизнес-уникальность.

Плохой вариант:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL
);

Так можно случайно создать двух пользователей с одинаковым email.

Правильнее:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

То есть:

id    -> Primary Key для связей
email -> UNIQUE constraint для бизнес-уникальности

PostgreSQL автоматически создаёт уникальный индекс для PRIMARY KEY и UNIQUE constraints, и именно этот индекс обеспечивает проверку уникальности.

Когда натуральный ключ можно использовать как Primary Key #

Когда значение:

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

Примеры:

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,
    name TEXT NOT NULL
);

Здесь code = 'AZ', TR, US может быть нормальным натуральным ключом.

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

Итог #

В прикладной разработке чаще делают так:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username TEXT NOT NULL
);

id — суррогатный ключ, стабильный технический идентификатор.
email — натуральный бизнес-идентификатор, но не основной ключ для связей.

Это самый практичный подход: связи строятся по стабильному id, а бизнес-уникальность защищается отдельным UNIQUE.

29. Что такое GIN index в PostgreSQL? #

Что такое GIN index #

GIN index в PostgreSQL — это индекс для поиска внутри составных значений: массивов, jsonb, документов полнотекстового поиска и похожих структур.

GIN расшифровывается как Generalized Inverted Index — обобщённый инвертированный индекс. PostgreSQL описывает его как индекс для случаев, когда значение состоит из нескольких элементов, а запрос ищет наличие конкретного элемента внутри этого значения. Например: документ содержит слова, массив содержит элементы, jsonb содержит ключи и значения.

Простая идея #

Обычный B-tree индекс хорошо работает с простыми значениями:

WHERE id = 10
WHERE email = 'a@test.com'
WHERE created_at > '2026-01-01'

А GIN нужен, когда в одной ячейке лежит много внутренних элементов.

Например:

tags = ARRAY['python', 'postgresql', 'backend']

GIN индексирует не всю ячейку как одно значение, а отдельные элементы:

python     -> строки, где есть python
postgresql -> строки, где есть postgresql
backend    -> строки, где есть backend

То есть это похоже на словарь:

элемент -> список строк, где он встречается

Поэтому он и называется инвертированным индексом.

Где используют GIN #

Чаще всего GIN используют для:

массивов,
jsonb,
полнотекстового поиска,
поиска по trigram через расширение pg_trgm.

Пример с массивом #

Таблица:

CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

Индекс:

CREATE INDEX articles_tags_gin_idx
ON articles
USING GIN (tags);

Запрос:

SELECT *
FROM articles
WHERE tags @> ARRAY['postgresql'];

Оператор @> означает: массив слева содержит массив справа.

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

Пример с JSONB #

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data JSONB
);

Пример данных:

{
  "brand": "Samsung",
  "color": "black",
  "storage": 256
}

GIN индекс:

CREATE INDEX products_data_gin_idx
ON products
USING GIN (data);

Запрос:

SELECT *
FROM products
WHERE data @> '{"brand": "Samsung"}';

Здесь GIN помогает искать документы jsonb, которые содержат нужный ключ или пару ключ-значение.

Пример с полнотекстовым поиском #

CREATE TABLE posts (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    body TEXT
);

Индекс:

CREATE INDEX posts_body_fts_idx
ON posts
USING GIN (to_tsvector('russian', body));

Запрос:

SELECT *
FROM posts
WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'postgresql индекс');

Здесь PostgreSQL разбивает текст на лексемы и GIN хранит связь:

слово/лексема -> строки, где оно встречается

Чем GIN отличается от B-tree #

B-tree подходит для данных, которые можно упорядочить в линейном порядке. PostgreSQL документация описывает B-tree как индекс для типов, которые можно отсортировать.

GIN не про сортировку. Он про быстрый поиск по внутренним компонентам.

Когда GIN не нужен #

GIN обычно не нужен для таких запросов:

WHERE id = 100
WHERE email = 'test@example.com'
WHERE price > 500
WHERE created_at BETWEEN '2026-01-01' AND '2026-02-01'
ORDER BY created_at DESC

Для этого обычно используют B-tree.

Минусы GIN #

GIN индекс может быть тяжёлым:

занимает больше места,
медленнее обновляется при INSERT/UPDATE/DELETE,
может быть избыточным на маленьких таблицах,
плохо подходит для частых массовых изменений.

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

Итог #

GIN index — это индекс для поиска внутри сложных значений.

Его используют, когда запросы выглядят примерно так:

-- массив содержит элемент
WHERE tags @> ARRAY['python']

-- jsonb содержит ключ/значение
WHERE data @> '{"status": "active"}'

-- полнотекстовый поиск
WHERE search_vector @@ to_tsquery('postgresql')

-- поиск по похожести/подстрокам через pg_trgm
WHERE name ILIKE '%phone%'

Для обычных полей вроде id, email, created_at, price чаще нужен не GIN, а обычный B-tree индекс.

30. LEFT JOIN, RIGHT JOIN #

LEFT JOIN #

LEFT JOIN возвращает:

1. Все строки из левой таблицы.

2. Совпавшие строки из правой таблицы.

3. Если совпадения справа нет — PostgreSQL подставляет NULL в поля правой таблицы.

LEFT JOIN и LEFT OUTER JOIN — одно и то же. Слово OUTER можно не писать. PostgreSQL указывает, что LEFT, RIGHT и FULL являются outer join-типами.

Пример:

SELECT users.id, users.name, orders.id AS order_id
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

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

users
id | name
---+------
1  | Alex
2  | Bob
3  | Tom

orders
id | user_id | amount
---+---------+-------
10 | 1       | 500
11 | 1       | 300
12 | 2       | 700

Результат LEFT JOIN:

user_id | name | order_id
--------+------+---------
1       | Alex | 10
1       | Alex | 11
2       | Bob  | 12
3       | Tom  | NULL

Пользователь Tom попал в результат, хотя у него нет заказов. Поэтому поля из orders стали NULL.

RIGHT JOIN #

RIGHT JOIN работает наоборот:

1. Все строки из правой таблицы.

2. Совпавшие строки из левой таблицы.

3. Если совпадения слева нет — поля левой таблицы будут NULL.

SELECT users.id AS user_id, users.name, orders.id AS order_id
FROM users
RIGHT JOIN orders ON orders.user_id = users.id;

Результат будет содержать все строки из orders, даже если для какого-то заказа не найден пользователь.

Пример:

users
id | name
---+------
1  | Alex
2  | Bob

orders
id | user_id | amount
---+---------+-------
10 | 1       | 500
11 | 2       | 700
12 | 99      | 300

Результат RIGHT JOIN:

user_id | name | order_id
--------+------+---------
1       | Alex | 10
2       | Bob  | 11
NULL    | NULL | 12

Заказ 12 попал в результат, потому что orders стоит справа, а RIGHT JOIN сохраняет все строки правой таблицы.

Главное отличие #

users LEFT JOIN orders

означает:

Покажи всех пользователей, даже если у них нет заказов.

users RIGHT JOIN orders

означает:

Покажи все заказы, даже если для них нет пользователя.

LEFT JOIN обычно используют чаще #

RIGHT JOIN почти всегда можно переписать через LEFT JOIN, просто поменяв таблицы местами.

Вот это:

SELECT *
FROM users
RIGHT JOIN orders ON orders.user_id = users.id;

эквивалентно этому:

SELECT *
FROM orders
LEFT JOIN users ON users.id = orders.user_id;

На практике LEFT JOIN читается проще, потому что основная таблица стоит первой. Поэтому в backend-разработке чаще пишут LEFT JOIN, а RIGHT JOIN используют редко.

В MySQL прямо указано, что на этапе парсинга RIGHT OUTER JOIN преобразуется в эквивалентный LEFT JOIN. Это не значит, что в PostgreSQL обязательно происходит точно так же, но хорошо показывает, что логически RIGHT JOIN можно выразить через LEFT JOIN.

Частый кейс LEFT JOIN: найти записи без связей #

Например, найти пользователей без заказов:

SELECT users.id, users.name
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

Логика:

Берём всех пользователей.
Пытаемся присоединить заказы.
Оставляем только тех, у кого заказ не найден.

Это типичный паттерн:

LEFT JOIN ... WHERE right_table.id IS NULL

Важный нюанс с WHERE #

Ошибка:

SELECT users.id, users.name, orders.id AS order_id
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.amount > 500;

На первый взгляд это LEFT JOIN, но условие в WHERE отфильтрует строки, где orders.amount IS NULL. В результате пользователи без заказов пропадут, и запрос начнёт вести себя ближе к INNER JOIN.

Правильнее переносить условие в ON, если нужно сохранить строки из левой таблицы:

SELECT users.id, users.name, orders.id AS order_id
FROM users
LEFT JOIN orders 
    ON orders.user_id = users.id
   AND orders.amount > 500;

Теперь пользователи без подходящих заказов останутся в результате, а поля orders будут NULL.

Итог #

LEFT JOIN:

Оставляет все строки слева.

RIGHT JOIN:

Оставляет все строки справа.

В реальном коде чаще используют LEFT JOIN, потому что его легче читать и почти любой RIGHT JOIN можно переписать через LEFT JOIN, поменяв порядок таблиц.

31. Как работает FULL OUTER JOIN? #

Что делает FULL OUTER JOIN #

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

1. Если строки совпали по условию ON — они объединяются в одну строку результата.

2. Если строка есть только в левой таблице — поля правой таблицы будут NULL.

3. Если строка есть только в правой таблице — поля левой таблицы будут NULL.

В PostgreSQL FULL OUTER JOIN сначала работает как обычный INNER JOIN для совпавших строк, а затем добавляет несовпавшие строки с обеих сторон, заполняя отсутствующую сторону NULL. FULL JOIN и FULL OUTER JOIN — одно и то же, слово OUTER можно не писать.

Пример #

Есть таблица пользователей:

users
id | name
---+------
1  | Alex
2  | Bob
3  | Tom

И таблица заказов:

orders
id | user_id | amount
---+---------+-------
10 | 1       | 500
11 | 2       | 700
12 | 99      | 300

Запрос:

SELECT
    users.id AS user_id,
    users.name,
    orders.id AS order_id,
    orders.user_id AS order_user_id,
    orders.amount
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id;

Результат:

user_id | name | order_id | order_user_id | amount
--------+------+----------+---------------+-------
1       | Alex | 10       | 1             | 500
2       | Bob  | 11       | 2             | 700
3       | Tom  | NULL     | NULL          | NULL
NULL    | NULL | 12       | 99            | 300

Что здесь произошло:

Alex и Bob имеют заказы — строки объединились.
Tom есть в users, но заказов нет — поля orders стали NULL.
Заказ с user_id = 99 есть в orders, но пользователя нет — поля users стали NULL.

Отличие от LEFT JOIN и RIGHT JOIN #

LEFT JOIN сохраняет все строки только из левой таблицы:

users LEFT JOIN orders ON orders.user_id = users.id

Смысл:

Показать всех пользователей, даже если у них нет заказов.

RIGHT JOIN сохраняет все строки только из правой таблицы:

users RIGHT JOIN orders ON orders.user_id = users.id

Смысл:

Показать все заказы, даже если для них нет пользователя.

FULL OUTER JOIN сохраняет обе стороны:

users FULL OUTER JOIN orders ON orders.user_id = users.id

Смысл:

Показать всех пользователей и все заказы, даже если связи между ними нет.

Упрощённая формула #

Логически FULL OUTER JOIN можно воспринимать как:

INNER JOIN
+
несовпавшие строки слева
+
несовпавшие строки справа

Или как объединение идеи LEFT JOIN и RIGHT JOIN.

Где используют FULL OUTER JOIN #

Чаще всего — для сверки данных.

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

payments_from_bank
payments_from_system

Нужно найти:

платежи, которые есть и там, и там;
платежи, которые есть только в банке;
платежи, которые есть только в системе.

Запрос:

SELECT
    bank.payment_id AS bank_payment_id,
    system.payment_id AS system_payment_id,
    bank.amount AS bank_amount,
    system.amount AS system_amount
FROM payments_from_bank AS bank
FULL OUTER JOIN payments_from_system AS system
    ON system.payment_id = bank.payment_id;

Такой запрос покажет полную картину расхождений.

Как найти только несовпавшие строки #

Если нужны только строки, которые есть только с одной стороны:

SELECT
    users.id AS user_id,
    users.name,
    orders.id AS order_id,
    orders.user_id AS order_user_id
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id
WHERE users.id IS NULL
   OR orders.id IS NULL;

Результат будет содержать:

пользователей без заказов;
заказы без существующего пользователя.

То есть совпавшие строки будут исключены.

Важный нюанс с WHERE #

Как и с LEFT JOIN, фильтрация в WHERE может случайно убрать строки с NULL.

Например:

SELECT *
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id
WHERE orders.amount > 500;

Этот запрос уберёт пользователей без заказов, потому что у них orders.amount = NULL, а условие NULL > 500 не считается истинным.

Если нужно сохранить строки без заказов, условие лучше писать аккуратно:

SELECT *
FROM users
FULL OUTER JOIN orders
    ON orders.user_id = users.id
   AND orders.amount > 500;

Или явно учитывать NULL в WHERE, если это нужно по логике запроса.

Итог #

FULL OUTER JOIN нужен, когда важно не потерять данные ни с одной стороны.

SELECT *
FROM table_a
FULL OUTER JOIN table_b ON table_b.a_id = table_a.id;

Он возвращает:

совпавшие строки из обеих таблиц;
строки только из левой таблицы;
строки только из правой таблицы.

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

32. Что такое обычный JOIN в SQL? #

Обычный JOIN — это INNER JOIN #

Когда пишут просто:

SELECT *
FROM users
JOIN orders ON orders.user_id = users.id;

это то же самое, что:

SELECT *
FROM users
INNER JOIN orders ON orders.user_id = users.id;

То есть обычный JOIN возвращает только те строки, для которых нашлось совпадение в обеих таблицах. В PostgreSQL JOIN, INNER JOIN и CROSS JOIN описаны в разделе joined tables; для INNER JOIN результат содержит строки, где условие соединения истинно.

Пример #

Таблица users:

id | name
---+------
1  | Alex
2  | Bob
3  | Tom

Таблица orders:

id | user_id | amount
---+---------+-------
10 | 1       | 500
11 | 1       | 300
12 | 2       | 700

Запрос:

SELECT
    users.id AS user_id,
    users.name,
    orders.id AS order_id,
    orders.amount
FROM users
JOIN orders ON orders.user_id = users.id;

Результат:

user_id | name | order_id | amount
--------+------+----------+-------
1       | Alex | 10       | 500
1       | Alex | 11       | 300
2       | Bob  | 12       | 700

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

Как он работает логически #

Упрощённо:

Берётся строка из users.
PostgreSQL ищет строки из orders, где orders.user_id = users.id.
Если совпадение есть — строка попадает в результат.
Если совпадения нет — строка отбрасывается.

То есть условие:

ON orders.user_id = users.id

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

Главное отличие от LEFT JOIN #

JOIN / INNER JOIN:

Показывает только совпавшие строки.

LEFT JOIN:

Показывает все строки слева, даже если справа совпадения нет.

Пример:

-- Только пользователи с заказами
SELECT *
FROM users
JOIN orders ON orders.user_id = users.id;

-- Все пользователи, даже без заказов
SELECT *
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

Где используют обычный JOIN #

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

Например:

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

Пример:

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

Смысл:

Показать заказы вместе с пользователями.

Если заказ не связан с существующим пользователем, он не попадёт в результат.

Важный момент #

JOIN может размножать строки.

Если у одного пользователя несколько заказов:

Alex -> order 10
Alex -> order 11

то в результате будет две строки с Alex:

Alex | 10
Alex | 11

Это нормально. JOIN возвращает не «одну строку на пользователя», а все найденные пары строк, которые удовлетворяют условию ON.

Итог #

Обычный JOIN в SQL — это INNER JOIN.

Он возвращает только строки, которые имеют совпадение в обеих таблицах:

SELECT *
FROM table_a
JOIN table_b ON table_b.a_id = table_a.id;

Если совпадения нет — строка не попадает в результат. Для сохранения строк без совпадений используют LEFT JOIN, RIGHT JOIN или FULL OUTER JOIN.

33. Что обычно должно быть условием соединения в JOIN? #

Главное правило #

В обычном JOIN условием соединения чаще всего должно быть сравнение:

дочерняя_таблица.foreign_key = родительская_таблица.primary_key

Например:

SELECT
    orders.id,
    users.username,
    orders.amount
FROM orders
JOIN users ON users.id = orders.user_id;

Здесь условие соединения:

users.id = orders.user_id

То есть заказ связывается с пользователем через внешний ключ orders.user_id, который ссылается на первичный ключ users.id.

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

Типичный вариант #

Есть таблица пользователей:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL
);

Есть таблица заказов:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    amount NUMERIC NOT NULL
);

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

SELECT *
FROM orders
JOIN users ON users.id = orders.user_id;

Смысл:

Соедини заказ с тем пользователем, чей users.id равен orders.user_id.

Что обычно стоит в ON #

Чаще всего:

ON child_table.parent_id = parent_table.id

Примеры:

-- Комментарий + его автор
comments.user_id = users.id

-- Заказ + пользователь
orders.user_id = users.id

-- Файл + владелец
files.owner_id = users.id

-- Товар в заказе + сам товар
order_items.product_id = products.id

-- Уведомление + пользователь
notifications.user_id = users.id

То есть условие должно отражать реальную связь между сущностями.

Если связь составная #

Иногда внешний ключ состоит из нескольких колонок. Тогда в JOIN нужно сравнить все части ключа.

Например:

CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Если другая таблица ссылается на такую пару, условие будет составным:

ON a.order_id = b.order_id
AND a.product_id = b.product_id

PostgreSQL поддерживает foreign key по группе колонок; в таком случае набор колонок ссылается на соответствующий набор колонок другой таблицы.

Можно ли соединять не по Primary Key #

Да, можно. JOIN не обязан использовать только PRIMARY KEY.

Технически ON может быть любым булевым выражением:

ON table_a.some_column = table_b.some_column

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

Например, можно соединить по уникальному email:

SELECT *
FROM users
JOIN profiles ON profiles.email = users.email;

Но в реальных схемах лучше связывать таблицы через стабильный id, а email оставить как UNIQUE, потому что email может измениться.

Плохие условия соединения #

Плохой пример:

SELECT *
FROM orders
JOIN users ON users.username = orders.comment;

Формально SQL может это выполнить, но связи между username и comment нет. Такой JOIN даст бессмысленные или случайные совпадения.

Ещё частая ошибка:

SELECT *
FROM orders
JOIN users ON orders.id = users.id;

Это почти всегда неверно. orders.id — id заказа, users.id — id пользователя. Они могут случайно совпасть численно, но это не означает, что заказ принадлежит этому пользователю.

Правильно:

SELECT *
FROM orders
JOIN users ON orders.user_id = users.id;

JOIN через USING #

Если в обеих таблицах колонка называется одинаково, можно использовать USING.

Например:

SELECT *
FROM orders
JOIN users USING (user_id);

Но это сработает только если в обеих таблицах есть колонка user_id.

Обычно в нормализованной схеме родительская таблица имеет id, а дочерняя — user_id, поэтому чаще пишут явно:

ON users.id = orders.user_id

PostgreSQL указывает, что условие соединения задаётся через ON, USING или неявно через NATURAL; при этом ON — самый общий вариант.

NATURAL JOIN лучше избегать #

NATURAL JOIN сам соединяет таблицы по колонкам с одинаковыми именами.

Например:

SELECT *
FROM users
NATURAL JOIN orders;

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

Поэтому в backend-разработке лучше писать условие явно:

JOIN orders ON orders.user_id = users.id

Итог #

Обычно условие соединения в JOIN должно быть таким:

ON дочерняя_таблица.foreign_key = родительская_таблица.primary_key

Например:

ON orders.user_id = users.id

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

34. Можно ли написать JOIN ON 1=1 и что будет результатом такого соединения? #

Да, можно #

SELECT *
FROM users
JOIN orders ON 1 = 1;

Такой JOIN будет работать. Условие 1 = 1 всегда истинно, поэтому каждая строка из users соединится с каждой строкой из orders.

То есть результатом будет декартово произведение таблиц.

В PostgreSQL CROSS JOIN эквивалентен INNER JOIN ... ON TRUE, а если одна таблица содержит N строк, другая M строк, результат содержит N * M строк.

Пример #

Таблица users:

id | name
---+------
1  | Alex
2  | Bob

Таблица orders:

id | amount
---+-------
10 | 500
11 | 700
12 | 300

Запрос:

SELECT
    users.name,
    orders.id AS order_id,
    orders.amount
FROM users
JOIN orders ON 1 = 1;

Результат:

name | order_id | amount
-----+----------+-------
Alex | 10       | 500
Alex | 11       | 700
Alex | 12       | 300
Bob  | 10       | 500
Bob  | 11       | 700
Bob  | 12       | 300

Было:

2 пользователя
3 заказа

Стало:

2 * 3 = 6 строк

Это то же самое, что CROSS JOIN #

Эти запросы логически эквивалентны:

SELECT *
FROM users
JOIN orders ON 1 = 1;

SELECT *
FROM users
JOIN orders ON TRUE;

SELECT *
FROM users
CROSS JOIN orders;

PostgreSQL также указывает, что CROSS JOIN эквивалентен INNER JOIN ON (TRUE), то есть строки не отбрасываются условием соединения.

Почему это обычно опасно #

Если в одной таблице 100 000 строк, а в другой 50 000 строк:

100 000 * 50 000 = 5 000 000 000 строк

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

Когда это может быть нужно #

JOIN ON 1=1 или CROSS JOIN используют редко, но осознанно:

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

Пример нормального использования:

SELECT
    products.name,
    sizes.size
FROM products
CROSS JOIN sizes;

Если есть 10 товаров и 5 размеров, получится 50 комбинаций.

Итог #

JOIN ON 1=1 разрешён, но это не обычное соединение по связи между таблицами.

JOIN orders ON orders.user_id = users.id

соединяет связанные строки.

JOIN orders ON 1 = 1

соединяет каждую строку с каждой.

По смыслу это CROSS JOIN. В обычных прикладных запросах такое условие почти всегда означает ошибку или забытое условие связи.

35. Как PostgreSQL обеспечивает консистентность данных при репликации? #

PostgreSQL обеспечивает консистентность при репликации через WAL — Write-Ahead Log.

Все изменения сначала записываются в WAL на primary-сервере, затем WAL передаётся на standby/replica, и реплика воспроизводит эти записи в том же порядке. За счёт этого реплика приходит к тому же состоянию данных, что и primary, но с возможной задержкой.

В PostgreSQL параметр wal_level = replica записывает достаточно информации в WAL для архивации, streaming replication и read-only запросов на standby. Для logical replication нужен уровень logical.

Как это работает по шагам #

1. Клиент выполняет INSERT, UPDATE, DELETE на primary.

2. PostgreSQL формирует WAL-записи для этих изменений.

3. При commit запись о транзакции фиксируется в WAL.

4. Реплика получает WAL по streaming replication или через архив WAL.

5. Реплика последовательно применяет WAL-записи.

6. После replay commit-записи изменения становятся видимыми на standby.

PostgreSQL прямо указывает, что после replay commit record на standby изменения этой транзакции становятся видимыми для новых snapshot на standby.

Важный момент: реплика не «сама пересчитывает» данные #

При physical replication standby не выполняет заново SQL-запросы вида:

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

Она получает уже записанные WAL-изменения и воспроизводит их на уровне хранения данных. Поэтому physical replication ближе к копированию изменений файлов/страниц БД, а не к повторному выполнению бизнес-операций.

В документации PostgreSQL logical replication противопоставляется physical replication: logical работает с объектами данных и их изменениями, а physical использует точные адреса блоков и побайтовую репликацию.

Асинхронная репликация #

По умолчанию streaming replication обычно асинхронная.

Это значит:

primary подтвердил COMMIT клиенту,
но реплика ещё может не успеть получить или применить WAL.

Итог:

на primary данные уже есть;
на replica эти данные появятся чуть позже.

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

Для чтения с реплики это означает eventual consistency: реплика со временем догоняет primary, но не обязана показывать самый свежий результат прямо сейчас. PostgreSQL прямо пишет, что между primary и standby есть измеримая задержка, поэтому один и тот же запрос почти одновременно на primary и standby может вернуть разные результаты; данные на standby являются eventually consistent относительно primary.

Синхронная репликация #

В синхронном режиме primary может ждать подтверждения от standby перед тем, как окончательно подтвердить commit клиенту.

Для этого настраивают, например:

synchronous_standby_names = 'standby1'
synchronous_commit = on

PostgreSQL указывает, что при такой конфигурации commit ждёт подтверждения, что standby записал commit record в durable storage. Это повышает гарантию, что изменения не будут потеряны при сбое primary, но увеличивает время ответа транзакции минимум на сетевой round-trip до standby.

Есть более строгий вариант:

synchronous_commit = remote_apply

В этом случае standby подтверждает не просто получение/запись WAL, а то, что commit record уже воспроизведён, и транзакция стала видимой на реплике.

Консистентность чтения на реплике #

Standby в режиме Hot Standby может принимать подключения и выполнять read-only запросы. Но запись на такой реплике запрещена: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, COPY FROM не разрешены.

Это важно для консистентности:

primary принимает изменения;
standby только воспроизводит WAL;
пользовательские записи на standby не смешиваются с WAL primary.

То есть реплика не расходится с primary из-за локальных изменений.

Что происходит при конфликте чтения и применения WAL #

На standby могут выполняться долгие SELECT. Одновременно реплика должна применять WAL с primary. Иногда WAL-изменение конфликтует с долгим read-only запросом на standby.

Например, primary удалил строки или выполнил VACUUM, а standby ещё держит snapshot, которому нужны старые версии строк.

В таких случаях PostgreSQL должен выбрать:

или задержать применение WAL;
или отменить конфликтующий запрос на standby.

За это отвечают параметры вроде:

max_standby_streaming_delay
max_standby_archive_delay

Документация указывает, что администратор должен выбирать эти параметры в зависимости от приоритета: высокая доступность и малый replication lag либо более долгие аналитические запросы на standby.

Роль replication slots #

Replication slot помогает не потерять WAL, который ещё нужен реплике.

Без slot primary может удалить старые WAL-сегменты, если реплика сильно отстала. Тогда реплика уже не сможет догнать primary без пересоздания.

С replication slot primary знает, что конкретной реплике ещё нужны определённые WAL-записи, и удерживает их. В настройках PostgreSQL max_replication_slots определяет максимальное число replication slots, а wal_level должен быть replica или выше для их использования.

Минус: если реплика долго не работает, WAL может накапливаться и занять много места на диске.

Logical replication #

При logical replication PostgreSQL реплицирует не весь кластер поблочно, а изменения выбранных таблиц через модель publication/subscription.

Механизм консистентности там другой:

сначала снимается snapshot исходных данных;
таблица копируется на subscriber;
после этого изменения с publisher непрерывно отправляются и применяются на subscriber.