Вопросы на собеседовании по SQL
191 вопрос с реальных собеседований в 17 подтемах. У первых вопросов каждой подтемы — короткий ответ и источник.
Базовые манипуляции и структура (DML)
14 вопросовКак на уровне СУБД гарантировать, что значения в столбце будут уникальными без дубликатов?
Короткий ответ
Уникальность на уровне СУБД гарантирует ограничение
UNIQUEилиPRIMARY KEY. PostgreSQL создаёт под него уникальный B-tree индекс, и каждая вставка или обновление проверяют значение через этот индекс.Источник: postgresql.org
В чем разница между DELETE и TRUNCATE?
Короткий ответ
DELETEудаляет строки, выбранные условиемWHERE, аTRUNCATEочищает таблицу целиком без сканирования, поэтому на больших таблицах он быстрее и сразу возвращает место на диске.Источник: postgresql.org
Что будет, если выполнить UPDATE без условия WHERE?
Короткий ответ
Обновятся все строки таблицы:
WHEREнеобязателен, и без него условию удовлетворяет каждая строка.UPDATE orders SET status = 'cancelled';отменит все заказы.Источник: postgresql.org
Как работает INSERT IGNORE или ON DUPLICATE KEY UPDATE и чем их заменить в PostgreSQL?
Короткий ответ
Это синтаксис MySQL. В PostgreSQL то же делает
INSERT ... ON CONFLICT:DO NOTHINGвместоINSERT IGNOREиDO UPDATEвместоON DUPLICATE KEY UPDATE.Источник: postgresql.org
Можно ли использовать LIMIT при удалении данных?
Короткий ответ
Зависит от СУБД: MySQL поддерживает
DELETE ... ORDER BY ... LIMIT n, а в PostgreSQL уDELETEнетLIMIT, и порцию строк удаляют через подзапрос.Источник: dev.mysql.com
Структура и Типы (DDL)
9 вопросовРазница между CHAR, VARCHAR и TEXT?
Короткий ответ
В PostgreSQL
CHAR(n)хранит строку фиксированной длины, дополняя пробелами;VARCHAR(n)хранит строку переменной длины доnсимволов;TEXT— строку любой длины (практический предел около 1 ГБ). По скорости разницы между ними нет, поэтому по умолчанию выбираютTEXTилиVARCHAR, аCHAR(n)почти никогда.Источник: postgresql.org
Что произойдет с данными при уменьшении длины VARCHAR?
Короткий ответ
В PostgreSQL
ALTER TABLE ... ALTER COLUMN ... TYPE varchar(n)с меньшимnзавершится ошибкой, если в колонке есть значения длиннее нового лимита; молча обрезать данные он не будет (исключение из стандарта: если лишние символы только пробелы, они усекаются без ошибки). Если все значения умещаются, изменение проходит без потери данных.Источник: postgresql.org
Зачем нужны типы данных UUID вместо INT для первичных ключей?
Короткий ответ
UUID выбирают, когда идентификатор должен создаваться без обращения к базе и без координации между узлами: на клиенте, в другом сервисе, в шарде. Автоинкрементный
INTтребует общего счётчика, а UUID, по RFC 9562, даёт короткие уникальные значения в распределённых системах без координации.Источник: rfc-editor.org
JOIN (Соединения)
12 вопросовОтличие JOIN от вложенных запросов?
Короткий ответ
JOINвозвращает столбцы обеих таблиц и повторяет строку для каждой найденной пары. Подзапрос вINилиEXISTSтолько отвечает, есть ли связанная строка, и строку слева не размножает. Скорость в выборе между ними не главное: форму выбирают по тому, какой результат нужен.Источник: postgresql.org
Какие есть виды JOIN?
Короткий ответ
Виды соединений различаются тем, какие строки остаются, когда пары не нашлось:
INNER,LEFT,RIGHT,FULLиCROSS.Источник: postgresql.org
Объясни разницу между LEFT JOIN и LEFT OUTER JOIN?
Короткий ответ
Разницы нет:
LEFT JOINиLEFT OUTER JOINозначают одно и то же, словоOUTERнеобязательно. То же верно дляRIGHTиFULL.Источник: postgresql.org
Что такое Self Join и приведи пример?
Короткий ответ
Self join — соединение таблицы с самой собой: одна и та же таблица берётся дважды под разными псевдонимами, и каждый играет свою роль.
Источник: postgresql.org
CTE (Common Table Expressions) & Вложенные запросы
12 вопросовРазница между CTE и подзапросом (Subquery)?
Короткий ответ
CTE (
WITH name AS (...)) — именованный временный результат, существующий в рамках одного запроса, на который можно сослаться по имени несколько раз; подзапрос записывается на месте использования и по имени не переиспользуется. Только CTE умеет рекурсию (WITH RECURSIVE), обращаясь к собственному результату.Источник: postgresql.org
Влияет ли CTE на производительность (материализация в разных СУБД)?
Короткий ответ
В PostgreSQL 12 и новее CTE, на который запрос ссылается один раз, сам по себе запрос не замедляет; в разных СУБД и версиях поведение отличается.
Источник: postgresql.org
Можно ли использовать CTE внутри INSERT или UPDATE?
Короткий ответ
Да, и в двух смыслах:
WITHможно поставить передINSERT,UPDATE,DELETEиMERGE, а в PostgreSQL сам CTE может быть изменяющим оператором.Источник: postgresql.org
В каких случаях CTE лучше вложенных запросов?
Короткий ответ
CTE лучше вложенных запросов, когда запрос получается многошаговым, когда один результат нужен несколько раз и когда нужна рекурсия.
Источник: postgresql.org
Основы и структура индексов
21 вопросЧто такое индексы и для чего нужны?
Короткий ответ
Индекс — отдельная структура, которая позволяет находить строки по значению столбца, не просматривая всю таблицу. В PostgreSQL по умолчанию это B-tree; индекс хранится отдельно от таблицы (heap) и указывает на её строки.
Источник: postgresql.org
Влияние индексов на скорость INSERT/UPDATE/DELETE?
Короткий ответ
Каждый индекс замедляет запись, потому что индекс надо синхронизировать с таблицей: вставка добавляет запись в каждый индекс, а
UPDATEиDELETEсоздают или помечают версии строк, на которые указывают индексы. Документация PostgreSQL прямо говорит, что это добавляет нагрузку на операции изменения данных.Источник: postgresql.org
Какие есть виды индексов?
Короткий ответ
В PostgreSQL шесть встроенных типов индексов: B-tree (по умолчанию), Hash, GiST, SP-GiST, GIN и BRIN; плюс расширение bloom. Каждый подходит для своих операторов. Термины «кластеризованный», «полнотекстовый» и «фильтрованный» относятся к другому уровню: это либо особенности конкретной СУБД, либо способ применения базовых типов.
Источник: postgresql.org
Разница между Clustered и Non-clustered индексами?
Короткий ответ
Кластеризованный индекс определяет порядок хранения строк: строки таблицы хранятся отсортированно по ключу индекса. Некластеризованный индекс — отдельная структура с ключами и указателем на строку. Кластеризованный индекс может быть только один на таблицу, некластеризованных можно много.
Источник: learn.microsoft.com
Как работает B-Tree индекс?
Короткий ответ
B-tree — сбалансированное многоуровневое дерево отсортированных ключей: поиск спускается от корня через внутренние страницы к листьям, где лежат указатели на строки таблицы. Глубина дерева растёт логарифмически, поэтому поиск одной записи среди миллионов занимает несколько чтений страниц.
Источник: postgresql.org
В каком виде хранятся данные, если не создавать кластеризованный индекс?
Короткий ответ
Без кластеризованного индекса таблица в SQL Server хранится как heap, то есть неупорядоченная куча страниц, и строки находятся по идентификатору RID (файл, страница, слот). В PostgreSQL каждая таблица всегда heap, и кластеризованного индекса нет.
Источник: learn.microsoft.com
Плюсы и минусы использования Int и Guid для индексов?
Короткий ответ
Целочисленный ключ компактнее и растёт монотонно, поэтому вставки идут в конец индекса; GUID/UUID удобен для генерации без координации, но больше и при случайном порядке вставляется в произвольные места индекса.
Источник: rfc-editor.org
Работа Оптимизатора и План запроса
13 вопросовРазница Index Scan vs Index Seek?
Короткий ответ
Это разные операторы плана в SQL Server; в PostgreSQL оператора «Index Seek» нет.
Источник: learn.microsoft.com
Отличие EXPLAIN и EXPLAIN ANALYZE?
Короткий ответ
EXPLAINпоказывает план, который планировщик выбрал бы для запроса, с оценками стоимости и числа строк, но запрос не выполняет.EXPLAIN ANALYZEзапрос действительно выполняет и добавляет реальное время каждого узла (в миллисекундах) и фактическое число строк, поэтому им проверяют, насколько оценки близки к действительности.Источник: postgresql.org
Как читать EXPLAIN ANALYZE? На какие параметры смотреть в первую очередь?
Короткий ответ
В первую очередь смотрят, какой узел плана тратит больше всего собственного времени, совпадает ли оценка строк с фактом и как читается таблица. Цена (
cost) идёт последней.Источник: postgresql.org
Всегда ли СУБД использует индекс, который висит на столбце при поиске по нему, или решение зависит от оптимизатора запросов?
Короткий ответ
Нет (в PostgreSQL): наличие индекса лишь делает индексный план возможным, а выбирает план оптимизатор по оценке стоимости. Он берёт индекс, только когда считает его дешевле других способов, прежде всего последовательного чтения таблицы.
Источник: postgresql.org
Специальные типы индексов
22 вопросаКак работает хеш-таблица внутри индекса и почему поиск по ней считается O(1)?
Короткий ответ
Hash-индекс вычисляет хеш значения и по нему сразу определяет страницу-корзину (bucket), где могут лежать нужные записи, без спуска по дереву. Поэтому поиск по равенству в среднем занимает константное число шагов, то есть O(1) по числу строк, тогда как B-tree тратит время, растущее логарифмически (по глубине дерева).
Источник: postgresql.org
Почему Hash-индекс бесполезен для WHERE age > 25 или ORDER BY, но идеален для WHERE id = 500?
Короткий ответ
Hash-индекс хранит только хеш значения, а хеш не сохраняет порядок: близкие значения (25 и 26) получают несвязанные хеши и, как правило, оказываются в разных корзинах без порядка. Поэтому диапазон
age > 25иORDER BYпо нему из такого индекса не получить, аWHERE id = 500вычисляет хеш и идёт прямо в нужную корзину.Источник: postgresql.org
Что происходит в Hash-индексе, если два разных значения выдают один и тот же хеш?
Короткий ответ
Два разных значения с одинаковым хешем попадут в одну корзину, и при поиске СУБД перепроверяет найденные записи по таблице, отбрасывая ложные совпадения. Это работает, потому что хеш-индекс хранит только хеш, а не значение: документация PostgreSQL говорит, что отсутствие значения в индексе делает любое сканирование хеш-индекса lossy.
Источник: postgresql.org
Зачем нужны частичные индексы (Partial Indexes)?
Короткий ответ
Частичный индекс строится только по строкам, удовлетворяющим условию
WHERE, поэтому он меньше и дешевле в поддержке, чем индекс по всей таблице. Типичный случай: индексировать только редкие значения, а частые не индексировать.Источник: postgresql.org
Что такое Bitmap Scan?
Короткий ответ
Bitmap scan — способ чтения в PostgreSQL: индекс сканируется, в памяти строится битовая карта положений подходящих строк, затем строки читаются из таблицы по порядку расположения на диске. В плане это пара узлов:
Bitmap Index ScanиBitmap Heap Scan.Источник: postgresql.org
Почему Bitmap-индексы редко используют в OLTP-системах (где много UPDATE/INSERT) и предпочитают в OLAP (хранилищах данных)?
Короткий ответ
Bitmap-индекс (например, в Oracle) хранит для каждого значения ключа битовую карту строк, поэтому одна запись индекса указывает на множество строк. При изменении одной строки СУБД блокирует всю запись ключа, а значит, фактически блокирует все строки с этим значением: в OLTP с параллельными записями это приводит к ожиданиям.
Источник: docs.oracle.com
Почему GIN — лучший выбор для полнотекстового поиска и работы с массивами или JSONB?
Короткий ответ
GIN (Generalized Inverted Index) — инвертированный индекс: для каждого элемента составного значения (слово документа, элемент массива, ключ
jsonb) он хранит список строк, где элемент встречается. Поэтому он быстро отвечает на вопрос «в каких строках есть этот элемент», что и нужно для полнотекстового поиска, массивов иjsonb.Источник: postgresql.org
ACID и Жизненный цикл
15 вопросовЧто такое ACID?
Короткий ответ
ACID — четыре свойства транзакции: атомарность, согласованность, изоляция, долговечность. Транзакция группирует несколько шагов в одну операцию «всё или ничего».
Источник: postgresql.org
Какие есть уровни изоляции транзакций и какие проблемы они решают?
Короткий ответ
Стандарт SQL определяет четыре уровня изоляции по запрещённым аномалиям:
READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE. Каждый следующий запрещает больше.Источник: postgresql.org
Проблема "Фантомного чтения" (Phantom Read)?
Короткий ответ
Фантомное чтение — аномалия, при которой транзакция повторно выполняет запрос по условию и получает другой набор строк, потому что другая транзакция успела зафиксировать вставку, удаление или изменение строк, подходящих под условие.
Источник: postgresql.org
Какой уровень изоляции установлен по умолчанию в PostgreSQL?
Короткий ответ
В PostgreSQL по умолчанию действует
READ COMMITTED. На этом уровне каждый операторSELECTвидит данные, зафиксированные до его начала, то есть снимок на момент начала запроса, а не транзакции.Источник: postgresql.org
Какой вид изоляции транзакций PostgreSQL не поддерживает из-за особенностей версионирования строк?
Короткий ответ
В PostgreSQL нет отдельного уровня
READ UNCOMMITTED: его можно запросить, но он ведёт себя какREAD COMMITTED, поэтому грязное чтение невозможно.Источник: postgresql.org
Блокировки и Конкурентность
14 вопросовРазница между Pessimistic и Optimistic locking?
Короткий ответ
Пессимистическая блокировка предотвращает конфликты: ресурс блокируется до начала работы, и другие ждут. Оптимистическая допускает конфликт и обнаруживает его при фиксации: если данные изменились, транзакция откатывается или повторяется.
Источник: martinfowler.com
Что такое Deadlock и как СУБД его обнаруживает?
Короткий ответ
Deadlock (на примере PostgreSQL) — взаимная блокировка: две или более транзакции держат блокировки, нужные друг другу, и ни одна не может продолжить. СУБД обнаруживает цикл ожидания и прерывает одну из транзакций, остальные продолжают.
Источник: postgresql.org
Что такое "намеренные" блокировки (Intent Locks)?
Короткий ответ
Намеренная блокировка (intent lock) ставится на более высоком уровне иерархии (таблица, страница) и сообщает: внутри будут блокировки более низкого уровня (строк). Она нужна, чтобы СУБД могла быстро решить, можно ли заблокировать всю таблицу, не перебирая строки.
Источник: learn.microsoft.com
Два менеджера открыли одну карточку заказа и сохранили её с разницей в секунду. Приложение читает строку, меняет поля в памяти и пишет
UPDATE orders SET ... WHERE id = $1. Второе сохранение молча затёрло первое. Как поймать такой конфликт, не держа блокировку между чтением формы и записью?Короткий ответ
Нужна версия строки: столбец
version, который читается вместе с карточкой, а запись делается условно:UPDATE orders SET status = $2, version = version + 1 WHERE id = $1 AND version = $3.Источник: postgresql.org
Производительность и WAL
8 вопросовЧто такое Write-Ahead Logging (WAL)?
Короткий ответ
Write-Ahead Logging (WAL) — правило: изменения файлов данных можно записывать только после того, как описывающие их записи журнала сброшены на постоянный носитель. Поэтому для фиксации транзакции достаточно сбросить на диск журнал, а страницы данных можно записать позже.
Источник: postgresql.org
Чем опасна транзакция, которая висит открытой 10 минут в высоконагруженной системе?
Короткий ответ
Долгая открытая транзакция опасна тем, что удерживает блокировки и мешает очистке старых версий строк, поэтому таблицы раздуваются, а другие запросы встают в очередь.
Источник: postgresql.org
Как минимизировать риск возникновения дедлоков в коде приложения?
Короткий ответ
Главная мера — брать блокировки в одном и том же порядке во всех частях приложения. Документация PostgreSQL называет это лучшей защитой от взаимных блокировок: если обе транзакции обновляют строки в одном порядке, цикл ожидания невозможен.
Источник: postgresql.org
Функции, Триггеры, Представления (Views)
7 вопросовРазница между Хранимой процедурой и Функцией?
Короткий ответ
Функция вызывается из выражения и возвращает значение, процедура вызывается командой
CALLи может управлять транзакциями. Это различие стандарта SQL, и в PostgreSQL оно выражено прямо.Источник: postgresql.org
Можно ли вызвать транзакцию внутри функции?
Короткий ответ
Нет, в PostgreSQL функция не может управлять транзакцией:
COMMITиROLLBACKв ней недоступны, функция всегда выполняется внутри транзакции вызывающего. Управлять транзакциями можно в процедурах, вызванных командойCALL, и в анонимных блокахDO.Источник: postgresql.org
Как SQL Injection связан с хранимыми процедурами?
Короткий ответ
Хранимая процедура защищает от SQL-инъекций только если она не собирает SQL конкатенацией строк: с параметрами она работает так же, как параметризованный запрос, а с динамическим SQL, склеенным из входных данных, уязвима так же, как приложение.
Источник: cheatsheetseries.owasp.org
Фундаментал
18 вопросовЧем отличается Реляционная vs Не-реляционная базы данных
Короткий ответ
Реляционная база хранит данные в таблицах с заданной схемой, связывает их ключами и обеспечивает ACID-транзакции и SQL с соединениями; нереляционная (NoSQL) хранит данные в другой модели: документы, пары ключ-значение, колонки или графы.
Источник: learn.microsoft.com
Преимущеста NoSQL перед SQL?
Короткий ответ
Главные доводы за NoSQL: гибкая схема, горизонтальное масштабирование и оптимизация под конкретный шаблон доступа. Это не «быстрее вообще», а выигрыш за счёт отказа от части реляционных гарантий.
Источник: learn.microsoft.com
Что такое Вертикальное (SQL) и Горизонтального (NoSQL/Sharding) масштабирование?
Короткий ответ
Вертикальное масштабирование (scale up) увеличивает мощность одного узла: больше CPU, памяти, диска. Горизонтальное (scale out) добавляет узлы, и нагрузка с данными распределяется между ними. Шардирование — способ горизонтального масштабирования данных: набор строк делится на шарды с одинаковой схемой на разных серверах.
Источник: learn.microsoft.com
Какие есть типы NoSQL?
Короткий ответ
Основные типы NoSQL: документные, ключ-значение, колоночные (wide-column) и графовые.
Источник: learn.microsoft.com
Отличие ACID от BASE?
Короткий ответ
ACID задаёт жёсткие гарантии транзакции (атомарность, согласованность, изоляция, долговечность), BASE — менее строгую модель для распределённых хранилищ: Basically Available, Soft state, Eventually consistent. ACID выбирает согласованность, BASE — доступность.
Источник: aws.amazon.com
Расскажите про CAP-теорему
Короткий ответ
CAP-теорема: в сетевой распределённой системе при разделении сети нужно выбрать между согласованностью (C) и доступностью (A). Вариант «выбираем любые два из трёх» вводит в заблуждение: от разделения сети (P) отказаться нельзя, потому что сеть может терять и задерживать сообщения.
Источник: infoq.com
Манипуляции
7 вопросовКак изменить тип колонки в таблице с миллионом строк без простоя (downtime)?
Короткий ответ
Прямой
ALTER COLUMN ... TYPEобычно перезаписывает всю таблицу и её индексы под блокировкойACCESS EXCLUSIVE, поэтому на большой живой таблице его заменяют многошаговой миграцией: добавить новую колонку, постепенно заполнить её, переключить чтение и удалить старую.Источник: postgresql.org
Как работает 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».Источник: postgresql.org
Почему нельзя отфильтровать сумму в WHERE?
Короткий ответ
Агрегат нельзя использовать в
WHERE, потому чтоWHEREопределяет, какие строки попадут в агрегат, и должен вычисляться раньше. Фильтрацию по результату агрегата записывают вHAVING.Источник: postgresql.org
Оконные функции
7 вопросовНужно вывести по три самых дорогих заказа каждого клиента. Запрос с условием
WHERE row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 3падает с ошибкой. Почему и как написать запрос правильно?Короткий ответ
Оконную функцию нельзя использовать в
WHERE: PostgreSQL отвечаетwindow functions are not allowed in WHERE. Номер строки нужно посчитать во вложенном запросе и отфильтровать уровнем выше.Источник: postgresql.org
У двух сотрудников отдела одинаковая максимальная зарплата 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.Источник: postgresql.org
Нарастающий итог
sum(amount) OVER (ORDER BY paid_on)показывает одно и то же значение для двух платежей одного дня, хотя ожидалось, что итог вырастет на каждом. Почему и как исправить?Короткий ответ
С
ORDER BYрамка окна по умолчанию —RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, и в неё входят все строки до последней, равной текущей поORDER BY. Два платежа одного дня равны поpaid_on, поэтому обе строки видят сумму за весь день.Источник: postgresql.org
JSON и JSONB в PostgreSQL
5 вопросовВ каталоге признаки товаров лежат в колонке
attrs jsonb. У одной пары обуви размер записан числом ({"size": 42}), у другой строкой ({"size": "42"}), а цвет в третьей записан под ключомcolourвместоcolor. База все три вставки приняла. Почему и что из этого можно закрепить ограничениями?Короткий ответ
Колонка
jsonbпроверяет только то, что значение является корректным JSON. Структуру документа база не навязывает: тип значения, имена ключей и обязательность в схеме не заданы, поэтомуcolourдля неё просто другой ключ.Источник: postgresql.org
В
products(attrs jsonb)у ноутбука{"ram_gb": 16}. ЗапросSELECT id FROM products WHERE attrs->>'ram_gb' > '9'его не возвращает. Почему и как исправить?Короткий ответ
Оператор
->>возвращает значение какtext, и сравнение'16' > '9'идёт как сравнение строк посимвольно:'1'меньше'9', поэтому'16'меньше'9'. Число осталось внутри документа, наружу вышел текст.Источник: postgresql.org
В
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).Источник: postgresql.org
N+1 и диагностика запросов
3 вопросаСтраница «Последние заказы» показывает 50 заказов с именем клиента.
EXPLAIN ANALYZEкаждого запроса страницы показывает доли миллисекунды, а страница отвечает в разы дольше суммы этих времён. В журнале запросов на открытие одной страницы 51 запрос. Что происходит и как это исправить?Короткий ответ
Это проблема N+1: один запрос за списком и по запросу на каждую его строку (явный цикл в коде или ленивая загрузка в ORM). На 50 заказах получается 1 + 50 = 51 запрос.
Источник: postgresql.org
К странице из 20 последних заказов нужно добавить позиции каждого заказа (в
order_itemsу заказа несколько строк). Разработчик заменил цикл запросов однимJOIN order_itemsсLIMIT 20, и страница стала показывать меньше 20 заказов. Почему и как загрузить позиции без запроса на каждый заказ?Короткий ответ
LIMITотсекает строки результата, аJOINна «много» даёт строку на каждую пару «заказ — позиция». В проверке с четырьмя позициями на заказLIMIT 50вернул 50 строк, а заказов в них было 13. Следующая страница пагинации тоже начнётся не с того места.Источник: postgresql.org
Нужно снять нагрузку с сервера 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 оказывается вторым.
Источник: postgresql.org
Доступ: роли, привилегии и RLS
4 вопросаСервис ходит в базу под ролью, которая владеет схемой и всеми таблицами. Что может пойти не так и какие права нужны роли приложения, чтобы читать и писать свои таблицы?
Короткий ответ
Роль-владелец может с объектом всё, и это не ограничить: право изменять и удалять объект принадлежит владельцу и отдельной привилегией не выдаётся и не отзывается. Ошибка в коде, неудачная команда или SQL-инъекция в запросе сервиса получают то же самое:
DROP TABLE,ALTER TABLE,TRUNCATE. Владелец к тому же по умолчанию обходит политики защиты строк (RLS).Источник: postgresql.org
Включили RLS с политикой
tenant_id = current_setting('app.tenant_id')::bigint, но сервис по-прежнему видит строки всех арендаторов. Почему?Короткий ответ
Сервис подключается под ролью, которая владеет таблицей: владелец обходит политики, пока для таблицы не включено
FORCE ROW LEVEL SECURITY. Суперпользователи и роли с атрибутомBYPASSRLSобходят RLS всегда. Политика при этом написана верно и просто не применяется к этой роли.Источник: postgresql.org
Арендатор задаётся
SET app.tenant_id = ...в начале обработки запроса, сервис стоит за PgBouncer в режиме transaction. Почему иногда видны строки чужого арендатора и как задавать значение правильно?Короткий ответ
Обычный
SETдействует до конца сессии, то есть серверного соединения, а не клиента. В режиме transaction PgBouncer отдаёт клиенту серверное соединение только на время транзакции, и каждая следующая транзакция может попасть на другое соединение с другим состоянием сессии. Значение, выставленное черезSET, остаётся на соединении после транзакции и достаётся следующему клиенту, у которого арендатор другой. Политика читаетcurrent_settingи показывает строки того, кто выставил значение последним. В таблице совместимости PgBouncerSETиRESETв режиме transaction помечены как не работающие (Never).Источник: postgresql.org
