Как стать автором
Обновить
115.03
Рейтинг
Postgres Professional
Разработчик СУБД Postgres Pro

PostgreSQL 14: Часть 5 или «весенние заморозки» (Коммитфест 2021-03)

Блог компании Postgres ProfessionalPostgreSQLSQL
8 апреля 2021 г. в 15:00 по московскому времени закончился мартовский коммитфест, а вместе с ним и прием изменений в PostgreSQL 14.

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

В этой пойдет речь о последнем, мартовском. Заранее предупреждаю, что статья получилась огромная. Но плохо ли это? Чем длиннее список новых возможностей, тем лучше PostgreSQL 14! Это с одной стороны. А с другой, вовсе не обязательно читать всё подряд от начала и до конца. Текст состоит из описания патчей. В любом месте можно остановиться, с любого места можно начать.

А почитать есть о чем. Не верите? Вопросы на засыпку:

  • Может ли один запрос параллельно выполняться на разных серверах?
  • Как найти запрос из pg_stat_activity в pg_stat_statements?
  • Можно ли добавлять и удалять секции секционированной таблицы не останавливая приложение?
  • Как пустить разработчиков на прод чтобы они могли всё видеть, но ничего не могли изменить?
  • Почему VACUUM после COPY FREEZE заново переписывает всю таблицу и что с этим делать?
  • Можно ли сжимать TOAST чем-то кроме медленного zlib?
  • Как понять сколько времени длится блокировка найденная в pg_locks?
  • Для чего нужны CYCLE и SEARCH рекурсивному запросу?
  • Текст функций на каких языках (кроме C) не интерпретируется при вызове?

Приступим.

Мониторинг


Единый идентификатор запроса в ядре и модулях
commit: 5fd9dfa5, 4f0b0966

Как же всё-таки соединить pg_stat_activity и pg_stat_statements? Так ведь элементарно:

SELECT a.*, s.* 
FROM   pg_stat_activity a 
       JOIN pg_stat_statements s USING (queryid);

А теперь по порядку. Расширение pg_stat_statements уже давно вычисляет идентификатор запроса и сохраняет его в столбце queryid. Однако pg_stat_statements всё-таки расширение, а не функциональность ядра, да и алгоритм вычисления хеша для идентификатора запроса не был идеальным. В общем попытки добавить аналогичный столбец в pg_stat_activity до поры до времени срывались.

И вот случилось. Вычисление идентификатора перенесли в ядро. Однако по умолчанию pg_stat_statements будет по-прежнему вычислять свой идентификатор.

Но стоит только включить новый параметр compute_query_id, как pg_stat_statements переходит на использование серверного идентификатора, а в pg_stat_activity новый столбец queryid начинает формироваться тем же алгоритмом:

SHOW compute_query_id;
 compute_query_id
------------------
 off

Найдем запрос в pg_stat_activity:

SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();
 queryid |            query            
---------+-----------------------------
         | SELECT queryid,query       +
         | FROM   pg_stat_activity    +
         | WHERE pid=pg_backend_pid();

А теперь включим compute_query_id и посмотрим еще раз. Суперпользователь может включить параметр для своего сеанса:

SET compute_query_id=on;

SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();
       queryid       |            query            
---------------------+-----------------------------
 5840773237036126161 | SELECT queryid,query       +
                     | FROM   pg_stat_activity    +
                     | WHERE pid=pg_backend_pid();

Идентификатор запроса появился. Что же ему соответствует в pg_stat_statements?

SELECT queryid, query
FROM   pg_stat_statements
WHERE  queryid = 5840773237036126161\gx
-[ RECORD 1 ]-------------------------------------------------------------
queryid | 5840773237036126161
query   | SELECT queryid,query FROM pg_stat_activity WHERE pid=pg_backend_pid()

Конечно, тот же самый запрос. Так что два этих представления можно смело соединять по queryid.

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

Кстати, увидеть идентификатор можно в плане запроса:

EXPLAIN (VERBOSE, COSTS OFF) SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();
                      QUERY PLAN                      
------------------------------------------------------
 Function Scan on pg_catalog.pg_stat_get_activity s
   Output: s.queryid, s.query
   Function Call: pg_stat_get_activity(NULL::integer)
   Filter: (s.pid = pg_backend_pid())
 Query Identifier: 5840773237036126161

И, наконец, идентификатор можно добавить в журнал сервера. Для этого появилась новая спецпоследовательность %Q в log_line_prefix.

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

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

pg_stat_statements: toplevel
commit: 6b4d23fe

После установки pg_stat_statements.track = 'all' статистика начинает собираться и по вложенным запросам. Однако при анализе бывает полезно знать, является ли запрос верхнеуровневым или он вызывался из какой-то функции.

Новый столбец toplevel отвечает на этот вопрос:

CREATE TABLE t ();

SELECT pg_stat_statements_reset();
SET pg_stat_statements.track = 'all';

TRUNCATE t;
DO 'BEGIN TRUNCATE t; END;';

SELECT query, toplevel, calls 
FROM   pg_stat_statements
WHERE  query LIKE '%TRUNCATE%';
            query            | toplevel | calls
-----------------------------+----------+-------
 TRUNCATE t                  | f        |     1
 DO 'BEGIN TRUNCATE t; END;' | t        |     1
 TRUNCATE t                  | t        |     1

pg_stat_wal: статистика по записи и синхронизации с диском
commit: ff99918c

Еще одно новое для 14 версии представление pg_stat_wal обрастает функционалом.

Сбросим текущую статистику (SELECT pg_stat_reset_shared('wal')), включим новый параметр track_wal_io_timing и загрузим демо-базу большого размера. Теперь смотрим в представление:

SELECT * FROM pg_stat_wal\gx
-[ RECORD 1 ]----+------------------------------
wal_records      | 756763
wal_fpi          | 455817
wal_bytes        | 4719435190
wal_buffers_full | 159387
wal_write        | 163909
wal_sync         | 4186
wal_write_time   | 3211.339
wal_sync_time    | 118864.601
stats_reset      | 2021-04-12 12:50:30.878238+03

Интересуют последние столбцы. wal_write_time и wal_sync_time показывают, сколько времени было затрачено на запись буферов WAL на диск и синхронизацию с диском (fsync). А столбцы wal_write и wal_sync ― это счетчики: сколько раз буферы WAL сбрасывались на диск и сколько раз выполнялась синхронизация (fsync).

Счетчики будут накручиваться и при выключенном track_wal_io_timing. Включение параметра влияет только на wal_write_time и wal_sync_time. На некоторых платформах это может быть очень затратным, поэтому по умолчанию параметр отключен.

Ускорение сбора статистики и улучшения в протоколировании работы автоматической очистки
commit: c6fc50cb, 94d13d47

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

Кроме того (второй коммит), при включенном журналировании работы автоматической очистки в журнал будет записываться информации о вводе/выводе (если дополнительно включен track_io_timing). А для автоанализа вывод дополнили той же информацией, что выводилась и для автоочистки:

2021-03-30 16:32:00.534 MSK [28993] LOG:  automatic analyze of table "demo.bookings.bookings"
    buffer usage: 10423 hits, 16704 misses, 5 dirtied
    avg read rate: 138.978 MB/s, avg write rate: 0.042 MB/s
    I/O Timings: read=58.504 write=0.175
    system usage: CPU: user: 0.41 s, system: 0.05 s, elapsed: 0.93 s

В предыдущих версиях записывалась только строка system usage.

log_connections: информации о подключившемся внешнем пользователе
commit: 9afffcb8

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

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

Для примера настроим peer-аутентификацию так, чтобы пользователь ОС pluzanov подключался как postgres:

pg_hba.conf

local     all     all         peer map=m1

pg_ident.conf

m1     pluzanov     postgres

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

LOG:  connection received: host=[local]
LOG:  connection authenticated: identity="pluzanov" method=peer (/home/pluzanov/pg14/data/pg_hba.conf:84)
LOG:  connection authorized: user=postgres database=postgres application_name=psql

Анализ использования памяти произвольного серверного процесса
commit: 43620e32

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

Для примера выведем только первую итоговую строку:

SELECT * FROM pg_backend_memory_contexts LIMIT 1 \gx
-[ RECORD 1 ]-+-----------------
name          | TopMemoryContext
ident         |
parent        |
level         | 0
total_bytes   | 80776
total_nblocks | 6
free_bytes    | 11984
free_chunks   | 12
used_bytes    | 68792

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

Из второго сеанса суперпользователя выполняем:

SELECT pg_log_backend_memory_contexts(3003);

Теперь в журнале можно найти соответствующие строки:

LOG:  logging memory contexts of PID 3003
LOG:  level: 0; TopMemoryContext: 80776 total in 6 blocks; 14784 free (19 chunks); 65992 used
...

Запись в журнал ограничена первой сотней строк.

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

pg_locks.waitstart ― момент начала ожидания блокировки
commit: 46d6e5f5, f05ed5a5

В представлении pg_locks новый столбец ― waitstart. Для строк, ожидающих получения блокировки (granted=false), показывает, с какого момента времени началось ожидание.

Первый сеанс блокирует таблицу:

BEGIN;
LOCK TABLE tickets;

Второй сеанс пытается выполнить запрос к этой же таблице и «повисает»:

SELECT * FROM tickets LIMIT 1;

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

SELECT *, now()-waitstart AS wait_time
FROM   pg_locks
WHERE  NOT granted\gx
-[ RECORD 1 ]------+------------------------------
locktype           | relation
database           | 16384
relation           | 16439
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction  | 5/31
pid                | 8525
mode               | AccessShareLock
granted            | f
fastpath           | f
waitstart          | 2021-02-25 12:21:53.236232+03
wait_time          | 00:00:03.106643

Стоит учитывать, что на короткое время после начала ожидания значение waitstart может оставаться незаполненным, поэтому вполне возможна ситуация, когда для строк в pg_locks с granted=false момент начала ожидания не указан.

Улучшения в pg_stat_progress_copy
commit: 9d2d4570

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

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

CREATE TABLE flights_krr (LIKE flights);

Заполнять её будем из предварительно выгруженной информации о всех рейсах.

COPY flights TO '/home/pluzanov/pg14/flights.txt';

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

CREATE FUNCTION slow_ins() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_sleep(0.1);
    RETURN NEW;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER flights_krr_slow_ins
    BEFORE INSERT ON flights_krr
    FOR EACH ROW EXECUTE FUNCTION slow_ins();

Загружаем данные только по Краснодару:

COPY flights_krr FROM '/home/pluzanov/pg14/flights.txt'
    WHERE departure_airport = 'KRR';

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

SELECT * FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]----+----------
pid              | 29657
datid            | 16384
datname          | demo
relid            | 16720
command          | COPY FROM
type             | FILE
bytes_processed  | 19333118
bytes_total      | 26338755
tuples_processed | 1385
tuples_excluded  | 156235

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

Индексы


Индексы BRIN: новые классы операторов bloom и multi-minmax
commit: ab596105, 77b88cd1, a681e3c1

У Егора Рогова есть прекрасная серия статей об индексах в PostgreSQL. В том числе про BRIN и Bloom. Егор согласился написать об этой серии патчей, посвященных индексам BRIN. Ему и слово.

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

BRIN изначально обладал классами операторов двух типов: minmax и inclusive. Классы minmax сохраняют информацию о минимальном и максимальном значении внутри зоны, а inclusive — об ограничивающем прямоугольнике (для типов данных, к которым не применимы операции сравнения).

Теперь этот метод пополнился двумя новыми типами: bloom и minmax_multi. Рассмотрим по порядку.

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

Bloom

Традиционные классы операторов BRIN предполагают, что значения в проиндексированном столбце коррелированы с физическим расположением на диске. Иными словами, внутри одной зоны должен содержаться небольшой диапазон значений (в случае minmax) или значения должны лежать «кучно» (в случае inclusive).

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

Идея bloom состоит в том, чтобы для каждой зоны вычислять и хранить фильтр Блума для входящих в нее значений. Фильтр позволяет определить зоны, в которых может находиться искомое значение (здесь возможно ложноположительное срабатывание, как, впрочем, и в случае minmax). Очевидно, что классы операторов такого типа могут работать только с поиском по равенству.

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

На примере поиска данных по имени пассажира:

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE passenger_name = 'ALEFTINA EFREMOVA';
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on flights_bi (actual rows=28 loops=1)
   Filter: (passenger_name = 'ALEFTINA EFREMOVA'::text)
   Rows Removed by Filter: 30517048
 Planning Time: 0.063 ms
 Execution Time: 16559.124 ms


CREATE INDEX ON flights_bi USING brin(passenger_name text_bloom_ops);

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE passenger_name = 'ALEFTINA EFREMOVA';
                          QUERY PLAN
--------------------------------------------------------------
 Bitmap Heap Scan on flights_bi (actual rows=28 loops=1)
   Recheck Cond: (passenger_name = 'ALEFTINA EFREMOVA'::text)
   Rows Removed by Index Recheck: 325426
   Heap Blocks: lossy=5632
   ->  Bitmap Index Scan on flights_bi_passenger_name_idx 
       (actual rows=56320 loops=1)
         Index Cond: (passenger_name = 'ALEFTINA EFREMOVA'::text)
 Planning Time: 0.077 ms
 Execution Time: 83.796 ms

Конечно, хеш-индекс или B-дерево ускорят поиск еще лучше, но BRIN-индекс занимает всего 32 Мбайта, в то время как хеш-индекс — около 200 Мбайт, а B-дерево — больше гигабайта.

minmax_multi

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

Вот пример. BRIN-индекс хорошо работает для времени вылета, поскольку данные физически упорядочены именно по дате:

CREATE INDEX ON flights_bi USING brin(scheduled_time);

\set d 'bookings.now()::date - interval \'7 days\''
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE scheduled_time >= :d AND scheduled_time < :d + interval '1 day';
                          QUERY PLAN 
------------------------------------------------------------
 Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 12290
   Heap Blocks: lossy=1664
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx
       (actual rows=16640 loops=1)
         Index Cond: ...
 Planning Time: 0.475 ms
 Execution Time: 77.029 ms

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

UPDATE flights_bi
  SET airport_coord = point '(56.0211982727,57.9145011902)'
  WHERE airport_code = 'PEE';
VACUUM flights_bi;
UPDATE flights_bi
  SET airport_coord = point '(39.9566001892,43.4499015808)'
  WHERE airport_code = 'AER';
VACUUM flights_bi;

Строки отчасти перемешались, и теперь тот же индекс работает значительно хуже:

                         QUERY PLAN
------------------------------------------------------------
 Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 4004401
   Heap Blocks: lossy=71033
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx
       (actual rows=710600 loops=1)
         Index Cond: ...
 Planning Time: 9.322 ms
 Execution Time: 17385.129 ms

Попробуем создать BRIN-индекс с классом операторов minmax_multi:

CREATE INDEX ON flights_bi USING brin(scheduled_time timestamptz_minmax_multi_ops);

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

                         QUERY PLAN            
------------------------------------------------------------
 Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 672976
   Heap Blocks: lossy=13157
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx1
       (actual rows=131840 loops=1)
         Index Cond: ...
 Planning Time: 0.291 ms
 Execution Time: 378.037 ms

Покрывающие индексы типа SP-GiST
commit: 09c1c6ab

Возможность добавлять в индекс неключевые столбцы в предложении INCLUDE появилась еще в 11 версии, но только для индексов с типом B-дерево. В 12 версии добавили поддержку индексов GiST.

Теперь к ним присоединяются индексы SP-GiST.

REINDEX: перестройка индексов в другом табличном пространстве
commit: c5b28604, 57e6db70

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

Новое табличное пространство:

CREATE TABLESPACE new_place LOCATION '/home/pluzanov/pg14/data2';
\db
                List of tablespaces
    Name    |  Owner   |         Location          
------------+----------+---------------------------
 new_place  | postgres | /home/pluzanov/pg14/data2
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)

Перестраиваем все индексы таблицы:

REINDEX (TABLESPACE new_place) TABLE flights;

SELECT c.relname, c.relkind, ts.spcname
FROM   pg_class c
       LEFT JOIN pg_tablespace ts ON c.reltablespace = ts.oid
WHERE  relname LIKE 'flights%' AND relkind IN ('r','i');
                  relname                  | relkind |  spcname  
-------------------------------------------+---------+-----------
 flights                                   | r       |
 flights_flight_no_scheduled_departure_key | i       | new_place
 flights_pkey                              | i       | new_place
(3 rows)

Индексы переехали, а таблица осталась в табличном пространстве по умолчанию.

Второй коммит добавляет параметр --tablespace к команде reindexdb.

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


libpq: api для конвейерного режима работы
commit: acb7e4eb, 9aa491ab

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

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

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

Второй коммит добавляет в pgbench команды \startpipeline и \endpipeline для поддержки конвейерного режима libpq.

Параллельное выполнение REFRESH MATERIALIZED VIEW
commit: 9e7ccd9e

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

При более внимательном рассмотрении оказалось, что REFRESH MATERIALIZED VIEW вполне может использовать параллельные планы. Что и было сделано.

Эффективное сканирование по диапазону идентификаторов строк
commit: bb437f99

Для поиска по диапазону идентификаторов строк (tid) в плане запроса добавлен новый узел TID Range Scan.

Вот план запроса, выводящий все строки, расположенные на первой странице таблицы tickets:

EXPLAIN SELECT * FROM tickets WHERE ctid >= '(0,0)' AND ctid < '(1,0)';
                           QUERY PLAN                           
----------------------------------------------------------------
 Tid Range Scan on tickets  (cost=0.01..4.59 rows=59 width=104)
   TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(1,0)'::tid))

Аналогичный план в 13 версии существенно дороже:

                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Gather  (cost=1000.00..68857.51 rows=59 width=104)
   Workers Planned: 2
   ->  Parallel Seq Scan on tickets  (cost=0.00..67851.61 rows=25 width=104)
         Filter: ((ctid >= '(0,0)'::tid) AND (ctid < '(1,0)'::tid))

postgres_fdw: асинхронное выполнение узла Append
commit: 27e1f145

Дана секционированная таблица с тремя секциями:

\d bookings_range
               Partitioned table "bookings.bookings_range"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
 book_ref     | character(6)             |           |          |
 book_date    | timestamp with time zone |           |          |
 total_amount | numeric(10,2)            |           |          |
Partition key: RANGE (book_date)
Number of partitions: 3 (Use \d+ to list them.)

Сколько же в ней записей? Если честно ― неинтересно. Зато интересно посмотреть на план запроса:

EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bookings_range;
                         QUERY PLAN                         
------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on p201607 bookings_range_1
         ->  Async Foreign Scan on p201608 bookings_range_2
         ->  Async Foreign Scan on p201609 bookings_range_3

Что это за узел такой Async Foreign Scan? Неужели это именно то, о чем все подумали? Точно, это параллельное сканирование секций, представляющих собой внешние таблицы!

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

\det
     List of foreign tables
  Schema  |  Table  |   Server   
----------+---------+------------
 bookings | p201608 | srv_201608
 bookings | p201609 | srv_201609

Мечты о встроенном шардинге в PostgreSQL неторопливо сбываются. Но это крутой шаг вперед!

Узел плана Result Cache для соединения вложенными циклами
commit: 9eacee2e

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

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

Эта логика реализована в новом узле плана Result Cache. Чтобы увидеть её в действии, создадим мат. представление по 10 бронированиям, в которых больше всего билетов.

CREATE MATERIALIZED VIEW top10 AS
SELECT book_ref, ticket_no
FROM   tickets
WHERE  book_ref IN (
           SELECT book_ref
           FROM   tickets
           GROUP BY book_ref
           ORDER BY count(*) DESC LIMIT 10
);
ANALYZE top10;

Напомню, что в одно бронирование может входит несколько билетов. В нашем случае в каждое бронирование входит 5 билетов:

SELECT book_ref, COUNT(*) FROM top10 GROUP BY 1;
 book_ref | count
----------+-------
 581B6D   |     5
 831982   |     5
 88C866   |     5
 D6B5B2   |     5
 0D15DF   |     5
 E9F768   |     5
 876B11   |     5
 10EAF6   |     5
 A4CCF6   |     5
 A1A654   |     5

А теперь соединим мат. представление с таблицей бронирований и посмотрим на план запроса:

EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings JOIN top10 USING (book_ref);
                                   QUERY PLAN                                    
----------------------------------------------------------------------------
 Nested Loop (actual rows=50 loops=1)
   ->  Seq Scan on top10 (actual rows=50 loops=1)
   ->  Result Cache (actual rows=1 loops=50)
         Cache Key: top10.book_ref
         Hits: 40  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Index Scan using bookings_pkey on bookings (actual rows=1 loops=10)
               Index Cond: (book_ref = top10.book_ref)

Таблицы соединяются методом вложенных циклов. В качестве внешнего цикла выбрана top10, в которой 50 строк. Но только для 10 строк нужно реально ходить в таблицу bookings, в остальных случаях достаточно воспользоваться закешированными результатами. Расход памяти для кешей минимальный, поэтому вытеснений и переполнений не случилось.

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

SET enable_resultcache = OFF;

EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings JOIN top10 USING (book_ref);
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop (actual rows=50 loops=1)
   ->  Seq Scan on top10 (actual rows=50 loops=1)
   ->  Index Scan using bookings_pkey on bookings (actual rows=1 loops=50)
         Index Cond: (book_ref = top10.book_ref)

Это уже привычный по предыдущим версиям план запроса.

Вычисление nullif при построении плана запроса
commit: 9c5f67fd

Планировщик вычисляет выражения с nullif на этапе построения плана запроса.

В версии 13 сканирование таблицы включается в план и только на этапе выполнения постгрес всё-таки понимает, что это не нужно:

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT * FROM tickets WHERE NULLIF(1, 2) = 2;
                 QUERY PLAN                 
--------------------------------------------
 Result (actual rows=0 loops=1)
   One-Time Filter: (NULLIF(1, 2) = 2)
   ->  Seq Scan on tickets (never executed)

План этого же запроса в 14 версии:

           QUERY PLAN           
--------------------------------
 Result (actual rows=0 loops=1)
   One-Time Filter: false

Репликация


Уточнения в сообщении об ошибке подключения к реплике
commit: df938449

Сообщение об ошибке подключения к реплике с выключенным hot_standby стало более понятным. Раньше просто было «the database system is starting up», теперь «the database system is not accepting connections» и в детальном сообщении «Hot standby mode is disabled.»

Если же hot_standby включен, но восстановление еще не дошло до согласованного состояния, когда возможны подключения, то текст сообщения будет «the database system is not yet accepting connections», а детальное сообщение «Consistent recovery state has not been yet reached.»

Удаление временных файлов после аварийного завершения процесса
commit: cd91de0d

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

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

Упреждающее чтение WAL при восстановлении
commit: 1d257577, f003d9f8

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

Такая возможность включается новым параметром recovery_prefetch, выключенным по умолчанию. Размер журнала WAL для чтения вперед настраивается параметром wal_decode_buffer_size. А для мониторинга предназначено новое представление pg_stat_prefetch_recovery.

Текущая реализации основана на системном вызове posix_fadvise, доступном не на всех платформах.

Репликация: как убедиться, что восстановление приостановлено?
commit: 32fd2b57

Функция pg_wal_replay_pause приостанавливает процесс восстановления. Но остановка может произойти не сразу, ведь проверка запроса на остановку выполняется только в некоторых местах.

Как убедиться, что восстановление приостановлено? Есть функция pg_is_wal_replay_paused, которая могла бы дать ответ на этот вопрос, но на самом деле возвращает не совсем то, что нужно. Она возвращает истину в том случае, когда была запрошена приостановка функцией pg_wal_replay_pause, но реальная остановка возможно еще не случилась.

Чтобы не менять прежнее поведение, функцию pg_is_wal_replay_paused оставили как есть. И добавили новую: pg_get_wal_replay_pause_state, возвращающую значения:

  • not paused ― восстановление работает;
  • paused requested ― сразу после вызова pg_wal_replay_pause и до реальной остановки;
  • paused ― восстановление приостановлено.

ALTER SUBSCRIPTION… ADD/DROP PUBLICATION
commit: 82ed7748

Речь пойдет о логической репликации. В одной подписке можно подписаться на несколько публикаций. Это нормально:

CREATE SUBSCRIPTION sub1 .. PUBLICATION pub1, pub2, pub3;

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

ALTER SUBSCRIPTION sub1 SET PUBLICATION новый_список_публикаций;

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

В 14 версии при обновлении подписки, кроме SET, можно еще использовать:

ALTER SUBSCRIPTION sub1 ADD PUBLICATION pub4;
ALTER SUBSCRIPTION sub1 DROP PUBLICATION pub1;

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


Предопределенная роль pg_database_owner
commit: a14a0118

Роль pg_database_owner полезна для выдачи привилегий владельцам баз данных.

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

Для этого в шаблонной базе выдадим соответствующую привилегию для pg_database_owner.

\c template1 postgres
GRANT SELECT ON pg_statistic TO pg_database_owner;

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

\c postgres postgres
CREATE ROLE app_owner LOGIN;
CREATE DATABASE app_db OWNER app_owner;

Проверим:

\c app_db app_owner
SELECT COUNT(*) FROM pg_statistic;
 count
-------
   405

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

Предопределенные роли pg_read_all_data и pg_write_all_data
commit: 6c3ffd69

Нужен пользователь, который может всё читать, но ничего не может менять?

CREATE ROLE readonly LOGIN BYPASSRLS;
GRANT pg_read_all_data TO readonly;

Всё!

Пользователь readonly получил право USAGE на все схемы и права на чтение всех таблиц (включая системные, типа pg_authid), представлений и последовательностей. И так во всех базах данных кластера.

Дежурному админу нужно делать pg_dump/pg_dumpall, но не хочется выдавать ему права суперпользователя? Хм, что может быть проще.

Вторая роль pg_write_all_data дает права INSERT, UPDATE, DELETE на все таблицы, представления и последовательности. Опять же во всех схемах всех базах данных кластера.

Сжатие SSL больше не поддерживается
commit: f9264d15

Использовать сжатие SSL не рекомендовалось уже с 11 версии. Теперь от поддержки сжатия окончательно отказались.

Аутентификация по уникальному имени в клиентском сертификате
commit: 6d7a6fea

В аутентификации по сертификату появился новый параметр clientname, в котором можно указать какое имя в сертификате CN (Common Name) или DN (Distinguished Name) использовать для поиска соответствия с именем пользователя. Если параметр clientname не указан, то как и раньше используется CN.

SSL: установка расширения Server Name Indication (SNI)
commit: 5c55dc8b

libpq по умолчанию будет устанавливать расширение TLS «Server Name Indication». Имя хоста передается открытым текстом. Если это неприемлемо, то установку SNI можно отменить параметром libpq sslsni.

Сервер


Значение параметра vacuum_cost_page_miss снижено до 2
commit: e19594c5

Широкое использование дисков SSD заставляет разработчиков вносить коррективы в настройки системы.

В 12 версии значение параметра autovacuum_vacuum_cost_delay уменьшили с 20 до 2 миллисекунд и автоочистка по умолчанию стала работать в 10 раз агрессивнее.

На этот раз уменьшили значение vacuum_cost_page_miss c 10 до 2. Очистка от этого не станет агрессивнее, но такое значение лучше отражает соотношение затрат на обработку страницы, которую нужно загрузить в буферный кеш (vacuum_cost_page_miss) и которую придется сбросить на диск (vacuum_cost_page_dirty, по умолчанию 20).

Параметр checkpoint_completion_target: значение по умолчанию 0.9
commit: bbcc4eb2

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

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

Параллельное выполнение очистки и CREATE INDEX | REINDEX… CONCURRENTLY
commit: d9d07622

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

Теперь научили VACUUМ не ждать завершения этих двух видов команд. Очень хорошо, ведь на создание и/или перестройку больших индексов может уйти много времени.

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

Очистка пропускает сканирование индексов, когда это малоэффективно
commit: 5100010e

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

CREATE TABLE t (col1 int primary key, col2 int unique)
    WITH (autovacuum_enabled=false);
INSERT INTO t SELECT f.x, f.x FROM generate_series(1,100000) AS f(x);
UPDATE t SET col2 = -col2 WHERE col1 < 100;

Выполняем очистку:

VACUUM VERBOSE t;
INFO:  vacuuming "public.t"
INFO:  "t": index scan bypassed: 443 pages from table (0.23% of total) have 99 dead item identifiers
INFO:  launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO:  "t": found 99 removable, 100000 nonremovable row versions in 443 out of 443 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 27459
0 pages removed.
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s.

Любопытна вторая строка. Очистка сообщает, что в таблице очень мало страниц с мертвыми строками (0,23%). И поэтому пропускает очистку индексов.

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

Сколько же это «мало»? Мало ― когда количество страниц содержащих мертвые строки не превышает 2% от общего количества страниц в таблице.

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

VACUUM: очистка основной таблицы без TOAST-части
commit: 7cb3048f

Команда VACUUM выполняет очистку как основной таблицы, так и её TOAST-части. Если по каким-то причинам таблица TOAST неоправданно сильно разрослась или требует срочной заморозки строк, то её можно очистить отдельно от основной таблицы той же самой командой VACUUM. А вот вручную очистить только основную таблицу, без TOAST, возможности не было.

Теперь она появилась:

VACUUM (PROCESS_TOAST OFF) tickets;

Для VACUUM FULL отключить обработку таблицы TOAST нельзя. По умолчанию параметр PROCESS_TOAST включен.

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

Завершение автоочистки, если «пропал» postmaster
commit: 4753ef37

Если процесс postmaster аварийно завершается, то об этом узнают все остальные процессы и также завершаются.

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

Функция date_bin
commit: 49ab61f0

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

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

SELECT date_bin('6 hours', scheduled_departure, '1970-01-01') AS day_part,
       count(*)
FROM   flights
WHERE  scheduled_departure::date = bookings.now()::date
GROUP BY day_part
ORDER BY day_part;
        day_part        | count
------------------------+-------
 2017-08-15 00:00:00+03 |    13
 2017-08-15 06:00:00+03 |   224
 2017-08-15 12:00:00+03 |   255
 2017-08-15 18:00:00+03 |    53

Агрегатная функция bit_xor
commit: f9a0392e

В PostgreSQL давно есть побитовые агрегатные функции bit_and и bit_or. Теперь добавилась и bit_xor для исключающего или. Функция выдает установленный бит, если в агрегируемой выборке этот бит установлен в нечетном количестве значений.

На примере битовых строк (функция определена также для целых чисел):

SELECT bit_xor(n) FROM (
VALUES
  (b'100'),
  (b'110'),
  (b'111')
) t(n);
 bit_xor
---------
 101
(1 row)

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

Функция bit_count
commit: a6715af1

Новая функция SQL показывает, сколько бит установлено:

SELECT bit_count(B'001'), bit_count(B'011'), bit_count(B'111');
 bit_count | bit_count | bit_count
-----------+-----------+-----------
         1 |         2 |         3

Функция unistr
commit: f37fec83

Новая функция преобразует спецпоследовательности с символами Unicode в строку Unicode.

Например, вот первые несколько символов расширенной кириллицы:

SELECT unistr('\u0400\u0401\u0402\u0403\u0404');
 unistr
--------
 ЀЁЂЃЄ

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

Функция trim_array
commit: 0a687c8f

Функция обрезает с конца массива указанное количество элементов:

SELECT trim_array(ARRAY['A','B','C','D','E'], 2);
 trim_array
------------
 {A,B,C}

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

Функция extract, аналог date_part, но возвращает numeric
commit: a2da77cd

Конструкция EXTRACT преобразуется в вызов функции date_part, которая возвращает значение типа double precision:

SELECT pg_typeof(extract(epoch from now()));
    pg_typeof     
------------------
 double precision

В некоторых случаях точности этого типа может не хватить. Проблему бы решила замена у функции date_part типа возвращаемого значения с double precision на numeric, но менять тип возвращаемого значения существующей функции опасно.

Решили сделать так. Создали новый набор перегруженных функций с именем extract, входные параметры которых соответствуют параметрам date_part, а тип возвращаемого значения numeric. Конструкция EXTRACT теперь преобразуется в вызов новой функции extract и возвращает numeric:

SELECT pg_typeof(extract(epoch from now()));
 pg_typeof
-----------
 numeric

Функции date_part оставили без изменения для обратной совместимости.

Выбор метода сжатия данных
commit: bbe0a81d

PostgreSQL умеет сжимать большие значения в столбцах с типами переменной длины: text, bytea, json[b] и др. Для сжатия всегда используется zlib.

Но только не в PostgreSQL 14. Теперь можно выбирать из двух методов сжатия: zlib (точнее pglz) или lz4. По умолчанию используется pglz.

CREATE TABLE json_data (name text, data jsonb);
\d+ json_data
                                       Table "public.json_data"
 Column | Type  | Collation | Nullable | Default | Storage  | Compression ...
--------+-------+-----------+----------+---------+----------+-------------
 name   | text  |           |          |         | extended | pglz        
 data   | jsonb |           |          |         | extended | pglz        
Access method: heap

Но умолчанием можно управлять в новом параметре конфигурации default_toast_compression.

Изменим метод сжатия для столбца jsonb:

ALTER TABLE json_data ALTER COLUMN data SET COMPRESSION lz4;
\d+ json_data
                                       Table "public.json_data"
 Column | Type  | Collation | Nullable | Default | Storage  | Compression ...
--------+-------+-----------+----------+---------+----------+-------------
 name   | text  |           |          |         | extended | pglz        
 data   | jsonb |           |          |         | extended | lz4         
Access method: heap

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

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

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

Расширенная статистика по выражениям
commit: a4d75c86

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

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

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM flights WHERE to_char(scheduled_departure, 'DY') = 'MON';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------
 Gather  (cost=1000.00..5627.29 rows=1074 width=63)
         (actual rows=30552 loops=1)
...

Как видим планировщик ошибается в оценке кардинальности примерно в 30 раз. Что делать? Можно было бы создать функциональный индекс по выражению, для таких индексов статистика собирается отдельно. Но только не в этом примере, ведь функция to_char имеет категорию изменчивости stable, что не позволяет создать индекс по ней.

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

CREATE STATISTICS s_flights ON to_char(scheduled_departure, 'DY')
    FROM flights;
ANALYZE flights;

Статистики по выражениям можно посмотреть в новом представлением pg_stats_ext_exprs (по аналогии с pg_stats):

SELECT * FROM pg_stats_ext_exprs WHERE statistics_name = 's_flights'\gx
-[ RECORD 1 ]----------+--------------------------------------------------
schemaname             | bookings
tablename              | flights
statistics_schemaname  | bookings
statistics_name        | s_flights
statistics_owner       | postgres
expr                   | to_char(scheduled_departure, 'DY'::text)
null_frac              | 0
avg_width              | 7
n_distinct             | 7
most_common_vals       | {SAT,TUE,WED,THU,MON,SUN,FRI}
most_common_freqs      | {0.1483,0.14606667,0.14523333,0.14496666,0.14436667,0.14196667,0.1291}
histogram_bounds       |
correlation            | 0.1514369
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

Ну а что стало с оценкой кардинальности запроса? Всё в порядке:

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM flights WHERE to_char(scheduled_departure, 'DY') = 'MON';
                                        QUERY PLAN                                         
--------------------------------------------------------------------------
 Seq Scan on flights  (cost=0.00..5847.00 rows=31020 width=63)
                      (actual rows=30552 loops=1)
...

FDW: поддержка TRUNCATE
commit: 8ff1c946

Обертки сторонних данных теперь могут выполнять TRUNCATE для сторонних таблиц. В первую очередь поддержку этой команды добавили в postgres_fdw.

Очередь за разработчиками других fdw.

postgres_fdw: управление удаленными сеансами
commit: b1be3074

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

А если нам нужно всего один раз что-то получить из внешней таблице и больше туда обращаться не надо, дальше просто работаем локально? Удаленный сеанс будет «болтаться» без использования, и с этим ничего нельзя поделать. Средств для управления удаленным сеансом у нас нет.

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

Создадим два внешних сервера к соседней в кластере базе данных demo. Первый сервер повторно использует один раз открытое удаленное соединение, а второй каждый раз будет открывать новое.

CREATE EXTENSION postgres_fdw;

CREATE SERVER demo_srv_cached FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'demo');
CREATE SERVER demo_srv_not_cached FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'demo', keep_connections 'off');

CREATE USER MAPPING FOR postgres SERVER demo_srv_cached
OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING FOR postgres SERVER demo_srv_not_cached
OPTIONS (user 'postgres', password 'postgres');

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

CREATE FOREIGN TABLE bookings_cached (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
) SERVER demo_srv_cached 
  OPTIONS (schema_name 'bookings', table_name 'bookings');

CREATE FOREIGN TABLE bookings_not_cached (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
) SERVER demo_srv_not_cached 
  OPTIONS (schema_name 'bookings', table_name 'bookings');

Выполняем запрос к таблице, где соединение не кешируется:

SELECT count(*) FROM bookings_not_cached;
  count  
---------
 2111110

Ищем удаленный сеанс:

SELECT datname, pid  FROM pg_stat_activity
WHERE application_name = 'postgres_fdw';
 datname | pid
---------+-----
(0 rows)

А его уже нет, он закрыт. Другое дело запрос к таблице bookings_cached:

SELECT count(*) FROM bookings_cached;
  count  
---------
 2111110


SELECT datname, pid  FROM pg_stat_activity 
WHERE application_name = 'postgres_fdw';
 datname |  pid  
---------+-------
 demo    | 17876

Сеанс на месте и ждет новых запросов.

postgres_fdw: возможность импортировать определение отдельных секций
commit: a3740c48

Команда IMPORT FOREIGN SCHEMA разрешает импортировать определение всей секционированной таблицы целиком, со всеми секциями. А вот определение отдельной секции импортировать нельзя.

В случае такой необходимости можно воспользоваться CREATE FOREIGN TABLE, но теперь можно указать нужную секцию и во фразе LIMIT TO команды IMPORT FOREIGN SCHEMA.

Секционирование


ALTER TABLE… DETACH PARTITION CONCURRENTLY
commit: 71f4c8c6

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

Добавлять новые секции, не останавливая приложение, научились еще в 12 версии. ALTER TABLE… ATTACH PARTITION требует блокировку ShareUpdateExclusiveLock, совместимую с операциями DML. А вот с отключением секцией так не получилось сделать. ALTER TABLE… DETACH PARTITION требует эксклюзивной блокировки.

Спустя два года доработали и отключение секций. К DETACH PARTITION теперь можно добавить ключевое слово CONCURRENTLY.

В новом виде команда выполняется в две транзакции. Первая помечает секцию (pg_inherits.inhdetachpending) как планируемую к отключению. Это дает возможность параллельным запросам, которые уже выполняются или только запланировали доступ к секции, отработать не меняя плана, а все новые запросы уже не будут «видеть» секцию. Теперь нужно подождать завершения всех транзакций, работающих с таблицей и начатых до изменения признака pg_inherits.inhdetachpending, и во второй транзакции непосредственно отключить секцию.

Такая реализация несет в себе важные ограничения:

  • DETACH PARTITION CONCURRENTLY не может выполняться в начатой транзакции. И, как следствие, команда не может вызываться в функции. Более того, реализация такова, что и из процедуры не может вызываться. Если захочется написать обвязку вокруг команды (например добавление новой секции и сразу отключение и удаление старой), то придется это делать скриптами на клиенте.

  • У таблицы не должно быть секции по умолчанию.

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

CREATE TABLE logdata (ts date) PARTITION BY RANGE (ts);
CREATE TABLE ts_20210401 PARTITION OF logdata
    FOR VALUES FROM ('2021-04-01'::date) TO ('2021-04-02'::date);
CREATE TABLE ts_20210402 PARTITION OF logdata
    FOR VALUES FROM ('2021-04-02'::date) TO ('2021-04-03'::date);

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

|    BEGIN;
|    INSERT INTO logdata VALUES ('2021-04-02'::date);

Теперь отключаем секцию за 1 апреля в первом сеансе:

ALTER TABLE logdata DETACH PARTITION ts_20210401 CONCURRENTLY;

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

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

||    INSERT INTO logdata VALUES ('2021-04-01'::date);
ERROR:  no partition of relation "logdata" found for row
DETAIL:  Partition key of the failing row contains (ts) = (2021-04-01).

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

||    BEGIN;
||    INSERT INTO logdata VALUES ('2021-04-02'::date);
||    INSERT 0 1

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

На случай сбоя во время ожидания предусмотрена команда, завершающая начатое:

ALTER TABLE .. DETACH PARTITION .. FINALYZE;

Обработка UPDATE и DELETE для секционированных таблиц
commit: 86dc9005

Узел плана ModifyTable, используемый для выполнения любых команд UPDATE и DELETE, радикально переписали. Но сделано это в первую очередь для оптимизации работы с секционированными таблицами.

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

CREATE TABLE bookings_range (
       book_ref     character(6),
       book_date    timestamptz,
       total_amount numeric(10,2)
   ) PARTITION BY RANGE(book_date);

CREATE TABLE p201607 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
CREATE TABLE p201608 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE p201609 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');

INSERT INTO bookings_range
SELECT * FROM bookings WHERE book_date < '2016-10-01';

В следующем запросе используется функция to_timestamp с категорией изменчивости STABLE:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings_range
WHERE  book_date = to_timestamp('01.08.2016','DD.MM.YYYY');
                                        QUERY PLAN                                        
--------------------------------------------------------------------------
 Gather (actual rows=3 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append (actual rows=1 loops=3)
         Subplans Removed: 2
         ->  Parallel Seq Scan on p201608 bookings_range_1 
             (actual rows=1 loops=3)
               Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
               Rows Removed by Filter: 56109

Очень хороший план. Значение функции вычисляется при инициализации плана запроса и часть секций исключается из просмотра (видно по строке Subplans Removed). Такое поведение появилось еще в 12 версии (этот пример взят из статьи двухлетней давности).

Но вот при изменении и удалении строк всё гораздо хуже. Следующий запрос на удаление и в 12, и в 13 версии работает плохо:

-- BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
DELETE FROM bookings_range
WHERE  book_date = to_timestamp('01.08.2016','DD.MM.YYYY');
-- ROLLBACK;
                                     QUERY PLAN                                     
--------------------------------------------------------------------------
 Delete on bookings_range (actual rows=0 loops=1)
   Delete on p201607 bookings_range_1
   Delete on p201608 bookings_range_2
   Delete on p201609 bookings_range_3
   ->  Seq Scan on p201607 bookings_range_1 (actual rows=0 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 10878
   ->  Seq Scan on p201608 bookings_range_2 (actual rows=3 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 168326
   ->  Seq Scan on p201609 bookings_range_3 (actual rows=0 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 165421

Как видим, просматриваются все секции.

А теперь этот же запрос на удаление в 14 версии:

                                        QUERY PLAN                                        
--------------------------------------------------------------------------
 Delete on bookings_range (actual rows=0 loops=1)
   Delete on p201607 bookings_range
   Delete on p201608 bookings_range_1
   Delete on p201609 bookings_range
   ->  Append (actual rows=3 loops=1)
         Subplans Removed: 2
         ->  Seq Scan on p201608 bookings_range_1 (actual rows=3 loops=1)
               Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
               Rows Removed by Filter: 168326

Поведение практически идентичное запросу на выборку!

Автоматический сбор статистики по секционированным таблицам
commit: 0827e8af

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

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

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

Оптимизация добавления внешнего ключа на секционированную таблицу
commit: c3ffe348

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

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

Команды SQL


Фразы CYCLE и SEARCH для рекурсивных запросов
commit: 3696a600, f4adc41c

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

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

WITH RECURSIVE r(pktable, fktable, is_cycle, path) AS (
    SELECT pktable, fktable, false, ARRAY[pktable]
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_constraint'::regclass
  UNION ALL
    SELECT f.pktable, f.fktable, f.pktable = ANY(path), r.path||f.pktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    AND    NOT r.is_cycle
)
SELECT * FROM r WHERE NOT is_cycle;
    pktable    |    fktable    | is_cycle |            path            
---------------+---------------+----------+----------------------------
 pg_constraint | pg_constraint | f        | {pg_constraint}
 pg_constraint | pg_trigger    | f        | {pg_constraint}
 pg_trigger    | pg_trigger    | f        | {pg_constraint,pg_trigger}
(3 rows)

В качестве результата было бы достаточно первых двух столбцов pktable и fktable. Но в запрос добавлены еще два столбца is_cycle и path. Они нужны для того, чтобы рекурсивная часть запроса не ушла в бесконечный цикл. А это обязательно бы произошло, ведь на pg_constraint ссылается не только pg_trigger, но и сама pg_constraint. Кстати и pg_trigger ссылается сама на себя.

В 14 версии запрос может быть переписан так:

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_constraint'::regclass
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
)
  CYCLE pktable SET is_cycle USING path
SELECT * FROM r;
    pktable    |    fktable    | is_cycle |              path                     
---------------+---------------+----------+-------------------------------
 pg_constraint | pg_constraint | f        | {(pg_constraint)}
 pg_constraint | pg_trigger    | f        | {(pg_constraint)}
 pg_constraint | pg_constraint | t        | {(pg_constraint),(pg_constraint)}
 pg_trigger    | pg_trigger    | f        | {(pg_constraint),(pg_trigger)}
 pg_constraint | pg_trigger    | t        | {(pg_constraint),(pg_constraint)}
 pg_trigger    | pg_trigger    | t        | {(pg_constraint),(pg_trigger),(pg_trigger)}
(6 rows)

Что изменилось?

  • Появилось новая фраза CYCLE, в которой объявляется столбец, значения которого могут зациклиться. Здесь же объявляются названия для уже знакомых по предыдущему запросу столбцов с признаком зацикливания (SET) и массива значений (USING).
  • Столбцы, заданные в SET и USING, неявно создаются на этапе переписывания (rewrite) и могут использоваться в запросе.
  • Дополнительные три строки результата с признаком зацикливания можно отфильтровать во фразе WHERE внешнего запроса. Здесь они оставлены для наглядности, чтобы показать что запрос не уходит в бесконечный цикл.

Теперь посмотрим, как можно сортировать результаты рекурсивного запроса. Повторим предыдущий запрос для таблицы pg_attribute. В запросе дополнительно используется группировка, чтобы убрать дубликаты (например pg_constraint дважды ссылается на pg_attribute). А сортировка выполняется по столбцу sort_by, который объявлен в еще одной новой фразе SEARCH:

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_attribute'::regclass
    GROUP BY pktable, fktable
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    GROUP BY f.pktable, f.fktable
) SEARCH DEPTH FIRST BY pktable SET sort_by
  CYCLE pktable SET is_cycle USING path
SELECT pktable, fktable, sort_by FROM r WHERE NOT is_cycle
ORDER BY sort_by;
     pktable      |        fktable        |                    sort_by                    
------------------+-----------------------+-----------------------------------------------
 pg_attribute     | pg_attrdef            | {(pg_attribute)}
 pg_attribute     | pg_constraint         | {(pg_attribute)}
 pg_attribute     | pg_index              | {(pg_attribute)}
 pg_attribute     | pg_statistic          | {(pg_attribute)}
 pg_attribute     | pg_trigger            | {(pg_attribute)}
 pg_attribute     | pg_partitioned_table  | {(pg_attribute)}
 pg_attribute     | pg_statistic_ext      | {(pg_attribute)}
 pg_constraint    | pg_constraint         | {(pg_attribute),(pg_constraint)}
 pg_constraint    | pg_trigger            | {(pg_attribute),(pg_constraint)}
 pg_trigger       | pg_trigger            | {(pg_attribute),(pg_constraint),(pg_trigger)}
 pg_trigger       | pg_trigger            | {(pg_attribute),(pg_trigger)}
 pg_statistic_ext | pg_statistic_ext_data | {(pg_attribute),(pg_statistic_ext)}

Фраза SEARCH DEPTH FIRST BY pktable SET sort_by неявно создает столбец sort_by, опять же, на этапе переписывания запроса. Это так называемая сортировка в глубину. Если приглядеться, то видно, что столбец sort_by представляет собой точно такой же массив, как и path. Поэтому для сортировки в глубину можно не писать SEARCH, а сразу сортировать по столбцу path.

Другой вариант SEARCH BREADTH FIRST BY pktable SET sort_by.

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

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

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_attribute'::regclass
    GROUP BY pktable, fktable
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    GROUP BY f.pktable, f.fktable
) SEARCH BREADTH FIRST BY pktable SET sort_by
  CYCLE pktable SET is_cycle USING path
SELECT pktable, fktable, sort_by FROM r WHERE NOT is_cycle
ORDER BY sort_by;
     pktable      |        fktable        |       sort_by        
------------------+-----------------------+----------------------
 pg_attribute     | pg_attrdef            | (0,pg_attribute)
 pg_attribute     | pg_constraint         | (0,pg_attribute)
 pg_attribute     | pg_index              | (0,pg_attribute)
 pg_attribute     | pg_statistic          | (0,pg_attribute)
 pg_attribute     | pg_trigger            | (0,pg_attribute)
 pg_attribute     | pg_partitioned_table  | (0,pg_attribute)
 pg_attribute     | pg_statistic_ext      | (0,pg_attribute)
 pg_constraint    | pg_constraint         | (1,pg_constraint)
 pg_constraint    | pg_trigger            | (1,pg_constraint)
 pg_trigger       | pg_trigger            | (1,pg_trigger)
 pg_statistic_ext | pg_statistic_ext_data | (1,pg_statistic_ext)
 pg_trigger       | pg_trigger            | (2,pg_trigger)

Столбец sort_by в таком виде можно сформировать и без SEARCH BREADTH FIRST. Для этого в запрос добавляют еще один столбец, часто называемый level, для уровня вложенности, значение которого увеличивается на единицу в рекурсивной части запроса. Но теперь это делается автоматически.

Подводя итоги. Фразы CYCLE и SEARCH не добавляют принципиально новых возможностей для рекурсивных запросов. Но упрощают их написание. Теперь не нужно явно объявлять служебные столбцы и следить за их правильным формированием. Это не только удобнее, но и сокращает количество потенциальных ошибок.

К тому же эти фразы описаны в стандарте SQL, а теперь стали частью PostgreSQL.

GROUP BY DISTINCT
commit: be45be9c

Казалось бы, зачем DISTINCT в GROUP BY? Группировка на то и нужна, чтобы отсечь дубликаты.

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

Например вот разбивка рейсов по статусам и аэропортам вылета (выборка ограничена вылетами из Усть-Кута):

SELECT status, departure_airport, count(*)
FROM   flights_v
WHERE  departure_city = 'Усть-Кут'
GROUP BY ROLLUP(status, departure_airport);
  status   | departure_airport | count
-----------+-------------------+-------
           |                   |   396
 Arrived   | UKX               |   366
 On Time   | UKX               |     1
 Scheduled | UKX               |    28
 Cancelled | UKX               |     1
 Arrived   |                   |   366
 On Time   |                   |     1
 Cancelled |                   |     1
 Scheduled |                   |    28

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

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

GROUP BY ROLLUP(status,departure_airport), ROLLUP(status,arrival_airport);

Чтобы избежать этих дублей пригодится указание DISTINCT:

GROUP BY DISTINCT ROLLUP(status,departure_airport), ROLLUP(status,arrival_airport);

К тому же возможность указать DISTINCT в GROUP BY описана в стандарте SQL.

Псевдоним для условия соединения в запросе
commit: 055fee7e

В стандарте SQL:2016, а теперь и в PostgreSQL, есть возможность указать псевдоним для условия соединения. К столбцам, по которым выполняется соединение, можно обращаться по псевдониму:

SELECT x.*
FROM   ticket_flights JOIN boarding_passes USING (ticket_no, flight_id) AS x         
LIMIT 1;
   ticket_no   | flight_id
---------------+-----------
 0005435189093 |    198393

Подпрограммы на языке SQL
commit: e717a9a1

Текст функций и процедур в PostgreSQL задается и хранится в виде строки. Каждый раз при вызове подпрограммы её текст интерпретируется.

Эти утверждения не всегда будут действительны для подпрограмм на языке SQL в 14 версии.

Для соответствия стандарту и совместимости с другими СУБД тело подпрограммы на языке SQL теперь может быть указано следующим образом:

CREATE FUNCTION maximum(a integer, b integer) RETURNS integer
LANGUAGE SQL
    RETURN CASE WHEN a > b THEN a ELSE b END;

SELECT maximum(10,12);
 maximum
---------
      12

Что изменилось?
  • Нет конструкции AS $$ текст функции $$.
  • Новое ключевое слово RETURN для возврата значения.
  • При создании функции команда предварительно разбирается, результат разбора сохраняется в pg_proc.prosqlbody.
  • Текст функции не сохраняется в pg_proc.prosrc.

При вызове функции её команды заново не интерпретируются!

Отсюда следствие. Нет возможности использовать полиморфные типы данных для аргументов:

CREATE FUNCTION maximum(a anyelement, b anyelement) RETURNS anyelement
LANGUAGE SQL 
    RETURN CASE WHEN a > b THEN a ELSE b END;
ERROR:  SQL function with unquoted function body cannot have polymorphic arguments

Если полиморфная функция всё-таки нужна, то можно написать «по-старинке»:

CREATE FUNCTION maximum(a anyelement, b anyelement) RETURNS anyelement
LANGUAGE SQL
    AS $$SELECT CASE WHEN a > b THEN a ELSE b END$$;
CREATE FUNCTION

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

CREATE TABLE t (id int);

CREATE FUNCTION add_and_count (p int) RETURNS bigint
LANGUAGE SQL
BEGIN ATOMIC
    INSERT INTO t VALUES(p);
    SELECT count(*) FROM t;
END;

SELECT add_and_count(100);
 add_and_count
---------------
             1

Команды оборачиваются конструкцией BEGIN ATOMIC… END, возвращается значение последней команды.

Хранение функции в «разобранном» виде позволяет в том числе определить зависимости между объектами. Также как и для представлений. Например, функция add_and_count зависит от таблицы t. Точнее сохраняется зависимость от идентификатора таблицы, ее имя можно и поменять:

ALTER TABLE t RENAME TO t_new;
SELECT add_and_count(101);
 add_and_count
---------------
             2

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

DROP TABLE t_new;
ERROR:  cannot drop table t_new because other objects depend on it
DETAIL:  function add_and_count(integer) depends on table t_new
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Разумеется можно обратно получить текст функции, теперь уже с новым именем таблицы:

\sf add_and_count
CREATE OR REPLACE FUNCTION public.add_and_count(p integer)
 RETURNS bigint
 LANGUAGE sql
BEGIN ATOMIC
 INSERT INTO t_new (id)
   VALUES (add_and_count.p);
 SELECT count(*) AS count
    FROM t_new;
END

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


pg_amcheck ― утилита для поиска поврежденных данных
commit: 866e24d4, 97060928

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

А новая утилита командной строки pg_amcheck представляет собой удобный интерфейс для выполнения этих проверок.

Модуль pg_standby удален
commit: 514b411a

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

pg_dump --extension
commit: 6568cef2

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

В следующем примере в базе данных demo есть схема с таблицей, использующей столбец из расширения ltree.

CREATE SCHEMA app;
CREATE EXTENSION ltree SCHEMA app;
CREATE TABLE t (id int, data app.ltree);

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

$ pg_dump -d demo | grep 'CREATE EXTENSION'
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA app;

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

$ pg_dump -d demo -n app | grep 'CREATE EXTENSION'
$ 

Новый параметр --extension приходит на помощь:

$ pg_dump -d demo -n app --extension ltree | grep 'CREATE EXTENSION'
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA app;

Функции pg_terminate_backend и pg_wait_for_backend_termination
commit: aaf04325

Известно, что функция pg_terminate_backend отправляет процессу сигнал SIGTERM с требованием остановиться. Но процесс может остановиться не сразу, а в отдельных случаях может вообще не остановиться.

Теперь функции pg_terminate_backend можно параметром указать, сколько времени подождать до завершения процесса. Если за это время процесс останавливается, то pg_terminate_backend вернет истину, иначе false.

А если таймаут не указывать (текущее поведение), то сразу возвращается истина как признак того, что сигнал SIGTERM отправлен. В этом случае для уверенности в том, что процесс всё-таки остановился, можно воспользоваться новой функцией pg_wait_for_backend_termination, которая подождет завершения указанного процесса в течение указанного времени, по умолчанию 5 секунд.

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


libpq: возможность задать предпочтение для подключения к реплике
commit: ee28cacf

В строке подключения libpq можно указать несколько узлов. Это может быть полезным для балансировки читающей нагрузки между основным сервером и репликами. При подключении узлы перебираются по списку, пока подключение не будет установлено. Теперь можно дополнительно указать, какой тип сервера нужно выбрать. Для этого у параметра подключения target_session_attrs появились новые значения. Кроме any и read-write теперь можно задать:
  • read-only ― допускаются подключения либо к реплике, либо должен быть включен параметр default_transaction_read_only;
  • primary ― допускаются подключения только к основному серверу;
  • standby ― допускаются подключения только реплике;
  • prefer-standby ― сначала постараться найти в списке серверов реплику и подключиться к ней; если реплики так и не нашлось, то пойти по второму кругу и подключиться к любому серверу.

Трассировка в libpq
commit: 198b3716

Для трассировки взаимодействия между клиентом и сервером в библиотеке libpq есть функция PQtrace. По признанию самих разработчиков её вывод долгое время был практически бесполезен. В 14 версии ситуация изменится в лучшую сторону.

psql: указание типов аргументов в \df
commit: a3027e1e

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

В \df после имени функции можно перечислить типы данных для параметров. Например, агрегатная функция min перегружена 21 раз. Вариант для целочисленного параметра находится так:

\df min int
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | min  | integer          | integer             | agg
(1 row)

Имена типов задаются шаблоном, так же как в команде \dT.

Заодно в \dT добавили возможность указывать массивы (bookings[]) и принятые сокращения (int вместо integer).

psql: выход из редактора без сохранения
commit: 55873a00

psql записывает в буфер последную выполненную команду SQL. Команду в буфере можно редактировать, вызвав \е. Но что будет, если, находясь в редакторе, мы передумали и решили выйти без сохранения? А будет вот что. Команда в буфере останется прежней, но она будет выполнена! А раз мы передумали редактировать команду, то возможно и выполнять её не собирались.

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

Такие же изменения для похожих команд \e file, \ev, \ef.

UPD: Следующий патч откатили 15.04. Спасибо Виктору Егорову.
psql: показывать результаты всех запросов
commit: 3a513067

В psql есть возможность за один раз отправить несколько запросов на сервер и выполнить их в одной транзакции. Для этого команды разделяются символами \;

select 1\; select 2;
 ?column?
----------
        2

На экран выводится результат только последней команды, хотя выполнились обе.

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

select 1\; select 2;
 ?column?
----------
        1
(1 row)

 ?column?
----------
        2

Прежнее поведение можно вернуть, отключив новую встроенную переменную SHOW_ALL_RESULTS.

psql: автодополнение по табуляции улучшено для TRUNCATE
commit: 6b40d9bd

pgbench: синхронизация начала работы между потоками
commit: aeb57af8

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

С проблемой столкнулся Андрес Фройнд при тестировании серии патчей, повышающих масштабируемость подключений.

pgbench: функция permute для распределения случайных значений
commit: 6b258e3d

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

\set r random_zipfian(0, 100000000, 1.07)
\set k abs(hash(:r)) % 1000000

Теперь для этих целей можно можно воспользоваться новой функцией permute:

\set size 1000000
\set r random_zipfian(1, :size, 1.07)
\set k 1 + permute(:r, :size)

ECPG: новая команда DECLARE STATEMENT
commit: ad8305a4

Встраиваемый SQL обзавелся новой командой DECLARE STATEMENT.

Разное


Полноценная заморозка с COPY WITH FREEZE
commit: 7db0cd21

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

Посмотрим в деле на 13 версии. Сделаем копию таблицы bookings при помощи COPY WITH FREEZE.

COPY bookings TO '/home/pluzanov/pg13/bookings.txt';

BEGIN;
CREATE TABLE bookings_copy (LIKE bookings) WITH (autovacuum_enabled=off);
COPY bookings_copy FROM '/home/pluzanov/pg13/bookings.txt' WITH FREEZE;
COMMIT;

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

CHECKPOINT;
SELECT pg_stat_reset_shared('bgwriter');

Теперь вызовем очистку:

VACUUM VERBOSE bookings_copy;
INFO:  vacuuming "bookings.bookings_copy"
INFO:  "bookings_copy": found 0 removable, 2111110 nonremovable row versions in 13447 out of 13447 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1197
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.49 s, system: 0.26 s, elapsed: 5.01 s.

Судя по отчету, очистке нечего было делать во всех 13447 страницах таблицы. Но почему потребовалось столько времени?

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

CHECKPOINT;
SELECT buffers_checkpoint + buffers_clean + buffers_backend
FROM   pg_stat_bgwriter;
 ?column?
----------
    13460

Вот это номер! Есть стойкое ощущение, что очистка переписала все страницы таблицы, в которой ей как бы нечего делать.

И это соответствует действительности. В реализации COPY WITH FREEZE были упущены важнейшие моменты:
  • Каждая страница данных в своем заголовке имеет признак полной видимости всех строк на этой странице. И этот признак не проставлялся, хотя все строки загружаются замороженными, а значит видимыми всем. А это в свою очередь значит, что последующая очистка будет вынуждена проставлять этот признак в каждой загруженной странице.
  • Не обновлялась карта видимости. Это опять же придется делать последующей очистке. Конечно, карта видимости сильно меньше в размерах чем собственно данные, но от недостоверной информации могут пострадать запросы, использующие доступ к таблице только по индексу.

Теперь ошибки в реализации исправлены, и в 14 версии COPY честнее замораживает строки.

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

COPY FROM: оптимизация перекодировки символов
commit: f82de5c4

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

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

Параметр ssl_crl_dir ― каталог со списком отзыва сертификатов (CRL, Certificate Revocation List) для SSL-сервера
commit: f5465fad

Раньше файл со списком отзыва сертификатов задавался конфигурационным параметром ssl_crl_file или в параметре sslcrl строки подключения libpq. Теперь, в дополнение к этому способу, можно указать еще и специально подготовленный каталог с файлами в конфигурационном параметре ssl_crl_dir или в параметре sslcrldir строки подключения.

pg_upgrade: проверка версии утилит нового сервера
commit: f06b1c59

При запуске pg_upgrade проверяет, что версия самой утилиты pg_upgrade совпадает с версией нового сервера. Для этого версия pg_upgrade сверялась с версией pg_ctl. Проверку расширили и стали проверять не только версию pg_ctl, но и остальных исполняемых файлов, задействованных в обновлении: initdb, pg_dump, pg_dumpall, pg_restore, psql, vacuumdb, pg_controldata, postgres.

Семантика и производительность обратных ссылок в регулярных выражениях
commit: 4aea704a, 0c3405cf

Изменение поведения Том Лейн показывает на таком примере:

SELECT regexp_match('foof', '(^f)o*\1');

В 13 версии запрос не найдет соответствия. В качестве обратной ссылки \1 используется фрагмент (^f), вместе с ограничивающим символом ^, который не может быть найден в конце строки.

Однако в POSIX ограничивающие символы не должны учитыватся в обратных ссылках, и в 14 версии запрос найдет вторую f:

SELECT regexp_match('foof', '(^f)o*\1');
 regexp_match 
--------------
 {f}

А заодно (второй коммит) и производительность поиска улучшили.

Оптимизация работы регулярных выражений
commit: 08c0d6ad, 824bf719, cebc1d34, 58104308, ea1268f6, 2a0af7fe, 0fc1af17, 4604f83f

Том Лейн не ограничился предыдущим патчем и решил оптимизировать механизм регулярных выражений. Работа вылилась в большую серию патчей.

Отмена долгого запроса при потере соединения с клиентом
commit: c30f54ad
UPD: патч добавлен 17.04. Спасибо Максиму Милютину.

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

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

Параметр пока будет работать только в операционных системах, которые поддерживают нестандартное расширение POLLRDHUP системного вызова poll, в частности на linux.


На этом всё. Выход PostgreSQL 14 ожидается осенью, ну а в июле начнется прием патчей в 15 версию. Ждем с нетерпением!
Теги:postgresqlpostgressql
Хабы: Блог компании Postgres Professional PostgreSQL SQL
Всего голосов 14: ↑14 и ↓0+14
Просмотры3K

Похожие публикации

Лучшие публикации за сутки

Информация

Дата основания
Местоположение
Россия
Сайт
www.postgrespro.ru
Численность
51–100 человек
Дата регистрации
Представитель
Иван Панченко

Блог на Хабре