PostgreSQL 14: Часть 5 или «весенние заморозки» (Коммитфест 2021-03)
Напомню, что всё самое интересное о первых четырех коммитфестах можно найти в предыдущих статьях серии: июльский, сентябрьский, ноябрьский, январский.
В этой пойдет речь о последнем, мартовском. Заранее предупреждаю, что статья получилась огромная. Но плохо ли это? Чем длиннее список новых возможностей, тем лучше 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% от общего количества страниц в таблице.
В будущем возможны дальнейшие оптимизации на эту тему. Например пропускать очистку отд
