Продолжаем следить за новинками будущей 16-й версии. В начале декабря завершился третий коммитфест и вот его результаты.

Самое интересное из первых коммитфестов можно прочитать в предыдущих статьях серии: 2022-07, 2022-09.

В этот обзор попали следующие разработки:

meson: новая система сборки из исходных кодов
Документация: новая глава об обработке транзакций
psql: \d+ выделяет сторонние таблицы-секции секционированной таблицы
psql: поддержка расширенного протокола запросов
Предикатные блокировки для материализованных представлений
Отслеживание времени последнего сканирования индексов и таблиц
pg_buffercache: новая функция pg_buffercache_summary
walsender отображает имя базы данных в статусе процесса
Уменьшение размера WAL для записей о заморозке строк
Снижение энергопотребления при простое сервера
postgres_fdw: пакетный режим для COPY
Модернизация инфраструктуры работы с параметрами конфигурации
Оптимизация создания хеш-индексов
MAINTAIN ― новая привилегия для обслуживания таблиц
SET ROLE: управление переключением на другую роль
Директивы включения файлов в pg_hba.conf и pg_ident.conf
Поддержка регулярных выражений в pg_hba.conf

meson: новая система сборки из исходных кодов
commit: e6927270, 4c72102e

Собрать сервер из исходных кодов теперь можно с помощью новой сборочной системы ― meson. Первый коммит состоялся еще в сентябре. За ним последовали еще несколько десятков. В начале декабря появился коммит, добавляющий раздел в документацию с описанием нового процесса сборки. Есть еще страничка в wiki, где говорится в том числе о том, что стояло за таким решением. А одной из важных причин является упрощение сборки сервера под Windows и отказ в будущем от поддержки собственных инструментов, расположенных в каталоге src/tools/msvc.

Новая система сборки появилась в дополнение к autoconf и make. Речь об отмене старых проверенных инструментов не идет.

Но для целей этой статьи свежая 16-я версия сервера собиралась именно мезоном. Процесс сборки под Ubuntu был таким:

$ git clone git://git.postgresql.org/git/postgresql.git
$ cd postgresql
$ meson setup build --prefix=/home/pal/pg16 -Dpgport=5416
$ cd build
$ ninja
$ ninja install

Как видно, использованы параметры по умолчанию, кроме каталога установки и порта сервера.

Остается инициализировать кластер баз данных и запустить сервер. Эти шаги от сборочной системы уже не зависят.

Документация: новая глава об обработке транзакций
commit: 66bc9d2d

В документации появилась новая глава: 74.Transaction processing. Небольшая по размеру глава расположена в разделе «Внутреннее устройство». В ней дается краткий обзор виртуальных и реальных идентификаторов транзакций, их отображения в pg_locks, а также реализация вложенных транзакций и двухфазных транзакций.

psql: \d+ выделяет сторонние таблицы-секции секционированной таблицы
commit: bd95816f

Для секционированной таблицы команда \d+ отметит словом FOREIGN секции, являющиеся сторонними таблицами.

\d+ data

                                    Partitioned table "public.data"
  Column   | Type | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-----------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 data_year | text |           |          |         | extended |             |              |
Partition key: LIST (data_year)
Partitions: data_2020 FOR VALUES IN ('2020'), FOREIGN,
            data_2021 FOR VALUES IN ('2021'), FOREIGN,
            data_2022 FOR VALUES IN ('2022')

psql: поддержка расширенного протокола запросов
commit: 5b66de34

До сих пор psql напрямую не поддерживал протокол расширенных запросов. Поэтому параметризированные запросы можно было выполнять только косвенно, например через оператор PREPARE.

Теперь в psql добавлена команда \bind для привязки значений к параметрам следующего запроса. Вот как это работает:

SET log_statement='all';

\bind 42 'Answer: '
SELECT $2||$1;

  ?column?  
------------
 Answer: 42
(1 row)

\! tail -2 logfile

2022-12-05 12:58:05.924 MSK [16566] LOG:  execute <unnamed>: SELECT $2||$1;
2022-12-05 12:58:05.924 MSK [16566] DETAIL:  parameters: $1 = '42', $2 = 'Answer: '

Реализована лишь малая часть возможностей протокола расширенных запросов, тем не менее это очень полезное нововведение.

Предикатные блокировки для материализованных представлений
commit: 43351557

Предикатные блокировки используются для обеспечения согласованности изменений в транзакциях с уровнем изоляции SERIALIZABLE. Для обновления материализованных представлений командой REFRESH MATERIALIZED VIEW они не были реализованы. Ведь эта команда требует исключительной блокировки представления на время своей работы, что предотвращает любые аномалии конкурентного доступа.

Но если обновление выполняется с параметром CONCURRENTLY, то без предикатных блокировок транзакции с уровнем изоляции SERIALIZABLE могут столкнуться с аномалией несогласованной записи (write skew). Что и было исправлено.

Отслеживание времени последнего сканирования индексов и таблиц
commit: c0374718

В системе кумулятивной статистики, в дополнение к счетчикам количества обращений к таблицам и индексам, фиксируется информация о дате и времени последнего обращения. Для этого в pg_stat_all_tables добавлены столбцы last_seq_scan и last_idx_scan, а в pg_stat_all_indexes столбец last_idx_scan.

SELECT seq_scan, last_seq_scan, idx_scan, last_idx_scan
FROM   pg_stat_all_tables
WHERE  relid = 'tickets'::regclass;

 seq_scan |         last_seq_scan         | idx_scan |         last_idx_scan         
----------+-------------------------------+----------+-------------------------------
       23 | 2022-12-07 15:17:57.261575+03 |        2 | 2022-12-05 14:59:53.564968+03
(1 row)

SELECT indexrelname, idx_scan, last_idx_scan
FROM pg_stat_all_indexes
WHERE relid = 'tickets'::regclass;

     indexrelname     | idx_scan |         last_idx_scan         
----------------------+----------+-------------------------------
 tickets_pkey         |        2 | 2022-12-05 14:59:53.564968+03
 tickets_book_ref_idx |        0 |
(2 rows)

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

pg_buffercache: новая функция pg_buffercache_summary
commit: 2589434a

В расширение pg_buffercache добавлена функция для получения сводной информации о буферном кеше:

CREATE EXTENSION pg_buffercache;

SELECT * FROM pg_buffercache_summary()\gx

-[ RECORD 1 ]--+------------------
buffers_used   | 2544
buffers_unused | 13840
buffers_dirty  | 38
buffers_pinned | 0
usagecount_avg | 2.183176100628931

Эту информацию можно было получить и раньше агрегацией по представлению pg_buffercache. Но новая функция не требует блокировки буферов для своей работы, поэтому менее затратная, что удобно для использования в системах мониторинга.

walsender отображает имя базы данных в статусе процесса
commit: af205152

Подключение по протоколу репликации обслуживается процессом walsender. Для физической репликации или резервного копирования этот процесс подключается к экземпляру, но не к конкретной базе данных. Однако при создании подписки логической репликации, на сервере публикации создается логический слот репликации и walsender подключается к базе данных, где расположена публикация.

Для удобства мониторинга процессов walsender, обслуживающих логическую репликацию, статус процесса дополнен именем базы данных.

16(sub)=# CREATE SUBSCRIPTION sub CONNECTION 'port=5416 user=postgres dbname=demo' PUBLICATION pub;

NOTICE:  created replication slot "sub" on publisher
CREATE SUBSCRIPTION

16(pub)=# \! ps -o pid,command --ppid `head -n 1 /home/pal/pg16/data/postmaster.pid`

    PID COMMAND
  38514 postgres: checkpointer
  38515 postgres: background writer
  38517 postgres: walwriter
  38518 postgres: autovacuum launcher
  38519 postgres: logical replication launcher
  38522 postgres: postgres demo [local] idle
  38662 postgres: walsender postgres demo [local] START_REPLICATION

В этом примере видно, что имя базы данных demo отображается в статусе процесса walsender.

Уменьшение размера WAL для записей о заморозке строк
commit: 9e540599

Питер Гейган работает над проактивной заморозкой строк. Цель работы понятная ― для избежания проблем с зацикливанием счетчика транзакций, нужно начинать заморозку строк заранее. Однако более ранняя заморозка ведет к генерации большего объема WAL и увеличивает связанные с этим накладные расходы (репликация, архивация WAL).

В этом патче Питер предложил более компактно (~ в 5 раз) формировать записи WAL, касающиеся заморозки строк.

Снижение энергопотребления при простое сервера

commit: cd4329d9, 05a7be93

Когда основной сервер простаивает, процесс startup на физических репликах всё равно просыпается раз в 5 секунд, чтобы проверить, не появился ли файл, указанный в promote_trigger_file. Но для продвижения реплики есть и другие механизмы: pg_ctl promote и функция pg_promote. Для сокращения потребления ресурсов на такие просыпания параметр promote_trigger_file удалили.

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

postgres_fdw: пакетный режим для COPY

commit: 97da4824

Пакетный режим вставки записей в стороннюю таблицу появился еще в 14-й версии. Размер пакета задается параметром batch_size на уровне стороннего сервера или отдельной таблицы. Но реализован он был только для команды INSERT. Данный патч добавляет поддержку пакетного режима вставки для команды COPY FROM.

Проведем замер скорости вставки разными командами. Для этого в базе данных postgres создадим пустую таблицу bookings, в которую будем вставлять данные.

postgres=# CREATE TABLE public.bookings (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
);

В базе данных demo этого же кластера создадим стороннюю таблицу для только что созданной.

CREATE EXTENSION postgres_fdw;

CREATE SERVER srv
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (dbname 'postgres', batch_size '1');

CREATE USER MAPPING FOR postgres
    SERVER srv
    OPTIONS (user 'postgres');

CREATE FOREIGN TABLE bookings_remote (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
) SERVER srv 
    OPTIONS (schema_name 'public', table_name 'bookings');

Размер пакета задан на уровне сервера и равен 1, т.е. пакетный режим отключен, записи передаются по одной.

Выгрузим содержимое таблицы демо-базы bookings в файл:

\COPY bookings TO 'bookings.txt'

Теперь загружаем содержимое файла в стороннюю таблицу, с включенным замером времени:

\timing on
\COPY bookings_remote FROM 'bookings.txt'

COPY 2111110
Time: 57327,152 ms (00:57,327)

На вставку немногим более двух миллионов строк ушла почти минута. Установим размер пакета равным 100 и повторим замер:

TRUNCATE bookings_remote;
ALTER SERVER srv OPTIONS (SET batch_size '100');

\COPY bookings_remote FROM 'bookings.txt'

COPY 2111110
Time: 8780,000 ms (00:08,780)

Скорость вставки увеличилась более чем в 6 раз!

Любопытно что команда INSERT в пакетном режиме работает еще быстрее:

TRUNCATE bookings_remote;
INSERT INTO bookings_remote SELECT * FROM bookings;

INSERT 0 2111110
Time: 6669,504 ms (00:06,670)

Так что возможности оптимизации вставки еще не исчерпаны. Кроме того, было бы здорово научить postgres_fdw использовать пакетный режим для изменения и удаления строк, а не только для чтения и вставки.

Модернизация инфраструктуры работы с параметрами конфигурации
commit: f13b2088, 3057465a, 407b50f2, 9c911ec0

Эта серия коммитов направлена на оптимизацию хранения в памяти и ускорение доступа к конфигурационным параметрам. В том числе и к пользовательским параметрам.

Наиболее наглядно оптимизация видна на примере, который Том Лейн привел в первом письме обсуждения:

do $$
    begin
        for i in 1..10000 loop
        perform set_config('foo.bar' || i::text, i::text, false);
    end loop;
end $$;

На моем компьютере создание 10 тысяч пользовательских параметром в 15-й версии заняло ~ 6 секунд, а после применения патчей время сократилось до ~ 30 миллисекунд.

Оптимизация создания хеш-индексов
commit: e09d7a12, d09dbeb9

При создании хеш-индексов значения сортировались только по номеру корзины. Если дополнительно сортировать и по значению, то вставка последующих значений выполняется быстрее.

Первый коммит выполнен еще в июле, а второй ноябрьский. В целом создание хеш-индексов ускорилось на 5-15%.

MAINTAIN ― новая привилегия для обслуживания таблиц
commit: 60684dd8

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

В 16-й версии появляется привилегия MAINTAIN, которую можно выдавать на материализованные представления и таблицы, включая системные:

GRANT MAINTAIN ON pg_class, pg_attribute, pg_type TO alice;

В выводе команды \dp для этой привилегии используется символ m:

\dp pg_catalog.pg_class

                                     Access privileges
   Schema   |   Name   | Type  |     Access privileges      | Column privileges | Policies
------------+----------+-------+----------------------------+-------------------+----------
 pg_catalog | pg_class | table | postgres=arwdDxtm/postgres+|                   |
            |          |       | =r/postgres               +|                   |
            |          |       | alice=m/postgres           |                   |

Какие же возможности предоставляются этой привилегией? К ним относятся выполнение команд: ANALYZE, VACUUM (включая VACUUM FULL), CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW и LOCK TABLE.

Кроме того, добавлена предопределенная роль pg_maintain. Членство в этой роли предоставляет привилегию MAINTAIN для всех отношений в базе данных. Это может быть удобно для обслуживания системы обычной ролью, не имеющей прав выполнять DDL и DML операции в базе данных.

Первоначальный вариант патча предполагал добавление двух привилегий ― VACUUM и ANALYZE, но в конечном итоге остановились на привилегии MAINTAIN с более широкими возможностями по обслуживанию.

SET ROLE: управление переключением на другую роль
commit: 3d14e171

Данный патч продолжает тему структурирования работы с ролями и привилегиями. В предыдущей статье говорилось о предоставлении членства в ролях и о наследовании привилегий.

Теперь речь пойдет о возможности переключения на другую роль командой SET ROLE. Предположим, мы решили включить роль alice в pg_read_all_data с наследованием привилегий:

postgres=# GRANT pg_read_all_data TO alice WITH INHERIT TRUE;

Теперь роль alice может пользоваться привилегиями pg_read_all_data. Но ничего не помешает alice выполнить переключение на pg_read_all_data и … создавать от ее имени объекты:

postgres=# \c - alice

You are now connected to database "postgres" as user "alice".

alice=> CREATE TABLE t (id int);

CREATE TABLE

alice=> \dt t

            List of relations
 Schema | Name | Type  |      Owner       
--------+------+-------+------------------
 public | t    | table | pg_read_all_data

На самом деле кое-что помешает. Для создания объектов роль pg_read_all_data должна иметь привилегию CREATE в какой-либо схеме, например в public. А начиная с 15-й версии этой привилегии у псевдо-роли public больше нет. Поэтому для вышеприведенного примера предварительно была выполнена команда:

postgres=# GRANT CREATE ON SCHEMA public TO public;

Тем не менее, в определенных ситуациях переключение на другую роль может быть нежелательным. Если мы предоставили членство в роли с INHERIT TRUE, то зачем вообще переключаться? В новой версии это можно запретить той же командой GRANT:

postgres=# GRANT pg_read_all_data TO alice WITH INHERIT TRUE, SET FALSE;

Теперь alice может пользоваться привилегиями pg_read_all_data, но не сможет на неё переключиться.

alice=> SET ROLE pg_read_all_data;

ERROR:  permission denied to set role "pg_read_all_data"

Возможность переключения на роль хранится в столбце set_option таблицы pg_auth_members, рядом с inherit_option и admin_option.

SELECT roleid::regrole, member::regrole, grantor::regrole,
       admin_option, inherit_option, set_option
FROM pg_auth_members
WHERE member = 'alice'::regrole\gx

-[ RECORD 1 ]--+-----------------
roleid         | pg_read_all_data
member         | alice
grantor        | postgres
admin_option   | f
inherit_option | t
set_option     | f

Директивы включения файлов в pg_hba.conf и pg_ident.conf
commit: a54b658c

Конфигурационные файлы pg_hba.conf и pg_ident.conf, также как и postgresql.conf, стали поддерживать директивы include, include_if_exists, include_dir для включения других файлов.

А в представления pg_hba_file_rules и pg_ident_file_mappings добавлены столбцы с именем файла и номером правила/соответствия:

SELECT * FROM pg_hba_file_rules
WHERE file_name LIKE '%hba_ident_test.conf' \gx

-[ RECORD 1 ]---------------------------------
rule_number | 1
file_name   | /home/pal/pg/hba_ident_test.conf
line_number | 1
type        | local
database    | {all}
user_name   | {alice}
address     |
netmask     |
auth_method | peer
options     | {map=m1}
error       |

SELECT * FROM pg_ident_file_mappings
WHERE file_name LIKE '%ident_test.conf' \gx

-[ RECORD 1 ]-----------------------------
map_number  | 1
file_name   | /home/pal/pg/ident_test.conf
line_number | 1
map_name    | m1
sys_name    | student
pg_username | alice
error       |

Поддержка регулярных выражений в pg_hba.conf

commit: 8fea8683, a9039713, fc579e11

В файле pg_hba.conf для имени пользователя и имени базы данных можно указывать регулярные выражения. Если любое из этих полей начинается с косой черты (/), то значение считается регулярным выражением.

На этом пока всё. Ждем результатов следующего январского коммитфеста.

PostgreSQL 16: Часть 3 или Коммитфест 2022-11