# Вопросы на собеседовании по SQL

191 вопрос с реальных собеседований. Здесь открытые: первые вопросы каждой подтемы с коротким ответом (первый абзац эталона) и источником. Все вопросы с ответами и AI-разбором — в тренажёре JobJump.

Страница: https://jobjump.app/questions/sql

## Базовые манипуляции и структура (DML)

### Как на уровне СУБД гарантировать, что значения в столбце будут уникальными без дубликатов?

Уникальность на уровне СУБД гарантирует ограничение `UNIQUE` или `PRIMARY KEY`. PostgreSQL создаёт под него уникальный B-tree индекс, и каждая вставка или обновление проверяют значение через этот индекс.

Источник: https://www.postgresql.org/docs/18/ddl-constraints.html

### В чем разница между DELETE и TRUNCATE?

`DELETE` удаляет строки, выбранные условием `WHERE`, а `TRUNCATE` очищает таблицу целиком без сканирования, поэтому на больших таблицах он быстрее и сразу возвращает место на диске.

Источник: https://www.postgresql.org/docs/18/sql-truncate.html

### Что будет, если выполнить UPDATE без условия WHERE?

Обновятся все строки таблицы: `WHERE` необязателен, и без него условию удовлетворяет каждая строка. `UPDATE orders SET status = 'cancelled';` отменит все заказы.

Источник: https://www.postgresql.org/docs/18/sql-update.html

### Как работает INSERT IGNORE или ON DUPLICATE KEY UPDATE и чем их заменить в PostgreSQL?

Это синтаксис MySQL. В PostgreSQL то же делает `INSERT ... ON CONFLICT`: `DO NOTHING` вместо `INSERT IGNORE` и `DO UPDATE` вместо `ON DUPLICATE KEY UPDATE`.

Источник: https://www.postgresql.org/docs/18/sql-insert.html#SQL-ON-CONFLICT

### Можно ли использовать LIMIT при удалении данных?

Зависит от СУБД: MySQL поддерживает `DELETE ... ORDER BY ... LIMIT n`, а в PostgreSQL у `DELETE` нет `LIMIT`, и порцию строк удаляют через подзапрос.

Источник: https://dev.mysql.com/doc/refman/8.4/en/delete.html

## Структура и Типы (DDL)

### Разница между CHAR, VARCHAR и TEXT?

В PostgreSQL `CHAR(n)` хранит строку фиксированной длины, дополняя пробелами; `VARCHAR(n)` хранит строку переменной длины до `n` символов; `TEXT` — строку любой длины (практический предел около 1 ГБ). По скорости разницы между ними нет, поэтому по умолчанию выбирают `TEXT` или `VARCHAR`, а `CHAR(n)` почти никогда.

Источник: https://www.postgresql.org/docs/18/datatype-character.html

### Что произойдет с данными при уменьшении длины VARCHAR?

В PostgreSQL `ALTER TABLE ... ALTER COLUMN ... TYPE varchar(n)` с меньшим `n` завершится ошибкой, если в колонке есть значения длиннее нового лимита; молча обрезать данные он не будет (исключение из стандарта: если лишние символы только пробелы, они усекаются без ошибки). Если все значения умещаются, изменение проходит без потери данных.

Источник: https://www.postgresql.org/docs/18/sql-altertable.html

### Зачем нужны типы данных UUID вместо INT для первичных ключей?

UUID выбирают, когда идентификатор должен создаваться без обращения к базе и без координации между узлами: на клиенте, в другом сервисе, в шарде. Автоинкрементный `INT` требует общего счётчика, а UUID, по RFC 9562, даёт короткие уникальные значения в распределённых системах без координации.

Источник: https://www.rfc-editor.org/rfc/rfc9562

## JOIN (Соединения)

### Отличие JOIN от вложенных запросов?

`JOIN` возвращает столбцы обеих таблиц и повторяет строку для каждой найденной пары. Подзапрос в `IN` или `EXISTS` только отвечает, есть ли связанная строка, и строку слева не размножает. Скорость в выборе между ними не главное: форму выбирают по тому, какой результат нужен.

Источник: https://www.postgresql.org/docs/18/functions-subquery.html

### Какие есть виды JOIN?

Виды соединений различаются тем, какие строки остаются, когда пары не нашлось: `INNER`, `LEFT`, `RIGHT`, `FULL` и `CROSS`.

Источник: https://www.postgresql.org/docs/18/queries-table-expressions.html

### Объясни разницу между LEFT JOIN и LEFT OUTER JOIN?

Разницы нет: `LEFT JOIN` и `LEFT OUTER JOIN` означают одно и то же, слово `OUTER` необязательно. То же верно для `RIGHT` и `FULL`.

Источник: https://www.postgresql.org/docs/18/queries-table-expressions.html

### Что такое Self Join и приведи пример?

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

Источник: https://www.postgresql.org/docs/18/tutorial-join.html

## CTE (Common Table Expressions) & Вложенные запросы

### Разница между CTE и подзапросом (Subquery)?

CTE (`WITH name AS (...)`) — именованный временный результат, существующий в рамках одного запроса, на который можно сослаться по имени несколько раз; подзапрос записывается на месте использования и по имени не переиспользуется. Только CTE умеет рекурсию (`WITH RECURSIVE`), обращаясь к собственному результату.

Источник: https://www.postgresql.org/docs/18/queries-with.html

### Влияет ли CTE на производительность (материализация в разных СУБД)?

В PostgreSQL 12 и новее CTE, на который запрос ссылается один раз, сам по себе запрос не замедляет; в разных СУБД и версиях поведение отличается.

Источник: https://www.postgresql.org/docs/18/queries-with.html

### Можно ли использовать CTE внутри INSERT или UPDATE?

Да, и в двух смыслах: `WITH` можно поставить перед `INSERT`, `UPDATE`, `DELETE` и `MERGE`, а в PostgreSQL сам CTE может быть изменяющим оператором.

Источник: https://www.postgresql.org/docs/18/queries-with.html

### В каких случаях CTE лучше вложенных запросов?

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

Источник: https://www.postgresql.org/docs/18/queries-with.html

## Основы и структура индексов

### Что такое индексы и для чего нужны?

Индекс — отдельная структура, которая позволяет находить строки по значению столбца, не просматривая всю таблицу. В PostgreSQL по умолчанию это B-tree; индекс хранится отдельно от таблицы (heap) и указывает на её строки.

Источник: https://www.postgresql.org/docs/18/indexes-intro.html

### Влияние индексов на скорость INSERT/UPDATE/DELETE?

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

Источник: https://www.postgresql.org/docs/18/indexes-intro.html

### Какие есть виды индексов?

В PostgreSQL шесть встроенных типов индексов: B-tree (по умолчанию), Hash, GiST, SP-GiST, GIN и BRIN; плюс расширение bloom. Каждый подходит для своих операторов. Термины «кластеризованный», «полнотекстовый» и «фильтрованный» относятся к другому уровню: это либо особенности конкретной СУБД, либо способ применения базовых типов.

Источник: https://www.postgresql.org/docs/18/indexes-types.html

### Разница между Clustered и Non-clustered индексами?

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

Источник: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

### Как работает B-Tree индекс?

B-tree — сбалансированное многоуровневое дерево отсортированных ключей: поиск спускается от корня через внутренние страницы к листьям, где лежат указатели на строки таблицы. Глубина дерева растёт логарифмически, поэтому поиск одной записи среди миллионов занимает несколько чтений страниц.

Источник: https://www.postgresql.org/docs/18/btree.html

### В каком виде хранятся данные, если не создавать кластеризованный индекс?

Без кластеризованного индекса таблица в SQL Server хранится как heap, то есть неупорядоченная куча страниц, и строки находятся по идентификатору RID (файл, страница, слот). В PostgreSQL каждая таблица всегда heap, и кластеризованного индекса нет.

Источник: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes

### Плюсы и минусы использования Int и Guid для индексов?

Целочисленный ключ компактнее и растёт монотонно, поэтому вставки идут в конец индекса; GUID/UUID удобен для генерации без координации, но больше и при случайном порядке вставляется в произвольные места индекса.

Источник: https://www.rfc-editor.org/rfc/rfc9562

## Работа Оптимизатора и План запроса

### Разница Index Scan vs Index Seek?

Это разные операторы плана в SQL Server; в PostgreSQL оператора «Index Seek» нет.

Источник: https://learn.microsoft.com/sql/relational-databases/showplan-logical-and-physical-operators-reference

### Отличие EXPLAIN и EXPLAIN ANALYZE?

`EXPLAIN` показывает план, который планировщик выбрал бы для запроса, с оценками стоимости и числа строк, но запрос не выполняет. `EXPLAIN ANALYZE` запрос действительно выполняет и добавляет реальное время каждого узла (в миллисекундах) и фактическое число строк, поэтому им проверяют, насколько оценки близки к действительности.

Источник: https://www.postgresql.org/docs/18/sql-explain.html

### Как читать EXPLAIN ANALYZE? На какие параметры смотреть в первую очередь?

В первую очередь смотрят, какой узел плана тратит больше всего собственного времени, совпадает ли оценка строк с фактом и как читается таблица. Цена (`cost`) идёт последней.

Источник: https://www.postgresql.org/docs/18/using-explain.html

### Всегда ли СУБД использует индекс, который висит на столбце при поиске по нему, или решение зависит от оптимизатора запросов?

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

Источник: https://www.postgresql.org/docs/18/indexes-examine.html

## Специальные типы индексов

### Как работает хеш-таблица внутри индекса и почему поиск по ней считается O(1)?

Hash-индекс вычисляет хеш значения и по нему сразу определяет страницу-корзину (bucket), где могут лежать нужные записи, без спуска по дереву. Поэтому поиск по равенству в среднем занимает константное число шагов, то есть O(1) по числу строк, тогда как B-tree тратит время, растущее логарифмически (по глубине дерева).

Источник: https://www.postgresql.org/docs/18/hash-index.html

### Почему Hash-индекс бесполезен для WHERE age > 25 или ORDER BY, но идеален для WHERE id = 500?

Hash-индекс хранит только хеш значения, а хеш не сохраняет порядок: близкие значения (25 и 26) получают несвязанные хеши и, как правило, оказываются в разных корзинах без порядка. Поэтому диапазон `age > 25` и `ORDER BY` по нему из такого индекса не получить, а `WHERE id = 500` вычисляет хеш и идёт прямо в нужную корзину.

Источник: https://www.postgresql.org/docs/18/hash-index.html

### Что происходит в Hash-индексе, если два разных значения выдают один и тот же хеш?

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

Источник: https://www.postgresql.org/docs/18/hash-index.html

### Зачем нужны частичные индексы (Partial Indexes)?

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

Источник: https://www.postgresql.org/docs/18/indexes-partial.html

### Что такое Bitmap Scan?

Bitmap scan — способ чтения в PostgreSQL: индекс сканируется, в памяти строится битовая карта положений подходящих строк, затем строки читаются из таблицы по порядку расположения на диске. В плане это пара узлов: `Bitmap Index Scan` и `Bitmap Heap Scan`.

Источник: https://www.postgresql.org/docs/18/indexes-bitmap-scans.html

### Почему Bitmap-индексы редко используют в OLTP-системах (где много UPDATE/INSERT) и предпочитают в OLAP (хранилищах данных)?

Bitmap-индекс (например, в Oracle) хранит для каждого значения ключа битовую карту строк, поэтому одна запись индекса указывает на множество строк. При изменении одной строки СУБД блокирует всю запись ключа, а значит, фактически блокирует все строки с этим значением: в OLTP с параллельными записями это приводит к ожиданиям.

Источник: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html

### Почему GIN — лучший выбор для полнотекстового поиска и работы с массивами или JSONB?

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

Источник: https://www.postgresql.org/docs/18/gin.html

## ACID и Жизненный цикл

### Что такое ACID?

ACID — четыре свойства транзакции: атомарность, согласованность, изоляция, долговечность. Транзакция группирует несколько шагов в одну операцию «всё или ничего».

Источник: https://www.postgresql.org/docs/18/tutorial-transactions.html

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

Стандарт SQL определяет четыре уровня изоляции по запрещённым аномалиям: `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, `SERIALIZABLE`. Каждый следующий запрещает больше.

Источник: https://www.postgresql.org/docs/18/transaction-iso.html

### Проблема "Фантомного чтения" (Phantom Read)?

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

Источник: https://www.postgresql.org/docs/18/transaction-iso.html

### Какой уровень изоляции установлен по умолчанию в PostgreSQL?

В PostgreSQL по умолчанию действует `READ COMMITTED`. На этом уровне каждый оператор `SELECT` видит данные, зафиксированные до его начала, то есть снимок на момент начала запроса, а не транзакции.

Источник: https://www.postgresql.org/docs/18/transaction-iso.html

### Какой вид изоляции транзакций PostgreSQL не поддерживает из-за особенностей версионирования строк?

В PostgreSQL нет отдельного уровня `READ UNCOMMITTED`: его можно запросить, но он ведёт себя как `READ COMMITTED`, поэтому грязное чтение невозможно.

Источник: https://www.postgresql.org/docs/18/transaction-iso.html

## Блокировки и Конкурентность

### Разница между Pessimistic и Optimistic locking?

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

Источник: https://martinfowler.com/eaaCatalog/optimisticOfflineLock.html

### Разница между Shared Lock (S) и Exclusive Lock (X)?

Shared (S) блокировка разрешает чтение и совместима с другими S-блокировками, а Exclusive (X) запрещает другим менять данные и читать их, кроме чтения с подсказкой `NOLOCK` или на уровне `READ UNCOMMITTED`. На ресурсе может быть много S-блокировок, но только одна X.

Источник: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

### Что такое Deadlock и как СУБД его обнаруживает?

Deadlock (на примере PostgreSQL) — взаимная блокировка: две или более транзакции держат блокировки, нужные друг другу, и ни одна не может продолжить. СУБД обнаруживает цикл ожидания и прерывает одну из транзакций, остальные продолжают.

Источник: https://www.postgresql.org/docs/18/explicit-locking.html

### Что такое "намеренные" блокировки (Intent Locks)?

Намеренная блокировка (intent lock) ставится на более высоком уровне иерархии (таблица, страница) и сообщает: внутри будут блокировки более низкого уровня (строк). Она нужна, чтобы СУБД могла быстро решить, можно ли заблокировать всю таблицу, не перебирая строки.

Источник: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

### Два менеджера открыли одну карточку заказа и сохранили её с разницей в секунду. Приложение читает строку, меняет поля в памяти и пишет `UPDATE orders SET ... WHERE id = $1`. Второе сохранение молча затёрло первое. Как поймать такой конфликт, не держа блокировку между чтением формы и записью?

Нужна версия строки: столбец `version`, который читается вместе с карточкой, а запись делается условно: `UPDATE orders SET status = $2, version = version + 1 WHERE id = $1 AND version = $3`.

Источник: https://www.postgresql.org/docs/18/sql-update.html

## Производительность и WAL

### Что такое Write-Ahead Logging (WAL)?

Write-Ahead Logging (WAL) — правило: изменения файлов данных можно записывать только после того, как описывающие их записи журнала сброшены на постоянный носитель. Поэтому для фиксации транзакции достаточно сбросить на диск журнал, а страницы данных можно записать позже.

Источник: https://www.postgresql.org/docs/18/wal-intro.html

### Чем опасна транзакция, которая висит открытой 10 минут в высоконагруженной системе?

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

Источник: https://www.postgresql.org/docs/18/runtime-config-client.html

### Как минимизировать риск возникновения дедлоков в коде приложения?

Главная мера — брать блокировки в одном и том же порядке во всех частях приложения. Документация PostgreSQL называет это лучшей защитой от взаимных блокировок: если обе транзакции обновляют строки в одном порядке, цикл ожидания невозможен.

Источник: https://www.postgresql.org/docs/18/explicit-locking.html

## Функции, Триггеры, Представления (Views)

### Разница между Хранимой процедурой и Функцией?

Функция вызывается из выражения и возвращает значение, процедура вызывается командой `CALL` и может управлять транзакциями. Это различие стандарта SQL, и в PostgreSQL оно выражено прямо.

Источник: https://www.postgresql.org/docs/18/sql-createprocedure.html

### Можно ли вызвать транзакцию внутри функции?

Нет, в PostgreSQL функция не может управлять транзакцией: `COMMIT` и `ROLLBACK` в ней недоступны, функция всегда выполняется внутри транзакции вызывающего. Управлять транзакциями можно в процедурах, вызванных командой `CALL`, и в анонимных блоках `DO`.

Источник: https://www.postgresql.org/docs/18/plpgsql-transactions.html

### Как SQL Injection связан с хранимыми процедурами?

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

Источник: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

## Фундаментал

### Чем отличается Реляционная vs Не-реляционная базы данных

Реляционная база хранит данные в таблицах с заданной схемой, связывает их ключами и обеспечивает ACID-транзакции и SQL с соединениями; нереляционная (NoSQL) хранит данные в другой модели: документы, пары ключ-значение, колонки или графы.

Источник: https://learn.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data

### Преимущеста NoSQL перед SQL?

Главные доводы за NoSQL: гибкая схема, горизонтальное масштабирование и оптимизация под конкретный шаблон доступа. Это не «быстрее вообще», а выигрыш за счёт отказа от части реляционных гарантий.

Источник: https://learn.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data

### Что такое Вертикальное (SQL) и Горизонтального (NoSQL/Sharding) масштабирование?

Вертикальное масштабирование (scale up) увеличивает мощность одного узла: больше CPU, памяти, диска. Горизонтальное (scale out) добавляет узлы, и нагрузка с данными распределяется между ними. Шардирование — способ горизонтального масштабирования данных: набор строк делится на шарды с одинаковой схемой на разных серверах.

Источник: https://learn.microsoft.com/en-us/azure/well-architected/performance-efficiency/scale-partition

### Какие есть типы NoSQL?

Основные типы NoSQL: документные, ключ-значение, колоночные (wide-column) и графовые.

Источник: https://learn.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data

### Отличие ACID от BASE?

ACID задаёт жёсткие гарантии транзакции (атомарность, согласованность, изоляция, долговечность), BASE — менее строгую модель для распределённых хранилищ: Basically Available, Soft state, Eventually consistent. ACID выбирает согласованность, BASE — доступность.

Источник: https://aws.amazon.com/compare/the-difference-between-acid-and-base-database/

### Расскажите про CAP-теорему

CAP-теорема: в сетевой распределённой системе при разделении сети нужно выбрать между согласованностью (C) и доступностью (A). Вариант «выбираем любые два из трёх» вводит в заблуждение: от разделения сети (P) отказаться нельзя, потому что сеть может терять и задерживать сообщения.

Источник: https://www.infoq.com/articles/cap-twelve-years-later-how-the-rules-have-changed/

## Манипуляции

### Как изменить тип колонки в таблице с миллионом строк без простоя (downtime)?

Прямой `ALTER COLUMN ... TYPE` обычно перезаписывает всю таблицу и её индексы под блокировкой `ACCESS EXCLUSIVE`, поэтому на большой живой таблице его заменяют многошаговой миграцией: добавить новую колонку, постепенно заполнить её, переключить чтение и удалить старую.

Источник: https://www.postgresql.org/docs/18/sql-altertable.html

### Как работает LIMIT 10 OFFSET 50? Почему OFFSET на больших объемах данных работает медленно?

`LIMIT 10 OFFSET 50` пропускает первые 50 строк результата и возвращает следующие 10. Большой `OFFSET` медленный, потому что пропущенные строки сервер всё равно вычисляет. Документация PostgreSQL: «rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient».

Источник: https://www.postgresql.org/docs/18/queries-limit.html

### Почему нельзя отфильтровать сумму в WHERE?

Агрегат нельзя использовать в `WHERE`, потому что `WHERE` определяет, какие строки попадут в агрегат, и должен вычисляться раньше. Фильтрацию по результату агрегата записывают в `HAVING`.

Источник: https://www.postgresql.org/docs/18/tutorial-agg.html

## Оконные функции

### Нужно вывести по три самых дорогих заказа каждого клиента. Запрос с условием `WHERE row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3` падает с ошибкой. Почему и как написать запрос правильно?

Оконную функцию нельзя использовать в `WHERE`: PostgreSQL отвечает `window functions are not allowed in WHERE`. Номер строки нужно посчитать во вложенном запросе и отфильтровать уровнем выше.

Источник: https://www.postgresql.org/docs/current/tutorial-window.html

### У двух сотрудников отдела одинаковая максимальная зарплата 300, у следующего 250. Какие номера получат эти трое от `row_number()`, `rank()` и `dense_rank()` с `ORDER BY salary DESC`?

`row_number()`: 1, 2, 3. `rank()`: 1, 1, 3. `dense_rank()`: 1, 1, 2.

Источник: https://www.postgresql.org/docs/current/functions-window.html

### Нарастающий итог `sum(amount) OVER (ORDER BY paid_on)` показывает одно и то же значение для двух платежей одного дня, хотя ожидалось, что итог вырастет на каждом. Почему и как исправить?

С `ORDER BY` рамка окна по умолчанию — `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, и в неё входят все строки до последней, равной текущей по `ORDER BY`. Два платежа одного дня равны по `paid_on`, поэтому обе строки видят сумму за весь день.

Источник: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

## JSON и JSONB в PostgreSQL

### В каталоге признаки товаров лежат в колонке `attrs jsonb`. У одной пары обуви размер записан числом (`{"size": 42}`), у другой строкой (`{"size": "42"}`), а цвет в третьей записан под ключом `colour` вместо `color`. База все три вставки приняла. Почему и что из этого можно закрепить ограничениями?

Колонка `jsonb` проверяет только то, что значение является корректным JSON. Структуру документа база не навязывает: тип значения, имена ключей и обязательность в схеме не заданы, поэтому `colour` для неё просто другой ключ.

Источник: https://www.postgresql.org/docs/18/datatype-json.html#JSON-DOC-DESIGN

### В `products(attrs jsonb)` у ноутбука `{"ram_gb": 16}`. Запрос `SELECT id FROM products WHERE attrs->>'ram_gb' > '9'` его не возвращает. Почему и как исправить?

Оператор `->>` возвращает значение как `text`, и сравнение `'16' > '9'` идёт как сравнение строк посимвольно: `'1'` меньше `'9'`, поэтому `'16'` меньше `'9'`. Число осталось внутри документа, наружу вышел текст.

Источник: https://www.postgresql.org/docs/18/functions-json.html

### В `events(payload jsonb)` 200 тысяч строк и индекс `CREATE INDEX ON events USING gin (payload jsonb_path_ops)`. Запрос `WHERE payload->>'shop_id' = '77'` всё равно читает всю таблицу. Почему и какие есть исправления?

GIN по колонке `payload` обслуживает операторы `jsonb` над самой колонкой (`@>`, `@?`, `@@`, а у класса по умолчанию `jsonb_ops` ещё `?`). Условие `payload->>'shop_id' = '77'` это сравнение текста, полученного из документа, и такой индекс его не использует: в плане последовательное чтение таблицы (`Seq Scan`, на большой таблице под `Gather`).

Источник: https://www.postgresql.org/docs/18/datatype-json.html#JSON-INDEXING

## N+1 и диагностика запросов

### Страница «Последние заказы» показывает 50 заказов с именем клиента. `EXPLAIN ANALYZE` каждого запроса страницы показывает доли миллисекунды, а страница отвечает в разы дольше суммы этих времён. В журнале запросов на открытие одной страницы 51 запрос. Что происходит и как это исправить?

Это проблема N+1: один запрос за списком и по запросу на каждую его строку (явный цикл в коде или ленивая загрузка в ORM). На 50 заказах получается 1 + 50 = 51 запрос.

Источник: https://www.postgresql.org/docs/18/using-explain.html

### К странице из 20 последних заказов нужно добавить позиции каждого заказа (в `order_items` у заказа несколько строк). Разработчик заменил цикл запросов одним `JOIN order_items` с `LIMIT 20`, и страница стала показывать меньше 20 заказов. Почему и как загрузить позиции без запроса на каждый заказ?

`LIMIT` отсекает строки результата, а `JOIN` на «много» даёт строку на каждую пару «заказ — позиция». В проверке с четырьмя позициями на заказ `LIMIT 50` вернул 50 строк, а заказов в них было 13. Следующая страница пагинации тоже начнётся не с того места.

Источник: https://www.postgresql.org/docs/18/functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME

### Нужно снять нагрузку с сервера PostgreSQL, жалоб пользователей на отдельные медленные страницы нет. Среди запросов в верху `pg_stat_statements` за час три: A выполнялся 20 раз при среднем 1500 мс, B 90 000 раз при среднем 0,5 мс, C 2 000 раз при среднем 12 мс. Какой запрос исправлять первым, чтобы снять нагрузку с базы?

B. Нагрузку на базу создаёт суммарное время, то есть число вызовов, умноженное на среднее: A занимает 20 × 1500 = 30 000 мс, B 90 000 × 0,5 = 45 000 мс, C 2 000 × 12 = 24 000 мс. Самый медленный по среднему A оказывается вторым.

Источник: https://www.postgresql.org/docs/18/pgstatstatements.html

## Доступ: роли, привилегии и RLS

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

Роль-владелец может с объектом всё, и это не ограничить: право изменять и удалять объект принадлежит владельцу и отдельной привилегией не выдаётся и не отзывается. Ошибка в коде, неудачная команда или SQL-инъекция в запросе сервиса получают то же самое: `DROP TABLE`, `ALTER TABLE`, `TRUNCATE`. Владелец к тому же по умолчанию обходит политики защиты строк (RLS).

Источник: https://www.postgresql.org/docs/18/ddl-priv.html

### Включили RLS с политикой `tenant_id = current_setting('app.tenant_id')::bigint`, но сервис по-прежнему видит строки всех арендаторов. Почему?

Сервис подключается под ролью, которая владеет таблицей: владелец обходит политики, пока для таблицы не включено `FORCE ROW LEVEL SECURITY`. Суперпользователи и роли с атрибутом `BYPASSRLS` обходят RLS всегда. Политика при этом написана верно и просто не применяется к этой роли.

Источник: https://www.postgresql.org/docs/18/ddl-rowsecurity.html

### Арендатор задаётся `SET app.tenant_id = ...` в начале обработки запроса, сервис стоит за PgBouncer в режиме transaction. Почему иногда видны строки чужого арендатора и как задавать значение правильно?

Обычный `SET` действует до конца сессии, то есть серверного соединения, а не клиента. В режиме transaction PgBouncer отдаёт клиенту серверное соединение только на время транзакции, и каждая следующая транзакция может попасть на другое соединение с другим состоянием сессии. Значение, выставленное через `SET`, остаётся на соединении после транзакции и достаётся следующему клиенту, у которого арендатор другой. Политика читает `current_setting` и показывает строки того, кто выставил значение последним. В таблице совместимости PgBouncer `SET` и `RESET` в режиме transaction помечены как не работающие (Never).

Источник: https://www.postgresql.org/docs/18/sql-set.html
