PostgreSQL 15: Часть 1 или Коммитфест 2021-07
Закончился июль, не только самый жаркий месяц года, но и месяц первого коммитфеста очередного релизного цикла PostgreSQL. Самое время собрать свежую версию сервера и посмотреть на принятые изменения.
SELECT version(); version
------------------------------------------------------------
PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
Напомню, что самое интересное о 14 версии можно прочитать в предыдущих статьях: 2020–07, 2020–09, 2020–11, 2021–01 и 2021–03.
PostgreSQL 14
А начать стоит с описания патчей принятых в 14 версию после заморозки кода 8 апреля (либо пропущенных мною раньше). К сожалению, придется перечислить и отмененные патчи.
Параметр recovery_init_sync_method
commit: 61752afb, 34a8b64b
Восстановление после сбоя при запуске сервера начинается с того, что каждый файл кластера синхронизируется с диском вызовом fsync. Если файлов много, это может занять продолжительное время.
Для linux появилась возможность задать в новом параметре recovery_init_sync_method значение syncfs. В этом случае для синхронизации файлов кластера будет использоваться соответствующий вызов, что может ускорить время восстановления.
Тела встроенных функций на языке SQL приведены к форме стандарта SQL
commit: 767982e3
Важное изменение 14 версии ― возможность определять тело функций на языке SQL в соответствии со стандартом SQL. В данном коммите встроенные функции сервера приводятся к этому формату.
К примеру описание функции age:
\sf age(timestamptz)CREATE OR REPLACE FUNCTION pg_catalog.age(timestamp with time zone)
RETURNS interval
LANGUAGE sql
STABLE PARALLEL SAFE STRICT COST 1
RETURN age((CURRENT_DATE)::timestamp with time zone, $1)
Сигнатура процедур с OUT-параметрами
commit: e56bce5d
В первоначальной реализации OUT-параметров для процедур обнаружились проблемы. В частности допускалось создание двух процедур с одинаковым именем, одинаковыми входными параметрами, но разными OUT-параметрами:
CREATE PROCEDURE p (a out int) LANGUAGE SQL AS 'SELECT 42';
CREATE PROCEDURE p (a out int, b out int) LANGUAGE SQL AS 'SELECT 42,42';
Теперь вторая команда выдаст ошибку:
ERROR: function "p" already exists with same argument types
Мир остался прежним, а сигнатура хранимых подпрограмм всё также состоит из имени подпрограммы и типов входных параметров.
Переименование Result Cache → Memoize
commit: 83f4fcc6
Названия нового в 14 версии узла плана выполнения «Result Cache» и параметра «enable_resultcache» оказались неудачными. Пока 14 версия не вышла решили переименовать: узел «Memoize» и параметр «enable_memoize».
Параметр force_parallel_mode «спрятали»
commit: ac725ee0
Новые пользователи PostgreSQL при виде строки в postgresql.conf:
#force_parallel_mode = off
иной раз убирают комментарий и включают параметр, ведь распараллеливание запросов это здорово. Но включение force_parallel_mode на уровне всего экземпляра может привести к сильному падению производительности. Параллельное выполнение помогает запросам, обрабатывающим большой объем данных, но для OLTP нагрузки с быстрыми запросами параллельность скорее вредна.
Но дело даже не в этом. Параметр force_parallel_mode нужен только для отладки запросов. Чтобы проверить, а может ли запрос в принципе выполняться параллельно и какой план в итоге получится. В работающих системах параметр всегда должен быть отключен.
Во избежание неправильного использования, параметр убрали из postgresql.conf, а в документации перенесли в категорию Developer Options.
Представление pg_stat_replication_slots
commit: 98681675, 8e90ec55, f5fc2f5b
Новое представление для мониторинга слотов логической репликации ― pg_stat_replication_slots содержит по одной строке на каждый логический слот. Накапливаемую статистику можно сбросить как для отдельного слота, так и для всех сразу вызовом функции pg_stat_reset_replication_slot.
Оптимизация поиска по списку значений: expr [NOT] IN (list)
commit: 50e17ad2, 29f45e29
Поиск по длинному списку значений заданных константами может выполняться достаточно долго. Для примера поищем билеты по списку кодов бронирования. В списке будет около тысячи элементов:
13=# SELECT count(*) FROM bookings WHERE total_amount > 500000;
count
-------
998
Запишем их через запятую в переменную psql:
SELECT string_agg(format('%L',book_ref), ',') AS book_ref_list
FROM bookings
WHERE total_amount > 500000\gset
После прогрева кеша несколькими исполнениями запускаем контрольный замер:
13=# SELECT count(*) FROM tickets WHERE book_ref IN (:book_ref_list);
count
-------
2679
(1 row)
Time: 3321,698 ms (00:03,322)
Но эффективнее использовать не линейный поиск по списку, а для начала на основе списка создать хеш-таблицу, а затем искать совпадения в этой хеш-таблице. В таком случае ситуация заметно улучшается. Тот же запрос в 14 версии:
14=# SELECT count(*) FROM tickets WHERE book_ref IN (:book_ref_list);
count
-------
2679
(1 row)
Time: 27,357 ms
Планы запросов приводить нет смысла, они одинаковые в обоих случаях.
Второй коммит выполняет эту же оптимизацию с хеш-таблицей для поиска по условию NOT IN (list), но доступна она будет уже только в 15 версии.
Частично откатили «Полноценная заморозка с COPY WITH FREEZE»
commit: 8e03eb92
Работа «Полноценная заморозка с COPY WITH FREEZE» из мартовского коммитфеста была частично отменена. Основная таблица замораживается командой COPY WITH FREEZE полноценно, то вот соответствующая TOAST-таблица, если есть, к сожалению это изменение не получит и последующая очистка будет переписывать все страницы.
Откатили «Отслеживание изменения версий правил сортировки у индексов»
commit: ec483147
Работа «Отслеживание изменения версий правил сортировки у индексов» из ноябрьского коммитфеста была отменена.
Откатили «Упреждающее чтение WAL при восстановлении»
commit: c2dc1934
Работа «Упреждающее чтение WAL при восстановлении» из мартовского коммитфеста была отменена.
Откатили «psql: показывать результаты всех запросов»
commit: fae65629ce
Работа «psql: показывать результаты всех запросов» из мартовского коммитфеста была отменена.
PostgreSQL 15
С доработками для 14 версии закончили, теперь посмотрим, что появится в 15 версии.
Клиентские приложения
psql: использование постраничника для просмотра результата команды \watch
commit: 7c09d279
Использование внешних утилит для просмотра результата запроса — замечательная возможность psql. Это делается установкой переменной окружения PSQL_PAGER (или PAGER).
К наиболее популярным просмотрщикам в UNIX относятся less и её предшественница more. Однако в последние годы у пользователей psql набирает популярность утилита pspg известного чешского разработчика Павла Стехуле.
pspg может не только показывать результаты запросов и справочную информацию (\?, \help), но и работать в потоковом режиме. Специально для этого в новой переменной окружения PSQL_WATCH_PAGER теперь можно задать программу для просмотра результатов запросов командой \watch.
Установите pspg и попробуйте в деле простейший инструмент мониторинга:
\setenv PSQL_WATCH_PAGER 'pspg --stream'
\x
SELECT * FROM pg_stat_database WHERE datname = current_database()\watch 1
В настоящий момент поддержка реализована только для Unix.
Документация
Новая глава о хеш-индексах
commit: e360aa05
Начиная с 10 версии поддержка хеш-индексов заметно улучшается от релиза к релизу. В этот раз появилась хоть и небольшая, но отдельная глава в документации.
Мониторинг
EXPLAIN: pg_temp в качестве схемы для временных объектов сеанса
commit: 48c5c906
EXPLAIN с параметром VERBOSE показывает точное имя схемы для временных объектов сеанса:
14=# CREATE TEMP TABLE t (id int);
14=# EXPLAIN(VERBOSE) SELECT * FROM t;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on pg_temp_4.t (cost=0.00..35.50 rows=2550 width=4)
Output: id
Но из-за такого поведения разработчикам трудно писать тесты, ведь имя схемы может меняться от запуска к запуску. Поэтому решили, что удобнее писать просто pg_temp:
15=# EXPLAIN(VERBOSE) SELECT * FROM t;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_temp.t (cost=0.00..35.50 rows=2550 width=4)
Output: id
Да и пользователей смущать не будет, а временную схему своего сеанса всегда можно узнать:
SELECT pg_my_temp_schema()::regnamespace;
pg_my_temp_schema
-------------------
pg_temp_4
Производительность
psql \copy from использует пакеты большего размера для отправки на сервер
commit: eec57115
Каждая строка для загрузки отправлялась на сервер отдельным пакетом. Это не очень здорово, особенно для «узких» таблиц ― с малым количеством столбцов небольшого размера.
Теперь данные для отправки на сервер собираются в пакеты по 8 кб.
wal_compression = lz4
commit: 4035cd5d
Для сжатия полных образов страниц WAL нужно включить параметр wal_compression:
14=# SELECT name, setting, vartype, enumvals
FROM pg_settings
WHERE name = 'wal_compression';
name | setting | vartype | enumvals
-----------------+---------+---------+----------
wal_compression | off | bool |
Но для сжатия будет использоваться pglz. Не было выбора метода сжатия до 15 версии. A теперь появился:
15=# SELECT name, setting, vartype, enumvals
FROM pg_settings
WHERE name = 'wal_compression';
name | setting | vartype | enumvals
-----------------+---------+---------+-------------------
wal_compression | off | enum | {pglz,lz4,on,off}
Как и в случае со сжатием TOAST основной выигрыш lz4 относительно pglz не в степени сжатия, а в скорости работы и соответственно меньшей нагрузке на процессор.
Значение on для обратной совместимости соответствует выбору pglz.
Сокращение размеров pg_depend, pg_shdepend
commit: a49d0812
Одна из самых запутанных таблиц системного каталога ― pg_depend. По крайней мере первичный ключ для неё так и не смогли сделать. А если заглянуть внутрь любой базы данных только что созданного кластера, то можно обнаружить следующее:
14=# SELECT count(*) AS total,
count(*) FILTER (WHERE deptype = 'p') AS count_p
FROM pg_depend;
total | count_p
-------+---------
8903 | 6995
Что же это за записи с deptype='p', занимающие значительную часть таблицы? Это способ указать, что объект является системным и его нельзя удалять. Т.е. это даже не зависимость между объектами, а просто пометка о том, что объект нельзя удалять. Таким образом помечаются все объекты системного каталога при инициализации кластера, и в дальнейшем этот тип зависимости больше не используется.
В 15 версии решили, что от таких записей лучше избавиться. Проверять является ли объект системным можно по значению его OID, ведь диапазон номеров выдаваемых во время initdb заранее известен.
15=# SELECT count(*) AS total,
count(*) FILTER (WHERE deptype = 'p') AS count_p
total | count_p
-------+---------
1908 | 0
Аналогичные изменения сделаны в pg_shdepend. Но там и было-то всего 12 записей и все с deptype='p'. Сейчас таблица инициализируется пустой.
Сокращено время планирования запросов к представлениям, с большой вложенностью других представлений
commit: 64919aaa
Если точнее, то время планирования запросов к вложенным одно в другое представлениям уменьшилось с O (N^3) на O (N^2).
Ускорение pg_checksums --enable
commit: 4c9f50d1
pg_checksums не будет переписывать контрольную сумму блока, если она совпадает с уже записанной в блоке. Раньше контрольная сумма записывалась в любом случае. В выводе утилиты будет указано сколько блоков прочитано и сколько записано.
Оптимизация полезна при повторном запуске pg_checksums --enable, когда предыдущий запуск завершился неудачно.
postgres_fdw: передача выражений CASE на удаленный сервер
commit: 5d44fff0
Вычисление выражений CASE происходит локально, что может быть очень неэффективным, особенно при изменении данных. В следующем примере aircrafts_data ― сторонняя таблица, в которую добавлен столбец range_type — категория дальности полета:
ALTER TABLE aircrafts_data
ADD COLUMN range_type text
CHECK (range_type IN ('local', 'short', 'mid', 'long'));
Заполним новый столбец:
14=# EXPLAIN (VERBOSE, COSTS OFF)
UPDATE aircrafts_data
SET body_type = CASE WHEN range < 1000 THEN 'local'
WHEN range >= 1000 AND range < 2500 THEN 'short'
WHEN range >= 2500 AND range < 6000 THEN 'mid'
WHEN range >= 6000 THEN 'long'
END;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on public.aircrafts_data
Remote SQL: UPDATE bookings.aircrafts_data SET body_type = $2 WHERE ctid = $1
-> Foreign Scan on public.aircrafts_data
Output: CASE WHEN (range < 1000) THEN 'local'::text
WHEN ((range >= 1000) AND (range < 2500)) THEN 'short'::text
WHEN ((range >= 2500) AND (range < 6000)) THEN 'mid'::text
WHEN (range >= 6000) THEN 'long'::text ELSE NULL::text
END, ctid, aircrafts_data.*
Remote SQL: SELECT aircraft_code, model, range, body_type, range_type, ctid
FROM bookings.aircrafts_data FOR UPDATE
Сначала на стороннем сервере выполняется запрос (с блокировкой строк), данные передаются на локальный сервер, вычисляется выражение CASE и только после этого построчно выполняются операторы UPDATE на удаленной стороне.
Теперь postgres_fdw научился передавать вычисление CASE на сторонний сервер. План того же запроса:
15=# EXPLAIN (VERBOSE, COSTS OFF)
UPDATE aircrafts_data
SET body_type = CASE WHEN range < 1000 THEN 'local'
WHEN range >= 1000 AND range < 2500 THEN 'short'
WHEN range >= 2500 AND range < 6000 THEN 'mid'
WHEN range >= 6000 THEN 'long'
END;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on public.aircrafts_data
-> Foreign Update on public.aircrafts_data
Remote SQL: UPDATE bookings.aircrafts_data SET body_type = (
CASE WHEN (range < 1000) THEN 'local'::text
WHEN ((range >= 1000) AND (range < 2500)) THEN 'short'::text
WHEN ((range >= 2500) AND (range < 6000)) THEN 'mid'::text
WHEN (range >= 6000) THEN 'long'::text ELSE NULL::text END
)
Секционирование: оптимизация времени планирования
commit: 475dbd0b, db632fbc
Время планирования запросов к секционированной таблице сократится в тех случаях, когда из большого количества секций исключаются почти все, и реально сканировать нужно одну или несколько секций. Оптимизация достигается за счет построения в памяти набора битовых карт для оставшихся секций, подлежащих сканированию.
Дополнительным плюсом стала возможность в некоторых запросах использовать индекс для сортировки данных нескольких секций. Речь о секционированных таблицах с разбиением по списку значений, когда допустимые значения в разных секциях пересекаются между собой.
Сортировка с использованием индекса для разбиения по диапазону появилась еще в 12 версии. Теперь посмотрим как она работает для разбиения по списку значений.
Секционированная таблица и индекс по ключу разбиения:
CREATE TABLE seasons (month_num int) PARTITION BY LIST (month_num);
CREATE TABLE winter PARTITION OF seasons FOR VALUES IN (12,1,2);
CREATE TABLE spring PARTITION OF seasons FOR VALUES IN (3,4,5);
CREATE TABLE summer PARTITION OF seasons FOR VALUES IN (6,7,8);
CREATE TABLE fall PARTITION OF seasons FOR VALUES IN (9,10,11);
CREATE INDEX ON seasons (month_num);
Для целей демонстрации посоветуем использовать индекс для сортировки:
SET enable_bitmapscan=off;
SET enable_sort=off;
Запрос к данным второй и третьей секций может сразу выдавать отсортированные по индексу данные из каждой секции:
EXPLAIN (COSTS OFF)
SELECT * FROM seasons WHERE month_num IN (4,5,6,7) ORDER BY month_num;
QUERY PLAN
----------------------------------------------------------------------
Append
-> Index Only Scan using spring_month_num_idx on spring seasons_1
Index Cond: (month_num = ANY ('{4,5,6,7}'::integer[]))
-> Index Only Scan using summer_month_num_idx on summer seasons_2
Index Cond: (month_num = ANY ('{4,5,6,7}'::integer[]))
Значения в этих двух секциях не чередуются, а последовательно увеличиваются, что дает гарантию правильной сортировки.
Конечно, если такой гарантии нет, то для финальной сортировки придется использовать узел Merge Append:
EXPLAIN (COSTS OFF)
SELECT * FROM seasons WHERE month_num IN (11,12) ORDER BY month_num;
QUERY PLAN
----------------------------------------------------------------------
Merge Append
Sort Key: seasons.month_num
-> Index Only Scan using winter_month_num_idx on winter seasons_1
Index Cond: (month_num = ANY ('{11,12}'::integer[]))
-> Index Only Scan using fall_month_num_idx on fall seasons_2
Index Cond: (month_num = ANY ('{11,12}'::integer[]))
Безопасность
pg_dump и схема public
commit: a7a7be1f, 7ac10f69
Права по умолчанию на схему public весьма небезопасны. Работа над изменением ситуации давно ведется.
Как часть этой работы начиная с 14 версии появились изменения в поведении pg_dump.
При создании копии базы данных утилитой pg_dump до 14 версии для схемы public формируются следующие команды:
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
COMMENT ON SCHEMA public IS 'standard public schema';
А вот в 14 и 15 версиях ничего выгружено не будет. При условии, что схему не меняли, и она по-прежнему принадлежит суперпользователю. Считается, что схема была создана автоматически при создании базы данных и повторно её пересоздавать не нужно. (Разумеется объекты схемы выгружаются, речь только о самой схеме.)
Но что, если по каким-то причинам назначен новый владелец схемы public?
В 13 версии:
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO alice;
COMMENT ON SCHEMA public IS 'standard public schema';
REVOKE ALL ON SCHEMA public FROM postgres;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO alice;
GRANT ALL ON SCHEMA public TO PUBLIC;
В 14 версии:
REVOKE ALL ON SCHEMA public FROM postgres;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO alice;
GRANT ALL ON SCHEMA public TO PUBLIC;
И наконец в 15 версии:
ALTER SCHEMA public OWNER TO alice;
В 15 версии также будут выгружены метки безопасности и комментарий к схеме. При условии, что метки были установлены, а комментарий менялся.
Репликация
Логическая репликация: поддержка подготовленных транзакций
commit: a8fd13ca
Логическая репликация теперь поддерживает двухфазные транзакции.
Подробное описание в блоге Fujitsu.
Запуск процессов checkpointer и bgwriter во время восстановления после сбоя
commit: 7ff23c6d
Занимаясь своими делами во время восстановления эти процессы могут помочь процессу startup. К тому же схема работы будет такой же как и на реплике, где процессы startup, checkpointer и bgwriter трудятся вместе.
Сервер
Тип numeric: масштаб может превышать точность или быть отрицательным
commit: 085f931f
Тип NUMERIC (точность, масштаб) по спецификации SQL должен иметь точность в диапазоне 1… 1000, а масштаб не должен выходить за пределы точности.
Разработчики решили отойти от стандарта и расширили границы для масштаба от -1000 до 1000, без привязки к точности:
SELECT 12345.67::numeric(10,-3), 0.0123::numeric(1,2);
numeric | numeric
---------+---------
12000 | 0.01
Функция unnest для мультидиапазонов
commit: 9e3c217b
Мультидиапазонные типы данных появились в 14 версии. Теперь в список функций поддерживающих этот тип данных добавилась unnest. С её помощью можно развернуть в строки диапазоны, входящие в состав мультидиапазона.
Если вернуться к примеру по приведенной ссылке, то получить список диапазонов времени для коммитфестов 14 версии можно следующим запросом:
SELECT unnest(working_period) from pg_commitfest WHERE version = '14';
unnest
-----------------------------------------------------
["2020-07-01 00:00:00+03","2020-08-01 00:00:00+03")
["2020-09-01 00:00:00+03","2020-10-01 00:00:00+03")
["2020-11-01 00:00:00+03","2020-12-01 00:00:00+03")
["2021-01-01 00:00:00+03","2021-02-01 00:00:00+03")
["2021-03-01 00:00:00+03","2021-04-01 00:00:00+03")
Первая попытка реализовать функцию unnest для мультидиапазонов была отменена. До повторной попытки об этом успел написать Depesz и поделился своей функцией unnest_multirange на основе регулярных выражений.
Команды SQL
Секционирование: ALTER TRIGGER… RENAME TO …
commit: 80ba4bb3
Переименование триггера уровня строки на секционированной таблице автоматически переименует соответствующие триггеры на таблицах-секциях.
ALTER TABLE… SET ACCESS METHOD
commit: b0483263
Да, теперь можно поменять табличный метод доступа. Жаль только, что выбора по-прежнему нет:
SELECT * FROM pg_am WHERE amtype = 't';
oid | amname | amhandler | amtype
-----+--------+----------------------+--------
2 | heap | heap_tableam_handler | t
Но для тестирования новых методов доступа такая возможность пригодится.
Разное
Петабайты в pg_size_pretty и pg_size_bytes
commit: ca2e4472
14=# SELECT pg_size_pretty(100000000000000000::bigint);
pg_size_pretty
----------------
90949 TB
И сколько не увеличивай дальше, всё равно pg_size_pretty будет возвращать размер в терабайтах.
Но нашлись люди, для которых такая размерность маловата. Поэтому в 15 версии появились петабайты:
15=# SELECT pg_size_pretty(100000000000000000::bigint);
pg_size_pretty
----------------
89 PB
Поддержка петабайт появилась и в обратной функции pg_size_bytes.
Завершение ожидающих процессов, когда «пропал» postmaster
commit: 2aca19f2, 70685385
По сути это продолжение мартовской работы по замене pg_usleep () на WaitLatch (). К процессам, которые будут оперативно реагировать на «пропажу» postmaster добавляются те, что выполняют резервное копирование (в частности дожидаются файлов WAL для завершения создания копии) и процессы, ожидающие блокировки при усечении таблицы в конце очистки.
На этом всё. Ждем официального выхода 14 версии и не забываем, что в конце сентября закончится второй коммитфест 15 версии, а значит будет о чем рассказать.
