PostgreSQL 18: Часть 5 или Коммитфест 2025-03 / forpes.ru

Главная
PostgreSQL 18: Часть 5 или Коммитфест 2025-03

PostgreSQL 18: Часть 5 или Коммитфест 2025-03 +13

09.09.2025 15:09

pluzanov 0 1100 Источник

25 сентября ожидается выход PostgreSQL 18. Эта статья о мартовском коммитфесте завершает описание новых возможностей 18-й версии. Статья получилась большая, ведь последний мартовский коммитфест по традиции наиболее объемный и богатый на новинки.

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

Утилиты pg_dump и pg_dumpall теперь могут выгружать базовую статистику таблиц, столбцов и индексов. А pg_restore, соответственно, научилась ее загружать. Под базовой статистикой понимается основная статистика, хранящаяся в pg_statistic. Расширенная статистика по-прежнему выгружается в виде команд CREATE STATISTICS и будет заново собираться при восстановлении.

При запуске pg_dump/pg_dumpall можно указать один из трех параметров:

$ pg_dump --with-statistics | --no-statistics | --statistics-only

По умолчанию статистика не выгружается для обратной совместимости.

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

CREATE TABLE tab (id int PRIMARY KEY);
INSERT INTO tab VALUES (1),(2),(3);
ANALYZE tab;

Запустим pg_dump с параметром --statistics-only и оставим в выводе только строки относящиеся к статистике:

$ pg_dump -t tab --statistics-only |egrep -v '^($|--|SET|.*set_config)'

SELECT * FROM pg_catalog.pg_restore_relation_stats(
    'version', '190000'::integer,
    'schemaname', 'public',
    'relname', 'tab',
    'relpages', '1'::integer,
    'reltuples', '3'::real,
    'relallvisible', '0'::integer,
    'relallfrozen', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
    'version', '190000'::integer,
    'schemaname', 'public',
    'relname', 'tab',
    'attname', 'id',
    'inherited', 'f'::boolean,
    'null_frac', '0'::real,
    'avg_width', '4'::integer,
    'n_distinct', '-1'::real,
    'histogram_bounds', '{1,2,3}'::text,
    'correlation', '1'::real
);
SELECT * FROM pg_catalog.pg_restore_relation_stats(
    'version', '190000'::integer,
    'schemaname', 'public',
    'relname', 'tab_pkey',
    'relpages', '2'::integer,
    'reltuples', '3'::real,
    'relallvisible', '0'::integer,
    'relallfrozen', '0'::integer
);

Для отношений (таблиц и индексов) сформирован вызов новой функции pg_restore_relation_stats, а для статистики столбцов — pg_restore_attribute_stats.

При восстановлении через pg_restore доступны те же три параметра:

$ pg_restore --with-statistics | --no-statistics | --statistics-only

По умолчанию используется другое значение --with-statistics. Здесь идея в том, что всё что включено в копию, включая статистику, по умолчанию нужно восстановить.

Сбор статистики после обновления сервера
commit: 1fd1bd871, 9c03c8d18, edba754f0, d5f1b6a75

Утилита pg_upgrade использует pg_dump/pg_restore для переноса схемы данных в новый кластер. В 18-й версии pg_upgrade будет по умолчанию запускать pg_dump c параметром --with-statistics, а значит базовая статистика будет переноситься на обновленный кластер.

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

Пункт 15 процедуры обновления сервера в документации pg_upgrade рекомендует после обновления сервера сначала собрать отсутствующую статистику:

$ vacuumdb --all --analyze-in-stages --missing-stats-only

Где --missing-stats-only новый параметр утилиты, обрабатывающий только объекты, где нет собранной статистики.

А уже затем выполнить:

$ vacuumdb --all --analyze-only

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

Если по какой-то причине статистику не нужно переносить, то для этого у pg_upgrade есть параметр --no-statistics.

pg_upgrade --swap: перемещение каталогов из старого кластера в новый
commit: 626d7236b

Несколько параметров pg_upgrade предназначены для выбора способа переноса файлов данных из старого кластера в новый: --copy, --copy-file-range, --clone, --link.

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

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

Эта работа продолжает серию оптимизаций в 18-й версии для обновления кластеров с очень большим количеством баз данных и объектов в них. О предыдущих оптимизациях можно прочитать здесь и здесь.

pg_combinebackup --link или жесткие ссылки вместо копирования файлов
commit: 99aeb8470

Новый параметр --link утилиты pg_combinebackup позволит сэкономить дисковое пространство и избежать длительного копирования файлов. Файлы выходной полной резервной копии будут созданы как жесткие ссылки на файлы исходных копий.

Этой возможностью следует пользоваться осторожно. Любое изменение файлов в выходной резервной копии (например после запуска сервера) также повлияет на файлы в исходных резервных копиях. И наоборот, изменение файлов в исходных копиях повлияет на восстановленную полную копию.

pg_dump[all], pg_restore: --no-policies
commit: cd3c45125

Новый параметр --no-policies утилит pg_dump и pg_dumpall позволяет не включать в резервные копии команды на создание и включение политик защиты строк. Утилита pg_restore с аналогичным параметром не будет выполнять эти команды.

pg_createsubscriber: включение параметра two_phase для всех подписок
commit: e117cfb2f

Новый параметр --enable-two-phase утилиты pg_createsubscriber включает параметр two_phase у всех созданных подписок.

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

pg_createsubscriber: удаление публикаций на подписчике
commit: e5aeed4b8

После окончании работы pg_createsubscriber на подписчике остаются публикации, которые были перенесены на него, пока сервер-подписчик еще был физической репликой. Если публикации не нужны, то новый параметр --clean=publications утилиты pg_createsubscriber удалит их.

В будущем, в параметре --clean могут появится другие типы объектов для удаления, например слоты репликации. Пока поддерживается только значение publications.

pg_createsubscriber: создание подписок для всех баз данных сервера публикации
commit: fb2ea12f4

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

Если же требуется реплицировать все базы данных, то можно использовать новый параметр --all, вместо -d. С параметром --all, для каждой базы данных сервера публикации (кроме шаблонных и тех, к которым запрещено подключение) будет создана отдельная подписка.

psql: конвейерный режим работы
commit: 41625ab8e, 3ce357584, 17caf6644, 2cce0fe44

Конвейерный режим работы поддерживается библиотекой libpq, начиная с 14-й версии. В этом режиме несколько команд SQL могут быть отправлены на выполнение, не дожидаясь ответа сервера. Результат их работы можно получить позже. Если сетевые задержки между клиентом и сервером значительные, то конвейерный режим позволяет добиться существенной оптимизации за счет сокращения времени на получение ответов сервера.

В 18-й версии в psql появились команды для реализации конвейерного режима работы.

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

$ sudo tc qdisc add dev lo root netem delay 500ms

Тестовый скрипт nopipeline.sql включает три простых запроса в одной транзакции:

$ cat pg18/nopipeline.sql

BEGIN;
SELECT clock_timestamp();
SELECT clock_timestamp();
SELECT clock_timestamp();
COMMIT;

Замерим время выполнения:

$ time psql -X -t -h localhost -f pg18/nopipeline.sql

BEGIN
 2025-06-20 15:14:09.353107+03

 2025-06-20 15:14:10.353771+03

 2025-06-20 15:14:11.35475+03

COMMIT

real    0m7,015s
user    0m0,001s
sys    0m0,006s

Другой скрипт pipeline.sql вызывает nopipeline.sql, помещенный между соответствующих команд конвейерного режима:

$ cat pg18/pipeline.sql

\startpipeline

\ir nopipeline.sql

\flushrequest
\getresults
\endpipeline

Общее время выполнения заметно сокращается:

$ time psql -X -t -h localhost -f pg18/pipeline.sql

BEGIN
 2025-06-20 15:14:20.610045+03

 2025-06-20 15:14:20.610109+03

 2025-06-20 15:14:20.610149+03

COMMIT

real    0m4,014s
user    0m0,001s
sys    0m0,006s

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

psql: информация о текущем подключении
commit: bba2fbc62

Вывод команды \conninfo существенно переработан и включает много дополнительной информации:

\conninfo
     Connection Information
      Parameter       |  Value   
----------------------+----------
 Database             | demo
 Client User          | postgres
 Socket Directory     | /tmp
 Server Port          | 5401
 Options              | 
 Protocol Version     | 3.0
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 28907
 TLS Connection       | false
 Superuser            | on
 Hot Standby          | off
(12 rows)

psql: настройка умолчания для интервала времени в команде \watch
commit: 1a759c832

По умолчанию запрос в команде \watch повторяется через 2 секунды. Интервал времени можно указать прямо в команде \watch. А теперь можно изменить умолчание, задав значение новой переменной psql WATCH_INTERVAL.

\echo :WATCH_INTERVAL

\set WATCH_INTERVAL 1

\t
SELECT to_char(now(), 'SS')\watch count=3

psql: \dx показывает версию расширения по умолчанию
commit: d696406a9

После установки новой версии расширения в файловой системе, нужно не забыть выполнить ALTER EXTENSION .. UPDATE. Новый столбец «Default version» в выводе команды \dx быстро подскажет, если установленная версия отличается от версии по умолчанию:

\dx

                          List of installed extensions
  Name   | Version | Default version |   Schema   |         Description          
---------+---------+-----------------+------------+------------------------------
 plpgsql | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(1 row)

NUMA: инструменты мониторинга систем с архитектурой неоднородного доступа к памяти
commit: 65c298f61, 8cc139bec, ba2a3c230

В 18-й версии появились средства мониторинга систем, поддерживающих архитектуру неоднородного доступа к памяти (NUMA). Пока только для ОС Linux. Сервер PostgreSQL должен быть собран с параметром --with-libnuma.

Проверить поддержку можно функцией:

SELECT pg_numa_available();

 pg_numa_available 
-------------------
 t
(1 row)

Представление pg_shmem_allocations_numa показывает как общая память распределена по узлам NUMA:

SELECT * FROM pg_shmem_allocations_numa LIMIT 3;

       name       | numa_node |  size  
------------------+-----------+--------
 commit_timestamp |         0 | 274432
 multixact_member |         0 | 270336
 multixact_offset |         0 | 139264
(3 rows)

А в расширении pg_buffercache появилось представление pg_buffercache_numa, показывающее распределение буферов общей памяти по узлам NUMA:

CREATE EXTENSION pg_buffercache;

SELECT * FROM pg_buffercache_numa LIMIT 3;

 bufferid | os_page_num | numa_node 
----------+-------------+-----------
        1 |           0 |         0
        1 |           1 |         0
        2 |           2 |         0
(3 rows)

pg_stat_get_backend_wal: статистика WAL для отдельного процесса
commit: 76def4cdd

В прошлой статье рассказывалось о функции pg_stat_get_backend_io для получения статистики ввода/вывода отдельного клиентского процесса. Теперь появилась похожая функция pg_stat_get_backend_wal для получения статистики WAL для клиентских процессов. Функция возвращает одну строку такой же структуры как представление pg_stat_wal:

SELECT * FROM pg_stat_get_backend_wal(pg_backend_pid())
\gx

-[ RECORD 1 ]----+-------
wal_records      | 161
wal_fpi          | 48
wal_bytes        | 228461
wal_buffers_full | 0
stats_reset      |

Одно из полезных применений — запросы с соединением pg_stat_activity и pg_stat_get_backend_wal. Например следующий запрос показывает самые активные, пишущие в WAL клиентские процессы:

SELECT *
FROM pg_stat_activity a,
     pg_stat_get_backend_wal(a.pid) b
WHERE a.backend_type = 'client backend'
ORDER BY b.wal_bytes DESC;

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

EXPLAIN: фактическое число строк с точностью до двух знаков после запятой
commit: ddb17e387, 95dbd827f

При анализе плана выполнения запроса может потребоваться рассчитывать кардинальность отдельных узлов плана. Для расчета кардинальности нужно умножать фактическое количество строк (actual rows) на количество итераций (loops).

Вывод фактического количества строк в команде EXPLAIN ANALYZE всегда обрезался до целого числа. В ряде случаев это может существенно повлиять на расчет, например если 0,5 обрезается до 0. Сам планировщик использует точное дробное значение, но в плане этого не было видно.

Теперь вывод фактического количества строк для всех узлов плана включает два знака после запятой:

EXPLAIN (analyze, costs off, buffers off, summary off, timing off)
SELECT count(*) FROM tickets;

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1.00 loops=3)
               ->  Parallel Seq Scan on tickets (actual rows=983285.67 loops=3)
(6 rows)

EXPLAIN: интерфейс для добавления команде новых параметров
commit: c65bc2e1d, 50ba65e73, 8d5ceb113

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

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

В качестве примера такого расширения в contrib добавлено pg_overexplain. Это расширение добавляет два параметра в EXPLAIN: debug и range_table. Расширение не создает объектов в базе данных, поэтому выполнять CREATE EXTENSION не требуется. Достаточно загрузить модуль pg_overexplain, например в текущем сеансе:

LOAD 'pg_overexplain';

Теперь можно указывать новые параметры EXPLAIN:

EXPLAIN (debug)
SELECT * FROM bookings;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Seq Scan on bookings  (cost=0.00..34558.10 rows=2111110 width=21)
   Disabled Nodes: 0
   Parallel Safe: true
   Plan Node ID: 0
 PlannedStmt:
   Command Type: select
   Flags: canSetTag
   Subplans Needing Rewind: none
   Relation OIDs: 16422
   Executor Parameter Types: none
   Parse Location: 0 to end
(11 rows)

EXPLAIN (range_table)
SELECT * FROM bookings;

                            QUERY PLAN                             
-------------------------------------------------------------------
 Seq Scan on bookings  (cost=0.00..34558.10 rows=2111110 width=21)
   Scan RTI: 1
 RTI 1 (relation, in-from-clause):
   Eref: bookings (book_ref, book_date, total_amount)
   Relation: bookings
   Relation Kind: relation
   Relation Lock Mode: AccessShareLock
   Permission Info Index: 1
 Unprunable RTIs: 1
(9 rows)

Какую именно дополнительную информацию выводят эти два параметра можно посмотреть в документации к расширению и/или в исходном коде.

Журналирование неудачных попыток захватить блокировку
commit: 6d376c3b0

Включение нового параметра log_lock_failures позволит записывать в журнал сервера детальную информацию о невозможности получить блокировку командой SELECT .. NOWAIT.

В первом сеансе захватим блокировку строки:

BEGIN;
SELECT pg_current_xact_id(), pg_backend_pid();

 pg_current_xact_id | pg_backend_pid 
--------------------+----------------
                981 |         112875
(1 row)

SELECT * FROM bookings WHERE book_ref = '000004' FOR UPDATE NOWAIT;

 book_ref |       book_date        | total_amount 
----------+------------------------+--------------
 000004   | 2016-08-13 15:40:00+03 |     55800.00
(1 row)

Во втором сеансе включим новый параметр и попробуем получить блокировку этой же строки:

SET log_lock_failures = on;

BEGIN;
SELECT pg_current_xact_id(), pg_backend_pid();

 pg_current_xact_id | pg_backend_pid 
--------------------+----------------
                982 |         113146
(1 row)

SELECT * FROM bookings WHERE book_ref = '000004' FOR UPDATE NOWAIT;

ERROR:  could not obtain lock on row in relation "bookings"
ROLLBACK;

Детальная информация о невозможности захватить блокировку записана в журнал сервера:

\! tail -5 logfile

2025-08-07 11:06:16.882 MSK [113146] LOG:  process 113146 could not obtain ShareLock on transaction 981
2025-08-07 11:06:16.882 MSK [113146] DETAIL:  Process holding the lock: 112875, Wait queue: .
2025-08-07 11:06:16.882 MSK [113146] STATEMENT:  SELECT * FROM bookings WHERE book_ref = '000004' FOR UPDATE NOWAIT;
2025-08-07 11:06:16.882 MSK [113146] ERROR:  could not obtain lock on row in relation "bookings"
2025-08-07 11:06:16.882 MSK [113146] STATEMENT:  SELECT * FROM bookings WHERE book_ref = '000004' FOR UPDATE NOWAIT;

Журналирование времени на подключение нового сеанса
commit: 9219093ca, 18cd15e70, cb1456423

Параметр log_connections отвечает за журналирование информации о подключении новых сеансов. Раньше значения параметра были логическими (вкл/выкл). Теперь это список значений для журналирования различных аспектов подключения: receipt, authentication, authorization, setup_durations или all.

Если журналировать подключения не нужно, значение параметра должно быть пустой строкой. Список из первых трех значений (receipt, authentication, authorization) соответствует включенному параметру в предыдущих версиях.

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

Пример журналирования подключения (log_connections = 'all'):

2025-08-07 12:13:05.100 MSK [115446] LOG:  connection received: host=[local]
2025-08-07 12:13:05.101 MSK [115446] LOG:  connection authenticated: user="postgres" method=trust (/home/pal/master/data/pg_hba.conf:117)
2025-08-07 12:13:05.101 MSK [115446] LOG:  connection authorized: user=postgres database=demo application_name=psql
2025-08-07 12:13:05.102 MSK [115446] LOG:  connection ready: setup total=2.234 ms, fork=0.413 ms, authentication=0.208 ms

Для обратной совместимости логические значения on, off, true, false, yes, no, 1, 0 пока поддерживаются, но объявлены как устаревшие.

log_line_prefix: IP-адрес локального сервера
commit: 3516ea768

В параметре listen_addresses можно указать несколько адресов, по которым сервер будет принимать подключения клиентов. Если необходимо понимать по какому из этих адресов подключился клиент, то в параметре log_line_prefix можно использовать новый спецсимвол %L.

pg_stat_statements: нормализация команд со списками констант в IN
commit: 62d712ecf, 9fbd53dea

До 18-й версии следующие три запроса занимали три строки в pg_stat_statements. Хотя различаются они только числом констант в условии IN.

SELECT pg_stat_statements_reset();

SELECT * FROM flights WHERE flight_id IN (1,2);
SELECT * FROM flights WHERE flight_id IN (1,2,3);
SELECT * FROM flights WHERE flight_id IN (1,2,3,4);

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

SELECT queryid, query, calls
FROM pg_stat_statements
WHERE query ~ 'flights'\gx

-[ RECORD 1 ]-----------------------------------------------------
queryid | 7334393817289890276
query   | SELECT * FROM flights WHERE flight_id IN ($1 /*, ... */)
calls   | 3

Дополнительные инструменты мониторинга переполнения буфера WAL
commit: eaf502747, ce5bcc4a9, 320545bfc, 6a8a7ce47

Еще в 14-й версии появилось представление pg_stat_wal со столбцом wal_buffers_full. Столбец показывает сколько раз данные сбрасывались на диск из-за переполнения буфера WAL, что помогает подобрать значение параметру wal_buffers.

Теперь счетчик wal_buffers_full доступен и на более низких уровнях. Он появился в pg_stat_statements, командах VACUUM/ANALYZE с параметром verbose, а также в EXPLAIN с параметром wal:

CREATE TABLE bookings_copy (LIKE bookings);

EXPLAIN (wal, analyze, summary off, buffers off, timing off, costs off)
INSERT INTO bookings_copy SELECT * FROM bookings;

                         QUERY PLAN                          
-------------------------------------------------------------
 Insert on bookings_copy (actual rows=0.00 loops=1)
   WAL: records=2111110 bytes=164490318 buffers full=18464
   ->  Seq Scan on bookings (actual rows=2111110.00 loops=1)
(3 rows)

Отслеживание времени простоя при выполнении очистки и анализа
commit: bb8dff999, 7720082ae

Новый параметр позволит отслеживать время простоя при выполнении очистки и анализа:

\dconfig+ track_cost_delay_timing

                    List of configuration parameters
        Parameter        | Value | Type |  Context  | Access privileges 
-------------------------+-------+------+-----------+-------------------
 track_cost_delay_timing | off   | bool | superuser | 
(1 row)

С настройками по умолчанию, команды VACUUM и ANALYZE выполняются без задержек. А вот для автоматической очистки и анализа время простоя предусмотрено.

\dconfig *vacuum_cost_(delay|limit)

   List of configuration parameters
          Parameter           | Value 
------------------------------+-------
 autovacuum_vacuum_cost_delay | 2ms
 autovacuum_vacuum_cost_limit | -1
 vacuum_cost_delay            | 0
 vacuum_cost_limit            | 200
(4 rows)

Подбирая оптимальные сочетания параметров *cost_limit и *cost_delay теперь можно ориентироваться на соотношение общего времени выполнения и времени простоя.

Информация о простое доступна в нескольких источниках:

в представлениях pg_stat_progress_vacuum и pg_stat_progress_analyze появился новый столбец delay_time;
команды VACUUM и ANALYZE с параметром verbose выводят строку delay time;
строка delay time выводится в журнале сервера для операций очистки и анализа.

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

ALTER SYSTEM SET track_cost_delay_timing = on;
ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

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

CREATE TABLE tickets_copy AS SELECT * FROM tickets;

SELECT 2949857

Через некоторое время в журнале сервера появились строки delay time для очистки и анализа новой таблицы:

2025-06-30 16:47:54.144 MSK [211678] LOG:  automatic vacuum of table "demo.bookings.tickets_copy": index scans: 0
…
    delay time: 11564.377 ms
…
2025-06-30 16:47:55.488 MSK [211678] LOG:  automatic analyze of table "demo.bookings.tickets_copy"
    delay time: 622.403 ms
…

vacuum_truncate: управление обрезанием пустых страниц в конце таблицы
commit: 0164a0f9e

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

В 18-й версии появился новый параметр vacuum_truncate, который включает (по умолчанию) или отключает обрезание пустых страниц для всех таблиц. Его действие распространяется как на ручную очистку, так и на автоматическую.

Стоит напомнить, что до 18-й версии было две возможности отключить обрезание пустых страниц. Можно выполнить очистку вручную с выключенным параметром truncate:

VACUUM(truncate off);

Или отключить параметр truncate для отдельных таблиц:

ALTER TABLE таблица SET (vacuum_truncate=off);

Более частая автоочистка «мертвых» строк в больших таблицах
commit: 306dc520b

Автоочистка приступает к обработке таблицы, когда количество «мертвых» строк превысит значение рассчитанное по формуле:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × число_строк_в_таблице

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

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

least(
  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × число_строк_в_таблице,
  autovacuum_vacuum_max_threshold
)

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

\dconfig+ autovacuum_vacuum_[m|s|t]*

                          List of configuration parameters
            Parameter            |   Value   |  Type   | Context | Access privileges 
---------------------------------+-----------+---------+---------+-------------------
 autovacuum_vacuum_max_threshold | 100000000 | integer | sighup  | 
 autovacuum_vacuum_scale_factor  | 0.2       | real    | sighup  | 
 autovacuum_vacuum_threshold     | 50        | integer | sighup  | 
(3 rows)

С настройками по умолчанию, новый параметр применяется к таблицам, размер которых превышает 500 миллионов строк.

Более частая автоочистка после вставки новых строк
commit: 99f8f3fbb, 06eae9e62

Автоочистка срабатывает не только на рост количества «мертвых» строк, но и на вставку новых строк. Таблицы, в которые данные активно добавляются, обрабатываются автоочисткой по следующему принципу. Количество вставленных строк с момента предыдущей автоочистки должно превысить:

autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × число_строк_в_таблице

С ростом таблицы, автоочистка будет приходить все реже и реже. Чтобы автоочистка обрабатывала такие таблицы чаще, в 18-й версии расчет изменился. Теперь в формуле используется доля (autovacuum_vacuum_insert_scale_factor) не от общего числа строк в таблице, а от количества незамороженных строк в таблице. Точнее доля от количества незамороженных страниц.

autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × число_незамороженных_страниц_в_таблице

Для быстрого расчета этого значения в pg_class, к уже имеющемуся столбцу relpages, добавлен столбец relallfrozen.

Нетерпеливая заморозка в помощь агрессивной очистке
commit: 052026c9b

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

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

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

Чтобы не делать всю работу по заморозке за раз, нетерпеливая очистка прекращает обработку страниц c признаком all_visible в случае, если количество успешно замороженных страниц достигнет 20% (константа в коде) от общего количества all_visible, но не all_frozen страниц в таблице.

Нетерпеливая очистка также прекратит сканирование all_visible страниц, если количество неуспешных попыток установить страницам признак all_frozen превысит значение нового параметра vacuum_max_eager_freeze_failure_rate. По умолчанию это 3% от общего числа страниц в таблице:

\dconfig+ vacuum_max_eager_freeze_failure_rate

                         List of configuration parameters
              Parameter               | Value | Type | Context | Access privileges 
--------------------------------------+-------+------+---------+-------------------
 vacuum_max_eager_freeze_failure_rate | 0.03  | real | user    | 
(1 row)

Значение 0 отключает нетерпеливый режим работы очистки.

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

Асинхронный ввод/вывод
commit: fdd146a8e, 93bc3d75d, 46250cdcb, 60f566b4f, 2a5e709e7, ae3df4b34, f4d0730bb, b27f8637e, 12ce89fd0, 047cba7fa, 50cb7505b, c325a7633, 247ce06b8, 55b454d0e, da7226993, …

Асинхронный ввод/вывод в PostgreSQL теперь реальность. Метод выполнения асинхронных операций определяет параметром io_method:

\dconfig+ io_method

              List of configuration parameters
 Parameter | Value  | Type |  Context   | Access privileges 
-----------+--------+------+------------+-------------------
 io_method | worker | enum | postmaster | 
(1 row)

Всего доступно три метода.

Первый — worker — доступен на всех платформах и используется по умолчанию. Асинхронные операции выполняются отдельными процессами. Количество процессов настраивается в параметре io_workers, по умолчанию 3:

SELECT pid 
FROM pg_stat_activity 
WHERE backend_type = 'io worker';

  pid  
-------
 17630
 17631
 17632
(3 rows)

Второй метод — io_uring — доступен на платформах Linux, где обеспечивает максимальную эффективность. Для его использования сервер должен быть собран с параметром --with-liburing.

Третий метод — sync — выполнение операций в синхронном режиме. Это текущее поведение.

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

Техническое описание реализации можно прочитать в README.md.

io_combine_limit: максимальный размер увеличен до 1МБ
commit: 10f664684, 06fb5612c

Появившийся в 17-й версии интерфейс потокового чтения опирается на параметр io_combine_limit. Его значение по умолчанию — 128КБ. Значение можно было увеличить максимум в два раза до 256КБ.

В 18-й версии значение по умолчанию осталось прежним, но его можно увеличить до 1МБ. Также оно не должно превышать значение нового параметра io_max_combine_limit, для изменения которого требуется перезапуск сервера.

\dconfig+ io_*combine_limit

                    List of configuration parameters
      Parameter       | Value |  Type   |  Context   | Access privileges 
----------------------+-------+---------+------------+-------------------
 io_combine_limit     | 128kB | integer | user       | 
 io_max_combine_limit | 128kB | integer | postmaster | 
(2 rows)

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

Применение интерфейса потокового чтения
commit: 32acad7d1, 925682260, c3e775e60, c5c239e26, 69273b818, e215166c9, 2b73a8cd3, d9c7911e1, 043799fa0

Интерфейс потокового чтения теперь используется не только при последовательном сканировании и анализе таблиц (с 17-й версии), но и для следующих операций:

очистка таблиц и индексов (Btree, GiST, SP-GiST);
сканирование таблицы по битовой карте (Bitmap Heap Scan);
прогрев буферного кеша (pg_prewarm);
проверка целостности отношений (функция verify_heapam модуля amcheck).

Индексы BTree: пропуск условия по лидирующему столбцу в индексе
commit: 9a2e2a285, 92fe23d93, 0fbceae84

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

В таблице продаж можно анализировать данные в двух разрезах: по отделам (deptno_id) и продуктам (product_id):

CREATE TABLE sales (
   deptno_id int,
   product_id int,
   amount numeric(20,2)
);

INSERT INTO sales
SELECT random(1,100)  AS deptno_id,
       random(1,1000) AS product_id,
       random (0.01, 10_000.00) AS amount
FROM generate_series(1, 10_000_000);

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

CREATE INDEX idx ON sales (deptno_id, product_id);
VACUUM ANALYZE sales;

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

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

EXPLAIN (analyze, costs off, summary off, timing off)
SELECT COUNT(*)
FROM sales
WHERE product_id = 42;

                               QUERY PLAN                                  
------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=506
   ->  Index Only Scan using idx on sales (actual rows=9887.00 loops=1)
         Index Cond: (product_id = 42)
         Heap Fetches: 0
         Index Searches: 102
         Buffers: shared hit=506

Индекс используется! Но как это возможно?

Для объяснения лучше представить, что во фразу WHERE неявно добавлено еще одно условие:

WHERE product_id = 42 AND deptno_id = ANY(<список всех возможных значений>)

В таком виде PostgreSQL еще с 17-й версии научился использовать индекс для столбца deptno_id.

Сначала составляется список всех возможных значений столбца deptno_id. Это не сложно сделать проходом по индексу, ведь столбец на первом месте. Затем берем первое значение списка и ищем в индексе все строки, где product_id = 42. Затем берем второе значение в списке и опять ищем в индексе строки с нужным продуктом. И так для каждого элемента в списке отделов.

В результате индекс будет сканироваться многократно. Точное количество проходов по индексу показывается в плане отдельной строкой Index Searched. В нашем примере это 102: 100 — количество отделов и еще 2 видимо на построение списка отделов.

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

SELECT relpages FROM pg_class WHERE oid = 'sales'::regclass;

 relpages 
----------
    54055
(1 row)

Индексы GiST: быстрое создание индексов методом сортировки
commit: e4309f73f, e9e7b6604

Возможность ускорить создание индексов GiST методом сортировки (sortsupport) появилась в 14-й версии. Для этого нужно определить опорную функцию сравнения двух значений для соответствующих классов операторов. В 14-й версии такая функция появилась для класса операторов point_ops, что позволили резко ускорить создание индексов для типа point.

В 18-й версии добавлены опорные функции сравнения для классов операторов, используемых в расширении btree_gist, а также для range_ops. Поскольку метод сортировки для создания индексов самый быстрый, то он используется по умолчанию:

CREATE TABLE t
AS
SELECT g.id,
       format('[%s,%s]', -1*random(0,1000000), random(0,1000000))::int4range AS r
FROM generate_series(1,1_000_000) AS g(id);

CREATE INDEX ON t USING gist(id, r);

Time: 762,247 ms

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

CREATE INDEX ON t USING gist(id, r) WITH (buffering=on);

Time: 17579,742 ms (00:17,580)

Планировщик: замена IN (VALUES..) на поиск в массиве
commit: c0962a113

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

EXPLAIN (costs off)
SELECT * FROM flights
WHERE flight_id IN (VALUES(1),(2),(3));

                      QUERY PLAN                      
------------------------------------------------------
 Nested Loop
   ->  HashAggregate
         Group Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Index Scan using flights_pkey on flights
         Index Cond: (flight_id = "*VALUES*".column1)
(6 rows)

В 18-й версии конструкция IN (VALUES ..) будет заменена на поиск по массиву, что во многих случаях эффективнее:

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

Планировщик: устранение избыточных соединений таблицы самой с собой
commit: fc069a3a6

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

CREATE TABLE t (id int PRIMARY KEY);

EXPLAIN (costs off)
SELECT *
FROM t a1 JOIN t a2 USING (id)
WHERE a1.id > 0 OR a2.id < 1000;

             QUERY PLAN              
-------------------------------------
 Seq Scan on t a2
   Filter: ((id > 0) OR (id < 1000))
(2 rows)

Оптимизацией можно управлять параметром enable_self_join_elimination, который включен по умолчанию:

\dconfig enable_self_join_elimination

   List of configuration parameters
          Parameter           | Value 
------------------------------+-------
 enable_self_join_elimination | on
(1 row)

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

                   QUERY PLAN                   
------------------------------------------------
 Hash Join
   Hash Cond: (a1.id = a2.id)
   Join Filter: ((a1.id > 0) OR (a2.id < 1000))
   ->  Seq Scan on t a1
   ->  Hash
         ->  Seq Scan on t a2
(6 rows)

PL/pgSQL: передача именованных значений параметров курсоров с использованием =>
commit: 246dedc5d

В предыдущих версиях при передаче параметров курсора по имени разрешался только синтаксис имя := значение, хотя для параметров подпрограмм возможны два разделителя имени и значения := или =>.

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

DO $$ DECLARE
    cur CURSOR(x int) FOR
        SELECT g.x
        FROM generate_series(1,100) AS g(x)
        WHERE g.x = cur.x;
BEGIN
    OPEN cur (x => 42); CLOSE cur;
    OPEN cur (x := 42); CLOSE cur;
END;
$$;

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

multiple_unique_conflicts: еще один конфликт логической репликации
commit: 73eba5004

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

А сейчас пополнился список конфликтов. Новый конфликт — multiple_unique_conflicts.

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

idle_replication_slot_timeout: аннулирование неактивных слотов по таймауту
commit: ac0e33136

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

max_active_replication_origins: максимальное количество источников репликации
commit: 04ff636cb

Максимальное количество источников репликации (replication origins) определялось тем же параметром, что и количество слотов репликации — max_replication_slots. Однако слоты репликации используются на сервере публикаций при создании публикаций, а источники репликации используются и нужны для подписок на сервере подписчике.

Новый параметр max_active_replication_origins контролирует максимальное количество источников репликации, в то время как max_replication_slots по-прежнему ограничивает максимальное количество слотов репликации.

\dconfig+ max_active_replication_origins

                         List of configuration parameters
           Parameter            | Value |  Type   |  Context   | Access privileges 
--------------------------------+-------+---------+------------+-------------------
 max_active_replication_origins | 10    | integer | postmaster | 
(1 row)

Поддержка протокола авторизации OAuth 2.0
commit: b3f0be788

Добавлена поддержка протокола авторизации OAuth версии 2.0. Для этого в pg_hba.conf нужно настроить новый метод авторизации/аутентификации oauth.

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

Клиенты работающие через libpq, смогут задавать соответствующие параметры для авторизации через OAuth. Для проверки авторизации, потребуется подключить модуль проверки (задается в параметре oauth_validator_libraries). Модули проверки не поставляется вместе с сервером PostgreSQL, поэтому их придется разработать самостоятельно, согласно описанному в документации интерфейсу, или воспользоваться сторонними решениями.

dblink: использование сквозной аутентификации SCRAM
commit: 3642df265

В предыдущей статье уже рассказывалось об использовании нового параметра use_scram_passthrough для подключения к внешним базам данных через postgres_fdw без указания пароля в настройках USER MAPPING.

Аналогичная возможность появилась и в расширении dblink. Для настройки сквозной аутентификации SCRAM в dblink_fdw используется аналогичный параметр use_scram_passthrough. Пример настройки можно посмотреть по ссылке выше.

pgcrypto: поддержка алгоритмов sha256crypt и sha512crypt для хеширования паролей
commit: 749a9e20c

В функциях crypt и get_salt расширения pgcrypto теперь поддерживаются основанные на SHA-2 алгоритмы sha256crypt и sha512crypt:

WITH cte AS (
  SELECT crypt('Hello, World!', gen_salt('sha256crypt')) AS passwd
)
SELECT cte.passwd,
       crypt('Hello, World!', cte.passwd) = cte.passwd AS match
FROM cte\gx

-[ RECORD 1 ]-----------------------------------------------------------------------
passwd | $5$rounds=5000$O57oLxAqYK9wqn9m$qgCp3qJAoeS7ufx9cmX5ZYqfk2x5cO3/FtRCpQYa6D/
match  | t

pgcrypto: поддержка режима CFB для стандарта шифрования AES
commit: 9ad1b3d01

В функциях encrypt/decrypt расширения pgcrypto теперь поддерживается режим CFB (Cipher Feedback Mode):

WITH cte AS (
  SELECT encrypt('Hello, World!', '0123456789', 'aes-cfb') AS encrypted_str
)
SELECT encode(
         decrypt(cte.encrypted_str, '0123456789', 'aes-cfb'),
         'escape'
       )
FROM cte;

    encode     
---------------
 Hello, World!
(1 row)

ALTER DEFAULT PRIVILEGES и большие объекты
commit: 0d6c47766

Привилегии по умолчанию для больших объектов теперь можно настраивать командой ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO public;

SELECT lo_create(42);

 lo_create 
-----------
        42
(1 row)

\lo_list+

                   Large objects
 ID |  Owner   |  Access privileges   | Description 
----+----------+----------------------+-------------
 42 | postgres | =r/postgres         +| 
    |          | postgres=rw/postgres | 
(1 row)

libpq: параметр sslkeylogfile
commit: 2da74d8d6

Новый параметр подключения sslkeylogfile библиотеки libpq задает имя файла, куда записываются информация о секретах TLS-соединения в формате NSS.

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

Темпоральные первичные, уникальные и внешние ключи
commit: fc0438b4e, 89f908a6d, 34768ee36

Работа с темпоральными данными описана в стандарте SQL. В 17-й версии была предпринята первая попытка реализовать темпоральные ограничения целостности. Но незадолго до выпуска, поддержку первичных, уникальных и внешних ключей отменили. Сейчас вторая попытка.

Для создания темпорального первичного ключа в его состав включается столбец диапазонного временного типа с ключевыми словами WITHOUT OVERLAPS:

CREATE EXTENSION btree_gist;

CREATE TABLE prices(
    product_id int,
    price numeric,
    during daterange,
    PRIMARY KEY (product_id, during WITHOUT OVERLAPS)
);

В таблице prices каждая строка идентифицируется по комбинации product_id и диапазона дат в during. Это гарантирует, что один и тот же продукт не может иметь разные цены в один момент времени.

INSERT INTO prices VALUES
    (1, 42, '[2024-01-01, 2025-01-01)'),
    (1, 43, '[2025-01-01, 2026-01-01)');

INSERT 0 2

INSERT INTO prices VALUES
    (1, 44, '[2025-09-01, 2026-01-01)');

ERROR:  conflicting key value violates exclusion constraint "prices_pkey"
DETAIL:  Key (product_id, during)=(1, [2025-09-01,2026-01-01)) conflicts with existing key (product_id, during)=(1, [2025-01-01,2026-01-01)).

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

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

CREATE TABLE offers(
    offer_id int,
    product_id int,
    during daterange,
    PRIMARY KEY (offer_id, product_id),
    FOREIGN KEY (product_id, PERIOD during)
        REFERENCES prices (product_id, PERIOD during)
);    

INSERT INTO offers VALUES
    (1, 1, '[2025-09-01, 2026-03-01)');

ERROR:  insert or update on table "offers" violates foreign key constraint "offers_product_id_during_fkey"
DETAIL:  Key (product_id, during)=(1, [2025-09-01,2026-03-01)) is not present in table "prices".

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

Виртуальные вычисляемые столбцы
commit: 83ea6c540, cdc168ad4

Вычисляемые столбцы появились еще в 12-й версии. Для их определения используется конструкция:

GENERATED ALWAYS AS (expr) STORED

Ключевое слово STORED говорит о том, что при вставке/изменении строки значение столбца вычисляется и записывается на диск.

Стандартом SQL предусмотрен и другой способ определения вычисляемых столбцов — VIRTUAL, когда значение не хранится на диске, а вычисляется в момент обращения к значению столбца. Этот способ теперь поддерживается в 18-й версии.

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

CREATE TABLE t (
  id uuid PRIMARY KEY,
  creation_date timestamptz 
    GENERATED ALWAYS AS (uuid_extract_timestamp(id)) VIRTUAL
);

Во многих случаях это можно рассматривать как время создания записи:

INSERT INTO t
SELECT uuidv7()
FROM generate_series(1,3);

SELECT * FROM t;

                  id                  |       creation_date        
--------------------------------------+----------------------------
 01990953-f898-7c70-926e-2812a5972378 | 2025-09-02 10:28:42.136+03
 01990953-f899-7177-9035-ceeff8f87ea5 | 2025-09-02 10:28:42.137+03
 01990953-f899-71f6-8bde-52e6b1c7df39 | 2025-09-02 10:28:42.137+03
(3 rows)

Столбец creation_date не занимает места на диске, но может быть полезен для анализа данных.

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

Ограничения целостности NOT NULL: создание без проверки, включение/отключение наследования
commit: a379061a2, f4e53e10b

Ограничения целостности NOT NULL теперь записываются в pg_constraint. Поэтому к ним можно обращаться по имени. Что это дает?

Ограничение можно создать без проверки существующих записей (первый коммит):

CREATE TABLE t (id int);
INSERT INTO t VALUES (null);

ALTER TABLE t ADD CONSTRAINT id_nn NOT NULL id NOT VALID;

\d+ t

                                            Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           | not null |         | plain   |             |              | 
Not-null constraints:
    "id_nn" NOT NULL "id" NOT VALID
Access method: heap

А позже выполнить проверку, при необходимости исправив данные:

ALTER TABLE t VALIDATE CONSTRAINT id_nn;

ERROR:  column "id" of relation "t" contains null values

UPDATE t SET id = 1;

UPDATE 1

ALTER TABLE t VALIDATE CONSTRAINT id_nn;

ALTER TABLE

Можно устанавливать и снимать свойство INHERIT (второй коммит). При установке ограничение будет добавлено во все дочерние таблицы, если они есть.

ALTER TABLE t ALTER CONSTRAINT id_nn INHERIT;

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

ALTER TABLE t ALTER CONSTRAINT id_nn NO INHERIT;

Удалить ограничение NOT NULL также можно по имени:

ALTER TABLE t DROP CONSTRAINT id_nn;

Ограничения целостности: NOT ENFORCED
commit: ca87c415e, eec0040c4, b663b9436

Согласно стандарту SQL, ограничения целостности можно объявлять как NOT ENFORCED. Такая возможность добавлена в 18-ю версию для внешних ключей и ограничений CHECK.

CREATE TABLE t (
  id int PRIMARY KEY,
  parent_id int REFERENCES t NOT ENFORCED
);

Внутренние триггеры для реализации внешнего ключа не будут созданы, а для новых записей (в отличие от NOT VALID) не будут выполняться проверки.

Можно добавить новое, отключенное, ограничение:

ALTER TABLE t ADD CONSTRAINT id_check CHECK (id > 0) NOT ENFORCED;

\d t

                   Table "public.t"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 id        | integer |           | not null | 
 parent_id | integer |           |          | 
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "id_check" CHECK (id > 0) NOT ENFORCED
Foreign-key constraints:
    "t_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES t(id) NOT ENFORCED
Referenced by:
    TABLE "t" CONSTRAINT "t_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES t(id) NOT ENFORCED

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

ALTER TABLE t ALTER CONSTRAINT t_parent_id_fkey ENFORCED;

Однако с ограничениями CHECK так не получится:

ALTER TABLE t ALTER CONSTRAINT id_check ENFORCED;

ERROR:  cannot alter enforceability of constraint "id_check" of relation "t"

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

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

file_copy_method: способ копирования файлов при создании базы данных
commit: f78ca6f3e

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

WAL_LOG — стратегия основана на записи изменений в WAL и хорошо подходит для создания баз данных из небольших шаблонных баз, например template1. Эта стратегия используется по умолчанию.
FILE_COPY — копирование файлов. Подходит для клонирования баз данных большого размера.

В 18-й версии для стратегии FILE_COPY в новом параметре file_copy_method можно задавать способ копирования файлов.

 \dconfig+ file_copy_method

               List of configuration parameters
    Parameter     | Value | Type | Context | Access privileges 
------------------+-------+------+---------+-------------------
 file_copy_method | copy  | enum | user    | 
(1 row)

Значение по умолчанию COPY, оно соответствует старому поведению. А новое значение CLONE использует системный вызов copy_file_range в Linux и FreeBSD или copyfile на macOS. Поддержка остальных ОС может быть добавлена в дальнейшем. Значение CLONE будет полезно для файловых систем поддерживающих механизм копирования при записи (COW).

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

CREATE DATABASE ... STRATEGY=FILE_COPY

но и при переносе базы данных в другое табличное пространство:

ALTER DATABASE ... SET TABLESPACE ...

extension_control_path: расположение управляющих файлов расширений
commit: 4f7f7b037

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

\dconfig+ extension_control_path

                     List of configuration parameters
       Parameter        |  Value  |  Type  |  Context  | Access privileges 
------------------------+---------+--------+-----------+-------------------
 extension_control_path | $system | string | superuser | 
(1 row)

Значение по умолчанию $system указывает на прежнее место в каталоге SHAREDIR:

SELECT setting || '/extension' FROM pg_config() WHERE name = 'SHAREDIR';

                  ?column?                  
--------------------------------------------
 /home/pal/master/share/postgresql/extension
(1 row)

Недетерминированные правила сортировки: поддержка функций для поиска подстроки
commit: 329304c90

Для строк с недетерминированными правилами сортировки в 18-й версии можно использовать не только поиск по LIKE, но и поиск подстроки, реализованный в функциях position, strpos, replace, split_part, string_to_array, string_to_table.

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

SELECT strpos('PostgreSQL' COLLATE "ignore_case", 'sql');

 strpos 
--------
      8
(1 row)

Функции gamma и lgamma
commit: a3b6dfd41

Математические функции gamma и lgamma стали доступны в SQL:

SELECT g.x, gamma(g.x), lgamma(g.x),
       factorial(g.x-1), ln(gamma(g.x))
FROM generate_series(1,5) AS g(x);

 x | gamma |       lgamma       | factorial |         ln         
---+-------+--------------------+-----------+--------------------
 1 |     1 |                  0 |         1 |                  0
 2 |     1 |                  0 |         1 |                  0
 3 |     2 | 0.6931471805599453 |         2 | 0.6931471805599453
 4 |     6 |  1.791759469228055 |         6 |  1.791759469228055
 5 |    24 | 3.1780538303479458 |        24 | 3.1780538303479458
(5 rows)

Преобразование целочисленных типов в bytea и обратно
commit: 6da469bad

Целочисленные типы (smallint, int и bigint) можно преобразовать в bytea. Обратное преобразование также поддерживается:

SELECT 42::bytea::int;

 int4 
------
   42
(1 row)

Функция pg_get_loaded_modules: информация о загруженных в общую память библиотеках
commit: 9324c8c58, 55527368b

Некоторые расширения не имеют интерфейса SQL, их функциональность «прячется» в загруженной в общую память библиотеке. Например auto_explain.

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

LOAD 'auto_explain';

SELECT * FROM  pg_get_loaded_modules();

 module_name  | version |    file_name    
--------------+---------+-----------------
 auto_explain | 18beta1 | auto_explain.so
(1 row)

pg_buffercache: вытеснение таблицы или всего кеша
commit: dcf7e1697

В 17-й версии в расширении pg_buffercache появилась функция pg_buffercache_evict, позволяющая вытеснить произвольный буфер из общего буферного кеша. Но чтобы вытеснить все буферы таблицы/индекса или очистить кеш целиком нужно было вызывать функцию многократно, что не столько трудоемко, сколько медленно. А для тестов и экспериментов такая функциональность может быть очень полезной.

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

Выполним запрос к таблице tickets и посмотрим сколько буферов в кеше она занимает:

CREATE EXTENSION pg_buffercache;

SELECT count(*) FROM tickets;

  count  
---------
 2949857
(1 row)

SELECT count(*)              
FROM pg_buffercache
WHERE relfilenode = (
        SELECT relfilenode
        FROM pg_class
        WHERE oid = 'tickets'::regclass
);

 count 
-------
   282
(1 row)

Вытесним из кеша все буферы таблицы:

SELECT * FROM pg_buffercache_evict_relation('tickets'::regclass);

 buffers_evicted | buffers_flushed | buffers_skipped 
-----------------+-----------------+-----------------
             282 |               0 |               0
(1 row)

SELECT count(*)                                                  
FROM pg_buffercache
WHERE relfilenode = (
        SELECT relfilenode
        FROM pg_class
        WHERE oid = 'tickets'::regclass
);

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

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

amcheck: проверка индексов GIN
commit: 14ffaece0

В расширение amcheck добавлена функция gin_index_check для проверки индексов GIN.

CREATE FOREIGN TABLE LIKE
commit: 302cf1575

Определить структуру внешней таблицы теперь можно при помощи фразы LIKE. Структура будет скопирована с локальной таблицы. Это удобно для работы с обертками сторонних данных, не поддерживающих команду IMPORT FOREIGN SCHEMA.

Например, если мы хотим загружать данные о бронированиях в таблицу bookings через file_fdw, то для создания промежуточной внешней таблицы можно выполнить:

CREATE EXTENSION file_fdw;
CREATE SERVER srv FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE ft_bookings
  (LIKE bookings INCLUDING all EXCLUDING comments)
SERVER srv OPTIONS (program 'some_program');

\d ft_bookings

                          Foreign table "bookings.ft_bookings"
    Column    |           Type           | Collation | Nullable | Default | FDW options 
--------------+--------------------------+-----------+----------+---------+-------------
 book_ref     | character(6)             |           | not null |         | 
 book_date    | timestamp with time zone |           | not null |         | 
 total_amount | numeric(10,2)            |           | not null |         | 
Server: srv
FDW options: (program 'some_program')

Среди дополнительных свойств во фразе INCLUDING поддерживаются: comments, constraints, defaults, generated, statistics или all. Список меньше чем для обычных таблиц, т.к. для внешних таблиц не все свойства имеют смысл.

COPY .. TO: поддержка материализованных представлений
commit: 534874fac

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

COPY (SELECT * FROM mat_view) TO …

Теперь COPY TO напрямую работает с материализованными представлениями и можно выполнить:

COPY mat_view TO …

Функция json_strip_nulls удаляет пустые значения в массивах
commit: 4603903d2

Функция json[b]_strip_nulls удаляет из json все поля со значением NULL. Но если поле является массивом, то элементы массива со значением NULL остаются. В следующем примере поле f2 удалено, а значение NULL в элемента массива поля f3 осталось:

SELECT json_strip_nulls('{"f1":1, "f2":null, "f3":[1,2,null]}');

     json_strip_nulls     
--------------------------
 {"f1":1,"f3":[1,2,null]}
(1 row)

Новый параметр функции strip_in_arrays позволяет удалять NULL и из элементов массивов:

SELECT json_strip_nulls('{"f1":1, "f2":null, "f3":[1,2,null]}',
         strip_in_arrays=>true
       );

  json_strip_nulls   
---------------------
 {"f1":1,"f3":[1,2]}
(1 row)

Новая функция array_sort
commit: 6c12ae09f, 53d3daa49

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

SELECT array_sort(ARRAY[42.2,null,21.1,5,10],
         descending=>true,
         nulls_first=>true
       );

      array_sort       
-----------------------
 {NULL,42.2,21.1,10,5}
(1 row)

А в рамках другой работы (второй коммит) оптимизирована сортировка массивов.

На этом о 18-й версии всё. Но уже пора приступать к изучению новых возможностей 19-й версии.

PostgreSQL 18: Часть 5 или Коммитфест 2025-03 +13

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

Мониторинг

[Авто]очистка и анализ

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

Процедурные языки

Репликация

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

Сервер

Команды SQL и встроенные функции

Комментарии (0)