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 версии, а значит будет о чем рассказать.