Напомню, что всё самое интересное о первых четырех коммитфестах можно найти в предыдущих статьях серии: июльский, сентябрьский, ноябрьский, январский.
В этой пойдет речь о последнем, мартовском. Заранее предупреждаю, что статья получилась огромная. Но плохо ли это? Чем длиннее список новых возможностей, тем лучше 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_countCREATE 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.
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
Том Лейн не ограничился предыдущим патчем и решил оптимизировать механизм регулярных выражений. Работа вылилась в большую серию патчей.
На этом всё. Выход PostgreSQL 14 ожидается осенью, ну а в июле начнется прием патчей в 15 версию. Ждем с нетерпением!
ссылка на оригинал статьи https://habr.com/ru/company/postgrespro/blog/550632/
Добавить комментарий