Хорошей традицией на постгресовых конференциях стало устраивать викторины с розыгрышем билетов на следующие конференции. Наша компания Postgres Professional на недавнем PgDay’17 разыгрывала билеты на PgConf.Russia 2018, которая пройдет в феврале 2018 года в Москве. В этой статье представлен обещанный разбор вопросов викторины.
Участникам конференции были предложены следующие вопросы:
1. При выполнении на базе read-only запросов была обнаружена запись на диск. Кто (что) виноват?
Варианты ответа: WAL, Hint bits, Vacuum, Russian Hackers, Еноты.
Еноты и пылесос тоже могли работать с системой и писать на диск. Суть вопроса, конечно, именно в записи, произошедшей в результате чтения, а не случайно совпавшей с ним по времени.
Правильный ответ — Hint bits, в русской документации это “вспомогательные биты”. К сожалению, в документации мало о них говорится, но это восполнено в Wiki. Эти биты находятся в заголовке кортежа, и предназначены для ускорения вычисления его видимости. Они содержат информацию о том:
- создан ли кортеж транзакцией, которая уже зафиксирована,
- создан ли кортеж транзакцией, которая была прервана,
- удален ли кортеж транзакцией, которая уже зафиксирована,
- удален ли кортеж транзакцией, которая была прервана.
Значения этих битов могут быть проставлены и при операции чтения, если при определении видимости было определено значение какого-либо из них.
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). Подробнее об этом можно узнать из из документации и доклада Николая Шаплова Что у него внутри.
Кстати, если в таблице t1 только одно из двух полей будет NULL, t1 займет столько же места, сколько и t2. Хотя NULL не занимает места, действует выравнивание, и поэтому в целом на занимаемый записями объем это не влияет. Выравнивание еще встретится нам в задаче №4.
Дотошный читатель возразит: «ну хорошо, сами NULL’ы не хранятся, но где-то же должна храниться та самая битовая строка t_bits, где по биту отводится на каждое поле, способное принимать значение NULL! Она не нужна для таблицы t2, но нужна для t1. Поэтому t1 может занять и больше места, чем t2».
Но дотошный читатель забыл про выравнивание. Заголовок записи без t_bits занимает ровно 23 байта. А под t_bits будет в t1 выделен один байт заголовка записи; а в случае t2 он будет съеден выравниванием.
Если у Вас установлено расширение pageinspect, можно заглянуть в заголовок записи, и, справляясь с документацией, увидеть разницу:
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-разрядной архитектуре разницы нет. О внутреннем устройстве записей можно узнать из документации и уже упоминавшегося доклада Николая Шаплова Что у него внутри.
5. Какой тип занимает больше места на диске: timetz или timestamptz?
Результат неожиданный: timetz занимает больше места (12 байт), чем timestamptz (8 байт),
почему же так? Это историческое наследие. И никто не собирается от него избавиться? См. ответ Тома Лэйна. Кстати, если кому нибудь действительно понадобилось timetz (time with time zone) на практике, напишите нам об этом.
6. Как можно проверить консистентность БД для того, чтобы убедиться, что часть данных в БД не потеряна?
Ответ простой: в PostgreSQL пока такого средства нет. Чексуммы есть во многих местах, но защищают не всё. Поэтому ответы типа “задампить и сравнить” мы вынуждены были считать правильными. В Postgres Pro ведется работа над улучшением самоконтроля целостности.
7. Какое из условий ниже истинно и почему?
(10,20)>(20,10)
array[20,20]>array[20,10]
Сравнение строк производится слева направо, поэтому первое выражение ложно.
Сравнение массивов производится также, поэтому второе — истино.
8. Действие каких подсистем выключает настройка track_counts = off?
Варианты ответа: Statistics Collector, Checkpointer, WAL archiving, Autovacuum, Bgwriter, Ни одна из перечисленных
В документации сказано, что track_counts включает сбор статистики доступа к таблицам и индексам, которая нужна, в том числе, для автовакуума. С помощью этой статистики автоваккум решает, за какие таблицы ему браться. Подробности можно почитать в комментариях к исходникам автовакуума.
Конечно, архивирование WAL, Bgwriter и checkpointer с этим параметром не связаны.
9. Каков будет результат запроса
select NULL IS NULL IS NULL ?Это, наверное, самый простой вопрос. Ответ False, т.к. NULL — это NULL, и всё это истина.
10. С какими типами индексов не работает команда
CLUSTER [VERBOSE] table_name [ USING index_name ] и почему?
Команда CLUSTER упорядочивает таблицу в соответствие с некоторым индексом. Некоторые индексы могут задавать порядок, а некоторые — нет.
Ответ можно узнать с помощью команды
select amname from pg_am where amclusterable ;Эта команда выдаст два ответа — ожидаемый btree и не очень ожидаемый GiST. Казалось бы, какой порядок задает GIST-индекс? Раскроем страшную тайну — CLUSTER просто перестраивает таблицу, обходя ее в порядке обхода индекса. Для GiST порядок не столь определен, как для B-Tree, и зависит от порядка, в котором записи помещались в таблицу. Тем не менее, этот порядок есть, и есть сообщения, что кластеризация по GIST в отдельных случаях помогает.
При использовании пространственных индексов, например, она означает, что геометрически близкие объекты будут находится, скорее всего, ближе друг другу и в таблице.
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, в описанной схеме с репликацией ее увеличивает.
Подробнее об этом можно почитать в архиве списков рассылки и в документации:
Надёжность и журнал упреждающей записи, synchronous_commit.
12. Что даст запрос:
select #array[1,2,3] - #array[2,3]Чтобы ответить на этот вопрос, надо знать, как PostgreSQL работает с массивами. Унарный оператор “#”, определенный в расширении intarray
вычисляет длину массива. При этом напрашивается ответ: в первом массиве три элемента, во втором — два. Казалось бы — ответом будет число 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. Повторная команда не создаст ни одной записи в БД. (см также Задачи, расположенные по цепочке, Квант №10, 1987 )
14. Что использовалось при составлении данного теста: UNION или UNION ALL?
История этого вопроса такова. Случайно, в процессе подготовки викторины, в ней два раза был напечатан один и тот же вопрос. Увидев это, Иван Фролков пошутил “надо было использовать UNION, а не UNION ALL”. Шутка понравилась “товарищу полковнику” ( www.anekdot.ru/id/-10077921 ), и викторина была пополнена 14-м вопросом.
Благодарности
В составлении викторины участвовали:
Алексей Шишкин, Алексей Игнатов, Арсений Шер, Анастасия Лубенникова, Александр Алексеев, Иван Панченко, Иван Фролков.
За 12-й вопрос мы благодарны Николаю Шуляковскому из mail.ru.
Победители викторины получили промокоды, которые они могут ввести вместо оплаты участия в PgConf.Russia 2018
.
Автор: x-wao
