Продолжаем следить за новинками 17-й версии PostgreSQL. На этот раз поговорим о том, чем запомнился сентябрьский коммитфест.
Самое интересное об июльском коммитфесте ― в предыдущей статье серии: 2023-07.
Удален параметр old_snapshot_threshold
Новый параметр event_triggers
Новые функции to_bin и to_oct
Новое системное представление pg_wait_events
EXPLAIN: счетчик времени на JIT-компиляцию преобразования кортежей
Планировщик: уточнение оценки начальной стоимости узла WindowAgg
pg_constraint: ограничения NOT NULL
Нормализация CALL, DEALLOCATE и команд управления двухфазной фиксацией
unaccent: целевое выражение правил поддерживает значения в кавычках
COPY FROM: FORCE_NOT_NULL и FORCE_NULL
Аудит подключений без аутентификации
pg_stat_subscription: новый столбец worker_type
Функция pg_promote при неуспешном переключении на реплику
Выбор метода синхронизации с диском в утилитах сервера
pg_restore: оптимизация восстановления в несколько потоков большого количества таблиц
pg_basebackup и pg_receivewal с параметром dbname
Имена параметров для ряда встроенных функций
psql: \watch min_rows
Удален параметр old_snapshot_threshold
commit: f691f5b8
В версии 9.6 появился параметр old_snapshot_threshold. Параметр определяет время для снимка данных, в течение которого гарантируется, что старые версии строк не будут удалены. По истечении этого времени очистка имеет право вычищать версии строк, видимые в снимке. При обращении к удаленным версиям строк транзакция получит ошибку, так знакомую пользователям Oracle: «Snapshot too old».
В реализации обнаружили проблемы, в том числе с производительностью, а элегантного решения пока не нашлось. Поэтому параметр удалили.
Новый параметр event_triggers
commit: 7750fefd
Параметр event_triggers включает/отключает триггеры событий.
\dconfig+ event_triggers List of configuration parameters
Parameter | Value | Type | Context | Access privileges
----------------+-------+------+-----------+-------------------
event_triggers | on | bool | superuser |
(1 row)Предназначен в первую очередь для отладки триггеров. Ранее в документации к команде CREATE EVENT TRIGGER предлагалось запускать сервер в однопользовательском режиме, если ошибочно работающий триггер не удается удалить.
Новые функции to_bin и to_oct
commit: 260a1f18
К уже существующей функции to_hex для шестнадцатеричных чисел добавлены функции для перевода в двоичную и восьмеричную системы счисления:
SELECT to_bin(2), to_oct(8), to_hex(16); to_bin | to_oct | to_hex
--------+--------+--------
10 | 10 | 10
(1 row)См. также
Waiting for PostgreSQL 17 – Add to_bin() and to_oct(). – select * from depesz;
Новое системное представление pg_wait_events
commit: 1e68e43d
В документации к представлению pg_stat_activity для описания столбцов wait_event_type и wait_event прилагаются таблица с описанием типов событий ожидания и несколько таблиц с описанием событий ожидания каждого типа. Эти же описания теперь можно получить запросом к новому системному представлению pg_wait_events.
Это может быть удобно в системах мониторинга для получения описания ожиданий текущих процессов. Чем сейчас занят процесс контрольной точки?
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, w.description
FROM pg_stat_activity a JOIN pg_wait_events w
ON (a.wait_event_type = w.type AND a.wait_event = w.name)
WHERE a.backend_type = 'checkpointer'\gx-[ RECORD 1 ]---+---------------------------------------------
pid | 21090
state |
wait_event_type | Activity
wait_event | CheckpointerMain
description | Waiting in main loop of checkpointer processEXPLAIN: счетчик времени на JIT-компиляцию преобразования кортежей
commit: 5a3423ad
В выводе EXPLAIN ANALYZE, а также в pg_stat_statements, отдельно учитывается время, затраченное на JIT-компиляцию преобразования кортежей (tuple deforming).
В следующем запросе JIT-компиляция используется для вычисления числа ????:
EXPLAIN (analyze, summary off)
WITH pi AS (
SELECT random() x, random() y
FROM generate_series(1,10000000)
)
SELECT 4*sum(1-floor(x*x+y*y))/count(*) val FROM pi; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=525000.00..525000.02 rows=1 width=8) (actual time=3934.330..3934.331 rows=1 loops=1)
CTE pi
-> Function Scan on generate_series (cost=0.00..150000.00 rows=10000000 width=16) (actual time=725.126..1654.054 rows=10000000 loops=1)
-> CTE Scan on pi (cost=0.00..200000.00 rows=10000000 width=16) (actual time=725.129..3429.925 rows=10000000 loops=1)
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.954 ms (Deform 0.161 ms), Inlining 8.476 ms, Optimization 25.632 ms, Emission 22.098 ms, Total 57.160 ms
(8 rows)Значение Generation в последней строке плана ― это суммарное время на компиляцию выражений (управляется параметром jit_expressions) и на компиляцию преобразования кортежей (jit_tuple_deforming). Сколько из этого времени заняла компиляция преобразования кортежей в 17-й версии отдельно указано в скобках (Deform).
Поскольку таблиц в этом примере нет, то можно не тратить время на компиляцию преобразования кортежей. Отключим соответствующий параметр и увидим изменения в счетчиках времени:
SET jit_tuple_deforming = off;Часть плана запроса, касающаяся JIT:
JIT:
Functions: 5
Options: Inlining true, Optimization true, Expressions true, Deforming false
Timing: Generation 0.665 ms (Deform 0.000 ms), Inlining 15.209 ms, Optimization 25.897 ms, Emission 16.664 ms, Total 58.435 msПланировщик: уточнение оценки начальной стоимости узла WindowAgg
commit: 3900a02c
Планировщик сильно занижал стоимость получения первой строки из оконной функции в тех случаях, когда оконная функция обрабатывает большое количество строк.
В следующем, немного надуманном, примере планировщик выбирает nested loop для соединения таблиц, т. к. запрос возвращает всего одну строку, а стоимость получения первой строки очень мала:
16=> EXPLAIN (analyze, settings)
SELECT t.ticket_no, SUM(tf.amount) OVER ()
FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no
LIMIT 1; QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..0.86 rows=1 width=46) (actual time=30416.237..30416.239 rows=1 loops=1)
-> WindowAgg (cost=0.56..2525161.21 rows=8391708 width=46) (actual time=30416.236..30416.237 rows=1 loops=1)
-> Nested Loop (cost=0.56..2420264.86 rows=8391708 width=20) (actual time=0.049..27742.105 rows=8391852 loops=1)
-> Seq Scan on tickets t (cost=0.00..78913.45 rows=2949845 width=14) (actual time=0.015..287.636 rows=2949857 loops=1)
-> Index Scan using ticket_flights_pkey on ticket_flights tf (cost=0.56..0.76 rows=3 width=20) (actual time=0.007..0.009 rows=3 loops=2949857)
Index Cond: (ticket_no = t.ticket_no)
Settings: jit = 'off', search_path = 'bookings, public'
Planning Time: 0.456 ms
Execution Time: 30454.862 ms
(9 rows)Нас интересует вторая строка плана с узлом WindowAgg. Начальная стоимость равна 0.56, хотя на получение первой строки ушло ~30 секунд.
В 17-й версии оценка исправлена и планировщик выбирает более уместный hash join, что сразу отражается на скорости выполнения запроса:
17=> EXPLAIN (analyze, settings)
SELECT t.ticket_no, SUM(tf.amount) OVER ()
FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no
LIMIT 1; QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=523778.31..523778.31 rows=1 width=46) (actual time=7157.639..7157.642 rows=1 loops=1)
-> WindowAgg (cost=523778.31..523778.36 rows=8392150 width=46) (actual time=7157.638..7157.640 rows=1 loops=1)
-> Hash Join (cost=130215.56..418876.48 rows=8392150 width=20) (actual time=740.005..5075.390 rows=8391852 loops=1)
Hash Cond: (tf.ticket_no = t.ticket_no)
-> Seq Scan on ticket_flights tf (cost=0.00..153881.50 rows=8392150 width=20) (actual time=0.066..794.699 rows=8391852 loops=1)
-> Hash (cost=78938.47..78938.47 rows=2949847 width=14) (actual time=737.972..737.973 rows=2949857 loops=1)
Buckets: 262144 Batches: 32 Memory Usage: 6190kB
-> Seq Scan on tickets t (cost=0.00..78938.47 rows=2949847 width=14) (actual time=0.029..307.040 rows=2949857 loops=1)
Settings: search_path = 'bookings, public', jit = 'off'
Planning Time: 0.331 ms
Execution Time: 7202.957 ms
(11 rows)pg_constraint: ограничения NOT NULL
commit: b0e96f31
В системном каталоге pg_constraint официально появился новый, но хорошо известный, тип ограничения целостности ― NOT NULL. По разным причинам ограничения NOT NULL в pg_constraint не записывались.
Это изменение не несет новых возможностей для разработки приложений, но информация в системном каталоге становится более полной, а работа с ограничениями — более последовательной.
Ограничения NOT NULL в pg_constraint имеют тип contype = 'n':
CREATE TABLE t (
col1 int NOT NULL,
col2 int NOT NULL
);
SELECT conname, contype, conkey
FROM pg_constraint
WHERE conrelid = 't'::regclass; conname | contype | conkey
-----------------+---------+--------
t_col1_not_null | n | {1}
t_col2_not_null | n | {2}
(2 rows)Ограничение можно удалить по имени, как и любое другое:
ALTER TABLE t DROP CONSTRAINT t_col2_not_null;Увидеть ограничения можно в отдельной секции вывода команды \d+:
postgres@postgres(17.0)=# \d+ t Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
col1 | integer | | not null | | plain | | |
col2 | integer | | | | plain | | |
Not-null constraints:
"t_col1_not_null" NOT NULL "col1"
Access method: heapВ настоящий момент ограничения NOT NULL нельзя создать без проверки существующих данных (NOT VALID) или объявить отложенными (DEFERRED). Кроме того, ограничения NOT NULL для таблиц системного каталога в pg_constraint не записаны.
Нормализация CALL, DEALLOCATE и команд управления двухфазной фиксацией
commit: 11c34b34, 638d42a3, bb45156f
Механизм нормализации команд научился заменять параметры процедур в вызовах CALL на константы. Теперь вызов процедуры с разными параметрами будет учитываться в pg_stat_statements одной строкой:
CALL p(1);
CALL p(2);
CALL p(3);
SELECT queryid, query, calls
FROM pg_stat_statements
WHERE query ILIKE 'CALL%'; queryid | query | calls
---------------------+------------+-------
7076031282516132618 | CALL p($1) | 3
(1 row)Кроме того, на константы заменяются идентификаторы транзакций в командах управления двухфазной фиксацией (второй коммит) и имена подготовленных операторов в DEALLOCATE (третий коммит).
См. также
Нормализация DDL и служебных команд
Нормализация DDL и служебных команд, продолжение
unaccent: целевое выражение правил поддерживает значения в кавычках
commit: 59f47fb9
Для устранения диакритических знаков встроенный полнотекстовый поиск предлагает использовать словарь unaccent, поставляемый с сервером в виде одноименного расширения.
При определении собственных правил словаря целевые выражения теперь можно заключать в кавычки. Это нужно для того, чтобы целевое выражение могло начинаться с пробела или заканчиваться им. Раньше лидирующие и хвостовые пробелы обрезались.
COPY FROM: FORCE_NOT_NULL и FORCE_NULL
commit: f6d4c9cf
Параметры FORCE_NOT_NULL и FORCE_NULL команды COPY появились достаточно давно, в версиях 9.0 и 9.4 соответственно. Но требовали явного перечисления столбцов, даже если их нужно было применить ко всем столбцам таблицы.
Вместо перечисления всех столбцов теперь можно указать символ *.
CREATE TABLE t (col1 text NOT NULL, col2 text NOT NULL);
COPY t FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);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.
>> "",""
>> \.
COPY 1SELECT * FROM t WHERE col1 = '' and col2 = ''; col1 | col2
------+------
|
(1 row)Аудит подключений без аутентификации
commit: e48b19c5
Если пользователь подключается без аутентификации, то при включенном параметре log_connections запись об этом появится в журнале сервера с указанием номера сработавшей строки (117) с методом «trust» в pg_hba.conf:
2023-10-19 11:40:07.609 MSK [111233] LOG: connection authenticated: user="postgres" method=trust (/home/pal/master/data/pg_hba.conf:117)
2023-10-19 11:40:07.609 MSK [111233] LOG: connection authorized: user=postgres database=demo application_name=psqlРаньше для метода «trust» в журнал записывалась только вторая строка.
Заметим, что функция SYSTEM_USER по-прежнему возвращает NULL для таких подключений.
pg_stat_subscription: новый столбец worker_type
commit: 13aeaf07
Новый столбец worker_type представления pg_stat_subscription поможет быстро определить, какую работу выполняет процесс, обслуживающий подписку. Возможные значения говорят за себя: apply, parallel apply и table synchronization.
Стоит напомнить, что сервер подписки научился применять транзакции несколькими процессами в 16-й версии.
Функция pg_promote при неуспешном переключении на реплику
commit: f593c551
Один из вариантов переключения на реплику ― это вызов функции pg_promote. Если переключиться не удалось, то функция возвращает false. И неважно, что послужило причиной:
- не уложились в таймаут на переключение,
- не получилось отправить сигнал SIGUSR1 постмастеру,
- или даже постмастер аварийно завершился в процессе переключения.
Для двух последних случаев в журнал сервера записывалось предупреждение. В 17-й версии эти предупреждения превратились в ошибки, соответственно функция pg_promote будет возвращать false только при превышении таймаута, в остальных случаях завершаться ошибкой.
Выбор метода синхронизации с диском в утилитах сервера
commit: 8c16ad3b
Утилиты сервера, пишущие файлы на диск, получили параметр --synch-method. Речь о следующих утилитах: initdb, pg_basebackup, pg_checksums, pg_dump, pg_rewind и pg_upgrade.
По умолчанию используется --synch-method=fsync.
В линукс-системах можно указать syncfs для синхронизации всей файловой системы сразу, вместо вызова fsync на каждый файл. Во многих случаях это будет быстрее. Однако возможны и побочные эффекты, если с этой же файловой системой активно работают другие приложения, изменяющие файлы: в таком случае придется дополнительно синхронизировать и их запись. В документации появилось новое приложение Appendix O. syncfs() Caveats, предупреждающее об этом.
pg_restore: оптимизация восстановления в несколько потоков большого количества таблиц
commit: c103d073, 5af0263a, 9bfd44bb
Восстановление в несколько потоков утилитой pg_restore выполнялось не оптимально при большом количестве таблиц в резервной копии. Речь о десятках тысяч таблиц и больше.
Проблема в том, что родительский процесс-координатор каждый раз сортировал список таблиц, чтобы найти самую большую и передать ее на восстановление рабочему процессу. Ресурсов на сортировку тратилось так много, что сам родительский процесс становился узким местом: рабочие процессы простаивали в ожидании работы.
Для оптимизации выбора следующей таблицы список таблиц поместили в двоичную кучу. Теперь pg_restore будет значительно быстрее восстанавливать из копий с большим количеством таблиц.
pg_basebackup и pg_receivewal с параметром dbname
commit: cca97ce6
pg_basebackup и pg_receivewal принимают параметр dbname для подключения. Вообще он не нужен этим утилитам, ведь резервные копии и WAL — это уровень всего кластера баз данных. Но в случае подключения через прокси, в частности pgbouncer, требуется указывать базу данных.
Нужно ли подключаться через pgbouncer для выполнения резервного копирования ― это отдельный вопрос. Но если другого способа подключиться нет, то данный патч дает такую возможность.
Имена параметров для ряда встроенных функций
commit: b575a26c
Название параметров необязательно при определении функции, обязателен только тип данных. Именно так и созданы большинство встроенных функций. Но если у функции более одного параметра с одинаковыми типами, то назначение параметров сложно разобрать в выводе \df, нужно заглянуть в документацию.
16=> \df string_agg List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | string_agg | bytea | bytea, bytea | agg
pg_catalog | string_agg | text | text, text | agg
(2 rows)В определение нескольких агрегатных функций, с более чем одним параметром, добавили название параметра. Теперь стало нагляднее:
17=> \df string_agg List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+------------------------------+------
pg_catalog | string_agg | bytea | value bytea, delimiter bytea | agg
pg_catalog | string_agg | text | value text, delimiter text | agg
(2 rows)psql: \watch min_rows
commit: f347ec76
В 16-й версии команде \watch добавили параметр count, позволяющий указать количество повторений выполнения запроса.
Теперь появился еще один способ автоматически завершить работу \watch. Если запрос вернет меньше строк, чем указано в параметре min_rows, то повторение запроса прекращается. Может быть полезным для мониторинга выполнения заданий.
CREATE TABLE job (id int, status text DEFAULT 'NEW');
INSERT INTO job VALUES (1),(2),(3);Для удобства демонстрации обработка заданий выполняется вместе с мониторинговым запросом:
WITH process_job AS (
UPDATE job SET status = 'PROCESSED'
WHERE id = (SELECT id
FROM job
WHERE status = 'NEW'
ORDER BY id LIMIT 1)
)
SELECT * FROM job WHERE status = 'NEW'
\watch min_rows=1Mon 16 Oct 2023 05:55:53 PM MSK (every 2s)
id | status
----+--------
1 | NEW
2 | NEW
3 | NEW
(3 rows)
Mon 16 Oct 2023 05:55:55 PM MSK (every 2s)
id | status
----+--------
2 | NEW
3 | NEW
(2 rows)
Mon 16 Oct 2023 05:55:57 PM MSK (every 2s)
id | status
----+--------
3 | NEW
(1 row)
Mon 16 Oct 2023 05:55:59 PM MSK (every 2s)
id | status
----+--------
(0 rows)На этом пока всё. Ждем ноябрьского коммитфеста 17-й версии.
Sleuthhound
Павел, спасибо за обзор, очень интересно!