Вместе с окончанием мартовского коммитфеста, закончился прием изменений в 16-ю версию. Пришла пора посмотреть, что в нем было нового и интересного.

Надеюсь, что представленный материал вместе с предыдущими статьями серии (2022-07, 2022-09, 2022-11, 2023-01) поможет сформировать представление о новинках PostgreSQL 16.

В мартовском коммитфесте традиционно много разработок. Для удобства просмотра они сгруппированы по разделам:

Новое представление pg_stat_io показывает статистику ввода/вывода.

Сразу оговоримся, что речь о дисковом вводе/выводе с точки зрения PostgreSQL. Кеширование на более низком уровне (ОС, контроллер диска) в нем не учитывается. Кроме того, в настоящий момент учитываются только операции с буферным кешем. Перенос таблиц/индексов в другое табличное пространство, запись WAL ― могут быть добавлены позже.

Каждая строка представления идентифицируется тремя столбцами:

backend_type ― тип процесса, знакомый по pg_stat_activity.backend_type;
object ― тип объектов: relation (постоянные таблицы, индексы, …) или temp relation;
context ― тип операции над объектом: normal, vacuum, bulkread, bulkwrite.

Названия операций интуитивно понятные, за точным описанием стоит заглянуть в документацию на pg_stat_io.

Посмотрим на одну из строк:

SELECT *
FROM pg_stat_io
WHERE backend_type = 'client backend' AND
      object = 'relation' AND
      context = 'bulkread'
\gx

-[ RECORD 1 ]+------------------------------
backend_type | client backend
object       | relation
context      | bulkread
reads        | 427497
read_time    | 752.489
writes       | 77220
write_time   | 215.93
extends      |
extend_time  |
op_bytes     | 8192
hits         | 38683
evictions    | 94210
reuses       | 330437
fsyncs       |
fsync_time   |
stats_reset  | 2023-04-29 09:13:58.798952+03

В этой строке показана накопительная статистика всех клиентских процессов (client backend) выполнявших массовое чтение (bulkread) из постоянных отношений (relation). К таким операциям относится последовательное сканирование больших таблиц с использованием буферного кольца.

Видна статистика по количеству попаданий (hits) в буферный кеш, вытеснений (evictions) из кеша, повторных использований в кольцевом буфере (reuses). Также видно, что клиентским процессам приходилось большую часть вытесняемых буферов записывать на диск (writes).

Столбцы reads, writes, extends (расширение файлов отношений) показывают количество выполненных операций. Для перевода в единицы памяти, значения нужно умножить на op_bytes. Поскольку сейчас учитываются только операции с буферным кешем, то op_bytes всегда равен размеру страницы ― 8 КБ. Для сбора статистики времени выполнения (столбцы *_time) должен быть включен параметр track_io_timing.

В качестве еще одного примера, посмотрим какие процессы записывали буферы из кеша на диск и оценим вклад каждого из них:

SELECT backend_type, SUM(writes) blocks,
       pg_size_pretty(SUM(writes*op_bytes)) size,
       round(SUM(write_time)) "time, ms"
FROM pg_stat_io
WHERE writes > 0
GROUP BY ROLLUP (backend_type)
ORDER BY blocks;

   backend_type    | blocks |  size   | time, ms
-------------------+--------+---------+----------
 background writer |  17198 | 134 MB  |      187
 checkpointer      |  30436 | 238 MB  |      139
 background worker |  76929 | 601 MB  |      213
 autovacuum worker |  88870 | 694 MB  |      528
 client backend    | 369031 | 2883 MB |     1055
                   | 582464 | 4551 MB |     2122
(6 rows)

Два главных процесса записи на диск background writer и checkpointer сделали меньше всех. Явный признак, что конфигурация системы не идеальна: возможно стоит увеличить буферный кеш и/или настроить background writer на более агрессивную работу.

Похожую информацию можно получить из представления pg_stat_bgwriter. Столбцы buffers_clean и buffers_checkpoint покажут сколько буферов на диск записали процессы background writer и checkpointer. Однако столбец buffers_backend крайне не информативен. В нем собираются результаты не только клиентских процессов, но и прочих, в частности процессов автоочистки. Кроме того, операции расширения файлов отношений не учитываются отдельно (как в pg_stat_io), а также приплюсовываются к buffers_backend, хотя это не запись из буферного кеша на диск.

См. также
Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics (Hubert 'depesz' Lubaczewski)
Waiting for Postgres 16: Cumulative I/O statistics with pg_stat_io (Lukas Fittl)

Счетчик новых версий строк, перенесенных на другую страницу при выполнении UPDATE
commit: ae4fdde1

В семействе статистических представлений pg_stat_*_tables появился новый столбец — n_tup_newpage_upd. Вместе с существующими столбцами n_tup_hot_upd и n_tup_upd он позволяет оценить эффективность оптимизации HOT.

Оптимизация HOT применяется при обновлении не индексированных столбцов таблицы. На странице создается новая версии строки, но в индексах таблицы новые записи не создаются. В этом суть оптимизации. Но чтобы она сработала, нужно чтобы на странице было достаточно места для размещения новой версии строки. Если места не хватает, то версия строки создается на другой странице и во всех индексах также создаются новые записи. Именно такие изменения строк учитываются в новом счетчике n_tup_newpage_upd.

Если значение счетчика достаточно велико, то это повод задуматься об уменьшении значения fillfactor для таблицы. Резервируя место на странице, мы увеличиваем шансы на срабатывание оптимизации HOT.

В отдельной базе данных подготовим pgbench к работе:

=# CREATE DATABASE test_fillfactor;
=# \c test_fillfactor

$ pgbench -i test_fillfactor

Запускаем тест на 10 секунд и смотрим статистику изменений строк таблицы pgbench_accounts:

$ pgbench -T 10 test_fillfactor

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pgbench_accounts'\gx

-[ RECORD 1 ]-----+-----------------
relname           | pgbench_accounts
n_tup_upd         | 11307
n_tup_hot_upd     | 9644
n_tup_newpage_upd | 1663

Сумма n_tup_hot_upd и n_tup_newpage_upd равняется n_tup_upd. Это говорит о том, что в тесте все команды UPDATE не обновляют индексированные столбцы и могут использовать оптимизацию HOT. Но примерно в 15% случаев оптимизация HOT не сработала из-за того, что на странице не хватило места для новой версии строки.

Уменьшим fillfactor и повторим тест, предварительно сбросив накопленную статистику:

ALTER TABLE pgbench_accounts SET (fillfactor = 80);
SELECT pg_stat_reset_single_table_counters('pgbench_accounts'::regclass);

$ pgbench -T 10 test_fillfactor

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pgbench_accounts'\gx

-[ RECORD 1 ]-----+-----------------
relname           | pgbench_accounts
n_tup_upd         | 11707
n_tup_hot_upd     | 11704
n_tup_newpage_upd | 3

Ситуация радикально изменилась. Только трижды не хватило места на странице для новой версии строки.

Любопытно, что кандидатом на изменение fillfactor является системная таблица pg_statistic:

VACUUM ANALYZE;

SELECT relname, n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'pg_statistic'\gx

-[ RECORD 1 ]-----+-------------
relname           | pg_statistic
n_tup_upd         | 458
n_tup_hot_upd     | 182
n_tup_newpage_upd | 276

pg_buffercache: новая функция pg_buffercache_usage_counts
commit: f3fa3132

В 16-й версии расширение pg_buffercache пополнилось функцией pg_buffercache_summary, она упоминалась в статье о ноябрьском коммитфесте.

Добавлена еще одна функция, показывающая сводную информацию о буферном кеше в разбивке по счетчику использования:

SELECT * FROM pg_buffercache_usage_counts();

 usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
           0 |   15791 |     0 |      0
           1 |     105 |     1 |      0
           2 |      89 |     4 |      0
           3 |      22 |     1 |      0
           4 |      32 |     3 |      0
           5 |     345 |    27 |      0
(6 rows)

Основное преимущество pg_buffercache_summary и pg_buffercache_usage_counts перед представлением pg_buffercache в скорости работы. Функции не требуют блокировки буферов, поэтому выполняются значительно быстрее.

Нормализация DDL и служебных команд, продолжение
commit: daa8365a

Это продолжение работы по нормализации запросов в 16-й версии.

На этот раз изменения коснулись команд: DECLARE, EXPLAIN,CREATE MATERIALIZED VIEW и CREATE TABLE AS. Но что особенно важно, нормализация учитывает константы в тексте запросов и заменяет их на параметры.

Создадим три временные таблицы с константой во фразе AS SELECT:

SELECT format('create temp table tmp on commit drop as select %s', g.i)
FROM generate_series(1,3) as g(i)\gexec

SELECT 1
SELECT 1
SELECT 1

А теперь убедимся, что в pg_stat_statements все три команды считаются одной:

SELECT queryid, query, calls                                                                               
FROM pg_stat_statements
WHERE query ILIKE 'create temp table%';

       queryid       |                       query                       | calls
---------------------+---------------------------------------------------+-------
 2417319575362882285 | create temp table tmp on commit drop as select $1 |     3
(1 row)

См. также
Postgres 16 highlight — Normalization of utilities in pg_stat_statements (Michael Paquier)

EXPLAIN (generic_plan): общий план параметризированного запроса
commit: 3c05284d

Идея этой разработки следующая.

Долгие запросы записываются в журнал сервера для последующего анализа.

SHOW log_min_duration_statement;

 log_min_duration_statement
----------------------------
 100ms

Приложение использует расширенный протокол запросов и отправляет на сервер запросы с параметрами. Для их имитации воспользуемся новой в 16-й версии командой \bind в psql:

\bind ABCDEF
SELECT count(*) FROM tickets WHERE book_ref = $1;

 count
-------
     0
(1 row)

Результат запроса не важен. Важно, что он выполнялся больше 100 миллисекунд и попал в журнал сервера:

$ tail -2 logfile

LOG:  duration: 172.195 ms  execute <unnamed>: SELECT count(*) FROM tickets WHERE book_ref = $1;
DETAIL:  parameters: $1 = 'ABCDEF'

Теперь стоит задача разобраться, почему запрос работает долго. Для этого нужно увидеть план запроса. Но простая попытка добавить слово EXPLAIN к тексту запроса завершается ошибкой:

EXPLAIN SELECT count(*) FROM tickets WHERE book_ref = $1;

ERROR:  there is no parameter $1
LINE 1: EXPLAIN SELECT count(*) FROM tickets WHERE book_ref = $1;
                                                              ^

Проблема в том, что EXPLAIN не умеет строить планы для запросов с параметрами. Ведь в зависимости от значений параметров план может быть разный.

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

Но для анализа может помочь и общий план запроса (generic plan), не зависящий от значений параметров. Теперь его можно получить:

EXPLAIN (generic_plan)
SELECT count(*) FROM tickets WHERE book_ref = $1;

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Aggregate  (cost=65779.07..65779.08 rows=1 width=8)
   ->  Gather  (cost=1000.00..65779.07 rows=2 width=0)
         Workers Planned: 2
         ->  Parallel Seq Scan on tickets  (cost=0.00..64778.87 rows=1 width=0)
               Filter: (book_ref = $1)

Запланировано последовательное сканирование таблицы несколькими процессами. Значит, таблица большого размера. А выбрана будет всего одна запись. Скорее всего по столбцу book_ref нет индекса, который бы ускорил запрос.

См. также
EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16 (Laurenz Albe)

auto_explain: протоколирование идентификатора запроса
commit: 9d2d9728

Согласно документации, протоколирование запросов с включенным параметром auto_explain.log_verbose эквивалентно выводу EXPLAIN с параметром VERBOSE. Однако идентификатор запроса не выводился, что и было исправлено:

SET compute_query_id = on;

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_verbose = on;
SET auto_explain.log_level = 'NOTICE';

SELECT 1;

NOTICE:  duration: 0.009 ms  plan:
Query Text: SELECT 1;
Result  (cost=0.00..0.01 rows=1 width=4)
  Output: 1
Query Identifier: -7037075969593950510
 ?column?
----------
        1
(1 row)

Портировать в предыдущие версии не стали, т.к. это может повлиять на работу инструментов анализа журнала сервера.

PL/pgSQL: GET DIAGNOSTICS… PG_ROUTINE_OID
commit: d3d53f95

В коде функции можно получить ее идентификатор:

CREATE FUNCTION f (OUT func_oid oid)
AS 'BEGIN GET DIAGNOSTICS func_oid = PG_ROUTINE_OID; END;'
LANGUAGE plpgsql;

SELECT f() oid, f()::regprocedure signature;

  oid  | signature
-------+-----------
 16528 | f()

Клиентские приложения

psql: переменные SHELL_ERROR и SHELL_EXIT_CODE
commit: b0d8f2d9, 31ae2aa9

Две новые переменные подскажут, как завершилась последняя команда ОС:

16=# \! pwd

/home/pal/pg16

16=# \echo SHELL_ERROR: :SHELL_ERROR, SHELL_EXIT_CODE: :SHELL_EXIT_CODE

SHELL_ERROR: false, SHELL_EXIT_CODE: 0

Первый коммит устанавливает эти переменные для команды! и команд в обратных кавычках (``). Второй коммит для команд \g, \o, \w и \copy.

В ряде случаев переменные сознательно не устанавливаются. К ним относятся: вызов программы постраничника, вызов редактора (\e) и установка приглашения командой в обратных кавычках (\set PROMPT1 %`команда`).

psql: \watch и количество повторений
commit: 00beecfe

Команда \watch выполняет запрос до тех пор пока не будет прервана. Теперь количество повторений можно указать:

16=# SELECT 1 \watch interval=1 count=2

Fri 14 Apr 2023 02:47:28 PM MSK (every 1s)

 ?column?
----------
        1
(1 row)

Fri 14 Apr 2023 02:47:29 PM MSK (every 1s)

 ?column?
----------
        1
(1 row)

16=#

psql: \df+ не показывает исходный код функций
commit: 3dfae91f

Команда \df+ среди прочего выводит исходный код функций, который может занимать много места. Это затрудняет просмотр списка функций.

В 16-й версии \df+ больше не будет показывать исходный код всех функций, кроме внутренних. Код внутренней функции представляет собой название функции на языке C и всегда помещается в одну строку.

А для просмотра исходного кода функций в psql есть другие команды: \sf и \ef.

pg_dump: поддержка методов сжатия LZ4 и zstd
commit: 0da243fe, 84adc8e2

До 16-й версии pg_dump поддерживал один метод сжатия ― gzip. Первый коммит добавляет поддержку LZ4, второй ― zstd.

Сжатие резервных копий возможно для всех форматов, кроме tar.

См. также
LZ4 and ZSTD pg_dump compression in PostgreSQL 16 (Pavlo Golub)

pg_dump и секционированные таблицы
commit: a563c24c

Для выгрузки отдельных таблиц предназначен параметр --table. Но если требуется выгрузить секционированную таблицу со всеми секциями, то нужно либо явно перечислять все секции, либо использовать знак % для указания шаблона имени таблицы, под который подойдут все секции. И то, и другое не идеальное решение. С одной стороны количество секций может меняться, с другой ― секции не обязаны подчиняться какому-то шаблону именования, либо под шаблон могут попасть посторонние таблицы.

В 16-й версии в pg_dump благодаря новому параметру --table-and-children перечислять секции или подбирать шаблон не понадобится. Параметр действует не только для секционированных таблиц, но и для таблиц с наследованием.

Для аналогичных целей, но уже при исключении таблиц, добавлены параметры: --exclude-table-and-children и --exclude-table-data-and-children.

pg_verifybackup --progress
commit: d07c2948

Утилита pg_basebackup с параметром --progress показывает прогресс создания резервной копии.

Этот же параметр добавлен утилите проверки резервной копии pg_verifybackup. Во время проверки статус обновляется каждую секунду, а по завершении мы увидим примерно следующее:

$ pg_verifybackup --progress /home/pal/pg16/backup/

2723991/2723991 kB (100%) verified
backup successfully verified

libpq: балансировка подключений
commit: 7f5b1981

Возможность указать при подключении несколько экземпляров PostgreSQL была и раньше. Если есть несколько реплик, то в строке подключения их можно перечислить в параметрах host, hostaddr и port. Клиенты будут пытаться подключаться к репликам в заданном порядке.

psql "host=replica1,replica2,replica3"

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

Для равномерного распределения подключений между репликами можно на стороне приложения при формировании строки подключения перемешивать список реплик. А можно воспользоваться новым параметром подключения load_balance_hosts:

psql "host=replica1,replica2,replica3 load_balance_hosts=random"

Значение random говорит о том, что перед подключением список узлов будет перемешан.

Администрирование и сопровождение сервера

initdb: установка параметров конфигурации при инициализации кластера
commit: 3e51b278

Новый параметр утилиты initdb -с или --set позволяет переопределить значение любого параметра конфигурации.

Для экспериментов с логической репликацией создадим второй экземпляр сервера на свободном порту и с параметром wal_level равным logical:

$ initdb -k -U postgres "-c wal_level=logical" "-c port=5402" -D ~/pg16/data2
$ pg_ctl start -D ~/pg16/data2 -l logfile2
$ psql -p 5402 -c "SHOW wal_level"

 wal_level
-----------
 logical

Указанные при инициализации параметры записываются в конец postgresql.conf:

$ tail -2 ~/pg16/data2/postgresql.conf

 wal_level = logical
 port = 5402

Автоочистка: регулирование нагрузки на лету
commit: 7d71d3dd

Если автоочистка большой таблицы длится очень долго, то ее можно ускорить прямо на лету. Для этого нужно изменить параметры autovacuum_vacuum_cost_limit и/или autovacuum_vacuum_cost_delay и обновить конфигурацию.

В предыдущих версиях процессы автоочистки перечитывали конфигурационные файлы только после завершения обработки очередной таблицы. А теперь это делается перед каждой проверкой на паузу в работе (autovacuum_vacuum_cost_delay).

См. также
Updating the Cost Limit On The Fly (Robert Haas)

Управление размером общей памяти для очистки и анализа
commit: 1cbbee03, 4830f102

Чтобы не забить общую память, очистка таблицы выполняется в специальной области памяти ― кольцевом буфере. Размер кольцевого буфера невелик и составляет 256 КБ. Однако при очистке таблицы большого размера может пострадать производительность работы всего сервера. Для переиспользования страниц в небольшом кольцевом буфере приходится постоянно записывать на диск грязные страницы и WAL. Частая синхронизация WAL с диском может стать узким местом в системе ввода/вывода.

Теперь размером этой области памяти можно управлять через новый параметр vacuum_buffer_usage_limit. Значение по умолчанию прежнее ― 256 КБ, а допустимые значения находятся в диапазоне от 128 КБ до 16 ГБ (но используется не более ⅛ буферного кеша).

При ручном запуске команд VACUUM и ANALYZE размер можно переопределить в новом параметре команд BUFFER_USAGE_LIMIT. При значении 0 очистка сможет занять весь буферный кеш:

VACUUM(ANALYZE, BUFFER_USAGE_LIMIT 0);

SELECT buffers_used, buffers_unused FROM pg_buffercache_summary();

 buffers_used | buffers_unused
--------------+----------------
        16384 |              0

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

Очистка в агрессивном режиме для предотвращения зацикливания счетчика транзакций (второй коммит) должна выполняться как можно быстрее, поэтому автоматически отключает ограничение на размер общей памяти.

Для описания принципа работы «кольцевого буфера» в глоссарии появился термин «стратегия доступа к буферам» (Buffer Access Strategy).

VACUUM только для TOAST-таблицы
commit: 4211fbd8

Чтобы выполнить очистку только TOAST-таблицы, нужно иметь привилегию USAGE на схему pg_toast и предварительно определить имя таблицы:

SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'tickets'::regclass;

      reltoastrelid      
-------------------------
 pg_toast.pg_toast_16443

VACUUM pg_toast.pg_toast_16443;

С отключенным новым параметром PROCESS_MAIN вычислять имя не обязательно:

VACUUM (PROCESS_MAIN false) tickets;

Утилита vacuumdb получила соответствующий параметр --no-process-main.

Удален параметр vacuum_defer_cleanup_age
commit: be504a3e, 1118cd37

Установка параметру vacuum_defer_cleanup_age значения, превышающего текущий номер транзакции, может привести к повреждению данных. Подробности в первом коммите.

Писать полноценные тесты и портировать их в предыдущие версии ― задача сложная и возможно не нужная. Ведь вероятно параметр _vacuum_defer_cleanupage применяется редко. Вместо него удобнее использовать появившиеся позже hot_standby_feedback и слоты репликации.

Поэтому второй коммит удаляет параметр vacuum_defer_cleanup_age. Пользоваться им всё равно неудобно: большое значение будет приводить к излишнему разрастанию таблиц, а маленькое не поможет предотвратить конфликты применения WAL на реплике.

Даже без перехода на 16-ю версию есть повод проверить vacuum_defer_cleanup_age в конфигурационных файлах и запланировать отказ от него.

pg_walinspect: трактовка параметра end_lsn
commit: 5c1b6628

Для функций pg_get_wal_records_info, pg_get_wal_stats, pg_get_wal_block_info изменена трактовка параметра end_lsn. Если значение параметра не указано, то функции выдают информацию от start_lsn и до конца журнала предзаписи.

Это позволило удалить дублирующие функции pg_get_wal_records_info_till_end_of_wal и pg_get_wal_stats_till_end_of_wal.

pg_walinspect: pg_get_wal_fpi_info → pg_get_wal_block_info
commit: 9ecb134a

В предыдущей статье серии упоминалась новая функция pg_get_wal_fpi_info расширения pg_walinspect. Функция существенно переработана и переименована в pg_get_wal_block_info.

Локализация

ICU: правило сортировки UNICODE
commit: 0d21d4b9

В соответствии со стандартом SQL в 16-й версии добавлено правило сортировки UNICODE для провайдера ICU.

\dO unicode

                                     List of collations
   Schema   |  Name   | Provider | Collate | Ctype | ICU Locale | ICU Rules | Deterministic?
------------+---------+----------+---------+-------+------------+-----------+----------------
 pg_catalog | unicode | icu      |         |       | und        |           | yes

Для этого правила используется алгоритм сортировки по умолчанию технического стандарта UNICODE.

ICU: канонизация локалей
commit: ea1db8ae, 1671f990

Названия локалей для провайдера ICU будут приводиться к каноническому виду в соответствии с правилами BCP 47. Заодно это предотвратит появление ошибочных правил сортировки:

CREATE COLLATION test_icu(provider=icu, locale='invalid_locale');

NOTICE:  using standard form "invalid-locale" for locale "invalid_locale"
ERROR:  ICU locale "invalid-locale" has unknown language "invalid"
HINT:  To disable ICU locale validation, set parameter icu_validation_level to DISABLED.

Для отключения проверки локалей можно использовать новый параметр icu_validation_level (второй коммит).

ICU: пользовательские правила для адаптации алгоритма сортировки
commit: 30a53b79

Новый параметр rules команды CREATE COLLATION позволяет настроить алгоритм сортировки под конкретные нужды.

Создадим правило сортировки, где времена года сортируются начиная с зимы:

CREATE COLLATION seasons (
    provider = icu, locale = 'ru', rules = '& З < В < Л < О'
);

WITH seasons(name) AS (
    VALUES ('Лето'), ('Зима'), ('Весна'), ('Осень')
)
SELECT * FROM seasons ORDER BY name COLLATE seasons;

 name  
-------
 Зима
 Весна
 Лето
 Осень
(4 rows)

Пользовательские правила можно также указывать в команде CREATE DATABASE и при вызове утилит createdb, initdb.

См. также:
ICU Documentation: Collation Customization
How collation works (Peter Eisentraut)
How collation of punctuation and whitespace works (Peter Eisentraut)

Безопасность

libpq: новый параметр require_auth
commit: 3a465cc6

Файл pg_hba.conf настроен следующим образом:

SELECT rule_number rule, type, database, user_name, auth_method
FROM pg_hba_file_rules
WHERE database != '{replication}';

 rule | type  | database | user_name |  auth_method  
------+-------+----------+-----------+---------------
    1 | local | {all}    | {all}     | trust
    2 | host  | {all}    | {all}     | scram-sha-256

Новый параметр libpq require_auth позволяет указать метод аутентификации, который должен применить сервер. Если перечислено несколько методов, тогда сервер может использовать любой из них:

$ psql 'host=localhost require_auth=md5,scram-sha-256' -c 'SELECT system_user'
Password for user postgres:

      system_user       
------------------------
 scram-sha-256:postgres
(1 row)

В этом примере для подключения по TCP/IP должно использоваться второе правило с методом scram-sha-256. Поскольку этот метод, вместе с md5, перечислен клиентом в параметре require_auth, то он и применяется. (О новой функции 16-й версии system_user можно прочитать в статье о сентябрьском коммитфесте.)

Если же метод аутентификации в require_auth не соответствует методу из pg_hba.conf, то подключиться не получится:

$ psql 'host=localhost require_auth=md5' -c 'SELECT system_user'

psql: error: connection to server at "localhost" (127.0.0.1), port 5416 failed: auth method "md5" requirement failed: server requested SASL authentication

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

psql 'host=localhost require_auth=!md5,!password' -c 'SELECT system_user'
Password for user postgres:

      system_user       
------------------------
 scram-sha-256:postgres
(1 row)

Специальное значение none используется, когда требуется подключение без аутентификации. В нашем случае это сработает для локального подключения из первого правила в pg_hba.conf:

$ psql 'require_auth=none' -c 'SELECT system_user'

 system_user
-------------

(1 row)

Вместо параметра require_auth можно использовать переменную окружения PGREQUIREAUTH.

См. также
Postgres 16 highlight — require_auth for libpq (Michael Paquier)

scram_iterations: счетчик итераций для шифрования паролей с использованием SCRAM-SHA-256
commit: b5777430

В предыдущих версиях значение счетчика зашито в коде и равняется 4096.

Теперь можно генерировать более устойчивые к атакам пароли, увеличив значение счетчика итераций в новом параметре scram_iterations. В RFC 7677 рекомендуется не менее 15000.

Надо помнить, что более высокое значение увеличивает не только защиту, но и время на аутентификацию.

См. также
Postgres 16 highlight — Control of SCRAM iterations (Michael Paquier)

Функции и команды SQL

Поддержка стандарта SQL/JSON
commit: 7081ac46, 6ee30209

Эта большая работа была принята еще год назад, но все-таки незадолго до выхода 15-й версии ее откатили.

Со второй попытки в 16-й версию попали функции-конструкторы (JSON_ARRAY, JSON_ARRAYAGG, JSON_OBJECT, JSON_OBJECTAGG) и предикаты (IS JSON [VALUE], IS JSON ARRAY, IS JSON OBJECT, IS JSON SCALAR) .

Работа над остальными частями будет продолжена в 17-й версии.

Новые функции pg_input_error_info и pg_input_is_valid
commit: b8da37b3, 1939d262

Функция pg_input_is_valid проверяет, является ли первый аргумент допустимым входным значением для типа данных из второго аргумента:

SELECT pg_input_is_valid('value', 'jsonb');

 pg_input_is_valid
-------------------
 f

Функция pg_input_error_info выдаст подробную информацию об ошибке:

SELECT * FROM pg_input_error_info('value', 'jsonb')\gx

-[ RECORD 1 ]--+-----------------------------------
message        | invalid input syntax for type json
detail         | Token "value" is invalid.
hint           |
sql_error_code | 22P02

Функции реализуют «мягкую» (soft) проверку входных значений, отсутствие которой в прошлом релизном цикле не позволило включить SQL/JSON в 15-ю версию.

См. также
Waiting for PostgreSQL 16 – Add test scaffolding for soft error reporting from input functions (Hubert 'depesz' Lubaczewski)

Фонетический алгоритм Daitch-Mokotoff Soundex
commit: a290378a

Поддержка фонетического алгоритма Daitch-Mokotoff Soundex добавлена в расширение fuzzystrmatch.

CREATE EXTENSION fuzzystrmatch;

SELECT daitch_mokotoff('Holubica'),
       daitch_mokotoff('Golubitsa'),
       daitch_mokotoff('Holubica') && daitch_mokotoff('Golubitsa');

 daitch_mokotoff | daitch_mokotoff | ?column?
-----------------+-----------------+----------
 {587500,587400} | {587400}        | t

См. также
Soundexing and Genealogy by Gary Mokotoff

Новые функции array_shuffle и array_sample
commit: 888f2ea0

Функция array_shuffle перемешивает элементы исходного массива, а array_sample возвращает массив с указанным количеством случайных элементов исходного массива:

WITH a(digits) AS (
    SELECT '{1,2,3,4,5,6,7,8,9,0}'::int[]
)
SELECT array_shuffle(a.digits),
       array_sample(a.digits, 3)
FROM a;

     array_shuffle     | array_sample
-----------------------+--------------
 {1,0,9,8,2,3,5,7,4,6} | {9,1,4}

Функции могут пригодиться для работы с методом Монте-Карло.

См. также
Waiting for PostgreSQL 16 – Add array_sample() and array_shuffle() functions (Hubert 'depesz' Lubaczewski)

Новая агрегатная функция any_value
commit: 2ddab010

Описанная в стандарте SQL агрегатная функция any_value возвращает произвольное непустое значение для группы строк.

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

CREATE TABLE t (id int);
INSERT INTO t VALUES(1),(1),(2),(3),(3) RETURNING *;

 id
----
  1
  1
  2
  3
  3
(5 rows)

Требуется удалить дубли одним запросом. (Подобного рода задачи встречаются на собеседованиях.)

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

DELETE FROM t 
WHERE ctid IN (SELECT any_value(ctid)
               FROM t 
               GROUP BY id HAVING count(*) = 2
);

DELETE 2

SELECT * FROM t;

 id
----
  1
  2
  3
(3 rows)

COPY: загрузка значений по умолчанию
commit: 9f8377f7

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

CREATE TABLE periods (
    id int PRIMARY KEY,
    date_from date NOT NULL DEFAULT '0001-01-01',
    date_to   date NOT NULL DEFAULT '3999-12-31'
);

Данные в таблицу загружаются командой COPY из файлов. Поставщики файлов вместо отсутствующих дат проставляют N/A. Повлиять на формирование файлов возможности нет.

\! cat data.txt

1    2023-04-01    2023-05-01
2    2023-04-08    N/A
3    N/A    2023-04-22

Загрузить такой файл без предварительных преобразований поможет новый параметр DEFAULT команды COPY. Когда указанное в нем значение встречается в данных, вычисляется значение по умолчанию для соответствующего столбца:

\COPY periods FROM 'data.txt' (DEFAULT 'N/A');

SELECT * FROM periods;

 id | date_from  |  date_to   
----+------------+------------
  1 | 2023-04-01 | 2023-05-01
  2 | 2023-04-08 | 3999-12-31
  3 | 0001-01-01 | 2023-04-22
(3 rows)

timestamptz: добавление и вычитание интервалов времени
commit: 75bd846b

Операторы + и — можно использовать для добавления и вычитания интервалов из значений типа время с часовым поясом (timestamptz). Результат таких операций зависит от установленного в параметре timezone часового пояса.

Чтобы арифметика не зависела от текущего часового пояса, можно воспользоваться новыми функциями date_add и date_subtract. Последний параметр этих функций позволяет явно указать часовой пояс для вычислений:

SET timezone = 'UTC';

SELECT '2021-10-31 00:00:00+02'::timestamptz + '1 day'::interval,
     date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw');

        ?column?        |        date_add        
------------------------+------------------------
 2021-10-31 22:00:00+00 | 2021-10-31 23:00:00+00

Параметр для часового пояса также появился в функции generate_series.

Независимость от параметров сеанса позволила объявить как immutable все три функции в варианте с часовым поясом.

XML: форматирование значений
commit: 483bdb2a

У функции XMLSERIALIZE появился параметр INDENT для форматирования вывода:

SELECT XMLSERIALIZE(
    DOCUMENT '<feature><name>Support [NO] INDENT option in XMLSERIALIZE()</name><author>Jim Jones</author><reviewers>Peter Smith and Tom Lane</reviewers><committer>Tom Lane</committer></feature>' AS TEXT
    INDENT);

                        xmlserialize                         
-------------------------------------------------------------
 <feature>                                                  +
   <name>Support [NO] INDENT option in XMLSERIALIZE()</name>+
   <author>Jim Jones</author>                               +
   <reviewers>Peter Smith and Tom Lane</reviewers>          +
   <committer>Tom Lane</committer>                          +
 </feature>                                                 +

pg_size_bytes: поддержка «B»
commit: ce1215d9

Для некоторых параметров значение можно задавать в байтах.

SHOW log_parameter_max_length;

 log_parameter_max_length
--------------------------
 512B

Требуется запросом получить число байт. Используем pg_size_bytes:

SELECT pg_size_bytes(current_setting('log_parameter_max_length'));

 pg_size_bytes
---------------
           512

В предыдущих версиях функция pg_size_bytes выдаст ошибку, потому что понимает только bytes, но не B.

Новые функции: erf, erfc
commit: d5d57414

В дополнение к random_normal в 16-й версии появилась функция ошибок erf и дополнительная функцией ошибок erfc. На правило 68-95-99.7 теперь можно посмотреть так:

SELECT erf(1 / sqrt(2)) AS erf_1,
       erf(2 / sqrt(2)) AS erf_2,
       erf(3 / sqrt(2)) AS erf_3;

       erf_1        |       erf_2        |       erf_3        
--------------------+--------------------+--------------------
 0.6826894921370859 | 0.9544997361036416 | 0.9973002039367398

Функция erfc возвращает значение близкое к 1 — erf().

Производительность

Параллельное выполнение полного и правого хеш-соединения
commit: 11c2d6fd

В предыдущих версиях следующий запрос всегда выполнялся последовательно. В 16-й версии доступно параллельное выполнение:

EXPLAIN (costs off)
SELECT count(*)
FROM bookings b FULL OUTER JOIN tickets t USING (book_ref);

                          QUERY PLAN                           
---------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Hash Full Join
                     Hash Cond: (t.book_ref = b.book_ref)
                     ->  Parallel Seq Scan on tickets t
                     ->  Parallel Hash
                           ->  Parallel Seq Scan on bookings b

Варианты реализации правого антисоединения
commit: 16dc2703

Посмотрим на план запроса, выводящего рейсы, на которые еще не выписаны билеты.

15=# EXPLAIN
SELECT * FROM flights f
WHERE NOT EXISTS (SELECT 1 FROM ticket_flights t WHERE t.flight_id = f.flight_id);

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Hash Anti Join  (cost=291530.67..338842.08 rows=133627 width=63)
   Hash Cond: (f.flight_id = t.flight_id)
   ->  Seq Scan on flights f  (cost=0.00..4772.67 rows=214867 width=63)
   ->  Hash  (cost=153851.52..153851.52 rows=8391852 width=4)
         ->  Seq Scan on ticket_flights t  (cost=0.00..153851.52 rows=8391852 width=4)

Для выполнения запроса в 15-й версии используется антисоединение (узел Hash Anti Join). Хеш-таблица строится по ticket_flights ― большей из двух таблиц запроса. На создание хеш-таблицы потребуются соответствующие расходы памяти и времени, что отражено в высокой начальной стоимости запроса по отношению к общей стоимости.

В 16-й версии планировщик рассматривает вариант антисоединения, в котором таблицы поменялись местами. И если такой план дешевле, то он и будет выбран:

16=# EXPLAIN         
SELECT * FROM flights f
WHERE NOT EXISTS (SELECT 1 FROM ticket_flights t WHERE t.flight_id = f.flight_id);

                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Hash Right Anti Join  (cost=9767.51..288502.03 rows=134347 width=63)
   Hash Cond: (t.flight_id = f.flight_id)
   ->  Seq Scan on ticket_flights t  (cost=0.00..153850.08 rows=8391708 width=4)
   ->  Hash  (cost=4772.67..4772.67 rows=214867 width=63)
         ->  Seq Scan on flights f  (cost=0.00..4772.67 rows=214867 width=63)

Модификация узла плана получила название Hash Right Anti Join.

Такая же модификация появилась для соединения слиянием.

Переработан механизм расширения отношений
commit: 31966b15, 00d1e02b

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

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

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

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

Индексы BRIN не блокируют HOT-обновления
commit: 19d8e230

HOT-обновление не применяется, если хотя бы один изменяемый столбец команды UPDATE проиндексирован любым типом индекса.

Однако индексы BRIN не содержат ссылок на табличные строки. И использовать HOT для обновления столбцов с такими индексами вполне безопасно. Поэтому индексы BRIN больше не препятствуют оптимизации HOT:

CREATE INDEX flights_bi ON flights USING brin(actual_departure);
SELECT pg_stat_reset_single_table_counters('flights'::regclass);

UPDATE flights SET actual_departure = actual_departure + '5 min'::interval
WHERE flight_id = 1;

SELECT n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE relname = 'flights';

 n_tup_hot_upd | n_tup_newpage_upd
---------------+-------------------
             1 |                 0

Один из счетчиков увеличивается.

Эту разработку приняли еще в 15-ю версию, но позже откатили. Сейчас вторая попытка.

postgres_fdw: прерывание транзакций на удаленных серверах в параллельном режиме
commit: 983ec230

В 15-й версии появилась возможность асинхронной фиксации транзакций с внешними таблицами, подключенными через postgres_fdw.

Теперь и прерывание транзакций выполняется асинхронно с включенным новым параметром стороннего сервера parallel_abort.

force_parallel_mode → debug_parallel_query
commit: 5352ca22

Параметр force_parallel_mode переименован в debug_parallel_query.

Причина в том, что многие пользователи ошибочно включают параметр для увеличения производительности выполнения запросов. Но запросы будут выполняться медленнее!

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

См. также
New old “debug_parallel_query” setting in PostgreSQL 16 (Pavlo Golub)

Прямой ввод/вывод (только для разработчиков)
commit: faeedbce

PostgreSQL не работает с файлами напрямую, а только через операционную систему. В рамках этой работы будет реализован прямой ввод/вывод данных и WAL. Работа продолжается, поэтому в 16-й версии придется жить по-старому.

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

Логическая репликация

Логическая репликация с физической реплики
commit: 0fdab27a

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

Для этого параметр wal_level должен иметь значение logical не только на основном сервере (primary), но и на физической реплике (replica):

replica=# SELECT pg_is_in_recovery(), current_setting('wal_level');

 pg_is_in_recovery | current_setting
-------------------+-----------------
 t                 | logical

Создаем подписку на основном сервере:

primary=# CREATE TABLE t (id int primary key);
primary=# INSERT INTO t VALUES (42);

primary=# CREATE PUBLICATION pub FOR TABLE t;

Убедимся, что на реплике появилась таблица и подписка:

replica=# SELECT * FROM t;

 id
----
 42
(1 row)

replica=# \dRp+

                              Publication pub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t"

Теперь на отдельном сервере (subscriber) подпишемся на публикацию, но в строке подключения укажем реплику, которая расположена на порту 5401:

subscriber=# CREATE TABLE t (id int primary key);
subscriber=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=5401' PUBLICATION pub;

Выполнение этой команды должно привести к созданию слота репликации на физической реплике. Но если основной сервер в этот момент бездействует, то создание слота затянется. Чтобы не ждать, можно выполнить на основном сервере следующую функцию:

primary=# SELECT pg_log_standby_snapshot();

Реплика «проснется» и слот будет создан только на ней:

replica=# SELECT slot_name, active FROM pg_replication_slots WHERE slot_name = 'sub';

 slot_name | active
-----------+--------
 sub       | t
(1 row)

primary=# SELECT slot_name, active FROM pg_replication_slots WHERE slot_name = 'sub';

 slot_name | active
-----------+--------
(0 rows)

Что касается логической репликации, то она уже работает:

subscriber=# SELECT * FROM t;

 id
----
 42
(1 row)

См. также
Postgres 16 highlight: Logical decoding on standby (Bertrand Drouvot)

Использование неуникальных индексов с REPLICA IDENTITY FULL
commit: 89e46da5

Если у таблицы в публикации нет первичного или уникального ключа, то в качестве репликационного идентификатора применяется REPLICA IDENTITY FULL. А на стороне подписчика для поиска измененных или удаленных строк выполняется последовательное сканирование таблицы.

В 16-й версии логическая репликация сможет использовать неуникальный индекс для идентификации измененных строк на подписчике. Управлять выбором индекса нельзя. Если у таблицы несколько индексов, то будет выбран первый попавшийся. Но это должен быть индекс типа B-дерево, не частичный и он должен включать хотя бы один столбец таблицы (не полностью функциональный).

Начальная синхронизация в двоичном формате
commit: ecb69652

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

CREATE SUBSCRIPTION .. WITH (binary);

Но до 16-й версии это влияло только на отправку изменений. А начальная синхронизация данных использовала текстовый формат передачи данных. Теперь и команда COPY, выполняющая начальную синхронизацию, будет работать в двоичном формате.

Права на создание подписки и применение изменений
commit: c3afe8cf, 1e10d49b, 48267598

Первый коммит создает предопределенную роль pg_create_subscription. Члены этой роли смогут создавать подписки (соответственно и слоты репликации на сервере публикации), не являясь суперпользователями.

В целях безопасности, владелец подписки будет переключаться на роль владельца таблицы при применении операций INSERT, UPDATE, DELETE и TRUNCATE (второй коммит). Поэтому у владельца подписки должны быть права на выполнение соответствующих команд SET ROLE.

Прежнего поведения, когда изменения применяются с правами владельца подписки, можно добиться, включив новый параметр подписки run_as_owner (третий коммит):

CREATE SUBSCRIPTION .. WITH (run_as_owner);

Параллельное применение изменений (только для разработчиков)
commit: 9f2213a7

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

Работа еще не завершена, поэтому пока без подробностей. Однако следы уже появились в 16-й версии. В секции для разработчиков появился параметр logical_replication_mode.

На этом всё. Ничего нового в PostgreSQL 16 уже не попадет. Ждем осеннего релиза!

PostgreSQL 16: Часть 5 или Коммитфест 2023-03