Весна уже в разгаре, а мы вспомним горячие новости самого зимнего, январского коммитфеста. И сразу начнем с козырей.

Предыдущие статьи о 17-й версии: 2023-07, 2023-09, 2023-11.

Инкрементальное резервное копирование
Логическая репликация: сохранение состояния подписки при обновлении сервера-подписчика
Регистр динамической общей памяти
EXPLAIN (memory): объем памяти для планирования запроса
pg_stat_checkpointer: мониторинг точек рестарта на репликах
Параллельное создание индексов BRIN
Запросы с условием IS [NOT] NULL для обязательных столбцов
Оптимизация SET search_path
Оптимизация GROUP BY
Вспомогательные функции планировщика при работе с диапазонными типами
PL/pgSQL: массивы для типов %TYPE и %ROWTYPE
jsonpath: новые методы конвертации данных
COPY… FROM: игнорирование ошибок преобразования форматов
to_timestamp: форматные маски TZ и OF
GENERATED AS IDENTITY в секционированных таблицах
ALTER COLUMN… SET EXPRESSION

Инкрементальное резервное копирование
commit: 174c4805, dc212340

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

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

Вот как это работает.

Запускаем walsummarizer ― процесс сканирующий WAL для сбора информации об измененных страницах. Для этого нужно включить параметр summarize_wal. Удобно, что перезапуск сервера не требуется, достаточно перечитать конфигурацию. Также важно, что процесс можно включить не только на основном сервере, но и на реплике:

ALTER SYSTEM SET summarize_wal = on;
SELECT pg_reload_conf();

Новый процесс появляется в pg_stat_activity. Теперь информация об измененных страницах будет сохраняться в компактном виде в каталоге pg_wal/summaries.

Сначала создаем полную резервную копию.

$ pg_basebackup -c fast -D backups/full

Изменим что-нибудь, например, сделаем копию таблицы.

CREATE TABLE tickets_copy AS SELECT * FROM tickets;

Теперь создаем инкрементальную копию относительно предыдущей полной копии. Для этого в параметре -i указываем файл манифеста полной копии.

$ pg_basebackup -c fast -D backups/increment1 \
    -i backups/full/backup_manifest

Еще немного изменений.

DELETE FROM tickets_copy;

А теперь создадим вторую инкрементальную копию, которая будет базироваться на предыдущей инкрементальной копии, а не на полной. Для этого в параметре -i указываем файл манифеста инкрементальной копии.

$ pg_basebackup -c fast -D backups/increment2 \
    -i backups/increment1/backup_manifest

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

$ pg_combinebackup backups/full backups/increment1 backups/increment2 \
    -o backups/full_combined

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

$ du -h -s backups/*

2,7G    backups/full
2,7G    backups/full_combined
411M    backups/increment1
25M     backups/increment2

Из новой полной копии запускаем сервер на свободном порту:

$ pg_ctl start -D backups/full_combined \
    -o '-p 5400' \
    -l backups/full_combined/logfile

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

$ psql -p 5400 -c 'SELECT count(*) FROM tickets_copy'

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

Подведем итоги. Для создания и работы с инкрементальными резервными копиями в архитектуре сервера предусмотрены следующие компоненты:

• Сбор информации об измененных страницах выполняет процесс walsummarizer, управляемый параметром summarize_wal.
• Для диагностики и мониторинга работы процесса walsummarizer, а также содержимого каталога pg_wal/summaries предназначены утилита pg_walsummary и функции SQL pg_available_wal_summaries, pg_wal_summary_contents, pg_get_wal_summarizer_state.
• В протокол репликации добавлена команда UPLOAD_MANIFEST, а у команды BASE_BACKUP появился параметр INCREMENTAL.
• Для создания инкрементальной копии утилита pg_basebackup получила параметр -i (--incremental). С этим параметром pg_basebackup создает инкрементальную копию, используя новые возможности протокола репликации.
• Утилита pg_combinebackup реконструирует полную резервную копию из инкрементальной копии и всех копий, от которых она зависит.

См. также
Incremental Backup: What To Copy? (Robert Haas)
Incremental Backups: Evergreen and Other Use Cases (Robert Haas)
Waiting for Postgres 17: Incremental base backups (Lukas Fittl)
Waiting for PostgreSQL 17 – Add support for incremental backup. (Hubert 'depesz' Lubaczewski)

Логическая репликация: сохранение состояния подписки при обновлении сервера-подписчика
commit: 9a17be1e

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

Регистр динамической общей памяти
commit: 8b2bcf3f, abb0b4fc

Для использования общей памяти модули и библиотеки обычно требуют загрузить их через shared_preload_libraries, а для этого необходимо перезапустить сервер. Процессы могут запрашивать выделение динамической общей памяти (через DSM API), но, чтобы другие процессы могли ей воспользоваться, они должны знать, где ее искать. Именно эту задачу решает патч: ведет реестр выделенной динамической общей памяти (первый коммит).

Первым расширением, использующем новый интерфейс, стало pg_prewarm (второй коммит). Вызов функций autoprewarm_start_worker и autoprewarm_dump_now больше не отъедает общую память, если библиотека pg_prewarn не была загружена при старте сервера.

EXPLAIN (memory): объем памяти для планирования запроса
commit: 5de890e3

Память нужна не только для выполнения запроса, но и для его планирования. Сколько именно памяти было выделено и использовано для построения плана, можно увидеть с новым параметром memory команды EXPLAIN:

EXPLAIN (memory, costs off)
SELECT * FROM tickets;

                   QUERY PLAN                    
-------------------------------------------------
 Seq Scan on tickets
 Planning:
   Memory: used=7920 bytes  allocated=8192 bytes
(3 rows)

EXPLAIN (memory, costs off)
SELECT * FROM tickets a, tickets b;

                    QUERY PLAN                     
---------------------------------------------------
 Nested Loop
   ->  Seq Scan on tickets a
   ->  Materialize
         ->  Seq Scan on tickets b
 Planning:
   Memory: used=19392 bytes  allocated=65536 bytes
(6 rows)

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

pg_stat_checkpointer: мониторинг точек рестарта на репликах
commit: 12915a58

В новое для 17-й версии представление pg_stat_checkpointer добавлены столбцы для мониторинга точек рестарта: restartpoints_timed, restartpoints_req и restartpoints_done. Последний столбец поможет понять, сколько точек рестарта было реально выполнено. Дело в том, что точки рестарта на реплике не могут выполняться чаще, чем контрольные точки на основном сервере. Поэтому пока на реплику не пришла запись WAL о прохождении контрольной точки, запрошенные точки рестарта будут завершаться аварийно.

Параллельное создание индексов BRIN
commit: b4375717

Индексы BRIN могут создаваться параллельно несколькими процессами. Раньше это работало только для индексов на основе B-дерева.

Запросы с условием IS [NOT] NULL для обязательных столбцов
commit: b262ad44

В 16-й версии для каждой строки таблицы tickets выполняется проверка, что столбец ticket_no не пустой:

16=# EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no IS NOT NULL;

            QUERY PLAN             
-----------------------------------
 Seq Scan on tickets
   Filter: (ticket_no IS NOT NULL)
(2 rows)

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

16=# EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no IS NULL;

                QUERY PLAN                
------------------------------------------
 Index Scan using tickets_pkey on tickets
   Index Cond: (ticket_no IS NULL)
(2 rows)

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

Кроме того, бывают ситуации, когда для оптимизации агрегатов min/max планировщик может сам добавить условие IS NOT NULL на этапе переписывания запроса. Это, в свою очередь, может дополнительно привести к плохому выбору индекса. Подробности есть в переписке.

В 17-й версии для построения более оптимальных планов для условий IS [NOT] NULL проверяется наличие у столбца ограничения NOT NULL:

17=# EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_date IS NOT NULL;

      QUERY PLAN      
----------------------
 Seq Scan on bookings
(1 row)

17=# EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no IS NULL;

        QUERY PLAN        
--------------------------
 Result
   One-Time Filter: false
(2 rows)

Стоит обратить внимание, что необходимо ограничение именно NOT NULL. Ограничение CHECK (column_name IS NOT NULL) хотя и выполняет ту же проверку, но учитываться планировщиком не будет.

Оптимизация SET search_path
commit: ad57c2a7, a86c61c9

Установка параметра search_path в определении функции обеспечивает более безопасную работу с объектами базы данных. Но не бесплатно.

Выполним в psql простые замеры времени работы функции. Сначала без установки параметра:

16=# CREATE FUNCTION f() RETURNS int AS 'SELECT 0' LANGUAGE SQL;
16=# \timing on
16=# DO 'BEGIN PERFORM f() FROM generate_series(1, 10_000_000); END';

DO
Time: 1909,958 ms (00:01,910)

А теперь с установкой параметра:

16=# ALTER FUNCTION f SET search_path = a,b,c;
16=# DO 'BEGIN PERFORM f() FROM generate_series(1, 10_000_000); END';

DO
Time: 12594,300 ms (00:12,594)

В 17-й версии для установки search_path сделано несколько оптимизаций: используется хеш-таблица предыдущих значений, выполняется проверка на совпадение с предыдущим значением. Это позволило ускорить предыдущий пример:

17=# DO 'BEGIN PERFORM f() FROM generate_series(1, 10_000_000); END';

DO
Time: 9501,717 ms (00:09,502)

Оптимизация GROUP BY
commit: 0452b461

Эту оптимизацию включили еще в 15-ю версию, ее описание можно найти в обзоре мартовского коммитфеста 2022 года. Но перед самым выходом PostgreSQL 15 всё-таки откатили. Сейчас вторая попытка.

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

См. также
GROUP BY reordering (Adrien Nayrat)

Вспомогательные функции планировщика при работе с диапазонными типами
commit: 075df6b2

Предположим, что нам нужно узнать, какие рейсы выполнялись в первые семь дней августа 2018 года:

16=# EXPLAIN (costs off)
SELECT *
FROM flights
WHERE actual_departure <@ tstzrange('2017-08-01', '2017-08-08', '[)');

                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on flights
   Filter: (actual_departure <@ '["2017-08-01 00:00:00+03","2017-08-08 00:00:00+03")'::tstzrange)
(2 rows)

Заметим, что по столбцу actual_departure есть индекс, но он не может быть использован с оператором <@ .

В 17-й версии для операторов работы с диапазонами @> и <@ добавили вспомогательные функции планировщика, которые переписывают условие на сравнение значения с обеими границами диапазона.

План этого же запроса в 17-й версии:

17=# EXPLAIN (costs off)
SELECT *
FROM flights
WHERE actual_departure <@ tstzrange('2017-08-01', '2017-08-08', '[)');

                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using flights_actual_departure_idx on flights
   Index Cond: ((actual_departure >= '2017-08-01 00:00:00+03'::timestamp with time zone) AND (actual_departure < '2017-08-08 00:00:00+03'::timestamp with time zone))
(2 rows)

Переписанное в таком виде условие отлично использует индекс.

PL/pgSQL: массивы для типов %TYPE и %ROWTYPE
commit: 5e8674dc

В PL/pgSQL теперь можно объявлять массивы с конструкциями наследования типов %TYPE и %ROWTYPE.

DO $$
DECLARE
    seats_list seats.seat_no%TYPE[] :=
        (SELECT array_agg(seat_no)
         FROM seats
         WHERE aircraft_code = '733' AND
         fare_conditions = 'Business');
BEGIN
    RAISE NOTICE '%', seats_list;
END;
$$ LANGUAGE plpgsql;

NOTICE:  {1A,1C,1D,1F,2A,2C,2D,2F,3A,3C,3D,3F}
DO

Аналогично можно создать массив составного типа table_name%ROWTYPE[].

См. также
Waiting for PostgreSQL 17 – In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration. (Hubert 'depesz' Lubaczewski)

jsonpath: новые методы конвертации данных
commit: 66ea94e8

В язык jsonpath, в соответствии со стандартом SQL, добавлена поддержка новых методов конвертации данных: .bigint(), .boolean(), .date(), .decimal(), .integer(), .number(), .string(), .time(), .time_tz(), .timestamp(), .timestamp_tz().

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

\set json '{"key1": 42, "key2": "t", "key3": "Hello, World!"}'

SELECT jsonb_path_query(:'json', '$.key1.integer()'),
       jsonb_path_query(:'json', '$.key2.boolean()'),
       jsonb_path_query(:'json', '$.key3.string()');

 jsonb_path_query | jsonb_path_query | jsonb_path_query
------------------+------------------+------------------
 42               | true             | "Hello, World!"

COPY… FROM: игнорирование ошибок преобразования форматов
commit: 9e2d8701, b725b7ee, 72943960

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

В этом направлении сделан первый шаг. Команда COPY… FROM научилась игнорировать ошибки, связанные с неправильным форматом значений отдельных столбцов. Основой послужили функции «мягкой» обработки форматов значений, появившиеся в 16-й версии.

Таблица для примера:

CREATE TABLE t (id int PRIMARY KEY);

У команды COPY появился новый параметрon_error. Значение по умолчанию stop соответствует привычному поведению с остановкой на первой ошибке. Второе и пока последнее значение ignore говорит о том, что ошибки преобразования форматов будут игнорироваться:

COPY t FROM STDIN (on_error 'ignore');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> два
>> 3
>> \.

NOTICE:  1 row was skipped due to data type incompatibility
COPY 2

Из трех строк было загружено две. Одну строку загрузить не удалось, о чем говорит предупреждение. Во время долгой загрузки можно следить за количеством проигнорированных строк в столбце tuples_skipped представления pg_stat_progress_copy (третий коммит).

Как же узнать, какие именно входные строки не были загружены? Предполагается, что в будущем у параметра on_errorпоявятся значения file и table, в которых можно будет указать имя файла или таблицы соответственно.

Стоит обратить внимание, что игнорируются только ошибки преобразования форматов. Любые другие ошибки загрузки строк будут по-прежнему останавливать и отменять всю загрузку. Так, в следующем примере первая строка приводит к ошибке ограничения целостности первичного ключа, что не позволяет продолжить работу:

COPY t FROM STDIN (on_error 'ignore');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 3
>> 4
>> \.

ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(3) already exists.
CONTEXT:  COPY t, line 1

SELECT * FROM t;

 id
----
  1
  3
(2 rows)

См. также
Waiting for PostgreSQL 17 – Add new COPY option SAVE_ERROR_TO / Rename COPY option from SAVE_ERROR_TO to ON_ERROR (Hubert 'depesz' Lubaczewski)

to_timestamp: форматные маски TZ и OF
commit: 8ba6fdf9

Функция to_char уже давно понимает форматные маски TZ (сокращенное название часового пояса) и OF (смещение часового пояса от UTC).

Теперь добавлена поддержка этих форматных масок в функцию обратного преобразования to_timestamp:

SELECT to_timestamp('2024-02-18 23:50:00MSK', 'YYYY-MM-DD HH24:MI:SSTZ') tz,
       to_timestamp('2024-02-18 23:50:00+03', 'YYYY-MM-DD HH24:MI:SSOF') of;

           tz           |           of           
------------------------+------------------------
 2024-02-18 23:50:00+03 | 2024-02-18 23:50:00+03

GENERATED AS IDENTITY в секционированных таблицах
commit: 69958631

Столбцы идентификации, объявленные как GENERATED AS IDENTITY, полноценно поддерживаются в секционированных таблицах.

При создании новой секции или подключении секции из существующей таблицы (ATTACH PARTITION) столбец идентификации связывается с единой последовательностью, определенной для секционированной таблицы. Новые значения будут выбираться из последовательности как при вставке в отдельную секцию, так и в саму секционированную таблицу. При отключении секции (DETACH PARTITION) столбец теперь уже самостоятельной таблицы отключается от последовательности.

См. также
Waiting for PostgreSQL 17 – Support identity columns in partitioned tables (Hubert 'depesz' Lubaczewski)

ALTER COLUMN… SET EXPRESSION
commit: 5d06e99a

Появилась возможность изменить выражение у генерируемых столбцов таблицы. Раньше выражение можно было только удалить.

В качестве примера распределим строки таблицы по трем узлам в столбце node:

CREATE TABLE t (
    id int PRIMARY KEY,
    node int GENERATED ALWAYS AS (mod(id, 3)) STORED
);

WITH ins AS (
    INSERT INTO t SELECT x FROM generate_series(1,100) AS x
    RETURNING *
)
SELECT node, count(*) FROM ins GROUP BY 1 ORDER BY 1;

 node | count
------+-------
    0 |    33
    1 |    34
    2 |    33
(3 rows)

При изменении количества узлов можно заменить выражение одной командой:

ALTER TABLE t ALTER COLUMN node SET EXPRESSION AS (mod(id, 4));

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

SELECT node, count(*) FROM t GROUP BY 1 ORDER BY 1;

 node | count
------+-------
    0 |    25
    1 |    25
    2 |    25
    3 |    25
(4 rows)

См. также
Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression (Hubert 'depesz' Lubaczewski)

На этом пока всё. Впереди новости последнего мартовского коммитфеста 17-й версии.