Перейти к содержанию

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

191 вопрос с реальных собеседований в 17 подтемах. У первых вопросов каждой подтемы — короткий ответ и источник.

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

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: dev.mysql.com

Ещё 9 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

9 вопросов
  1. Разница между CHAR, VARCHAR и TEXT?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: rfc-editor.org

Ещё 6 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 8 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

Ответьте на эти вопросы голосом и получите разбор

Открыть приложениеНачать бесплатно

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

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 8 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

21 вопрос
  1. Что такое индексы и для чего нужны?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: rfc-editor.org

Ещё 14 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

13 вопросов
  1. Разница Index Scan vs Index Seek?

    Короткий ответ

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

    Источник: learn.microsoft.com

  2. Отличие EXPLAIN и EXPLAIN ANALYZE?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 9 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

  5. Что такое Bitmap Scan?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: docs.oracle.com

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 15 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

15 вопросов
  1. Что такое ACID?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 10 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

14 вопросов
  1. Разница между Pessimistic и Optimistic locking?

    Короткий ответ

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

    Источник: martinfowler.com

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 9 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

8 вопросов
  1. Что такое Write-Ahead Logging (WAL)?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 5 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

7 вопросов
  1. Разница между Хранимой процедурой и Функцией?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: cheatsheetseries.owasp.org

Ещё 4 вопроса с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

Фундаментал

18 вопросов
  1. Чем отличается Реляционная vs Не-реляционная базы данных

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: learn.microsoft.com

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

    Короткий ответ

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

    Источник: learn.microsoft.com

  5. Отличие ACID от BASE?

    Короткий ответ

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

    Источник: aws.amazon.com

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

    Короткий ответ

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

    Источник: infoq.com

Ещё 12 вопросов с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

Ответьте на эти вопросы голосом и получите разбор

Открыть приложениеНачать бесплатно

Манипуляции

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

    Короткий ответ

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

    Источник: postgresql.org

  2. Как работает 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

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 4 вопроса с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

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

    Короткий ответ

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

    Источник: postgresql.org

  2. У двух сотрудников отдела одинаковая максимальная зарплата 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

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 4 вопроса с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

JSON и JSONB в PostgreSQL

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

  3. В 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

Ещё 2 вопроса с ответамина Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

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

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

  3. Нужно снять нагрузку с сервера 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 вопроса
  1. Сервис ходит в базу под ролью, которая владеет схемой и всеми таблицами. Что может пойти не так и какие права нужны роли приложения, чтобы читать и писать свои таблицы?

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

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

    Короткий ответ

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

    Источник: postgresql.org

Ещё 1 вопрос с ответомна Basic, 290 ₽ в месяц

Открыть все вопросыОткрыть все вопросы

Дальше по теме

Другие темы

Все темы

Ответьте на вопросы по SQL голосом

Free без срока и без карты