На Higload++ 2017 года в Сколково наша компания Postgres Professional снова провела викторину с традиционной раздачей ништяков, в качестве которых выступили билеты на февральский PgConf.Russia 2018.

В этой статье разбираются вопросы викторины.

1. Какой тип индексов позволяет эффективно выполнить запрос SELECT * FROM users WHERE lower(name) = ’vasya’?

Функция lower в PostgreSQL приводит строку к нижнему регистру.

а) Частичные
б) Функциональные
в) Покрывающие
г) Вторичные

Многие догадались, что этот запрос помогает исполнять функциональный индекс по используемому в запросе выражению lower(name):

CREATE INDEX ON users ( lower(name) );

Однако лишь один из победителей викторины заметил, что частичный индекс

CREATE INDEX ON users ( somefield) WHERE lower(name) = ’vasya’ ;

где somefield — любое поле таблицы users, тоже окажется эффективен.

Для организации контекстно-независимых строк также можно использовать тип данных citext.

2. Какого типа индексов из следующего списка НЕ существует?

а) Частичные
б) Вторичные
в) Закрывающие
г) Функциональные

Правильный ответ: закрывающих. Покрывающие индексы уже есть, а закрывающие пока еще ждут своего создателя. Им можете стать и Вы!
Покрывающим индексом для какого-то запроса называется индекс, содержащихся в котором данных достаточно для исполнения этого запроса.
Для того, чтобы проще было создавать полезные покрывающие индексы, для PostgreSQL была разработана конструкция CREATE INDEX...INCLUDE. О ней можно почитать в презентации Анастасии Лубенниковой и Ивана Фролкова, авторов этой функциональности в PostgreSQL. Пока что в версию сообщества она не попала, и доступна только в Postgres Pro.

Суть конструкции INCLUDE в добавлении в индекс дополнительной (не ключевой) информации, расширяющей применяемость index only scan. Проиллюстрируем его работу на простом примере.

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

Пусть у есть таблица с полями и индексом по ним:

CREATE TABLE users (
    name text,
    score int
);
СREATE INDEX users_name_score ON users ( name, score );

Если по таблице выполняется запрос

    SELECT name, score FROM users WHERE name = 'vasya';

то выполнение этого запроса возможно в режиме index only scan.

Усложним задачу: пусть нам требуется также контроль уникальности поля name.
Для этого индекс users_name_score не подходит, т.к. если добавить к его определению параметр UNIQUE, он будет контролировать уникальность пары полей (name, score).

Можно создавать отдельный уникальный индекс по полю name, но это ресурсоемко, и тут приходят на помощь наши покрывающие индексы:

CREATE UNIQUE INDEX ON users ( name ) INCLUDE ( score );

Теперь значения поля score будут добавляться в индекс, но не в ключевую его часть, и это позволит решить 2 задачи одним индексом — и контроль уникальности, и обеспечение index only scan.

3. Что из следующего списка НЕ является индексом?

а) Hash
б) GIN
в) WHISKEY
г) RUM

Индекса WHISKEY пока нет. Придумайте, что может это означать? WHIthout Specific KEY? Кстати, как точно расшифровывается RUM, тоже ещё не решено.

В PostgreSQL давно есть индекс типа Hash, но на практике пользоваться им можно только начиная с 10-й версии, в которой для него наконец появилась поддержка WAL-логов. В эффективности HASH-индексов по сравнении с B-Tree высказывались большие сомнения, чем и объясняется столь длительное их пребывание в полунедоделанном состоянии.

GIN (Generalized Inverse iNdex) — это обобщенный обратный индекс, разработанный Олегом Бартуновым и Федором Сигаевым. В деталях о его устройстве написано в статье Егора Рогова. Он хорош для полнотекстового поиска, индексации массивов, JSON и JSONB.

RUM — новый тип индекса, разработанный в Postgres Professional, позволяющий ускорить полнотекстовый поиск. RUM реализован в расширении, входящем в состав Postgres Pro Enterprise, но его исходники являются open source и доступны на github. RUM несколько тяжелее, чем GIN, при модификации данных, но зато позволяет ускорять поиск благодаря хранению дополнительной информации в индексе.

4. Что обеспечивает выполнение буквы I в ACID ?

а) Лог транзакций
б) MVCC
в) Внешние ключи
г) Разделяемый кэш страниц

Правильный ответ — MVCC (MultiVersion Concurrency Control, Многоверсионное управление конкурентным доступом). ACID — принцип устройства СУБД, аббревиатура, означающая Atomicity, Consistency, Isolation, Durability. В данном вопросе нас интересует I — Изоляция транзакций, для её достижения как раз и используется мультиверсионность. В каждый момент времени в базе существует столько версий каждой записи, сколько нужно для того, чтобы каждая транзакция могла бы видеть ту версию, которая корректна для неё. Именно благодаря MVCC, например, транзакция с UPDATE, изменившая какую-либо запись, не мешает чтению той же самой записи всеми остальными. Старые, никому не нужные версии удаляются специальным процессом-пылесосом (Vacuum). Мультиверсионность для обеспечения изоляции транзакций при конкурентном доступе к данным используется в большинстве реляционных СУБД.

5. Может ли логическая репликация быть синхронной?

В PostgreSQL 10 официально появилась логическая репликация. Чтобы она работала в синхронном режиме, имя подписки, определенное в команде CREATE SUBSCRIPTION, должно быть упомянуто в параметре synchronous_standby_names на мастере.

6. Может ли пользователь X узнать, какие временные таблицы создал пользователь Y? Если да, то как, если нет — почему?

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

 SELECT nspname, 
             (  SELECT json_agg(json_build_object('name',relname, 'owner', rolname))
                 FROM pg_class 
                 JOIN pg_roles ON pg_roles.oid = relowner
                 WHERE relnamespace = pg_namespace.oid
             )
     FROM  pg_namespace 
     WHERE nspname LIKE 'pg_temp%';

Таким запросом можно увидеть все временные таблицы в системе, и кем они созданы.

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

Вопрос с подвохом. Быстрее всего, конечно, на C. Но иногда быстрее оказывается SQL, т.к. функция на SQL может быть включена в вызывающий её запрос и оптимизирована вместе с ним. Подробнее о таком свойстве функций на языке SQL (не путать с PL/PGSQL) можно прочитать в статье Ивана Фролкова.

Среди обычных процедурных языков PL/PgSQL не является самым быстрым. В ряде случаев PL/Perl или PL/Python, и тем более PL/v8 оказывается заметно быстрее. Разница особенно сильно появляется, если процедуры занимаются не только работой с БД, но и вычислениями или обработкой данных.

По вопросам сравнения процедурных языков можно обратиться к их документации (PL/Perl, PL/Python, PL/v8) и докладу Ивана Панченко.

8. Сколько раз будет вызвана функция F в запросе
SELECT F(a%a) FROM generate_series(-1,1) a ?

Правильный ответ — один раз. Выражение a%a означает взятие остатка от деления переменной a на себя. На ноль делить нельзя, поэтому на второй итерации запрос свалится. Таким образом, в функцию F успеет попасть только первое значение переменной a — минус один. О функции generate_series можно прочитать в документации.

9. Запрос SELECT t.abc FROM t; успешно выполняется, хотя колонки abc в таблице t нет (при этом t – действительно таблица). Как такое возможно, и что окажется в столбце abc выборки?

Такое возможно если определена функция abc(t%rowtype). Запись t.abc в данном случае эквивалентна записи abc(t). Это – документированная возможность, рудимент объектности в постгресе.

10. Экземпляр PostgreSQL работал с параметром fsync=off (не рекомендуем так делать), обслуживая сессии, ведущие активную запись в БД.  В самый разгар работы OOM killer убил один из backend’ов, а postmaster соответственно убил весь экземпляр.  После этого экземпляр PostgreSQL был запущен заново.  Чем завершится процесс recovery и почему?

Большинство участников знали, что fsync=off — очень-очень нехорошо: при сбое можно потерять данные. Но при каком сбое? fsync — это сброс буфера ОС на диск. Когда буфер ОС может быть не сброшен на диск? Конечно, когда нарушено функционирование ядра ОС. Например, при аппаратном сбое. В условии задачи ОС функционирует нормально — так что есть все основания ожидать, что данные на диск сброшены будут, и при запуске постгреса в следующий раз база восстановится из WAL'ов.

Благодарности

В составлении вопросов и ответов приняли участие Иван Фролков, Александр Алексеев, Арсений Шер и Иван Панченко. И, конечно, нельзя не спеть дифарамбы в адрес HighLoad++ и его организаторов. Молодцы, как всегда оказались на высоте. Настоящий праздник.

Результат викторины

На вопросы викторины ответили 50 участников Highload. По итогам было разыграно 7 бесплатных приглашений на конференцию PgConf.Russia 2018, которая планируется в Москве на 5-7 февраля 2018 г.