Dan Levy

Квиз: Погружение в Postgres. Часть 2

Вы SQL-кремень? Или SQL-слабак?

Часть 2 из 2. Назад к части 1

Добро пожаловать во вторую часть моего глубокого квиза по PostgreSQL!

Этот квиз охватывает смесь знакомых и малоизвестных фич и нюансов PostgreSQL: JOIN, NULL, ANSI SQL, транзакции, внутреннее устройство, индексация, выборка данных… и не только!

Удачи! 🍀

1. 

Разминка: Соединения

Какое из выражений НЕ является ❌ валидным типом JOIN в PostgreSQL?

Вы ведь знали про CROSS JOIN, верно?

Возможно, JOIN ALL заставил вас задуматься о другой операции — UNION ALL?

Правильный ответ здесь — JOIN ALL ❌.

Корректные типы JOIN это:

  • INNER JOIN (JOIN по умолчанию)
  • LEFT JOIN (или LEFT OUTER JOIN)
  • RIGHT JOIN (или RIGHT OUTER JOIN)
  • FULL JOIN (или FULL OUTER JOIN)
  • CROSS JOIN (декартово произведение, если только оно не фильтруется последующим предложением WHERE)

2. 

Столбцы Identity

Какой синтаксис (согласно стандарту SQL) используется для автоинкрементного столбца IDENTITY в PostgreSQL (v10+)?

Хотя SERIAL все еще часто используется, способ, соответствующий стандарту SQL, выглядит так:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) — это синтаксис SQL Server.
  • id INT AUTO_INCREMENT — это синтаксис MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY — это синтаксис MySQL.

У этого подхода есть несколько преимуществ перед SERIAL:

  • Соответствие стандарту SQL
  • Улучшенное управление владением последовательностью (sequence ownership)
  • Более четкая семантика при ручных вставках
  • Лучшая работа с репликацией

На самом деле SERIAL — это просто сокращение для создания последовательности и установки значения по умолчанию, что может привести к проблемам при создании дампов и восстановлении данных.

3. 

ANSI SQL

Какое предложение документация PostgreSQL описывает как расширение стандартного SQL?

RETURNING описано в документации PostgreSQL как расширение SQL. Оператор ILIKE и тип jsonb также специфичны для PostgreSQL.

Массивы и возможности SQL/JSON присутствуют в современных редакциях стандарта, поэтому нельзя объявлять их целиком нестандартными. WITH, LATERAL и оконные функции вошли в более поздние редакции SQL, а не в исходную редакцию ANSI SQL.

4. 

Транзакции

За исключением варианта «Зависит от побочных эффектов», что происходит после возникновения ошибки?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

Когда здесь возникает ошибка:

  • Вся транзакция откатывается
  • Никакие изменения не фиксируются
  • Транзакция помечается как неудавшаяся

Для частичной фиксации:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

Внутреннее устройство SQL

Какое условие ON PostgreSQL может использовать для выполнения HASH JOIN?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

Hash join возможен только при наличии условия равенства (=) в предложении ON.

  • = — это основной предикат для хеш-соединений
  • > и >= не являются предикатами для hash join
  • HASH JOIN не является валидным синтаксисом в PostgreSQL

Планировщик все равно может выбрать nested loop или merge join в зависимости от размера таблиц, статистики, индексов и настроек памяти.

6. 

Индексация

Какой индекс лучше всего подходит для этого запроса?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

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

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

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

Например, если у вас есть таблица task и вы знаете, что колонка status обладает более высокой селективностью, чем created_at, стоит выбрать индекс, начинающийся со status для запроса вида status = 'done' AND created_at > '2024-01-01'. Важен не порядок условий в блоке WHERE, а порядок колонок в индексе и форма предикатов.

B+Tree индекс может использоваться для:

  • Ведущей колонки или самого левого префикса колонок.
  • Операторов =, >, <, BETWEEN и IN, в зависимости от структуры запроса.
  • Сопоставления префиксов, например LIKE 'prefix%'.

Спасибо u/mwdb2 за исправление ошибки в предыдущей версии этого объяснения!

7. 

Кавычки 1

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

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

В PostgreSQL:

  • Двойные кавычки (") используются для идентификаторов (имена таблиц, имена столбцов)
  • Одинарные кавычки (') используются для строковых литералов

Лучшие практики:

  • Избегайте смешанного регистра или пробелов в именах
  • Используйте snake_case для идентификаторов
  • Используйте кавычки для идентификаторов только в случае крайней необходимости

8. 

Экранирование 2

Как указать имя столбца, содержащее точку?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL использует двойные кавычки для экранирования идентификаторов:

  • Двойные кавычки "..." для идентификаторов (имена столбцов, таблиц)
  • Одинарные кавычки '...' для строковых литералов
  • Запись first.name интерпретируется как имя_таблицы.имя_столбца

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

Чтобы избежать сюрпризов, лучше использовать snake_case и не применять специальные символы.

9. 

Выборка данных

Какой синтаксис является допустимым для случайной выборки 10% студентов?

Этот вопрос с подвохом! Здесь расставлено около 6 ловушек, вот некоторые из них:

  1. Описание высокого уровня + фрагменты синтаксиса. Проверяет, как вы адаптируете и собираете неполную информацию.
  2. Требуется хорошее знание синтаксиса TABLESAMPLE, так как ни один из вариантов с TABLESAMPLE не использует правильный синтаксис! (Коварно, я знаю! Продолжайте, я в вас верю!!! ❤️)
  3. Варианты ROW_NUMBER() OVER (ORDER BY RAND()) и ORDER BY RANDOM() > 10 — это отвлекающие маневры. Они выглядят похожими на правду, но это не так. Функции RAND() не существует. А RANDOM() > 10 — это логическое выражение, которое всегда будет ложным, так как RANDOM() всегда возвращает значение в диапазоне 0.0-1.0. Еще одна проблема: ORDER BY RANDOM() > 10 будет выполнять сортировку по логическому значению, а не по случайному числу. Но это > 10 выглядело так, будто это проценты, верно?
  4. BERNOULLI — это валидный метод, однако SAMPLETABLE — нет.
  5. WHERE RANDOM() >= 0.1 — это ловушка! RANDOM() возвращает значение в диапазоне 0.0 <= x < 1.0, поэтому >= 0.1 вернет около 90% строк, а не 10%.

Что касается метода TABLESAMPLE, PostgreSQL поддерживает два метода выборки:

  • BERNOULLI: Каждая строка имеет равную вероятность попадания в выборку.
  • SYSTEM: Выборка на уровне блоков (быстрее, но менее случайно).

Чтобы узнать больше о выборках, ознакомьтесь с этой статьей от Render.

Пример:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

Также стоит упомянуть, что сообщалось, что TABLESAMPLE не всегда обеспечивает высокую степень случайности. Если вам нужна более случайная выборка фиксированного размера, рассмотрите использование ORDER BY RANDOM() LIMIT 10. Но имейте в виду, что это вернет 10 строк, а не 10%, и может работать медленно на больших таблицах.

Существуют также сторонние расширения, предоставляющие лучшие методы случайной выборки, но это выходит за рамки данного вопроса! 😅

10. 

Индексация

Что делает это заклинание?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

Этот трюк называется Partial Index (частичный индекс).

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

Индекс доступен планировщику, если тот может доказать, что условие запроса подразумевает status = 'active'. Буквальное совпадение текста не обязательно, но возможности доказательства ограничены, особенно для параметризованных условий. Даже подходящий индекс не обязательно будет выбран.

Напоминание: Postgres не поддерживает «подсказки к запросам» (query hints), как другие СУБД. Планировщик запросов сам решает, когда использовать подходящие индексы.

11. 

Поведение NULL

Сколько строк вернет этот запрос?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

Ладно, это было с подвохом. Ловушка в том, что x = NULL никогда не найдет совпадений ни в одной строке, и запрос ВСЕГДА вернет одну строку со значением 0.

Так как NULL — это неизвестное значение, его нельзя сравнивать ни с чем, даже с самим собой.

Правильный способ проверки на NULL — использовать оператор IS NULL.

Пример:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

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

Что делает EXPLAIN ANALYZE для операторов UPDATE?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE на самом деле выполняет запрос! Будьте осторожны при анализе операций записи, а именно операторов UPDATE, DELETE и INSERT. Возможно, вам придется выполнять операции очистки после каждого EXPLAIN ANALYZE, чтобы получить повторяемые результаты.

Лучшие практики:

  • Используйте EXPLAIN (без ANALYZE) для UPDATE/DELETE/INSERT
  • Оборачивайте модификации в транзакции:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Отлично! Вы глубоко погрузились в нюансы PostgreSQL! 🐘

Надеюсь, вы узнали что-то новое или хотя бы набрали достаточно баллов, чтобы было чем похвастаться! 🏆

Хочется еще? Загляните в мою коллекцию квизов за порцией бесконечного* веселья!

Loading comments...