Хорошей традицией на постгресовых конференциях стало устраивать викторины с розыгрышем билетов на следующие конференции. Наша компания Postgres Professional на недавнем PgDay’17 ^[1] разыгрывала билеты на PgConf.Russia 2018 ^[2], которая пройдет в феврале 2018 года в Москве. В этой статье представлен обещанный разбор вопросов викторины.

Участникам конференции были предложены следующие вопросы:

1. При выполнении на базе read-only запросов была обнаружена запись на диск. Кто (что) виноват?

Варианты ответа: WAL, Hint bits, Vacuum, Russian Hackers, Еноты.

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

Правильный ответ — Hint bits, в русской документации это “вспомогательные биты”. К сожалению, в документации мало о них говорится, но это восполнено в Wiki ^[3]. Эти биты находятся в заголовке кортежа, и предназначены для ускорения вычисления его видимости. Они содержат информацию о том:

• создан ли кортеж транзакцией, которая уже зафиксирована,
• создан ли кортеж транзакцией, которая была прервана,
• удален ли кортеж транзакцией, которая уже зафиксирована,
• удален ли кортеж транзакцией, которая была прервана.

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

2. Сколько записей будет добавлено в pg_class командой:

CREATE TABLE t (id serial primary key, code text unique);

Поиск правильного ответа начнем с того факта, что в системной таблице pg_class хранятся не только таблицы, но и индексы, последовательности, представления и некоторые другие объекты.

Поскольку мы создаем таблицу, то одна запись в pg_class точно добавится. Итак, одна запись в pg_class есть.

Следующий факт, который нам понадобится, заключается в том, что для реализации ограничений PRIMARY KEY и UNIQUE в PostgreSQL используются уникальные индексы.

В создаваемой таблице есть и первичный ключ (id), и уникальный (code). Значит на каждое из этих ограничений будет создано по индексу. Промежуточный итог — 3 записи.

Теперь посмотрим на тип данных serial, который используется для столбца id. В действительности, такого типа данных нет, столбец id будет создан с типом integer, а указание serial означает, что нужно создать последовательность и указать её в качестве значения по умолчанию для id. Таким образом, будет создана последовательность, а количество записей в pg_class увеличивается до 4.

Столбец code объявлен с типом text, а этот тип может содержать очень большие значения, значительно превышающие размер страницы (обычно 8KB). Как их хранить? В PostgreSQL используется специальная технология для хранения значений большого размера — TOAST. Суть её в том, что если строка таблицы не помещается на страницу, то создается еще одна специальная toast-таблица, в которую будут записываться значения «длинных» столбцов. Для пользователя вся эта внутренняя кухня не видна, мы работаем с основной таблицей и можем даже не догадываться как там всё внутри устроено. А PostgreSQL для того чтобы быстро «склеивать» строки из двух таблиц создает еще и индекс на toast-таблицу. В итоге, наличие столбца code с типом text приводит к тому, что в pg_class создаются еще две записи: для toast-таблицы и для индекса на неё.

Общий итог и правильный ответ: 6 записей (сама таблица, два уникальных индекса, последовательность, toast-таблица и индекс на неё).

3. Есть две таблицы:

CREATE TABLE t1(x int, y int);
CREATE TABLE t2(x int not null, y int not null);

В обе добавили 1 млн записей. Какая таблица займет больше места на диске и почему?
Варианты ответа были такими: Первая, вторая, займут поровну, первая займет меньше или столько же, сколько вторая, зависит от версии PostgreSQL.

Если в первую таблицу добавили значения, не являющиеся NULL, то она будет занимать столько же места, сколько и вторая. Если же в нее добавили NULL’ы, она будет занимать меньше места.

Это так вследствие особенностей хранения NULL-ов (вернее, не-хранения: они не хранятся, вместо них в заголовке записи проставляются специальные биты, указывающие на то, что значение соответствующего поля — NULL). Подробнее об этом можно узнать из из документации ^[4] и доклада Николая Шаплова Что у него внутри ^[5].

Кстати, если в таблице t1 только одно из двух полей будет NULL, t1 займет столько же места, сколько и t2. Хотя NULL не занимает места, действует выравнивание, и поэтому в целом на занимаемый записями объем это не влияет. Выравнивание еще встретится нам в задаче №4.

Дотошный читатель возразит: «ну хорошо, сами NULL’ы не хранятся, но где-то же должна храниться та самая битовая строка t_bits, где по биту отводится на каждое поле, способное принимать значение NULL! Она не нужна для таблицы t2, но нужна для t1. Поэтому t1 может занять и больше места, чем t2».

Но дотошный читатель забыл про выравнивание. Заголовок записи без t_bits занимает ровно 23 байта. А под t_bits будет в t1 выделен один байт заголовка записи; а в случае t2 он будет съеден выравниванием.

Если у Вас установлено расширение pageinspect ^[6], можно заглянуть в заголовок записи, и, справляясь с документацией, увидеть разницу:

SELECT * FROM heap_page_items(get_raw_page('t1', 0)) limit 1;
SELECT * FROM heap_page_items(get_raw_page('t2', 0)) limit 1;

4. Есть таблица:

CREATE TABLE test(i1 int, b1 bigint, i2 int);

Можно ли переписать определение так, чтобы ее записи занимали меньше места на диске и если да, то как? Предложите Ваш вариант.

Тут все просто, дело в выравнивании. Если у вас 64-разрядная архитектура, то поля в записи, имеющие длину 8 байт и более, будут выровнены по 8 байт. Так процессор умеет быстрее читать их из памяти. Поэтому 4-байтные int нужно складывать рядом, тогда они будут занимать вместе 8 байт.

На 32-разрядной архитектуре разницы нет. О внутреннем устройстве записей можно узнать из документации ^[4] и уже упоминавшегося доклада Николая Шаплова Что у него внутри ^[5].

5. Какой тип занимает больше места на диске: timetz или timestamptz?

Результат ^[7] неожиданный: timetz занимает больше места (12 байт), чем timestamptz (8 байт),
почему же так? Это историческое наследие. И никто не собирается от него избавиться? См. ответ Тома Лэйна ^[8]. Кстати, если кому нибудь действительно понадобилось timetz (time with time zone) на практике, напишите нам об этом.

6. Как можно проверить консистентность БД для того, чтобы убедиться, что часть данных в БД не потеряна?

Ответ простой: в PostgreSQL пока такого средства нет. Чексуммы ^[9] есть во многих местах, но защищают не всё. Поэтому ответы типа “задампить и сравнить” мы вынуждены были считать правильными. В Postgres Pro ведется работа над улучшением самоконтроля целостности.

7. Какое из условий ниже истинно и почему?

(10,20)>(20,10)
array[20,20]>array[20,10]

Сравнение строк производится слева направо ^[10], поэтому первое выражение ложно.
Сравнение массивов производится также ^[11], поэтому второе — истино.

8. Действие каких подсистем выключает настройка track_counts = off?

Варианты ответа: Statistics Collector, Checkpointer, WAL archiving, Autovacuum, Bgwriter, Ни одна из перечисленных

В документации сказано ^[12], что track_counts включает сбор статистики доступа ^[13] к таблицам и индексам, которая нужна, в том числе, для автовакуума. С помощью этой статистики автоваккум решает, за какие таблицы ему браться. Подробности можно почитать в комментариях к исходникам автовакуума ^[14].

Конечно, архивирование WAL, Bgwriter и checkpointer с этим параметром не связаны.

9. Каков будет результат запроса

select NULL IS NULL IS NULL ?

Это, наверное, самый простой вопрос. Ответ False, т.к. NULL — это NULL, и всё это истина.

10. С какими типами индексов не работает команда

CLUSTER [VERBOSE] table_name [ USING index_name ] 

и почему?

Команда CLUSTER ^[15] упорядочивает таблицу в соответствие с некоторым индексом. Некоторые индексы могут задавать порядок, а некоторые — нет.

Ответ можно узнать с помощью команды

select amname from pg_am where amclusterable ;

Эта команда выдаст два ответа — ожидаемый btree и не очень ожидаемый GiST. Казалось бы, какой порядок задает GIST-индекс? Раскроем страшную тайну — CLUSTER просто перестраивает таблицу, обходя ее в порядке обхода индекса. Для GiST порядок не столь определен, как для B-Tree, и зависит от порядка, в котором записи помещались в таблицу. Тем не менее, этот порядок есть, и есть сообщения, что кластеризация по GIST ^[16] в отдельных случаях помогает.

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

11. Пусть настроена синхронная репликация и на мастере synchronous_commit = on.

При каком значении этой же опции на реплике задержка при выполнении COMMIT на мастере будет меньше, и почему?

synchronous_commit = on на мастере означает, что мастер будет считать COMMIT завершенным только после получения сообщения от реплики об успешной записи соответствующей части WAL’a на диск (ну, или о её застревании в буфере ОС, если у вас стоит fsync = off, но так делать не стоит, если данные представляют хоть какую-то ценность). Хитрость же в том, что момент, когда WAL сбрасывается на диск, определяется локальным значением synchronous_commit, то есть его значением на реплике.

Если на реплике synchronous_commit = off, запись произойдет не сразу, а когда WAL writer процесс сочтёт нужным ее выполнить; если точнее, он занимается этим раз в wal_writer_delay миллисекунд, но при большой нагрузке на систему сбрасываются только целиком сформированные страницы, так что в итоге максимальный промежуток времени между формированием WAL записи и ее записью на диск при асинхронном коммите получается 3 * wal_writer_delay. Все это время мастер будет терпеливо ждать, он не может объявить транзакцию завершенной до окончания записи.

Если же на реплике synchronous_commit имеет более высокое значение (хотя бы local), она сразу попытается записать WAL на диск, и поэтому задержка всего COMMIT потенциально будет меньше. Впрочем, и запись синхронного коммита можно отложить с помощью commit_delay, но это уже совсем другая история. Из этого всего следует неочевидный сходу вывод: выключенный synchronous_commit, который, казалось бы, должен уменьшать задержку COMMIT, в описанной схеме с репликацией ее увеличивает.

Подробнее об этом можно почитать в архиве списков рассылки ^[17] и в документации:
Надёжность и журнал упреждающей записи ^[18], synchronous_commit ^[19].

12. Что даст запрос:

select #array[1,2,3] - #array[2,3]

Чтобы ответить на этот вопрос, надо знать, как PostgreSQL работает с массивами. Унарный оператор “#”, определенный в расширении intarray ^[20]
вычисляет длину массива. При этом напрашивается ответ: в первом массиве три элемента, во втором — два. Казалось бы — ответом будет число 1! Но нет, если выполнить этот запрос, ответом будет ДВА. Откуда?

Важно учитывать также приоритет операторов (он намертво пришит к синтаксису и для пользовательских операторов это ведет часто к неочевидной семантике). У унарного # приоритет ниже, чем у оператора вычитания. Поэтому правильно запрос читается так:

select #( array[1,2,3] - #(array[2,3]))

Это означает, что из массива [1,2,3] “вычитается” число 2, остается массив [1,3], и в конце вычисляется длина этого массива. ДВА. Почти всем.

Ближе к концу викторины надо активизировать чувство юмора.

13. Сколько записей будет добавлено в pg_class командой:

CREATE TABLE t (id serial primary key, code text unique);

Этот вопрос “случайно” повторяет вопрос №2. См. также следующий вопрос.
Надо заметить, что ответ на 13-й вопрос, тем не менее, не должен повторять ответа на 2-й вопрос :) Это заметил всего лишь один из участников. Ведь таблица t уже создана в вопросе №2. Повторная команда не создаст ни одной записи в БД. (см также Задачи, расположенные по цепочке ^[21], Квант №10, 1987 )

14. Что использовалось при составлении данного теста: UNION или UNION ALL?

История этого вопроса такова. Случайно, в процессе подготовки викторины, в ней два раза был напечатан один и тот же вопрос. Увидев это, Иван Фролков пошутил “надо было использовать UNION, а не UNION ALL”. Шутка понравилась “товарищу полковнику” ( www.anekdot.ru/id/-10077921 ^[22] ), и викторина была пополнена 14-м вопросом.

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

В составлении викторины участвовали:
Алексей Шишкин, Алексей Игнатов, Арсений Шер, Анастасия Лубенникова, Александр Алексеев, Иван Панченко, Иван Фролков.

За 12-й вопрос мы благодарны Николаю Шуляковскому из mail.ru.

Победители викторины получили промокоды, которые они могут ввести вместо оплаты участия в PgConf.Russia 2018 ^[23]

.

Автор: x-wao

Источник ^[24]