pluzanov 27 июл 2022 в 19:23

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

38 мин

9.8K

Блог компании Postgres ProfessionalPostgreSQL*SQL*

Эта статья о мартовском коммитфесте завершает серию о принятых изменениях в PostgreSQL 15.

Предыдущие статьи посвящены первым четырем коммитфестам: 2021-07, 2021-09, 2021-11, 2022-01.

На момент публикации уже доступна вторая бета-версия PostgreSQL 15. Все приведенные ниже примеры легко попробовать самостоятельно.

Содержание

Команды SQL

MERGE
commit: 7103ebb7

Долгожданная команда SQL, с непростой историей реализации, наконец-то попала в 15-ю версию!

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

Сначала изменяемая таблица (её называют целевой) соединяется с источником (таблицей или подзапросом) по указанному условию соединения. Для каждой строки целевой таблицы, попавшей в результат соединения, можно указать что с ней делать.

Создадим две таблицы с одинаковой структурой и наполнением.

CREATE TABLE source_table (id int primary key, descr text);
CREATE TABLE target_table (LIKE source_table INCLUDING ALL);

INSERT INTO source_table VALUES
    (0, 'Ноль'),
    (1, 'Один'),
    (2, 'Два');
INSERT INTO target_table SELECT * FROM source_table;    
SELECT * FROM target_table ORDER BY id;

id | descr 
----+-------
  0 | Ноль
  1 | Один
  2 | Два
(3 rows)

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

Для примера, в целевой таблице изменим описание у всех строк.

UPDATE target_table SET descr = '';

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

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |  0 |
  1 | Один  |  1 |
  2 | Два   |  2 |
(3 rows)

Синхронизируем измененные строки с таблицей source_table.

В команде MERGE указываем целевую таблицу, источник и условие соединения. А также действие для строк в целевой таблице, которым нашлось соответствие (MATCHED) в источнике. В данном случае это изменение столбца descr. Дописывать к UPDATE фразы FROM и WHERE не нужно, т.к всегда обновляется текущая строка целевой таблицы.

MERGE INTO target_table t
USING source_table s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

MERGE 3

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |  0 | Ноль
  1 | Один  |  1 | Один
  2 | Два   |  2 | Два
(3 rows)

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

EXPLAIN (costs off)
MERGE INTO target_table t
USING source_table s
    ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

                  QUERY PLAN                  
----------------------------------------------
 Merge on target_table t
   ->  Hash Join
         Hash Cond: (t.id = s.id)
         ->  Seq Scan on target_table t
         ->  Hash
               ->  Seq Scan on source_table s

Способ соединения выбирает планировщик: соединение слиянием, вложенными циклами или, как в нашем случае, хеш-соединение.

Заметим, что такой же результат можно получить и без MERGE. Нужно воспользоваться фразой FROM команды UPDATE:

UPDATE target_table t
SET descr = s.descr
FROM source_table s
WHERE t.id = s.id;

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

DELETE FROM target_table WHERE id = 0;

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |    |
  1 | Один  |  1 | Один
  2 | Два   |  2 | Два
(3 rows)

К предыдущей команде MERGE нужно добавить еще одно действие с условием NOT MATCHED. А в качестве действия укажем команду INSERT, в которой опять же не нужно указывать таблицу:

MERGE INTO target_table t
USING source_table s
    ON t.id = s.id
WHEN NOT MATCHED THEN
    INSERT (id, descr) VALUES (s.id, s.descr)
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

MERGE 3

Условия WHEN обрабатываются сверху-вниз для каждой строки результата соединения. Первое подходящее ― выполняется, остальные игнорируются.

Убедимся, что данные синхронизированы:

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |  0 | Ноль
  1 | Один  |  1 | Один
  2 | Два   |  2 | Два
(3 rows)

Посмотрим на план предыдущей команды MERGE:

EXPLAIN (costs off)
MERGE INTO target_table t
USING source_table s
    ON t.id = s.id
WHEN NOT MATCHED THEN
    INSERT (id, descr) VALUES (s.id, s.descr)
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

                  QUERY PLAN                  
----------------------------------------------
 Merge on target_table t
   ->  Hash Left Join
         Hash Cond: (s.id = t.id)
         ->  Seq Scan on source_table s
         ->  Hash
               ->  Seq Scan on target_table t

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

Заметим, опять же, что синхронизации таблиц в этой ситуации можно было бы добиться другой командой:

INSERT INTO target_table
    SELECT * FROM source_table s
        ON CONFLICT (id) DO UPDATE SET descr = EXCLUDED.descr;

INSERT 0 3

Однако принцип действия у них разный. INSERT… ON CONFLICT пытается добавить строки, и если вставка не проходит из-за ограничения уникальности, то можно изменить существующую строку. А MERGE сначала соединяет источник с целевой таблицей и проставляет для каждой строки соединения признак [NOT] MATCHED, которым можно воспользоваться для выполнения обновления, удаления или вставки строк в целевой таблице.

Но что если нам нужно обработать строки, которые есть только в целевой таблице?

INSERT INTO target_table VALUES (3, 'Три');

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |  0 | Ноль
  1 | Один  |  1 | Один
  2 | Два   |  2 | Два
    |       |  3 | Три
(4 rows)

Предположим, что мы хотели бы удалить такие, «лишние» строки.

В MS SQL Server для таких случаев есть специальное условие MATCHED BY SOURCE. В PostgreSQL оно не поддерживается, но мы для целей примера воспользуемся возможностью использовать запрос в качестве источника. А запрос будет таким:

SELECT coalesce(s.id,t.id) full_id, s.*
FROM source_table s
     FULL OUTER JOIN target_table t ON t.id = s.id;

 full_id | id | descr
---------+----+-------
       0 |  0 | Ноль
       1 |  1 | Один
       2 |  2 | Два
       3 |    |
(4 rows)

В запросе выполняется полное внешнее соединение, вычисляемый столбец full_id ― комбинация всех возможных id из обеих таблиц. Если full_id заполнен, а s.id нет, то это как раз строки, отсутствующие в источнике и мы хотим их удалить.

Теперь можно записать команду MERGE так:

MERGE INTO target_table t
USING (
    SELECT coalesce(s.id,t.id) full_id, s.*
    FROM source_table s
              FULL OUTER JOIN target_table t ON t.id = s.id
) s
    ON t.id = s.full_id
WHEN NOT MATCHED THEN
    INSERT (id, descr) VALUES (s.id, s.descr)
WHEN MATCHED AND s.id IS NULL THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

MERGE 4

Обратите внимание, что в условие можно включать дополнительные логические выражения: WHEN MATCHED AND s.id IS NULL. А удаление строки свелось к одному слову DELETE.

Проверяем:

SELECT s.*, t.*
FROM source_table s
     FULL OUTER JOIN target_table t ON (t.id=s.id);

 id | descr | id | descr
----+-------+----+-------
  0 | Ноль  |  0 | Ноль
  1 | Один  |  1 | Один
  2 | Два   |  2 | Два
(3 rows)

Сколько строк и как именно было обработано командой MERGE?

Подробный ответ на этот вопрос покажет план выполнения запроса (надо отметить, что фраза RETURNING для MERGE не поддерживается).

EXPLAIN (analyze, costs off, timing off, summary off)
MERGE INTO target_table t
USING (
    SELECT coalesce(s.id,t.id) full_id, s.*
    FROM source_table s
              FULL OUTER JOIN target_table t ON t.id = s.id
) s
    ON t.id = s.full_id
WHEN NOT MATCHED THEN
    INSERT (id, descr) VALUES (s.id, s.descr)
WHEN MATCHED AND s.id IS NULL THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET descr = s.descr;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Merge on target_table t (actual rows=0 loops=1)
   Tuples Inserted: 0
   Tuples Updated: 3
   Tuples Deleted: 0
   Tuples Skipped: 0
   ->  Hash Left Join (actual rows=3 loops=1)
         Hash Cond: (COALESCE(s.id, t_1.id) = t.id)
         ->  Hash Full Join (actual rows=3 loops=1)
               Hash Cond: (s.id = t_1.id)
               ->  Seq Scan on source_table s (actual rows=3 loops=1)
               ->  Hash (actual rows=3 loops=1)
                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                     ->  Seq Scan on target_table t_1 (actual rows=3 loops=1)
         ->  Hash (actual rows=3 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 17kB
               ->  Seq Scan on target_table t (actual rows=3 loops=1)

Видим что обновляются все строки, но зачем? Ведь значения descr одинаковые в источнике и приемнике.

Уточним в последней ветке WHEN условие для обновления строк.

EXPLAIN (analyze, costs off, timing off, summary off)
MERGE INTO target_table t
USING (
    SELECT coalesce(s.id,t.id) full_id, s.*
    FROM source_table s
              FULL OUTER JOIN target_table t ON t.id = s.id
) s
    ON t.id = s.full_id
WHEN NOT MATCHED THEN
    INSERT (id, descr) VALUES (s.id, s.descr)
WHEN MATCHED AND s.id IS NULL THEN
    DELETE
WHEN MATCHED AND t.descr IS DISTINCT FROM s.descr THEN
    UPDATE SET descr = s.descr;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Merge on target_table t (actual rows=0 loops=1)
   Tuples Inserted: 0
   Tuples Updated: 0
   Tuples Deleted: 0
   Tuples Skipped: 3
   ->  Hash Left Join (actual rows=3 loops=1)
         Hash Cond: (COALESCE(s.id, t_1.id) = t.id)
         ->  Hash Full Join (actual rows=3 loops=1)
               Hash Cond: (s.id = t_1.id)
               ->  Seq Scan on source_table s (actual rows=3 loops=1)
               ->  Hash (actual rows=3 loops=1)
                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                     ->  Seq Scan on target_table t_1 (actual rows=3 loops=1)
         ->  Hash (actual rows=3 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 17kB
               ->  Seq Scan on target_table t (actual rows=3 loops=1)

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

Подводя итоги.

Реализация MERGE в PostgreSQL еще один важный шаг в сторону соответствия стандарту SQL.
Наличие MERGE может упростить переход на PostgreSQL с других СУБД, где команда поддерживается давно.
Функциональность MERGE шире, чем у существующей команды INSERT… ON CONFLICT. Эти две команды не всегда взаимозаменяемы.

За дополнительными подробностями реализации стоит обратиться к документации на команду MERGE.

Описание от depesz.

UPD: Следующий патч откатили: 2f2b18bd
Поддержка стандарта SQL/JSON
commit: a6baa4ba, f79b803d, f4fb45d1, 33a37760, 1a36bc9d, 606948b0, 49082c2c, 4e34747c, fadb48b0

Большое количество коммитов лишь частично передает огромный объем проделанной работы по включению в PostgreSQL поддержки стандарта SQL/JSON. Еще в 12-й версии появился специальный язык путей JSONPATH для доступа к отдельным частям JSON. А теперь появились стандартизированные функции для работы с JSON. Всё вместе позволяет комбинировать работу с JSON и реляционными данными в одной СУБД.

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

А теперь несколько примеров на демо-базе.

Для формирования значения JSON есть несколько функций-конструкторов. Они позволяют в том числе получить JSON из реляционных данных:

SELECT JSON_OBJECT('id': passenger_id, 'name': passenger_name RETURNING jsonb)
FROM tickets t
WHERE t.book_ref = '000010';

                     json_object                     
-----------------------------------------------------
 {"id": "5722 837257", "name": "ALEKSANDR SOKOLOV"}
 {"id": "0564 044306", "name": "LYUDMILA BOGDANOVA"}

Более сложный пример. Мы хотим бронирования и входящие в них билеты хранить как одно значение JSON в отдельной таблице. Билетов в одном бронировании может быть несколько, JSON_ARRAY добавит их в виде массива. Для простоты, ограничимся одним бронированием:

CREATE TABLE bookings_json AS
SELECT b.book_ref,
       JSON_OBJECT(
           'book_ref': b.book_ref,
           'book_date': b.book_date,
           'total_amount': b.total_amount,
           'tickets': JSON_ARRAY(
                   SELECT JSON_OBJECT(
                          'ticket_no': ticket_no,
                          'passenger': JSON_OBJECT('id':passenger_id, 'name': passenger_name)
                      )
               FROM tickets t
               WHERE t.book_ref = b.book_ref
           )
           RETURNING jsonb
       ) data
FROM bookings b
WHERE b.book_ref = '000010';

Вот что получилось:

SELECT book_ref, jsonb_pretty(data) FROM bookings_json;

 book_ref |                 jsonb_pretty                  
----------+-----------------------------------------------
 000010   | {                                            +
          |     "tickets": [                             +
          |         {                                    +
          |             "passenger": {                   +
          |                 "id": "5722 837257",         +
          |                 "name": "ALEKSANDR SOKOLOV"  +
          |             },                               +
          |             "ticket_no": "0005432295359"     +
          |         },                                   +
          |         {                                    +
          |             "passenger": {                   +
          |                 "id": "0564 044306",         +
          |                 "name": "LYUDMILA BOGDANOVA" +
          |             },                               +
          |             "ticket_no": "0005432295360"     +
          |         }                                    +
          |     ],                                       +
          |     "book_ref": "000010",                    +
          |     "book_date": "2017-01-08T19:45:00+03:00",+
          |     "total_amount": 50900.00                 +
          | }

Кроме конструкторов JSON_OBJECT, JSON_ARRAY есть и другие: JSON, JSON_ARRAYAGG, JSON_OBJECTAGG.

Описание функций-конструкторов от depesz.

С помощью различных предикатов можно проверить тип значения JSON в столбце data:

SELECT data IS JSON AS is_json,
       data IS JSON WITH UNIQUE KEYS AS is_unique_keys,
       data IS JSON ARRAY AS is_json_array,
       data IS JSON OBJECT AS is_json_object,
       data IS JSON SCALAR AS is_json_scalar
FROM bookings_json\gx

-[ RECORD 1 ]--+--
is_json        | t
is_unique_keys | t
is_json_array  | f
is_json_object | t
is_json_scalar | f

Описание предикатов от depesz.

Можно получить список билетов, входящих в бронирование:

SELECT book_ref,
       JSON_QUERY(data, '$.tickets[$i].ticket_no' PASSING g.x as i)
FROM bookings_json, generate_series(0,1) AS g(x);

 book_ref |   json_query    
----------+-----------------
 000010   | "0005432295359"
 000010   | "0005432295360"

Вторым параметром в функции JSON_QUERY указан путь на языке JSONPATH к массиву билетов. В этот путь можно передать параметр (PASSING) для доступа к отдельным элементам массива.

А вот так можно проверить наличия ключа в JSON и получить его значение:

SELECT JSON_EXISTS(data, '$.book_ref') book_ref_exists,
       JSON_VALUE(data, '$.book_ref') book_ref_value
FROM bookings_json;

 book_ref_exists | book_ref_value
-----------------+----------------
 t               | 000010

Эти функции можно использовать для создания ограничений целостности для таблицы. Например. Требуется, чтобы в JSON присутствовал ключ book_date, а значение ключа book_ref совпадало со значением столбца book_ref.

ALTER TABLE bookings_json
   ADD CONSTRAINT chk_book_ref CHECK (JSON_VALUE(data, '$.book_ref') = book_ref),
   ADD CONSTRAINT chk_book_date CHECK (JSON_EXISTS(data, '$.book_date'));

INSERT INTO bookings_json
    VALUES ('_12345', JSON_OBJECT('book_ref': '123456'));

ERROR:  new row for relation "bookings_json" violates check constraint "chk_book_date"
DETAIL:  Failing row contains (_12345, {"book_ref": "123456"}).

INSERT INTO bookings_json
    VALUES ('_12345', JSON_OBJECT('book_ref': '123456', 'book_date': now()));

ERROR:  new row for relation "bookings_json" violates check constraint "chk_book_ref"
DETAIL:  Failing row contains (_12345, {"book_ref": "123456", "book_date": "2022-06-15T19:05:28.33306+0...).

INSERT INTO bookings_json
    VALUES ('_12345', JSON_OBJECT('book_ref': '_12345', 'book_date': now()));

INSERT 0 1

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

Описание функций JSON_QUERY, JSON_EXISTS, JSON_VALUE от depesz.

А теперь посмотрим как значение JSON можно привести к реляционному виду:

SELECT b.book_ref, jt.*
FROM bookings_json b,
     JSON_TABLE(b.data,
         '$[*]'
         COLUMNS (
             book_date date PATH '$.book_date',
             total_amount numeric(18,2) PATH '$.total_amount',
             NESTED PATH '$.tickets[*]'
             COLUMNS (
                 ticket_no text PATH '$.ticket_no',
                 passenger_id text PATH '$.passenger.id',
                 passenger_name text PATH '$.passenger.name'
             )
         )
     ) jt;

 book_ref | book_date  | total_amount |   ticket_no   | passenger_id |   passenger_name   
----------+------------+--------------+---------------+--------------+--------------------
 000010   | 2017-01-08 |     50900.00 | 0005432295359 | 5722 837257  | ALEKSANDR SOKOLOV
 000010   | 2017-01-08 |     50900.00 | 0005432295360 | 0564 044306  | LYUDMILA BOGDANOVA
 _12345   | 2022-06-15 |              |               |              |
(3 rows)

Описание JSON_TABLE от depesz.

Здесь показана лишь часть того, что умеют новые функции SQL/JSON. Их возможности гораздо шире. Полное описание можно найти в документации, которую оформили отдельным коммитом (первый в вышеперечисленном списке).

Описание документации на SQL/JSON от depesz.

COPY: заголовок в первой строке данных
commit: 43f33dc0, 072132f0

До 15-й версии команда COPY поддерживала строку заголовка с названиями столбцов только для формата csv. В 15-й версии добавили поддержку и для текстового формата:

COPY (SELECT * FROM bookings LIMIT 3) TO stdout (header);

book_ref    book_date    total_amount
000004    2016-08-13 15:40:00+03    55800.00
00000F    2017-07-05 03:12:00+03    265700.00
000010    2017-01-08 19:45:00+03    50900.00

Строка заголовка поддерживается и при загрузке:

COPY bookings FROM stdout (header match);

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.
>> book_ref    book_date    total_amount
>> _12345    2022-05-24    1000
>> \.
COPY 1

Указание header вместе с match заставит выполнить проверку имен столбцов в строке заголовка с именами столбцов в таблице. При несовпадении получим весьма информативную ошибку:

COPY bookings FROM stdout (header match);

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.
>> book_id      book_date    total_amount
>> _12345    2022-05-24    1000
>> \.
ERROR:  column name mismatch in header line field 1: got "book_id", expected "book_ref"
CONTEXT:  COPY bookings, line 1: "book_id    book_date    total_amount"

Описание от depesz.

CLUSTER для секционированных таблиц
commit: cfdd03f4

VACUUM FULL для секционированных таблиц можно делать начиная с 10-й версии. Теперь для таких таблиц поддерживается схожая по функциональности команда CLUSTER.

range_agg для мультидиапазонов
commit: ae1619b

Функция range_agg поддерживает не только диапазонные, но и появившиеся в 14-й версии мультидиапазонные типы данных:

\df range_agg

                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
 pg_catalog | range_agg | anymultirange    | anymultirange       | agg
 pg_catalog | range_agg | anymultirange    | anyrange            | agg

Мониторинг

Перенос статистики в общую память
commit: 5891c7a8, b3abca68

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

Бороться с дисковым вводом/выводом можно переносом каталога stats_temp_directory в оперативную память. Тем не менее, такая архитектура сильно ограничивает добавление новых полезных счетчиков.

В 15-й версии сбор статистики перенесен в общую память. Обслуживающие процессы раз в секунду (задается при сборке) сами записывают информацию о проделанной работе. При аккуратном выключении сервера, статистика из общей памяти записывается в каталог pg_stat и продолжает накапливаться после следующего запуска. В случае сбоя сервера, статистика сбрасывается. Здесь всё осталось как и раньше.

Что изменилось? Больше нет процесса stats collector, ведь процессы сами записывают статистику. Да и само понятие «коллектор статистики» заменено на «система кумулятивной статистики» (Cumulative Statistics System). Кроме того, больше нет параметра stats_temp_directory, статистику больше не надо записывать во временные файлы. А согласованностью данных при чтении статистики можно управлять параметром stats_fetch_consistency.

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

explain показывает ввод/вывод для временных файлов
commit: efb0ef90, 76cbf7ed

Включим track_io_timing и выполним запрос в 14-й версии:

14=# SET track_io_timing = on;

14=# EXPLAIN (analyze, buffers, costs off)
SELECT count(*) FROM generate_series(1,1000000);

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate (actual time=206.451..206.452 rows=1 loops=1)
   Buffers: temp read=1709 written=1709
   ->  Function Scan on generate_series (actual time=92.500..164.520 rows=1000000 loops=1)
         Buffers: temp read=1709 written=1709
 Planning Time: 0.052 ms
 Execution Time: 208.279 ms

Статистика ввода/вывода не появилась. Да и откуда ей взяться, ведь к таблицам мы не обращались.

Однако:

15=# SET track_io_timing = on;

15=# EXPLAIN (analyze, buffers, costs off)
SELECT count(*) FROM generate_series(1,1000000);

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate (actual time=182.130..182.132 rows=1 loops=1)
   Buffers: temp read=1709 written=1709
   I/O Timings: temp read=2.573 write=9.350
   ->  Function Scan on generate_series (actual time=82.422..143.206 rows=1000000 loops=1)
         Buffers: temp read=1709 written=1709
         I/O Timings: temp read=2.573 write=9.350
 Planning Time: 0.045 ms
 Execution Time: 183.608 ms

Ввод/вывод появился. Строки с I/O Timings относятся к временным файлам, которые до первого коммита в вывод EXPLAIN не включались.

Второй коммит добавляет столбцы temp_blk_read_time и temp_blk_write_time в pg_stat_statements для тех же целей.

pg_stat_statements и JIT
commit: 57d6aea0

В pg_stat_statements новые столбцы, собирающие информацию о работе JIT:

\d pg_stat_statements

                      View "public.pg_stat_statements"
         Column         |       Type       | Collation | Nullable | Default
------------------------+------------------+-----------+----------+---------
 ...
 jit_functions          | bigint           |           |          |
 jit_generation_time    | double precision |           |          |
 jit_inlining_count     | bigint           |           |          |
 jit_inlining_time      | double precision |           |          |
 jit_optimization_count | bigint           |           |          |
 jit_optimization_time  | double precision |           |          |
 jit_emission_count     | bigint           |           |          |
 jit_emission_time      | double precision |           |          |

Теперь будет проще оценить влияние JIT на выполнение запросов.

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

recursive_worktable_factor: управление размером рабочей таблицы в рекурсивных запросах
commit: 0bd7af08

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

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

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

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

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

Посмотрим на план простейшего рекурсивного запроса:

EXPLAIN WITH RECURSIVE t(n) AS (
    SELECT 1                                 
  UNION ALL
    SELECT n+1 FROM t WHERE n < 1000
)
SELECT * FROM t;

                               QUERY PLAN                                
-------------------------------------------------------------------------
 CTE Scan on t  (cost=2.95..3.57 rows=31 width=4)
   CTE t
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=4)
           ->  WorkTable Scan on t t_1  (cost=0.00..0.23 rows=3 width=4)
                 Filter: (n < 1000)

Здесь усредненная оценка количества строк в рабочей таблице (узел WorkTable Scan) равна 3, а общее число строк рекурсивного запроса (узел Recursive Union) оценивается в 10 раз больше ― 31.

Но мы знаем точно, что этот рекурсивный запрос выполнит 1000 итераций и вернет 1000 строк, что превышает оценку примерно в 30 раз. Воспользуемся новым параметром для уточнения оценки:

SET recursive_worktable_factor = 300; -- 10*30

EXPLAIN WITH RECURSIVE t(n) AS (
    SELECT 1                                 
  UNION ALL
    SELECT n+1 FROM t WHERE n < 1000
)
SELECT * FROM t;

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 CTE Scan on t  (cost=90.03..110.05 rows=1001 width=4)
   CTE t
     ->  Recursive Union  (cost=0.00..90.03 rows=1001 width=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=4)
           ->  WorkTable Scan on t t_1  (cost=0.00..7.00 rows=100 width=4)
                 Filter: (n < 1000)

Теперь общая оценка кардинальности рекурсивного запроса стала точной, правда за счет избыточного увеличения среднего размера рабочей таблицы. Тем не менее, уменьшая или увеличивая значение recursive_worktable_factor можно влиять на оценку кардинальности узла WorkTable Scan, что в свою очередь влияет на оценку кардинальности узла Recursive Union.

UPD: Следующий патч откатили: f4c7c410
Оптимизация GROUP BY
commit: db0d67db

От перемены мест слагаемых сумма не меняется. И от переставления столбцов в списке GROUP BY результат не меняется, а вот производительность выполнения запроса измениться может!

Пример запроса к таблице ticket_flights, у которой есть индекс по столбцам ticket_no, flight_id:

\d ticket_flights

                     Table "bookings.ticket_flights"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 ticket_no       | character(13)         |           | not null |
 flight_id       | integer               |           | not null |
 fare_conditions | character varying(10) |           | not null |
 amount          | numeric(10,2)         |           | not null |
Indexes:
    "ticket_flights_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
...

Если в предложении GROUP BY поменять столбцы индекса местами, то планировщик «не видит» индекс, выбирая полное сканирование таблицы:

14=# EXPLAIN (analyze, costs off, timing off)
SELECT flight_id,ticket_no, count(*)
FROM ticket_flights
GROUP BY flight_id,ticket_no;

                           QUERY PLAN                           
----------------------------------------------------------------
 HashAggregate (actual rows=8391852 loops=1)
   Group Key: flight_id, ticket_no
   Batches: 745  Memory Usage: 4409kB  Disk Usage: 384560kB
   ->  Seq Scan on ticket_flights (actual rows=8391852 loops=1)
 Planning Time: 0.154 ms
 Execution Time: 5622.852 ms

Стоит лишь поменять местами столбцы в GROUP BY и планировщик понимает, что сканирование только индекса предпочтительнее:

14=# EXPLAIN (analyze, costs off, timing off)
SELECT flight_id,ticket_no, count(*)
FROM ticket_flights
GROUP BY ticket_no,flight_id;

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=8391852 loops=1)
   Group Key: ticket_no, flight_id
   ->  Index Only Scan using ticket_flights_pkey on ticket_flights (actual rows=8391852 loops=1)
         Heap Fetches: 0
 Planning Time: 0.161 ms
 Execution Time: 2342.767 ms

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

15=# EXPLAIN (analyze, costs off, timing off)
SELECT flight_id,ticket_no, count(*)
FROM ticket_flights
GROUP BY flight_id,ticket_no;

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 GroupAggregate (actual rows=8391852 loops=1)
   Group Key: ticket_no, flight_id
   ->  Index Only Scan using ticket_flights_pkey on ticket_flights (actual rows=8391852 loops=1)
         Heap Fetches: 0
 Planning Time: 0.221 ms
 Execution Time: 2302.012 ms

Впрочем оптимизацию можно отключить новым параметром enable_group_by_reordering.

Более эффективное управление памятью при сортировке строк
commit: 40af10b5

Увидеть оптимизацию можно на таком примере:

14=# SET max_parallel_workers_per_gather = 0;

14=# EXPLAIN (analyze, costs off, timing off, summary off)
SELECT * FROM bookings
WHERE total_amount > 300000
ORDER BY total_amount;

                       QUERY PLAN                       
--------------------------------------------------------
 Sort (actual rows=45623 loops=1)
   Sort Key: total_amount
   Sort Method: external merge  Disk: 1480kB
   ->  Seq Scan on bookings (actual rows=45623 loops=1)
         Filter: (total_amount > '300000'::numeric)
         Rows Removed by Filter: 2065487

В 14-й версии для сортировки используется метод external merge с записью на диск.

Тот же запрос, на тех же данных в 15-й версии будет использовать более эффективный метод quicksort:

                       QUERY PLAN                       
--------------------------------------------------------
 Sort (actual rows=45623 loops=1)
   Sort Key: total_amount
   Sort Method: quicksort  Memory: 3969kB
   ->  Seq Scan on bookings (actual rows=45623 loops=1)
         Filter: (total_amount > '300000'::numeric)
         Rows Removed by Filter: 2065487

Конечно, можно и в 14-й версии получить quicksort, увеличив work_mem. Но именно в этом и состоит оптимизация ― память расходуется эффективнее, что позволяет при одинаковом объеме work_mem выбрать более быстрый способ сортировки.

Индексы GiST: баланс между скоростью поиска и скоростью построения индекса методом сортировки
commit: f1ea98a7

В предыдущей, 14-й версии патч Андрея Бородина научил PostgreSQL строить GiST-индексы быстрее (по столбцам с типом данных point) за счет предварительной сортировки значений в Z-порядке. Да, скорость увеличилась, но в результате охватывающие прямоугольники могли сильно накладываться друг на друга. Примерно так:

А это плохо для производительности — при поиске придется просматривать лишние страницы. Теперь индекс строится лучше, пересечений стало гораздо меньше:

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

Подробнее о проблеме выбора между скоростью построения индекса и скоростью его использования Андрей рассказал в своем докладе на PGConf.Russia 2022.

Условие выполнения для монотонно возрастающих/убывающих оконных функций
commit: 9d9c02cc

Как найти 5 последних бронирований в таблице bookings? В PostgreSQL это очень простой и эффектно выполняющийся запрос:

14=# CREATE INDEX ON bookings(book_date);
14=# SET jit=off;

14=# EXPLAIN (analyze, costs off)
SELECT * FROM bookings ORDER BY book_date DESC LIMIT 5;

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Limit (actual time=0.016..0.022 rows=5 loops=1)
   ->  Index Scan Backward using book_date on bookings (actual time=0.015..0.020 rows=5 loops=1)
 Planning Time: 0.086 ms
 Execution Time: 0.037 ms

Главное, чтобы столбец book_date был проиндексирован, что и было предварительно сделано.

Фраза LIMIT доступна не во всех СУБД. В более общем виде запрос можно переписать с оконной функцией row_number:

14=# EXPLAIN (analyze, costs off)
SELECT * FROM (
    SELECT *, row_number() OVER (ORDER BY book_date desc) row_num
    FROM bookings
) t WHERE row_num <= 5;

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Subquery Scan on t (actual time=0.037..1737.146 rows=5 loops=1)
   Filter: (t.row_num <= 5)
   Rows Removed by Filter: 2111105
   ->  WindowAgg (actual time=0.036..1661.209 rows=2111110 loops=1)
         ->  Index Scan Backward using book_date on bookings (actual time=0.018..1011.011 rows=2111110 loops=1)
 Planning Time: 0.118 ms
 Execution Time: 1737.174 ms

Однако производительность резко падает. Оконная функция row_number ничего не знает о том, что после получения пятой строки выполнение запроса можно не продолжать. Поэтому узел WindowAgg честно получает все два с лишним миллиона записей и передает их узлу Subquery Scan. А узел Subquery Scan честно перебирает все полученные записи и применяет к ним фильтр (t.row_num <=5).

Вот как выполняется этот запрос в 15-й версии:

15=# EXPLAIN (analyze, costs off)
SELECT * FROM (
    SELECT *, row_number() OVER (ORDER BY book_date desc) row_num
    FROM bookings
) t WHERE row_num <= 5;

                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 WindowAgg (actual time=0.037..0.041 rows=5 loops=1)
   Run Condition: (row_number() OVER (?) <= 5)
   ->  Index Scan Backward using bookings_book_date_idx on bookings (actual time=0.018..0.026 rows=8 loops=1)
 Planning Time: 0.107 ms
 Execution Time: 0.069 ms

Узла Subquery Scan нет вообще, а узел WindowAgg понимает, что после получения 5-й строки нужно прекращать выполнение. Это видно по появившемуся в 15-й версии условию Run Condition. Производительность практически не отличается от варианта с LIMIT.

Как этого добились? Для функции row_number добавлена вспомогательная функция для планировщика, которая сообщает планировщику о том, что результат функции будет монотонно возрастать. А планировщик, в свою очередь, использует особый фильтр Run Condition. Как только условие в Run Condition перестанет быть истинным, выполнение узла WindowAgg прекращается.

Для демонстрации специально подобран запрос с максимальным эффектом от оптимизации. Если бы у оконной функции использовалась фраза PARTITION BY или в запросе было бы несколько оконных функций, эффективность была бы ниже.

Такие же вспомогательные функции были добавлены не только для row_number, но и для rank, dense_rank, count(*) и count(expr).

Оптимизация заморозки версий строк
commit: 44fa8488, 872770fd, 05023a23, 0b018fab, f3c15cbe

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

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

CREATE TABLE t (id integer);
INSERT INTO t VALUES (1);

VACUUM VERBOSE t;

INFO:  vacuuming "demo.bookings.t"
INFO:  finished vacuuming "demo.bookings.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 0 removed, 1 remain, 0 are dead but not yet removable
removable cutoff: 942, older by 0 xids when operation ended
new relfrozenxid: 941, which is 1 xids ahead of previous value
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 79.923 MB/s, avg write rate: 79.923 MB/s
buffer usage: 5 hits, 4 misses, 4 dirtied
WAL usage: 5 records, 4 full page images, 33167 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Обратите внимание на строку, начинающуюся с «new relfrozenxid:». По ней видно, что значение relfrozenxid для таблицы продвинулось на 1. В предыдущих версиях сервера продвижения не будет.

Кроме того, в предыдущих версиях нам пришлось бы получать значение relfrozenxid отдельным запросом к таблице pg_class. Теперь информация об изменении relfrozenxid выводится командой VACUUM VERBOSE, а также попадает в журнал сообщений сервера при срабатывании log_autovacuum_min_duration.

UPD: Следующий патч откатили: b4529005
Оптимизация работы с вложенными транзакциями
commit: 06f5295a

Существенно оптимизирована работа с большим количеством вложенных транзакций (SAVEPOINT). В каждом сеансе теперь кешируется номер транзакции верхнего уровня. Что позволяет существенно реже запрашивать информацию о полном списке вложенных транзакций из буферов SLRU, за которые может возникать конкуренция (событие ожидания SubtransSLRULock в pg_stat_activity).

postgres_fdw: параллельная фиксация транзакций на внешних серверах
commit:04e706d4

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

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

CREATE SERVER srv_1
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        ... 
        parallel_commit true
    );

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

BEGIN
INSERT INTO tab_srv_1;
INSERT INTO tab_srv_2;
INSERT INTO tab_srv_3;
COMMIT;

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

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

Репликация и резервное копирование

pg_basebackup --target
commit: 3500ccc3, e4ba69f3, c6306db2

До 15-й версии, резервная копия всегда создавалась на клиенте, т.е. там же, где запускается pg_basebackup.

Новый параметр --target или -t позволяет указать где размещать создаваемую резервную копию (первый коммит).

Кроме значения по умолчанию client (для обратной совместимости) можно указать:

server ― копия будет создаваться в файловой системе сервера.
blackhole ― утилита отработает, но резервная копия создаваться не будет. Сделано для целей тестирования.

Кроме того, появился интерфейс, позволяющее пользователям создавать расширения, в которых можно определять собственные цели (второй коммит).

В качестве примера реализации такого расширения, в составе contrib появилось расширение ― basebackup_to_shell (третий коммит). В этом расширении реализовано значение shell для параметра target. Что позволяет отправить резервную копию в команду операционной системы, указанную при запуске pg_basebackup.

Нужно понимать, что любое значение кроме client предполагает, что резервная копия будет создаваться на стороне сервера, а клиенту (где работает pg_basebackup) будут отправляются лишь сообщения о текущем статусе выполнения.

Указать цель, отличную от client, можно только если не нужно получать WAL во время работы pg_basebackup, т.е. если не выбран режим Xstream.

pg_basebackup --compress
commit: d4509942, 5c649fe1, 0ad80329, 751b8d23, 7cf085f0, dab29847, 51c0d186

До 15-й версии параметр compress использовался для сжатия резервной копии только в формате tar и только методом gzip.

Теперь сжатие возможно и для других форматов.

Кроме того, сжимать данные можно на стороне сервера, перед их отправкой клиенту по протоколу репликации. Это может быть удобным если, скорость сети не очень высока и мы готовы пожертвовать ресурсами сервера на сжатие данных и сэкономить на размере передаваемой копии. Важное уточнение, что сжимать на стороне сервера можно только файлы данных, но не файлы WAL. WAL можно сжимать только на стороне клиента.

Появился выбор и методов сжатия. Поддерживаются: gzip, lz4, zstd. Как на клиенте, так и на сервере. Более того, компрессия zstd возможна в параллельном режиме. В параметрах запуска можно указать требуемое количество рабочих процессов.

Такие масштабные доработки pg_basebackup для поддержки сжатия и целей резервирования потребовали доработать и протокол репликации.

Описание сжатия методом LZ4 на сервере от Роберта Хааса.
Описание параллельного сжатия zstd на сервере от Роберта Хааса.

wal_compression = zstd
commit: e9537321

В статье об июльском коммитфесте говорилось о том, что для сжатия WAL можно использовать не только pglz, но и lz4. Этот патч добавляет в список методов сжатия zstd:

SELECT enumvals FROM pg_settings WHERE name = 'wal_compression';

        enumvals        
------------------------
 {pglz,lz4,zstd,on,off}

По умолчанию, сжатие по-прежнему отключено. Но у тех кому важно сэкономить на объеме WAL, пожертвовав процессорным временем на сжатие, появился дополнительный выбор.

Модули архивации WAL
commit: 5ef1eefd

Для настройки непрерывного архивирования WAL мы включаем параметр archive_mode, а в archive_command прописываем команду операционной системы, которая копирует очередной заполненный сегмент WAL в архив.

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

В 15-й версии добавлена инфраструктура для создания модулей архивации. Теперь можно написать модуль, которому сервер будет передавать заполненные сегменты WAL для архивации. Библиотека с реализацией модуля указывается в новом параметре archive library. Она загружается при старте сервера.

Пример реализации модуля архивации появился в contrib, это расширение basic_archive.

По умолчанию параметр archive_library не заполнен (пустая строка) и для архивации используется команда из archive_command, как и в предыдущих версиях.

Описание от depesz.

Нежурналируемые последовательности
commit: 344d62fb

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

CREATE TEMP TABLE temp_table (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
SELECT relname, relkind, relpersistence
FROM pg_class
WHERE relname LIKE 'temp_table%';

      relname      | relkind | relpersistence
-------------------+---------+----------------
 temp_table        | r       | t
 temp_table_id_seq | S       | t
 temp_table_pkey   | i       | t

Однако в похожей ситуации для нежурналируемой таблицы создается обычная (persistent) последовательность:

14=# CREATE UNLOGGED TABLE unlogged_table (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

14=# SELECT relname, relkind, relpersistence
FROM pg_class
WHERE relname LIKE 'unlogged_table%';

        relname        | relkind | relpersistence
-----------------------+---------+----------------
 unlogged_table        | r       | u
 unlogged_table_id_seq | S       | p
 unlogged_table_pkey   | i       | u

В 15-й версии последовательность наследует признак relpersistence у таблицы.

15=# CREATE UNLOGGED TABLE unlogged_table (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
15=# SELECT relname, relkind, relpersistence
FROM pg_class
WHERE relname LIKE 'unlogged_table%';

        relname        | relkind | relpersistence
-----------------------+---------+----------------
 unlogged_table        | r       | u
 unlogged_table_id_seq | S       | u
 unlogged_table_pkey   | i       | u

Кроме того, нежурналируемые последовательности можно создавать и явно командой CREATE UNLOGGED SEQUENCE.

Важной особенностью нежурналируемых последовательностей является то, что их изменения не реплицируются, ведь изменения не пишутся в WAL. Это относится как к физической репликации, так и в будущем к логической репликации. (Патч для поддержки логической репликации последовательностей в последний момент был отменен для 15-й версии.)

Предвыборка WAL при восстановлении
commit: 5dc0418f

Асинхронная предвыборка данных ускоряет получение данных (если это позволяет дисковая подсистема) и может использоваться в нескольких ситуациях, например, при сканировании по битовой карте (Bitmap Heap Scan).

Служебные процессы тоже могут использовать предвыборку. Начиная с версии 13 заранее считываются страницы индекса, когда из них вычищаются ненужные индексные записи; начиная с версии 14 сбор статистической информации считывает анализируемые страницы. Размер предвыборки для служебных процессов управляется параметром maintenance_io_concurrency.

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

Предвыборка при восстановлении включается параметром recovery_prefetch. Еще один параметр wal_decode_buffer_size определяет, как далеко сервер будет заглядывать в журнал, чтобы найти там номера нужных страниц (по умолчанию просматривается 512 Кбайт, или 64 журнальные страницы).

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

pg_rewind: параметр --config-file
commit: 0d5c3875

Некоторые дистрибутивы PostgreSQL выносят конфигурационные файлы за пределы каталога с данными (PGDATA). И pg_rewind не может найти postgresql.conf при запуске целевого сервера. Новый параметр утилиты --config-file позволяет указать расположение postgresql.conf для таких случаев.

Расширение pg_walinspect ― SQL-интерфейс к содержимому WAL
commit: 2258e76f

Для исследования WAL теперь можно использовать не только утилиту pg_waldump, но и SQL-функции нового расширения pg_walinspect.

CREATE EXTENSION pg_walinspect;

SELECT pg_current_wal_insert_lsn();

 pg_current_wal_insert_lsn
---------------------------
 1/57ACBCA8
(1 row)

CREATE TABLE t (id int);

SELECT pg_current_wal_insert_lsn();

 pg_current_wal_insert_lsn
---------------------------
 1/57ADC288
(1 row)

SELECT * FROM pg_get_wal_stats('1/57ACBCA8', '1/57ADC288') WHERE count >0;

 resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
 Transaction                  |     2 |        3.7735848 |         790 |              15.259803 |        0 |                   0 |           790 |                1.1851007
 Storage                      |     1 |        1.8867924 |          42 |             0.81128067 |        0 |                   0 |            42 |               0.06300536
 Standby                      |     1 |        1.8867924 |          42 |             0.81128067 |        0 |                   0 |            42 |               0.06300536
 Heap2                        |    19 |        35.849056 |        1980 |               38.24609 |     4760 |           7.7418513 |          6740 |                 10.11086
 Heap                         |     3 |        5.6603775 |         625 |              12.072629 |        0 |                   0 |           625 |                0.9375797
 Btree                        |    27 |        50.943398 |        1698 |               32.79892 |    56724 |            92.25815 |         58422 |                 87.64045
(6 rows)

pg_waldump: новые фильтры
commit: 127aea2a, 52b55684

Новые параметры для фильтрации вывода утилиты pg_waldump:

--relation ― только записи указанного отношения,
--fork ― только записи указанного слоя (main, vm, fsm, init),
--block ― только записи указанного блока,
--fullpage ― только образы полных страниц (full page writes).

Монопольный режим резервного копирования окончательно удален
commit: 39969e2a

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

Монопольный режим уже давно (с 9.6) считается устаревшим. И в 15-й версии от него решили отказаться совсем. Теперь резервное копирование возможно только в немонопольном режиме. Вместо более недоступных функций pg_start_backup/pg_stop_backup используются новые: pg_backup_start/pg_backup_stop.

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

Фильтрация строк в публикации
commit: 52e4f0cd

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

Предположим, что ряд служебных записей таблицы test могут иметь отрицательные значения в столбце id:

pub=# CREATE TABLE test (
    id int PRIMARY KEY, 
    creation_date timestamptz DEFAULT now()
);
pub=# INSERT INTO test VALUES (-1), (0);

Эти служебные записи не нужно реплицировать. Поэтому при создании публикации указываем фильтр:

pub=# CREATE PUBLICATION test_pub FOR TABLE test WHERE (id >= 0);

Фильтрация столбцов в публикации
commit: 923def9a

Теперь предположим, что подписчикам не нужна информация о дате создания записей (столбец creation_date) в таблице test.

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

pub=# DROP PUBLICATION test_pub;
pub=# CREATE PUBLICATION test_pub FOR TABLE test (id) WHERE (id >= 0);
pub=# \dRp+

                            Publication test_pub
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.test" (id) WHERE (id >= 0)

На подписчике создаем таблицу test только со столбцом id и подписываемся на публикацию:

sub=# CREATE TABLE test (id int PRIMARY KEY);
sub=# CREATE SUBSCRIPTION test_sub
       CONNECTION 'port=5415 user=postgres dbname=postgres'
       PUBLICATION test_pub;

Проверяем:

sub=# SELECT * FROM test;

 id
----
  0
(1 row)

После начальной синхронизации в таблице test одна запись.

Отключение подписки после ошибки
commit: 705e20f8, 8d74fc96, 7a850732

В работе логической репликации могут быть сбои. Например из-за конфликта применения изменений. Механизмы автоматического разрешения конфликтов пока не реализованы, поэтому репликация приостанавливается до ручного разрешения. Процесс logical replication worker запишет информацию об ошибке в журнал сервера и остановится. Но он будет периодически перезапускаться в попытках продолжить применение изменений и в случае неуспеха опять писать в журнал сервера и «засыпать». И так будет продолжаться до разрешения конфликта.

В 15-й версии появляется возможность отключить подписку при возникновении конфликта, тем самым сэкономить на не успешных запусках logical replication worker.

Установим для подписки новый параметр disable_on_error (это можно сделать и при создании подписки):

sub=# ALTER SUBSCRIPTION test_sub SET (disable_on_error);

sub=# \x \dRs+

Expanded display is on.
List of subscriptions
-[ RECORD 1 ]------+----------------------------------------
Name               | test_sub
Owner              | postgres
Enabled            | t
Publication        | {test_pub}
Binary             | f
Streaming          | f
Two phase commit   | d
Disable on error   | t
Synchronous commit | off
Conninfo           | port=5415 user=postgres dbname=postgres
Skip LSN           | 0/0

Для создания конфликта добавим строку с id=1 сначала на подписчике, а затем и на сервере публикации.

Подписчик:

sub=# INSERT INTO test VALUES (1);

На поставщике добавим три строки в двух транзакциях. Это пригодится для описания следующего патча:

pub=# BEGIN;
pub=# INSERT INTO test VALUES (1);
pub=# INSERT INTO test VALUES (2);
pub=# COMMIT;

pub=# BEGIN;
pub=# INSERT INTO test VALUES (3);
pub=# COMMIT;

На публикующем сервере в таблице четыре строки:

pub=# SELECT * FROM test;

 id |         creation_date         
----+-------------------------------
 -1 | 2022-07-22 13:15:43.602303+03
  0 | 2022-07-22 13:15:43.602303+03
  1 | 2022-07-23 11:00:30.465183+03
  2 | 2022-07-23 11:00:30.465183+03
  3 | 2022-07-23 11:01:03.928878+03

А на подписчике из-за невозможности добавить вторую запись с id=1 так и осталось две:

sub=# SELECT * FROM test;

 id
----
  0
  1
(2 rows)

В журнал сервера записывается информация о конфликте:

2022-07-23 11:00:47.977 MSK [37313] CONTEXT:  processing remote data for replication origin "pg_32773" during "INSERT" for replication target relation "public.test" in transaction 1618 finished at 3/87E716B8
2022-07-23 11:00:47.978 MSK [37313] LOG:  logical replication subscription "test_sub" has been disabled due to an error

… и подписка отключается (вторая строка в журнале), благодаря параметру disable_on_conflict.

sub=# \dRs

            List of subscriptions
   Name   |  Owner   | Enabled | Publication
----------+----------+---------+-------------
 test_sub | postgres | f       | {test_pub}

В новом статистическом представлении pg_stat_subscription_stats есть счетчик ошибок применения изменений:

SELECT * FROM pg_stat_subscription_stats;

 subid | subname  | apply_error_count | sync_error_count | stats_reset
-------+----------+-------------------+------------------+-------------
 32773 | test_sub |                 1 |                0 |

Пропуск транзакции для разрешения конфликта
commit: 208c5d65

Для продолжения репликации можно удалить конфликтующую запись на подписчике. Это первый вариант разрешения конфликта.

Другой вариант ― пропустить конфликтующую транзакцию целиком и продолжить применение изменений со следующей. Для реализации такого варианта в команде ALTER SUBSCRIPTION появился параметр SKIP, в котором можно указать LSN команды завершающей конфликтующую транзакцию.

Как узнать этот номер LSN? Он есть в журнале сервера. Еще раз сообщение об ошибке из журнала:

2022-07-23 11:00:47.977 MSK [37313] CONTEXT:  processing remote data for replication origin "pg_32773" during "INSERT" for replication target relation "public.test" in transaction 1618 finished at 3/87E716B8

Пропускаем конфликтующую транзакцию:

sub=# ALTER SUBSCRIPTION test_sub SKIP (lsn = '3/87E716B8');

Для продолжения репликации осталось включить подписку:

sub=# ALTER SUBSCRIPTION test_sub ENABLE;
sub=# SELECT * FROM test;

 id
----
  0
  1
  3
(3 rows)

Репликация продолжила работу. Однако нужно учитывать, что пропускаются все команды конфликтующей транзакции. В нашем случае запись с id=2 «потерялась».

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

Права доступа к конфигурационным параметрам
commit: a0ffa885, 3e707fbb

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

При помощи команды GRANT, можно разрешить любой роли изменять параметры, требующие прав суперпользователя (первый коммит). Кроме того, можно не только разрешить устанавливать параметры в сеансе, но и прописывать их в конфигурационный файл (ALTER SYSTEM).

CREATE ROLE alice LOGIN;

GRANT SET, ALTER SYSTEM 
    ON PARAMETER log_min_duration_statement, pg_stat_statements.track
    TO alice;

Теперь Алиса может смело включать запись всех команд с временем выполнения в журнал сервера.

\c - alice
SET log_min_duration_statement=0;

Как же узнать выданные параметрам права доступа? Для этого в psql появилась команда \dconfig (второй коммит), показывающая информацию из нового системного каталога pg_parameter_acl.

\dconfig+ log_min_duration_statement

                        List of configuration parameters
         Parameter          | Value |  Type   |  Context  |  Access privileges   
----------------------------+-------+---------+-----------+----------------------
 log_min_duration_statement | -1    | integer | superuser | postgres=sA/postgres+
                            |       |         |           | alice=sA/postgres

Подобный функционал можно реализовать и в предыдущих версиях. Для этого нужно создать суперпользователем функцию, выполняемую с правами владельца (SECURITY DEFINER), и оставить права на исполнение только нужным ролям.

А вот за команду \dconfig стоит сказать разработчикам отдельное спасибо. Ей намного удобнее пользоваться для просмотра параметров, чем SHOW или запросами к pg_settings. Как быстро получить значения всех параметров, связанных с автоочисткой? Просто:

\dconfig auto*

         List of configuration parameters
               Parameter               |   Value   
---------------------------------------+-----------
 autovacuum                            | on
 autovacuum_analyze_scale_factor       | 0.1
 autovacuum_analyze_threshold          | 50
 autovacuum_freeze_max_age             | 200000000
 autovacuum_max_workers                | 3
 autovacuum_multixact_freeze_max_age   | 400000000
 autovacuum_naptime                    | 1min
 autovacuum_vacuum_cost_delay          | 2ms
 autovacuum_vacuum_cost_limit          | -1
 autovacuum_vacuum_insert_scale_factor | 0.2
 autovacuum_vacuum_insert_threshold    | 1000
 autovacuum_vacuum_scale_factor        | 0.2
 autovacuum_vacuum_threshold           | 50
 autovacuum_work_mem                   | -1

Представления с правами вызывающего
commit: 7faa5fc8

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

А вот обращения к представлениям, наоборот, по умолчанию выполняются с правами владельца. И до 15-й версии это единственный вариант выполнения.

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

CREATE ROLE alice LOGIN;
GRANT USAGE ON SCHEMA bookings TO alice;
GRANT SELECT ON flights_v TO alice;

alice=> SELECT count(*) FROM flights_v;

 count  
--------
 214867

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

\c - postgres
ALTER VIEW flights_v SET (security_invoker = true);

\c - alice
alice=> SELECT count(*) FROM flights_v;

ERROR:  permission denied for table flights

Описание от depesz.

Обычная роль больше не имеет ADMIN OPTION для самой себя
commit: 79de9842

За туманной формулировкой скрывается следующее. Роль с привилегией LOGIN больше не сможет исключать себя из других ролей. Например суперпользователь создал две роли и включил alice в bob:

CREATE ROLE alice LOGIN;
CREATE ROLE bob LOGIN;
GRANT alice TO bob;
\du alice|bob

           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 alice     |            | {}
 bob       |            | {alice}

Теперь alice хочет исключить себя из роли bob. Поведение в 14-й версии:

alice(14)=> REVOKE alice FROM bob;

REVOKE ROLE

alice(14)=> \du alice|bob

           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 alice     |            | {}
 bob       |            | {}

Тоже самое в 15-й версии:

alice(15)=> REVOKE alice FROM bob;

ERROR:  must have admin option on role "alice"

Больше alice не сможет исключить себя из других ролей.

Как следует из сообщения о коммите, при создании любой роли ей автоматически выдавалась ADMIN OPTION для самой себя. И сделано это было давно и непреднамеренно. Однако в результате обычная несуперпользовательская роль может нарушить систему привилегий выстроенную суперпользователем. Обычно на месте alice используются групповые роли без атрибута LOGIN, которые не имея возможности подключиться, не могут выполнить и REVOKE. Видимо поэтому на такое поведение долго не обращали внимание.

Системное администрирование

ICU на уровне кластера и базы данных
commit: f2553d43

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

CREATE DATABASE db
    LOCALE_PROVIDER = icu
    ICU_LOCALE = 'ru-x-icu'
    TEMPLATE = template0;

Аналогичные возможности есть и при инициализации кластера.

$ initdb --help | grep icu
      --icu-locale=LOCALE   set ICU locale ID for new databases
      --locale-provider={libc|icu}

Новые столбцы datlocprovider, daticulocale в pg_database показывают информацию о выбранном для БД провайдере и правиле сортировки ICU. Они добавлены в вывод команды \list в psql:

\list db

                                             List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges
------+----------+----------+-------------+-------------+------------+-----------------+-------------------
 db   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | ru-x-icu   | icu             |

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

Отслеживание версий правил сортировки для баз данных
commit: 37851a8b

Проверка номера версии уже была реализована для правил сортировки в системной таблице pg_collation. Теперь такая же проверка будет выполняться и для правил сортировки, указанных для баз данных:

SELECT datname,
       pg_database_collation_actual_version(oid) actual_version,
       datcollversion,
       datlocprovider
FROM pg_database;

  datname  | actual_version | datcollversion | datlocprovider
-----------+----------------+----------------+----------------
 postgres  | 2.31           | 2.31           | c
 demo      | 2.31           | 2.31           | c
 template1 | 2.31           | 2.31           | c
 template0 | 2.31           |                | c
 db        | 153.14.36.8    | 153.14.36.8    | i
(5 rows)

Новая функция pg_database_collation_actual_version показывает актуальную версию правила сортировки в операционной системе. А новая форма команды ALTER DATABASE… REFRESH COLLATION VERSION обновляет хранимую в pg_database версию в соответствии с версией операционной системы.

pg_upgrade: каталог для временных файлов
commit: 38bfae36

pg_upgrade в процессе своей работы создает временные файлы. В случае сбоя или при запуске с параметром --retain все эти файлы так и остаются в текущем каталоге и могут быть использованы для диагностики проблем с обновлением.

В новой версии временные файлы создаются в каталоге pg_upgrade_output.d, расположенном в PGDATA нового кластера БД.

pg_upgrade: сохранение имен файлов баз данных, табличных пространств и отношений
commit: 9a974cbc, aa010514

Утилита pg_upgrade будет сохранять идентификаторы pg_database.oid, pg_tablespace.oid и pg_class.relfilenode. А это значит, что имена файлов каталогов для баз данных и табличных пространств, а также имена файлов отношений останутся прежними после обновления.

CREATE DATABASE с журналированием в WAL
commit: 9c08aea6

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

В самом начале команды CREATE DATABASE выполняется контрольная точка, затем копируются файлы из шаблонной базы данных (по умолчанию template1), обновляются ссылки на файлы в системном каталоге и в конце еще раз выполняется контрольная точка. В случае сбоя можно спокойно восстановиться до начальной контрольной точки.

Однако контрольная точка на нагруженном сервере это достаточно долгая и ресурсоемкая операция. Чтобы избежать её, в 15-й версии по умолчанию используется новая стратегия создания базы данных. Контрольные точки не выполняются, данные копируются постранично для всех объектов шаблонной базы, с записью в WAL. И эта запись в WAL гарантирует возможность восстановления при сбое.

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

Поэтому создать базу данных можно и «по-старинке», явно указав стратегию FILE_COPY:

CREATE DATABASE new_db WITH strategy=FILE_COPY;

Представление pg_ident_file_mappings
commit: a2c84990

Содержимое файла pg_ident.conf можно получить запросом к представлению pg_ident_file_mappings.

Таким образом теперь у нас есть SQL-интерфейс ко всем конфигурационным файлам. В версии 9.5 появилось представление pg_file_settings для postgresql.conf и postgresql.auto.conf. А в 10-й версии представление pg_hba_file_rules для pg_hba.conf.

Прекращена поддержка второй версии Python
commit: 76a29ade

Хранимые подпрограммы на Python теперь будут работать только на plpython3u. plpython2u и plpythonu более недоступны.

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

psql: показывать результаты всех запросов
commit: 7844c991

Этот патч успел попасть еще в 14-ю версию, но позже был отменен. Напомню, что речь о том, что psql будет выводить результаты всех запросов, если они были отправлены на сервер за раз:

=# SELECT 1\; SELECT 2;

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

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

Раньше выводился только результат последнего запроса.

На этом всё. Большое спасибо Егору Рогову за помощь в описании некоторых изменений.

И ждем осенью выход 15-й версии.

А в конце этой недели заканчивается первый коммитфест 16-й версии. Продолжение следует!

Теги:

Хабы:

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

Содержание

Команды SQL

Мониторинг

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

Репликация и резервное копирование

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

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

Системное администрирование

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

Команды SQL

Мониторинг

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

Репликация и резервное копирование

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

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

Системное администрирование

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

Публикации

Информация