Продолжаем цикл статей с обзором изменений 19 версии. Сегодня о ноябрьском коммитфесте 2025 года.

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

Планировщик: предварительная группировка
Преобразование COUNT(1) и COUNT(not_null_col) в COUNT(*)
Параллельное сканирование по диапазону идентификаторов строк
COPY … TO с секционированными таблицами
Новая функция error_on_null
Вспомогательные функции планировщика для оптимизации функций, возвращающих набор строк (SRF)
Функции в стиле стандарта SQL с временными объектами
Индексы BRIN: применение интерфейса потокового чтения для очистки
WAIT FOR: ожидание синхронизации реплики с основным сервером
Логическая репликация последовательностей
pg_stat_replication_slots: счетчик превышения лимита памяти для декодирования транзакций
pg_buffercache: распределении буферов по страницам ОС
pg_buffercache: пометить буферы «грязными»
Время сброса статистики для отдельных отношений и функций
Мониторинг объема записанных в WAL полных образов страниц
Новый параметр log_autoanalyze_min_duration
psql: путь поиска в приглашении
psql: вывод логических значений
pg_rewind: пропуск копирования уже имеющихся на целевом сервере сегментов WAL
pgbench: продолжение работы после ошибок в командах SQL

Планировщик: предварительная группировка
commit: 8e11859102f

В следующем запросе большая таблица фактов (routes) соединяется с мелким справочником (airplanes_data). Результат группируется по столбцам справочника.

EXPLAIN (costs off)
SELECT a.airplane_code, a.model, count(*)
FROM routes r
     JOIN airplanes_data a USING (airplane_code)
GROUP BY a.airplane_code, a.model;

План запроса в 18-й версии:

                       QUERY PLAN                       
--------------------------------------------------------
 HashAggregate
   Group Key: a.airplane_code
   ->  Hash Join
         Hash Cond: (r.airplane_code = a.airplane_code)
         ->  Seq Scan on routes r
         ->  Hash
               ->  Seq Scan on airplanes_data a
(7 rows)

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

EXPLAIN (costs off)
SELECT a.airplane_code, a.model, r.total
FROM (SELECT r.airplane_code, count(*) AS total
      FROM routes r
      GROUP BY r.airplane_code
     ) r
     JOIN airplanes_data a USING (airplane_code);

                    QUERY PLAN                    
--------------------------------------------------
 Hash Join
   Hash Cond: (a.airplane_code = r.airplane_code)
   ->  Seq Scan on airplanes_data a
   ->  Hash
         ->  Subquery Scan on r
               ->  HashAggregate
                     Group Key: r_1.airplane_code
                     ->  Seq Scan on routes r_1
(8 rows)

А теперь план исходного запроса в 19-й версии:

EXPLAIN (costs off)
SELECT a.airplane_code, a.model, count(*)
FROM routes r
     JOIN airplanes_data a USING (airplane_code)
GROUP BY a.airplane_code, a.model;

                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: a.airplane_code
   ->  Sort
         Sort Key: a.airplane_code
         ->  Nested Loop
               ->  Partial HashAggregate
                     Group Key: r.airplane_code
                     ->  Seq Scan on routes r
               ->  Index Scan using airplanes_data_pkey on airplanes_data a
                     Index Cond: (airplane_code = r.airplane_code)
(10 rows)

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

Оптимизацию можно отключить новым параметром enable_eager_aggregate. По умолчанию она включена.

Но что если в результате предварительной группировки количество строк сильно не уменьшится? Тогда оптимизацию лучше не применять. Управлять этим можно через другой параметр min_eager_agg_group_size. Оптимизация сработает, если по оценкам планировщика среднее значение строк в группах будет не меньше, чем значение параметра. По умолчанию 8, т.е. для срабатывания оптимизации исходный набор должен уменьшиться не менее чем в 8 раз.

Преобразование COUNT(1) и COUNT(not_null_col) в COUNT(*)
commit: 42473b3b312

Если в агрегатную функцию COUNT в качестве параметра передать константу или столбец таблицы, объявленный как NOT NULL, то планировщик автоматически заменит вызов на COUNT(*):

EXPLAIN(verbose, costs off)
SELECT count(1), count(seat_no)
FROM seats;

                       QUERY PLAN                        
---------------------------------------------------------
 Aggregate
   Output: count(*), count(*)
   ->  Seq Scan on bookings.seats
         Output: airplane_code, seat_no, fare_conditions
(4 rows)

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

Замена выполняется во вспомогательной функции планировщика для функции COUNT. А в список структур для реализации во вспомогательных функциях добавлена SupportRequestSimplifyAggref. Подробности можно найти в файле src/include/nodes/supportnodes.h

Параллельное сканирование по диапазону идентификаторов строк
commit: 0ca3b16973a

Сканирование по диапазону идентификаторов строк (Tid Range Scan) появилось в 14-й версии. Теперь эта операция может выполняться параллельно несколькими процессами (Parallel Tid Range Scan):

EXPLAIN(costs off)
SELECT count(*)
FROM bookings
WHERE ctid > '(42,0)';

                      QUERY PLAN                       
-------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Tid Range Scan on bookings
                     TID Cond: (ctid > '(42,0)'::tid)
(6 rows)

COPY … TO с секционированными таблицами
commit: 4bea91f21f6, 266543a6205

До 19-й версии в команде COPY … TO нельзя было указать секционированную таблицу:

CREATE TABLE p (id int) PARTITION BY RANGE (id);
CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (0) TO (100);
INSERT INTO p VALUES (42);

18=# COPY p TO stdout;

ERROR:  cannot copy from partitioned table "p"
HINT:  Try the COPY (SELECT ...) TO variant.

Теперь это работает:

19=# COPY p TO stdout;

42

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

Новая функция error_on_null
commit: 2b75c38b707

Если переданное в новую полиморфную функцию значение не является NULL, то возвращается это значение:

SELECT error_on_null(42),
       error_on_null(ARRAY[1,NULL]),
       error_on_null(ROW());

 error_on_null | error_on_null | error_on_null 
---------------+---------------+---------------
            42 | {1,NULL}      | ()
(1 row)

Иначе ошибка:

SELECT error_on_null(NULL::int);

ERROR:  null value not allowed

Вспомогательные функции планировщика для оптимизации функций, возвращающих набор строк (SRF)
commit: b140c8d7a3f

Если в запросе вызывается функция на языке SQL, то при определенных условиях запрос из функции может быть подставлен в основной запрос. Это может существенно улучшить план запроса. Но для функций на других языках, например PL/pgSQL, такой возможности нет.

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

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

Функции в стиле стандарта SQL с временными объектами
commit: 572c40ba9, 698fa924b11

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

CREATE TEMP TABLE tmp AS SELECT 42 col;

При создании представления будет выдано предупреждение об этом:

CREATE VIEW v_tmp AS SELECT * FROM tmp;

NOTICE:  view "v_tmp" will be a temporary view
CREATE VIEW

Однако при создании функции в стиле стандарта SQL такого предупреждения не было. Функция спокойно создавалась в схеме public:

18=# CREATE FUNCTION f_tmp() RETURNS bigint LANGUAGE sql RETURN (SELECT count(*) FROM tmp);

CREATE FUNCTION

18=# \df 

                       List of functions
 Schema | Name  | Result data type | Argument data types | Type 
--------+-------+------------------+---------------------+------
 public | f_tmp | bigint           |                     | func
(1 row)

А в новых сеансах пропадала:

18=# \c 

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

18=# SELECT f_tmp();

ERROR:  function f_tmp() does not exist
LINE 1: SELECT f_tmp();

В 19-й версии при создании функции будет выдаваться предупреждение о том, что функция временная:

19=# CREATE FUNCTION f_tmp() RETURNS bigint LANGUAGE sql RETURN (SELECT count(*) FROM tmp);

NOTICE:  function "f_tmp" will be effectively temporary
DETAIL:  It depends on temporary table tmp.
CREATE FUNCTION

Индексы BRIN: применение интерфейса потокового чтения для очистки
commit: a6eac2273e6

В 18-й версии интерфейс потокового чтения стал использоваться в том числе и для очистки индексов Btree, GiST и SP-GiST. А теперь и для очистки индексов BRIN.

WAIT FOR: ожидание синхронизации реплики с основным сервером
commit: 447aae13b03

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

И такой механизм появился в виде новой команды SQL WAIT FOR LSN. Принцип работы такой: приложение запоминает текущую позицию LSN на основном сервере после сделанных изменений и дожидается применения этой позиции LSN на реплике при помощи команды WAIT FOR LSN.

На основном сервере создадим таблицу.

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

Запомним текущую позицию LSN. Именно ее применения будем дожидаться на реплике.

primary=# SELECT pg_current_wal_insert_lsn() AS lsn
\gset

Не покидая psql, подключаемся к реплике.

primary=# \connect postgres postgres localhost 5402
replica=# SELECT pg_is_in_recovery();

 pg_is_in_recovery 
-------------------
 t
(1 row)

Для целей демонстрации на реплике настроено отложенное на 30 секунд применение WAL. Поэтому таблица test здесь еще не появилась.

replica=# \dconfig recovery_min_apply_delay

 List of configuration parameters
        Parameter         | Value 
--------------------------+-------
 recovery_min_apply_delay | 30s
(1 row)

replica=# SELECT * FROM test;

ERROR:  relation "test" does not exist

Запускаем команду WAIT FOR LSN и дожидаемся, когда реплика применит запись с запомненным LSN.

replica=# WAIT FOR LSN :'lsn' WITH (MODE 'standby_replay');

 status  
---------
 success
(1 row)

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

replica=# SELECT * FROM test;

 id 
----
 42
(1 row)

Логическая репликация последовательностей
commit: b93172ca59f, 96b37849734, f0b3573c3aa, 5509055d695, f6a4c498dcf, 55cefadde87

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

Создадим последовательность на сервере публикации:

pub=# CREATE SEQUENCE seq;

Выбрать отдельные последовательности для включения в публикацию (пока?) нельзя, команда CREATE PUBLICATION включает в публикацию все последовательности:

pub=# CREATE PUBLICATION pub FOR ALL SEQUENCES;

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

sub=# CREATE SEQUENCE seq;
sub=# CREATE SUBSCRIPTION sub CONNECTION 'port=5401 dbname=postgres' PUBLICATION pub;

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

pub=# SELECT nextval('seq') FROM generate_series(1,3);

 nextval 
---------
       1
       2
       3
(3 rows)

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

pub=# SELECT * FROM pg_get_sequence_data('seq');

 last_value | is_called |  page_lsn  
------------+-----------+------------
          3 | t         | 2/650B51C0
(1 row)

Первые два столбца легко получить, просто выполнив запрос к последовательности, как к таблице. А последний — page_lsn — показывает LSN записи WAL, которая последний раз изменяла значение последовательности.

Состояние последовательности на подписчике можно посмотреть в pg_subscription_rel:

sub=# SELECT srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel;

 srrelid | srsubstate |  srsublsn  
---------+------------+------------
 seq     | r          | 2/650B3270
(1 row)

Если последовательности синхронизированы между серверами, то значение srsublsn должно совпасть с вышеприведенным page_lsn на сервере публикации. Сейчас это не так. Потому что продвижение последовательности на сервере публикации автоматически не переносится на сервер подписки. Требуется ручная синхронизация, которую можно выполнить командой:

sub=# ALTER SUBSCRIPTION sub REFRESH SEQUENCES;

sub=# SELECT srrelid::regclass, srsubstate, srsublsn FROM pg_subscription_rel;

 srrelid | srsubstate |  srsublsn  
---------+------------+------------
 seq     | r          | 2/650B51C0
(1 row)

Другая команда ALTER SUBSCRIPTION ... REFRESH PUBLICATION дополнительно включит в подписку и синхронизирует все новые последовательности, появившиеся на сервере публикации. Работу по синхронизации последовательностей выполняет процесс sequence synchronization worker.

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

sub=# SELECT * FROM seq;

 last_value | log_cnt | is_called 
------------+---------+-----------
          3 |       0 | t
(1 row)

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

SELECT subname, sync_seq_error_count
FROM pg_stat_subscription_stats
WHERE subname = 'sub';

 subname | sync_seq_error_count 
---------+----------------------
 sub     |                    0
(1 row)

pg_stat_replication_slots: счетчик превышения лимита памяти для декодирования транзакций
commit: d3b6183dd98

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

Ответить на этот вопрос поможет новый столбец mem_exceeded_count в представлении pg_stat_replication_slots. Значение столбца — счетчик, показывающий сколько раз не хватало памяти logical_decoding_work_mem и приходилось использовать диск. Эта информация пригодится для настройки logical_decoding_work_mem.

\d pg_stat_replication_slots

                   View "pg_catalog.pg_stat_replication_slots"
       Column        |           Type           | Collation | Nullable | Default 
---------------------+--------------------------+-----------+----------+---------
 slot_name           | text                     |           |          | 
 spill_txns          | bigint                   |           |          | 
 spill_count         | bigint                   |           |          | 
 spill_bytes         | bigint                   |           |          | 
 stream_txns         | bigint                   |           |          | 
 stream_count        | bigint                   |           |          | 
 stream_bytes        | bigint                   |           |          | 
 mem_exceeded_count  | bigint                   |           |          | 
 total_txns          | bigint                   |           |          | 
 total_bytes         | bigint                   |           |          | 
 slotsync_skip_count | bigint                   |           |          | 
 slotsync_last_skip  | timestamp with time zone |           |          | 
 stats_reset         | timestamp with time zone |           |          | 

pg_buffercache: распределении буферов по страницам ОС
commit: 4b203d499c6

Появившееся в 18-й версии представление pg_buffercache_numa показывает распределение каждого буфера общего буферного кеша по узлам NUMA (numa_node), а также по страницам операционной системы (os_page_num):

CREATE EXTENSION pg_buffercache;

SELECT *
FROM pg_buffercache_numa          
WHERE bufferid = 42;

 bufferid | os_page_num | numa_node 
----------+-------------+-----------
       42 |          82 |         0
       42 |          83 |         0
(2 rows)

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

SELECT *
FROM pg_buffercache_os_pages
WHERE bufferid = 42;

 bufferid | os_page_num 
----------+-------------
       42 |          82
       42 |          83
(2 rows)

pg_buffercache: пометить буферы «грязными»
commit: 9ccc049dfe6

В 17-й и 18-й версиях в расширение pg_buffercache были добавлены функции для вытеснения буферов из кеша на диск: pg_buffercache_evict, pg_buffercache_evict_relation, pg_buffercache_evict_all.

Теперь, в основном для целей тестирования, добавлены функции для пометки буфера «грязным»: pg_buffercache_mark_dirty, pg_buffercache_mark_dirty_relation, pg_buffercache_mark_dirty_all. Первая помечает грязным один указанный буфер, вторая — все буферы указанного отношения, третья — все буферы кеша.

Время сброса статистики для отдельных отношений и функций
commit: a5b543258aa, b71bae41a0c

Функция pg_stat_reset_single_table_counters позволяет сбросить накопленную статистику для отдельного отношения (таблицы, индекса, …). Аналогичная функция

pg_stat_reset_single_function_counters сбрасывает статистику отдельной функции. Однако дата/время сброса нигде не фиксировались.

Теперь в представления pg_stat_{all|user}_{tables|indexes}, а также pg_stat_user_functions добавлен столбец stats_reset.

SELECT pg_stat_reset_single_table_counters('bookings'::regclass);

 pg_stat_reset_single_table_counters 
-------------------------------------

(1 row)

SELECT stats_reset
FROM pg_stat_user_tables
WHERE relid = 'bookings'::regclass;

          stats_reset          
-------------------------------
 2026-01-13 22:08:12.686371+03
(1 row)

Мониторинг объема записанных в WAL полных образов страниц
commit: ad25744f436, f9a09aa29520

В общесистемное представление pg_stat_wal и функцию pg_stat_get_backend_wal добавлен столбец wal_fpi_bytes. Он показывает общий размер записанных в WAL полных образов страниц.

CREATE TABLE bookings_copy
  AS SELECT * FROM bookings;

SELECT 4905238

SELECT *
FROM pg_stat_get_backend_wal(pg_backend_pid())
\gx

-[ RECORD 1 ]----+----------
wal_records      | 9842578
wal_fpi          | 160
wal_bytes        | 759246449
wal_fpi_bytes    | 1014192
wal_buffers_full | 84566
stats_reset      | 

Также значение full page image bytes добавлено в вывод команд VACUUM и ANALYZE с параметром verbose, а также в журнал сервера:

VACUUM(verbose) bookings_copy;

INFO:  vacuuming "demo.bookings.bookings_copy"
…
WAL usage: 31248 records, 4 full page images, 1876574 bytes, 32768 full page image bytes, 0 buffers full
…
VACUUM

Новый параметр log_autoanalyze_min_duration
commit: dd3ae378301

Параметр log_autovacuum_min_duration срабатывал при превышении рабочим процессом автоочистки заданного времени выполнения как очистки, так и сбора статистики.

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

\dconfig+ log_auto*_min_duration

                       List of configuration parameters
          Parameter           | Value |  Type   | Context | Access privileges 
------------------------------+-------+---------+---------+-------------------
 log_autoanalyze_min_duration | 10min | integer | sighup  | 
 log_autovacuum_min_duration  | 10min | integer | sighup  | 
(2 rows)

psql: путь поиска в приглашении
commit: b3ce55f413c

В приглашение psql теперь можно включить путь поиска, точнее, значение параметра search_path. Для этого в переменные PROMPT1 и PROMPT2 нужно добавить %S.

postgres@demo=# \set PROMPT1 '%n%@%/:%S%R%x%# '
postgres@demo:bookings, "$user", public=# 
postgres@demo:bookings, "$user", public=# SET search_path = pg_catalog;

SET

postgres@demo:pg_catalog=# 

psql: вывод логических значений
commit: 645cb44c549

psql выводит истину и ложь как t и f:

SELECT 1=1, 1<>1;

 ?column? | ?column? 
----------+----------
 t        | f
(1 row)

Новые переменные команды \pset позволяют переопределить отображение логических значений.

\pset display_true 'True'
\pset display_false 'False'

SELECT 1=1, 1<>1;

 ?column? | ?column? 
----------+----------
 True     | False
(1 row)

Стоит напомнить, что вывод значений NULL переопределяется через \pset null.

pg_rewind: пропуск копирования уже имеющихся на целевом сервере сегментов WAL
commit: 6ae08d9583e, 5173bfd0443

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

pgbench: продолжение работы после ошибок в командах SQL
commit: 0ab208fa505

Утилита pgbench теперь может продолжать работу не только после ошибок сериализации или взаимоблокировок, но и после других ошибок в командах SQL. Это пригодится при работе с собственными скриптами тестирования.

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

CREATE TABLE test (id int PRIMARY KEY);

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

$ cat load.sql 

INSERT INTO test VALUES (random(1,100));

Очевидно, что многократное выполнение скрипта будет приводить к ошибкам уникальности. Чтобы их игнорировать, запустим pgbench с новым параметром --continue-on-error, а параметр --failures-detailed покажет процент ошибочных транзакций в строке number of other failures.

$ pgbench -d postgres -f load.sql --continue-on-error --failures-detailed -t 100

pgbench (19devel)
starting vacuum...end.
transaction type: load.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 65/100
number of failed transactions: 35 (35.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of other failures: 35 (35.000%)
latency average = 0.519 ms (including failures)
initial connection time = 4.194 ms
tps = 1251.974267 (without initial connection time)

Из 100 выполненных транзакций 35 завершились ошибкой, значит, в таблице сейчас 65 записей, что легко проверить:

$ psql -d postgres -c 'SELECT count(*) FROM test'

 count 
-------
    65
(1 row)

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