Это продолжении серии статей о принятых изменениях в PostgreSQL 18. На этот раз речь пойдет о завершившемся в ноябре коммитфесте.

А самое интересное из предшествующих коммитфестов можно прочитать здесь: 2024-07, 2024-09.

initdb: подсчет контрольных сумм включен по умолчанию
Планировщик: замена однотипных условий OR на поиск в массиве
Планировщик: перестановка выражений в DISTINCT
GROUPING SETS: HAVING -> WHERE
Очистка кеша типов данных и временные таблицы
Планировщик: инкрементальная сортировка при соединении слиянием
Новая функция array_reverse
Функции min и max для типа bytea
Статистика использования параллельных рабочих процессов
Новая функция pg_ls_summariesdir
pg_logicalsnapinspect: новый модуль contrib
Уточненные сообщения об ошибках при установке расширений
Ограничения NOT NULL в системном каталоге
Таблица TOAST для pg_index
COPY… FROM и file_fdw: ограничение на количество ошибочных строк
Недетерминированные правила сортировки: поддержка LIKE
TLS v1.3: настройка наборов шифров

initdb: подсчет контрольных сумм включен по умолчанию
commit: 983a588e0, 04bec894a

Простое, но важное изменение. При инициализации кластера утилитой initdb можно больше не указывать параметр --data-checksums или -k. Подсчет контрольных сумм теперь включен по умолчанию.

Если всё-таки контрольные суммы не нужны, то пригодится новый параметр --no-data-checksums.

Планировщик: замена однотипных условий OR на поиск в массиве
commit: d4378c000, ae4569161

План запроса в 17-й версии трижды сканирует индекс для каждого из однотипных условий:

17=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = 1 OR flight_id = 2 OR flight_id = 3;

                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on flights
   Recheck Cond: ((flight_id = 1) OR (flight_id = 2) OR (flight_id = 3))
   ->  BitmapOr
         ->  Bitmap Index Scan on flights_pkey
               Index Cond: (flight_id = 1)
         ->  Bitmap Index Scan on flights_pkey
               Index Cond: (flight_id = 2)
         ->  Bitmap Index Scan on flights_pkey
               Index Cond: (flight_id = 3)
(9 rows)

Запрос лучше переписать так, чтобы было достаточно сходить в индекс один раз:

17=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = ANY (ARRAY[1,2,3]);

                       QUERY PLAN                       
--------------------------------------------------------
 Index Scan using flights_pkey on flights
   Index Cond: (flight_id = ANY ('{1,2,3}'::integer[]))
(2 rows)

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

18=# EXPLAIN (costs off)
SELECT * FROM flights WHERE flight_id = 1 OR flight_id = 2 OR flight_id = 3;

                       QUERY PLAN                       
--------------------------------------------------------
 Index Scan using flights_pkey on flights
   Index Cond: (flight_id = ANY ('{1,2,3}'::integer[]))
(2 rows)

Планировщик: перестановка выражений в DISTINCT
commit: a8ccf4e93

Еще один пример оптимизации, дающий свободу выбора при написании запроса.

В 17-й версии планировщик научился переставлять местами выражения в GROUP BY в соответствии с положением выражений в индексе. В таблице ticket_flights есть индекс по составному первичному ключу, нас интересует порядок столбцов в индексе.

\d ticket_flights_pkey

     Index "bookings.ticket_flights_pkey"
  Column   |     Type      | Key? | Definition
-----------+---------------+------+------------
 ticket_no | character(13) | yes  | ticket_no
 flight_id | integer       | yes  | flight_id
primary key, btree, for table "bookings.ticket_flights"

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

17=# EXPLAIN (costs off)
SELECT flight_id,ticket_no          
FROM ticket_flights
GROUP BY flight_id,ticket_no; –- столбцы в индексе

                            QUERY PLAN                             
-------------------------------------------------------------------
 Group
   Group Key: ticket_no, flight_id
   ->  Index Only Scan using ticket_flights_pkey on ticket_flights
(3 rows)

Продолжение этой оптимизации ― научить планировщик выполнять подобную перестановку для DISTINCT, включая DISTINCT ON. Что и сделано для 18-й версии:

18=# EXPLAIN (costs off)
SELECT DISTINCT flight_id,ticket_no 
FROM ticket_flights;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Unique
   ->  Index Only Scan using ticket_flights_pkey on ticket_flights
(2 rows)

Обе оптимизации управляются конфигурационными параметрами и по умолчанию включены:

 \dconfig enable_*_reordering

  List of configuration parameters
         Parameter          | Value
----------------------------+-------
 enable_distinct_reordering | on
 enable_group_by_reordering | on
(2 rows)

GROUPING SETS: HAVING -> WHERE
commit: 67a54b9e8

В плане следующего запроса важно обратить внимание на строку Filter. Она находится под узлом Seq Scan, а не HashAggregate, как было раньше. Это значит что условие из HAVING теперь проверяется на этапе получения строк из таблицы, т. е. до агрегации.

EXPLAIN (costs off)
SELECT aircraft_code, status, COUNT(*)
FROM flights
GROUP BY GROUPING SETS ((aircraft_code, status), (status))
HAVING status = 'Arrived';

                     QUERY PLAN                     
----------------------------------------------------
 HashAggregate
   Hash Key: status, aircraft_code
   Hash Key: status
   ->  Seq Scan on flights
         Filter: ((status)::text = 'Arrived'::text)
(5 rows)

Конечно, можно было бы переписать запрос и перенести условие из HAVING в WHERE. Но хорошо что планировщик учится сам находить более эффективные методы выполнения.

Очистка кеша типов данных и временные таблицы
commit: cc5ef90ed, d0f020037, b85a9d046

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

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

\timing on
DO $$BEGIN
  FOR i IN 1 .. 20_000 LOOP
    EXECUTE format('CREATE TEMP TABLE t%s (id int)', i);
    EXECUTE format('INSERT INTO t%s VALUES ( (ROW(0)::t%s).id)', i, i);
  END LOOP;
END; $$;

DO $$BEGIN
  FOR i IN 1 .. 20_000 LOOP
    EXECUTE format('DROP TABLE t%s', i);
  END LOOP;
END; $$;

Время выполнения каждого из блоков в 17-й версии:

Time: 12251,384 ms (00:12,251)
Time: 22494,163 ms (00:22,494)

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

Time: 2872,074 ms (00:02,872)
Time: 1495,051 ms (00:01,495)

Ускорение более чем очевидно, особенно для второго анонимного блока.

Планировщик: инкрементальная сортировка при соединении слиянием
commit: 828e94c9d

Как удачно пошутил Томаш Вондра, инкрементальная сортировка добавляется инкрементально.

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

В описании патча есть такой пример:

CREATE TABLE t (a int, b int);
INSERT INTO t SELECT random(1,100), random(1,100) FROM generate_series(1,100000);
CREATE INDEX ON t (a);
ANALYZE t;

EXPLAIN (costs off)
SELECT *
FROM (SELECT * FROM t ORDER BY a) t1
     JOIN t t2 ON t1.a = t2.a AND t1.b = t2.b
ORDER BY t1.a, t1.b;

                  QUERY PLAN                   
-----------------------------------------------
 Merge Join
   Merge Cond: ((t.a = t2.a) AND (t.b = t2.b))
   ->  Incremental Sort
         Sort Key: t.a, t.b
         Presorted Key: t.a
         ->  Index Scan using t_a_idx on t
   ->  Sort
         Sort Key: t2.a, t2.b
         ->  Seq Scan on t t2
(9 rows)

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

                  QUERY PLAN                   
-----------------------------------------------
 Merge Join
   Merge Cond: ((t.a = t2.a) AND (t.b = t2.b))
   ->  Sort
         Sort Key: t.a, t.b
         ->  Index Scan using t_a_idx on t
   ->  Materialize
         ->  Sort
               Sort Key: t2.a, t2.b
               ->  Seq Scan on t t2
(9 rows)

Новая функция array_reverse
commit: 49d6c7d8d

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

SELECT array_reverse(string_to_array('PostgreSQL',NULL));

     array_reverse     
-----------------------
 {L,Q,S,e,r,g,t,s,o,P}
(1 row)

См. также
Waiting for PostgreSQL 18 – Add SQL function array_reverse() (Hubert 'depesz' Lubaczewski)

Функции min и max для типа bytea
commit: 2d24fd942

Как и следует из названия, для сортируемого типа данных bytea добавили отсутствующие функции min и max:

WITH t AS (
    SELECT book_ref::bytea AS c FROM bookings
)
SELECT min(c), max(c) FROM t;

      min       |      max       
----------------+----------------
 \x303030303034 | \x464646464639
(1 row)

Статистика использования параллельных рабочих процессов
commit: cf54a2c00, e7a9496de

В статистические представления pg_stat_statements (первый коммит) и pg_stat_database (второй коммит) добавлены два столбца: parallel_workers_to_launch и parallel_workers_launched. Они показывают количество запланированных параллельных рабочих процессов и сколько из них было реально запущено.

Посмотрим на столбцы в деле. Настройки параллельного выполнения не менялись:

\dconfig max_parallel_workers*|max_worker*

    List of configuration parameters
            Parameter            | Value
---------------------------------+-------
 max_parallel_workers            | 8
 max_parallel_workers_per_gather | 2
 max_worker_processes            | 8
(3 rows)

Сбросим статистику перед тестом:

SELECT pg_stat_statements_reset();
SELECT pg_stat_reset();

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

$ pgbench --client=10 --time=5 --file=test.sql

Файл test.sql содержит два запроса на агрегирование таблиц достаточно большого размера, чтобы был выбран параллельный план:

$ cat test.sql

SELECT count(*) FROM bookings;
SELECT count(*) FROM tickets;

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

SELECT query,
       parallel_workers_to_launch AS plan,
       parallel_workers_launched AS fact
FROM pg_stat_statements
WHERE query ~ 'tickets|bookings';

             query             | plan | fact
-------------------------------+------+------
 SELECT count(*) FROM bookings |   84 |   42
 SELECT count(*) FROM tickets  |   84 |   40
(2 rows)

А также более общую статистику базы данных:

SELECT parallel_workers_to_launch AS plan,
       parallel_workers_launched AS fact
FROM pg_stat_database
WHERE datname = current_database();

 plan | fact
------+------
  168 |   82
(1 row)

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

Новая функция pg_ls_summariesdir
commit: 4e1fad378

Для мониторинга работы инкрементального резервного копирования пригодится новая функция pg_ls_summariesdir, показывающая содержимое каталога pg_wal/summaries, включая размер файлов и дату последнего изменения.

SELECT * FROM pg_ls_summariesdir();

                       name                       | size  |      modification      
--------------------------------------------------+-------+------------------------
 0000000100000001412CF0980000000162404D08.summary | 17164 | 2025-02-03 15:22:33+03
(1 row)

Именно показ информации о размере файлов и дате последнего изменения отличают эту функцию от вызова pg_ls_dir('pg_wal/summaries'), которая выдает только имена файлов.

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

\df pg_ls_*dir

                                                                    List of functions
   Schema   |       Name        | Result data type |                                    Argument data types                                 | Type
------------+-------------------------+------------------+-------------------------------------------------------------------------------------------+------
 pg_catalog | pg_ls_archive_statusdir | SETOF record    | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_dir             | SETOF text    | text                                                                                      | func
 pg_catalog | pg_ls_dir             | SETOF text    | text, boolean, boolean                                                                    | func
 pg_catalog | pg_ls_logdir          | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_logicalmapdir   | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_logicalsnapdir  | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_replslotdir     | SETOF record  | slot_name text, OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
 pg_catalog | pg_ls_summariesdir    | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_tmpdir          | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
 pg_catalog | pg_ls_tmpdir          | SETOF record  | tablespace oid, OUT name text, OUT size bigint, OUT modification timestamp with time zone | func
 pg_catalog | pg_ls_waldir          | SETOF record  | OUT name text, OUT size bigint, OUT modification timestamp with time zone                 | func
(11 rows)

pg_logicalsnapinspect: новый модуль contrib
commit: 7cdfeee32

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

В настоящий момент включает две функции pg_get_logical_snapshot_meta и pg_get_logical_snapshot_info. Функции выдают информацию о снимках данных из файлов, расположенных в $PGDATA/pg_logical/snapshots.

Уточненные сообщения об ошибках при установке расширений
commit: 14e5680ee, 774171c4f, 924e03917

Для демонстрации возьмем расширение uom, для которого доступно три версии:

SELECT name, version
FROM pg_available_extension_versions
WHERE name = 'uom';

 name | version
------+---------
 uom  | 1.0
 uom  | 1.1
 uom  | 1.2
(3 rows)

В один из установочных скриптов SQL намеренно внесена ошибочная команда. Попробуем установить последнюю версию расширения:

17=# CREATE EXTENSION uom VERSION '1.2';

ERROR:  division by zero

Ошибка! Но в каком файле?

В 18-й версии сообщение и контекст ошибок при установке расширений значительно улучшены:

18=# CREATE EXTENSION uom VERSION '1.2';

ERROR:  division by zero
CONTEXT:  SQL statement "SELECT 1/0"
extension script file "uom--1.1--1.2.sql", near line 9

Ограничения NOT NULL в системном каталоге
commit: 14e87ffa5

Это вторая попытка записывать ограничения NOT NULL в системный каталог pg_constraint. Первая попытка была в 17-й версии, однако патч был отменен. Но описание патча осталось актуальным.

Таблица TOAST для pg_index
commit: b52c4fc3c

Для таблицы системного каталога pg_index не было таблицы TOAST. Значения столбцов не были слишком длинными. Теперь у pg_index есть TOAST:

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

     reltoastrelid      
------------------------
 pg_toast.pg_toast_2610
(1 row)

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

COPY… FROM и file_fdw: ограничение на количество ошибочных строк
commit: 4ac2a9bec, a39297ec0, 6c8f67032

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

CREATE TABLE t (id int PRIMARY KEY);

COPY t FROM STDIN (on_error 'ignore', log_verbosity verbose, reject_limit 1);
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:  skipping row due to data type incompatibility at line 2 for column "id": "один"
NOTICE:  skipping row due to data type incompatibility at line 3 for column "id": "два"
ERROR:  skipped more than REJECT_LIMIT (1) rows due to data type incompatibility
CONTEXT:  COPY t, line 3, column id: "два"

Превышение ограничения приводит к ошибке.

Аналогичные изменения сделаны для расширения file_fdw (третий коммит). В предыдущей статье рассказывалось о том, что расширение научилось обрабатывать ошибки преобразования форматов. Теперь, вслед за COPY, можно установить предельное количество ошибок при помощи параметра REJECT_LIMIT.

Недетерминированные правила сортировки: поддержка LIKE
commit: 85b7efa1c

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

CREATE COLLATION ignore_case
  (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

SELECT 'postgres' = 'POSTGRES' COLLATE "ignore_case" AS "ignore_case";

 ignore_case
-------------
 t
(1 row)

Но вот операция LIKE для таких строк раньше не поддерживалась. Исправлено:

SELECT 'PostgreSQL' LIKE 'post%' COLLATE "ignore_case";

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

См. также
Waiting for PostgreSQL 18 – Support LIKE with nondeterministic collations (Hubert 'depesz' Lubaczewski)

TLS v1.3: настройка наборов шифров
commit: 45188c2ea, 3d1ef3a15, 6c66b7443

Добавлен новый параметр ssl_tls13_ciphers для указания списка набора шифров, когда используется TLS версии 1.3. Для предыдущих версий используется прежний параметр ssl_ciphers.

На этом пока всё. Впереди основные события январского коммитфеста.

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