PostgreSQL: тест продвинутого сёрфера интернета

После того, как я успешно разобрал базовый и средний тест по PostgreSQL, стало ясно, что пора двигаться дальше. А что, если поднять ставки и взяться за что-то по-настоящему серьёзное? Именно с такой мыслью я отправился на поиски теста в интернетах. И знаете что? Я его нашёл. Этот тест обещал быть настоящим испытанием: оптимизация сложных запросов, работа с большими объёмами данных, продвинутые триггеры и CTE (common table expressions).

В этой статье я поделюсь своим опытом прохождения этого теста, если вы готовы нырнуть в дебри PostgreSQL вместе со мной, добро пожаловать!

👉🏻 Навигация по всем материалам в Telegram

Вопрос 1

Какая команда должна быть на месте [...] в запросе, чтобы создать новую запись в таблице role?

Варианты ответа:

1. {read'} as permission[]
2. array['read']::permission[]
3. array['read']
4. {permission.read}
5. "read"

Обоснование:

Описание структуры базы данных:

1. Определяется тип permission как перечисление (enum) с возможными значениями read, write, execute.
2. В таблице role есть поле permissions, которое представляет массив значений типа permission[].

Требование для записи данных в массив: Чтобы корректно вставить значения в поле типа permission[], необходимо использовать массив. В PostgreSQL массив создается с помощью функции array[] или фигурных скобок {}.

Анализ вариантов:

1. {read'} as permission[] — Неверно, так как это некорректный синтаксис. Объявление массива типа permission[] должно быть оформлено иначе.
2. array['read']::permission[] — Верно, так как это явное создание массива со спецификацией типа permission[].
3. array['read'] — Также верно, так как PostgreSQL автоматически приведет массив к соответствующему типу, если поле уже имеет тип permission[].
4. {permission.read} — Неверно, так как синтаксис {} подходит только для строковых значений. В этом случае требуются именно элементы перечисления типа permission.
5. "read" — Неверно, так как это строковое значение, а не массив типа permission[].

Чтобы корректно вставить значения в поле типа permission[], нужно использовать массив. В PostgreSQL массив создается с помощью функции array[] или фигурных скобок {}. Вариант array['read']::permission[] явным образом указывает тип массива и соответствует типу данных поля.

📌Правильный ответ:

array['read']::permission[]

Вопрос 2

Какое утверждение относительно генерируемых колонок является ложью?

Варианты ответа:

1. Можно ссылаться на другие генерируемые колонки
2. В выражении вычисления нельзя ссылаться на большинство системных таблиц
3. Для stored генерируемых колонок значение пересчитывается каждый раз при изменении значений в колонках, на которые ссылаются в выражении
4. Невозможно установить значение вручную через команды insert и update
5. Один из видов занимает место в памяти

Обоснование:
Генерируемые колонки (computed или virtual/generated columns) имеют определенные правила:

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

📌Правильный ответ:
Можно ссылаться на другие генерируемые колонки (неправда, так как это возможно только в некоторых случаях, а зачастую запрещено).

Вопрос 3

Как добавить пермиссию read во все существующие записи таблицы role, чтобы не было дубликатов?

Варианты ответа:

1. update role set permissions = array_append(permissions,'demoMode') where permissions->'demoMode' is null
2. update role set permissions = array_append(permissions,'read') where array_position(permission,'read') is -1
3. update table role alter column permissions = distinct(array_append(permissions, 'read'))
4. update table role alter column permissions = array_append(permissions, 'read')
5. update role set permissions = array_append(permissions,'read') where array_position(permission,'read') is null

Обоснование:
Чтобы добавить элемент в массив PostgreSQL без дублирования:

• array_append добавляет элемент в конец массива.
• Проверка через array_position гарантирует, что элемент (read) не добавляется, если он уже существует.
• Условие array_position(permissions, 'read') is null корректно проверяет отсутствие элемента в массиве перед добавлением.

Анализ вариантов:

1. Неверно. Проверка permissions->'demoMode' применяется к JSON-данным, что не подходит для массива типа permission[].
2. Верно. array_append добавляет read, если его нет в массиве (через проверку array_position).
3. Неверно. distinct нельзя применить к массиву напрямую.
4. Неверно. Нет проверки на существование read, поэтому дублирование возможно.
5. Неверно. Условие array_position(permission,'read') is null некорректно из-за ошибки в синтаксисе (должно быть permissions вместо permission).

📌Правильный ответ:

update role set permissions = array_append(permissions,'read') where array_position(permissions,'read') is null

Вопрос 4

Какая особенность при использовании <query1> UNION <query2> правдива?

Варианты ответа:

1. Intersect оператор имеет больший приоритет, если они встречаются в одном запросе.
2. Сохраняется порядок, то есть в результате работы UNION все строки из выборки <query2> будут после строк из выборки <query1>.
3. Совместимые типы данных автоматически не приводятся к одному типу (например, bigserial и smallint).
4. Допускаются дубликаты в результате.
5. Количество колонок в таблицах, к которым обращаются в запросах <query1> и <query2>, должно быть одинаковым.

Обоснование:

• UNION ключевые особенности:
1. Удаляет дубликаты строк из объединенного результата (если нужен вывод с дубликатами, используется UNION ALL).
2. Количество и порядок колонок в обеих выборках должны совпадать.
3. Типы данных автоматически приводятся к наиболее общему типу (например, smallint преобразуется в bigint при необходимости).
4. Сохраняется порядок строк, если явно указан ORDER BY.
5. Intersect имеет больший приоритет в случае комбинации операторов, но это не является специфической особенностью UNION.
• Анализ вариантов:
1. Неверно. Это относится к оператору INTERSECT, а не к UNION.
2. Неверно. Порядок строк не гарантируется, если не используется ORDER BY.
3. Неверно. Типы данных автоматически приводятся к одному совместимому типу.
4. Неверно. UNION удаляет дубликаты, поэтому дубликаты не допускаются.
5. Верно. Количество и порядок колонок должны совпадать, это обязательное требование для корректной работы UNION.

📌Правильный ответ:
5. Количество колонок в таблицах, к которым обращаются в запросах <query1> и <query2>, должно быть одинаковым.

Вопрос 5

Какие проблемы могут возникнуть при удалении записей из t1 при помощи оператора DELETE?

Варианты ответа:

1. Чтобы обойти ограничение референциальной целостности, придется использовать оператор TRUNCATE...CASCADE с условием WHERE.
2. При наличии записей в таблице t2, которые ссылаются на записи, которые вы хотите удалить, будет выброшена ошибка, которая запретит такого рода удаление.
3. Операция не завершится успехом, так как транзакция, в которой выполняется операция, не позволяет обращаться к вручную созданным последовательностям.
4. Если при удалении записи из t1 в ней не будет записи с id = nextval('seq'), удаление не завершится из-за ограничения референциальной целостности для таблицы t2.
5. При удалении записи из таблицы t1 все ссылающиеся на нее записи из таблицы t2 изменят значение на null, что в дальнейшем придется исправлять руками.

Обоснование:

• Рассмотрение структуры:
1. Таблица t1 содержит первичный ключ id.
2. Таблица t2 имеет внешний ключ, который ссылается на t1(id). При удалении записи из t1 срабатывает условие on delete set default, устанавливающее значение внешнего ключа в t2 на значение по умолчанию (default nextval('seq')).
• Проверка утверждений:
1. Неверно. Для удаления записей из t1 с учетом связей не требуется использовать TRUNCATE...CASCADE, так как в данном случае удаление разрешено с установкой значения по умолчанию.
2. Неверно. Ошибка не возникнет, так как срабатывает поведение on delete set default, которое предотвращает нарушение референциальной целостности.
3. Неверно. Удаление не связано с транзакциями последовательностей.
4. Неверно. Референциальная целостность обеспечивается автоматически через установку значения по умолчанию в t2.
5. Верно. При удалении записи из t1 значения в t2, ссылающиеся на удаляемую запись, изменяются на значение по умолчанию, что может потребовать последующей ручной корректировки.

📌Правильный ответ:
5. При удалении записи из таблицы t1 все ссылающиеся на нее записи из таблицы t2 изменят значение на null, что в дальнейшем придется исправлять руками.

Вопрос 6

В каких запросах планировщик будет использовать индекс, при условии, что в таблице 1 млн записей?

Структура таблицы и индексы:

Варианты запросов:

1. select * from some_table where name like '^...'
2. select * from some_table where param < 100 and id > 100
3. select * from some_table where lower(name) like '...'
4. select * from some_table where name like '%...%'
5. select * from some_table where id < 800000

Обоснование:

• Использование индексов:
  Индексы работают эффективно только в тех случаях, когда условие запроса может быть применено к самому индексу без необходимости построчного перебора таблицы. Это зависит от типа запроса и структуры индекса:
• Для поля id индекс может использоваться при любых сравнениях (<, >, =).
• Для поля name индекс может использоваться только при поиске, который начинается с фиксированной строки (name like '...'), но не при поиске с подстановкой в начале (name like '%...').
• Анализ запросов:
1. select * from some_table where name like '^...'
   Индекс используется, так как запрос начинается с конкретного символа.
2. select * from some_table where param < 100 and id > 100
   Поле param не имеет индекса, поэтому для него будет построчный перебор. Индекс может быть применен только для фильтрации id > 100.
3. select * from some_table where lower(name) like '...'
   Индекс не используется, так как функция lower() изменяет поле и делает индекс недоступным.
4. select * from some_table where name like '%...%'
   Индекс не используется, так как строка начинается с символа подстановки %, что требует построчного перебора.
5. select * from some_table where id < 800000
   Индекс используется, так как условие применимо напрямую к индексу id.

📌Правильный ответ:

5. select * from some_table where id < 800000

Запрос находит записи, используя индекс на поле id, что наиболее эффективно и точно соответствует условиям использования индекса в данной структуре таблицы.

Вопрос 7

Какой индекс лучше всего подходит для наиболее часто используемого запроса:

Варианты ответа:

1. create index <index_name> on some_table using hash(major,minor)
2. create index <index_name> on some_table(major,minor)
3. create index <index_name> on some_table(minor,major)
4. create index <index_name> on some_table using btree(minor,major)
5. create index <index_name> on some_table(major)
create index <index_name> on some_table(minor)

Обоснование:
Для наилучшей производительности важно учитывать порядок колонок в индексе. PostgreSQL при составлении индекса использует порядок колонок слева направо, а значит:

• Индекс (major, minor) подходит, так как запрос фильтрует сначала по major, а затем по minor.
• Индекс (minor, major) будет менее оптимальным, так как PostgreSQL не сможет эффективно использовать индекс, если фильтр по major идет первым.
• Хэш-индексы (hash) используются только для равенства, но они менее универсальны, чем B-деревья, и часто не поддерживают сортировку или диапазоны.
• Создание отдельных индексов на major и minor не так эффективно, как комбинированный индекс (major, minor), поскольку оптимизатор запросов не сможет объединить их для данной структуры фильтра.

📌Правильный ответ:
2. create index <index_name> on some_table(major,minor)

Вопрос 8

Существуют две транзакции (T1 и T2). Какой вид изолированности транзакции НЕ позволит выполнить commit?

Режимы изоляции:

1. Serializable
2. Repeatable Read
3. Read Uncommitted
4. Read Committed
5. Snapshot

Варианты ответа:

1. Serializable:
• Полная изоляция. Обеспечивает сериализуемое выполнение транзакций, имитируя последовательное выполнение.
• В данном случае транзакции T1 и T2 конфликтуют, так как одна читает данные, которые другая модифицирует (и наоборот). Такой конфликт приведет к невозможности выполнения одной из транзакций.
2. Repeatable Read:
• Обеспечивает неизменность данных, считанных транзакцией, но не предотвращает фантомные записи.
• В данной ситуации T1 и T2 могут завершиться, так как этот уровень изоляции не гарантирует предотвращения конфликта вставки новых данных.
3. Read Uncommitted:
• Минимальный уровень изоляции. Разрешает чтение "грязных" данных.
• В этом режиме обе транзакции успешно завершатся, даже если данные будут конфликтовать.
4. Read Committed:
• Гарантирует, что транзакция читает только подтвержденные данные, но изменения могут быть видны между чтениями.
• Здесь транзакции T1 и T2 также завершатся успешно, так как конфликт предотвращается только на уровне чтения.
5. Snapshot:
• Создает снимок данных на момент начала транзакции. Гарантирует, что транзакция работает с неизмененными данными.
• Конфликт здесь может быть устранен, если обе транзакции не видят изменения друг друга, но не гарантирует предотвращение конфликтов записи.

📌Правильный ответ:
Serializable

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

Вопрос 9

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

Варианты ответа:

1. SELECT FOR UPDATE
2. CREATE INDEX CONCURRENTLY
3. CREATE TRIGGER
4. TRUNCATE TABLE
5. INSERT

Обоснование:

• Команда CREATE INDEX CONCURRENTLY позволяет создать индекс без эксклюзивной блокировки таблицы, что позволяет другим транзакциям выполнять операции с таблицей параллельно.
• Другие команды, такие как SELECT FOR UPDATE, CREATE TRIGGER, TRUNCATE TABLE, и INSERT, требуют блокировок, которые конфликтуют с CREATE INDEX.
• CREATE INDEX CONCURRENTLY специально разработан для минимизации конфликтов.

📌Правильный ответ:
2. create index concurrently

Вопрос 10

Какие утверждения о следующем запросе правдивы?

Варианты ответа:

1. Для представления some_view можно создать индекс.
2. Чтение возможно сразу после создания представления some_view.
3. Возможна операция refresh materialized view concurrently some_view.
4. Так как использовалось выражение select *, при модификации таблицы some_table изменятся и колонки в представлении some_view.
5. Возможна операция вставки в представление some_view.

Обоснование:

1. Для представления some_view можно создать индекс:
• Верно. Материализованное представление хранится физически как таблица, и на него можно создавать индексы, что полезно для оптимизации запросов. Это утверждение остается корректным независимо от других условий.
2. Чтение возможно сразу после создания представления some_view:
• Неверно. Ключевое слово WITH NO DATA указывает, что данные в представление не загружаются при создании. Оно должно быть обновлено с помощью команды REFRESH MATERIALIZED VIEW, чтобы стать доступным для чтения.
3. Возможна операция refresh materialized view concurrently some_view:
• Вроде верно. Если для материализованного представления существует уникальный индекс, возможно его обновление с использованием опции CONCURRENTLY, что позволяет другим запросам обращаться к нему во время обновления.
4. Так как использовалось выражение select *, при модификации таблицы some_table изменятся и колонки в представлении some_view:
• Неверно. Материализованное представление не обновляется автоматически при изменении исходной таблицы. Для обновления данных необходимо выполнять команду REFRESH MATERIALIZED VIEW.
5. Возможна операция вставки в представление some_view:
• Неверно. Материализованные представления являются только для чтения, операции вставки или модификации данных в них невозможны.

📌Правильные утверждения:

Для представления some_view можно создать индекс.

Вопрос 11

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

Варианты ответа:

1. VACUUM
2. EXPLAIN (ANALYZE BUFFERS)
3. Нагрузить базу и использовать EXPLAIN
4. EXPLAIN ANALYZE
5. EXPLAIN

Обоснование:

• VACUUM: Эта команда предназначена для очистки и анализа таблиц, но она не используется для замеров производительности запросов.
• EXPLAIN: Показывает план выполнения запроса, но без выполнения самого запроса. Подходит для анализа структуры плана, но не дает замеров времени или использования ресурсов.
• EXPLAIN ANALYZE: Выполняет запрос и показывает реальный план выполнения, включая время выполнения. Это лучший способ замерить производительность запросов.
• EXPLAIN (ANALYZE BUFFERS): Дополняет вывод EXPLAIN ANALYZE информацией о количестве считанных и записанных блоков, что полезно для глубокого анализа.
• Нагрузить базу и использовать EXPLAIN: Не дает точных данных, так как нагрузка влияет на планировщик запросов и результаты могут быть неоднородными.

📌Правильный ответ:
4. EXPLAIN ANALYZE

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

Вопрос 12

Какой вид сканирования таблицы будет выбран планировщиком после выполнения следующих команд?

Варианты ответа:

1. bitmap index scan
2. index scan
3. index only scan
4. seq scan
5. bitmap heap scan

Обоснование:

1. Создание таблицы:
• Поле t имеет уникальный индекс (по умолчанию создается для serial unique). Это позволяет использовать индекс для поиска.
2. Запрос с фильтром t > 2:
• Поскольку поле t проиндексировано и используется в условии фильтрации, планировщик запросов PostgreSQL может использовать индекс.
• Однако выбор между seq scan, index scan, или другим видом сканирования зависит от количества записей, фильтруемого диапазона и статистики таблицы.
3. Когда выбирается seq scan:
• Если фильтруется большая часть данных, планировщик предпочитает последовательное сканирование (seq scan), так как оно быстрее, чем использование индекса для большого объема данных.
4. Условия для index scan и других видов индекса:
• Если фильтруется небольшая часть данных, планировщик использует индексное сканирование (index scan или bitmap index scan).
5. Данный случай:
• Таблица содержит 100,001 записей. Условие t > 2 исключает только первые три записи, поэтому большая часть данных остается.
• Планировщик выберет последовательное сканирование (seq scan), так как фильтруется слишком небольшая часть данных, и использование индекса не будет эффективным.

📌Правильный ответ:
seq scan

Вопрос 13

Как временно предотвратить вставку данных в таблицу action_log, где action = 'logIn', сохранив уже существующие записи?

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

1. Создать before insert trigger, запрещающий 'logIn'.
2. Создать constraint на колонку action, запрещающий 'logIn'.
3. Создать представление с условием: where action != 'logIn'.
4. Создать instead of trigger, запрещающий 'logIn'.
5. Создать after insert trigger, удаляющий вставленную строку.

Обоснование:

• before insert trigger:
• Это наиболее подходящее решение. Триггер проверяет условие до вставки, и если оно выполняется (action = 'logIn'), триггер может остановить операцию.
• constraint:
• Ограничение (constraint) обычно используется для постоянного контроля данных, а не для временных изменений. К тому же, его добавление может быть сложным для существующих данных.
• Представление:
• Представление не предотвращает вставку, а просто ограничивает видимость данных.
• instead of trigger:
• Используется для замены действия, например, вставки в представления. Это не подходит для физической таблицы.
• after insert trigger:
• Хотя можно удалить строки после вставки, это будет менее эффективно и создаст дополнительную нагрузку на базу.

📌Правильный ответ:
Создать before insert trigger, запрещающий 'logIn'.

Вопрос 14

Каков результат выполнения команды:

Варианты ответа:

1. Если выполнивший команду не superuser, manuel не получит никаких прав.
2. Если исполнивший команду superuser, manuel получит все права на таблицу kinds, а также сможет назначать права другим пользователям.
3. Если у manuel есть все права на схему, в которой находится таблица kinds, он получит права на модификацию данных.
4. Если выполнивший команду не owner, manuel не получит никаких прав.
5. Если выполнивший команду ни superuser, ни owner таблицы kinds, manuel получит только те права на таблицу kinds, что у есть у исполнителя команды.

Обоснование:

• GRANT команда:
  Позволяет передать привилегии пользователям или ролям. Чтобы успешно выполнить команду GRANT, исполнитель должен быть либо владельцем таблицы, либо superuser.
• Анализ вариантов:
1. Неверно. Пользователь, не являющийся superuser, может выполнять GRANT, если он является владельцем таблицы.
2. Неверно. Superuser может передать права, но права на назначение другим пользователям (GRANT OPTION) передаются отдельно.
3. Неверно. Права на схему не влияют напрямую на права на таблицу.
4. Верно. Если пользователь, выполнивший команду, не является владельцем таблицы и не superuser, он не может передать права на эту таблицу.
5. Неверно. GRANT не может передать больше прав, чем имеет исполнитель команды.

📌Правильный ответ:
4. Если выполнивший команду не owner, manuel не получит никаких прав.

Вопрос 15

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

Варианты ответа:

1. Репликация
2. Репликация вместе с горизонтальным шардингом
3. Горизонтальное шардинг
4. Репликация вместе с вертикальным шардингом
5. Вертикальное шардинг

Обоснование:
Для системы с большим количеством запросов по ключу важны масштабируемость и высокая доступность данных. Каждая методика обладает своими особенностями:

1. Репликация:
   Обеспечивает дублирование данных для повышения отказоустойчивости и производительности чтения, но не решает проблему масштабируемости записи.
2. Горизонтальное шардинг:
   Разделяет данные между разными серверами по ключу (например, по ID), что улучшает масштабируемость. Но без репликации уязвимость данных увеличивается.
3. Репликация + горизонтальный шардинг:
   Совмещает преимущества репликации (для отказоустойчивости) и шардинга (для масштабируемости). Это оптимальный вариант для систем с большим количеством запросов по ключу.
4. Вертикальное шардинг:
   Разделяет данные по структуре таблиц (например, одни таблицы на одном сервере, другие на другом). Этот подход не эффективен для запросов по ключу, так как данные одного ключа могут оказаться на разных серверах.
5. Репликация + вертикальный шардинг:
   Может улучшить отказоустойчивость, но не является решением для большинства запросов по ключу.

📌Правильный ответ:
2. Репликация вместе с горизонтальным шардингом

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

The conguration test will pass!

Заключение

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

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

Оставайтесь на связи, следите за обновлениями материалов в Telegram 🚀