PostgreSQL 14: Часть 4 или «январское наступление» (Коммитфест 2021-01)
Вот только несколько вопросов для затравки:
- Могут ли диапазоны содержать пропуски значений?
- Зачем нужна индексная нотация типу json?
- Может ли индекс при частых обновлениях разрастаться меньше, чем таблица? А вообще не разрастаться?
- Сколько времени простаивали сеансы в idle_in_transaction?
- Как построить ER-диаграмму для таблиц системного каталога?
Клиентские приложения
psql: \dX ― просмотр расширенной статистики
commit: ad600bba
Новая команда \dX показывает объекты расширенной статистики в виде списка.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics
-[ RECORD 1 ]+------------------------------------------------
Schema | bookings
Name | flights_from_to
Definition | departure_airport, arrival_airport FROM flights
Ndistinct |
Dependencies | defined
MCV | defined
Для каждого типа статистики (Dependencies, Ndistinct, MCV) отображается только факт сбора. Сами значения нужно смотреть в pg_statistic_ext_data, доступ к которой по умолчанию только у суперпользователей.
psql: \dtS показывает таблицы TOAST
commit: 7d80441d
Отдельную таблицу TOAST и раньше можно было посмотреть командой \d. Однако получить список таких таблиц командой \dt или \dtS было нельзя. Упущение исправлено, \dtS теперь показывает таблицы TOAST, поскольку они являются служебными.
Но есть один нюанс. Все таблицы TOAST расположены в схеме pg_toast, а эта схема вряд ли включена в search_path. Поэтому для получения списка требуется указать соответствующий шаблон:
\dtS pg_toast.*165*
List of relations
Schema | Name | Type | Owner
----------+----------------+-------------+----------
pg_toast | pg_toast_16529 | TOAST table | postgres
pg_toast | pg_toast_16539 | TOAST table | postgres
pg_toast | pg_toast_16580 | TOAST table | postgres
Автодополнение по табуляции в psql улучшено для команд: CLOSE, FETCH, MOVE и DECLARE
commit: 3f238b88
Дополнительное описание не требуется.
Документация
Вычитка и редактирование документации
commit: 2a5862f0
Многие отмечают, что у PostgreSQL прекрасная документация. А ведь её пишут сами разработчики, которые обычно не считаются мастерами пера. Как же удается поддерживать высокое качество? Всё просто. Как и в любом писательском деле нужны редакторы и корректоры. И вот Джастин Призби последние два года делает огромную и важную работу: вычитывает документацию. В результате появился большой список корректур из 18 патчей. А Микаэль Пакье, как коммитер, помогал ему.
И это лишь один, крупный коммит. А количество небольших патчей улучшающих документацию просто не перечесть.
Разное
Параметр idle_session_timeout ― принудительное завершение простаивающих сеансов
commit: 9877374b
Новый параметр idle_session_timeout задает таймаут бездействия сеансов. При превышения установленного лимита времени, сеанс будет прерван. Параметр очень похож на появившийся еще в 9.6 idle_in_transaction_session_timeout, но действует только на сеансы, в которых нет начатой транзакции. Поэтому если требуется прерывать бездействующие сеансы вне зависимости от того, начата в них транзакция или нет, то следует установить оба параметра.
Рекомендуется с особой осторожностью применять параметр в системах, где используются пулеры соединений или подключения по postgres_fdw.
Параметр может установить любой пользователь для своего сеанса. В следующем примере после установки параметра и секундного ожидания видим, что в журнале сервера появляется запись о прерывании сеанса. После этого попытка выполнить запрос завершается ошибкой, но psql автоматически устанавливает новое соединение:
SET idle_session_timeout = '1000ms';
-- пауза
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout
----------------------
0
Описание от depesz.
Информация о GSS в сообщении журнала сервера
commit: dc11f31a
Сообщение о подключении нового сеанса в журнале сервера дополнено информацией о GSS, если использовался этот метод аутентификации.
pageinspect: функции для индексов GiST
commit: 756ab291
Для всех кому интересно исследовать организацию и хранение индексов GiST расширение pageinspect предлагает новые функции.
Корректное поведение EXPLAIN в командах с IF NOT EXISTS
commit: e665769e
Попытка создать уже существующую таблицу с опцией IF NOT EXISTS приводит к выдаче предупреждения:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
Однако получение плана такой команды приводит к неожиданным результатам. Просто EXPLAIN выводит план для SELECT, который команда успевает построить до того, как будет проверять существование таблицы tickets. И никаких предупреждений!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
А EXPLAIN ANALYZE завершается ошибкой вместо предупреждения:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
В 14 версии поведение стало предсказуемым:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
QUERY PLAN
------------
(0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
QUERY PLAN
------------
(0 rows)
Такие же изменения для команды EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOT EXISTS.
Добавлены первичные и уникальные ключи в таблицы системного каталога
commit: dfb75e47, 62f34097
В таблицы системного каталога добавлены ограничения целостности: первичные и уникальные ключи. Уникальные индексы были и раньше, теперь на их основе сделаны ограничения.
Вот как это выглядит:
\d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
oid | oid | | not null |
relname | name | | not null |
relnamespace | oid | | not null |
...пропущено ...
Indexes:
"pg_class_oid_index" PRIMARY KEY, btree (oid)
"pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
"pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Но есть исключения: pg_depend, pg_shdepend. В этих двух таблицах по два индекса и оба неуникальные. Придумать уникальное сочетание столбцов не получилось. Надо признать, что разобраться в том, как устроена таблица зависимостей ― задача нетривиальная. И невозможность создать первичный ключ ― явное тому подтверждение.
А вот внешние ключи не добавили. На то есть веские причины:
- У ряда таблиц столбцы oid могут иметь значения 0, когда нет реального OID, на который можно ссылаться. Для создания внешнего ключа потребуется заменить везде 0 на NULL, а это огромная работа по переписыванию кода, за которую не готовы браться.
- У ряда таблиц столбец с потенциальным внешним ключом не просто типа oid, а oid[]. Создать внешний ключ по массиву невозможно.
Изначальная идея патча была в том, чтобы на основе информации из базы данных можно было построить диаграмму связей между таблицами системного каталога. Это могли бы автоматически делать внешние инструменты. Ведь строить диаграмму без информации о внешних ключах можно только вручную и с регулярным внесением изменений после каждого релиза, что крайне неудобно.
Осознавая, что задача патча не выполнена, сразу же после коммита Том Лейн инициировал новое обсуждение, в котором предложил компромиссное решение в виде функции, возвращающей список внешних ключей для всех таблиц системного каталога. Патч был принят уже после закрытия январского коммитфеста, но логичнее описать его сейчас.
Итак, внешних ключей у таблиц системного каталога не появилось. Но мы можем получить информацию о них, обратившись к функции pg_get_catalog_foreign_keys. Следующий запрос показывает кто ссылается на pg_attribute:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt
----------------------+-----------------------+----------+--------
pg_attrdef | {adrelid,adnum} | f | f
pg_constraint | {conrelid,conkey} | t | t
pg_constraint | {confrelid,confkey} | t | f
pg_index | {indrelid,indkey} | t | t
pg_statistic_ext | {stxrelid,stxkeys} | t | f
pg_statistic | {starelid,staattnum} | f | f
pg_trigger | {tgrelid,tgattr} | t | f
pg_partitioned_table | {partrelid,partattrs} | t | t
(8 rows)
Мониторинг
Параметр log_recovery_conflict_waits ― запись в журнал долгих ожиданий разрешения конфликтов восстановления
commit: 0650ff23
При включенном новом параметре log_recovery_conflict_waits, ожидание разрешения конфликта восстановления процессом startup будет записано в журнал сервера, если время ожидания превысит deadlock_timeout.
Смоделируем ситуацию. На реплике включаем параметр, затем начинаем транзакцию и ждем:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
А теперь на мастере:
DELETE FROM t;
VACUUM t;
Через секунду (deadlock_timeout) в журнале реплики появится детальное сообщение с описанием конфликта. Здесь и номер конфликтующего процесса, и позиция LSN где застряли, и имя файла (читай таблицы) с номером блока:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot
DETAIL: Conflicting process: 29119.
CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Еще через 30 секунд ожидания (max_standby_streaming_delay) сеанс на реплике будет прерван, как и полагается в таких случаях.
Это продолжение работы, начатой и описанной в предыдущем коммитфесте.
Представление pg_stat_database ― добавлена статистика о пользовательских сеансах
commit: 960869da
У разработчиков систем мониторинга прибавилось работы. Пользователей систем мониторинга ждут полезные и крутые изменения!
В pg_stat_database появилось много столбцов с дополнительной информации о сеансах пользователей в каждой базе данных кластера:
- session_time ― общее время всех сеансов, проведенной в этой БД;
- active_time ― время, потраченное на выполнение запросов;
- idle_in_transaction_time ― время простоя в незавершенных транзакциях;
- sessions ― количество сеансов;
- sessions_abandoned ― количество прерванных сеансов из-за потери соединения;
- sessions_fatal ― количество прерванных сеансов из-за ошибки уровня FATAL;
- sessions_killed ― количество прерванных сеансов по инициативе пользователя.
Описание от depesz.
ps: обновление статуса процессов при выполнении контрольной точки
commit: df9274ad
Следить за работой процессов startup, checkpointer можно запросами к pg_stat_activity. Но есть три ситуации, когда представление pg_stat_activity не доступно, а checkpointer работает. Речь о контрольной точке в конце процесса восстановления после сбоя, контрольной точке во время остановки сервера и о точке рестарта во время остановки реплики.
В этих трех ситуаций можно следить за статусом процессов startup и checkpointer в операционной системе, например утилитой ps.
Типичный пример ― восстановление после сбоя. В конце, после наката изменений из WAL, процесс startup выполняет контрольную точку и это может занять некоторое время. Однако статус процесса startup не меняется и показывает «recovering NNN». Хотя было бы полезно знать, что накат изменений завершен и осталось дождаться завершения контрольной точки. Теперь статус обновляется, понижая уровень тревоги DBA в экстремальной ситуации.
pg_stat_statements: когда была сброшена статистика
commit: 2e0fedf0
Нет сомнений в том, что статистику pg_stat_statements нужно регулярно сбрасывать. Иначе какой смысл накапливать информацию о запросах, выполненных вчера, неделю назад, месяц, год…
Но как узнать, когда статистика была последний раз сброшена? Очень просто. Заглядываем в pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset
-------------------------------+--------------------------
2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset
---------+-------------------------------
0 | 2021-02-03 13:25:44.738468+03
Представление pg_stat_statements_info появилось именно в 14 версии. Про столбец dealloc можно прочитать в предыдущей статье.
Описание от depesz.
Еще более полезной выглядит идея не просто регулярно сбрасывать статистику, а еще и сохранять содержимое pg_stat_statements перед каждым сбросом. Тогда, при наличии множества регулярно выполняемых срезов, можно получать информацию за интервалы времени в прошлом. Такой подход использует расширение для мониторинга pgpro_pwr.
Ход выполнения команды COPY
commit: 8a4f618e
В семействе представлений pg_stat_progress_* пополнение! Теперь можно наблюдать за прогрессом выполнения команды COPY.
Сделаем логическую копию демо-базы:
\! pg_dump -d demo -Fc -f demo.dump
Теперь развернем копию в базе данных postgres в два потока и, пока процесс идет, заглянем в представление pg_stat_progress_copy:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+-------------------------
pid | 18771
datname | postgres
relid | bookings.tickets
bytes_processed | 19088527
bytes_total | 0
lines_processed | 189820
-[ RECORD 2 ]---+-------------------------
pid | 18772
datname | postgres
relid | bookings.boarding_passes
bytes_processed | 14833287
bytes_total | 0
lines_processed | 567652
Столбец bytes_total был бы заполнен размером файла при выполнении команды COPY… FROM 'file'. Но в приведенном примере загрузка идет из копии pg_dump, поэтому размер не известен.
Статус можно отслеживать не только для загрузки (COPY… FROM), но и для выгрузки (COPY… TO) данных.
Описание от depesz.
Производительность
Оптимизация очистки буферного кеша
commit: d6ad34f3, bea449c6
Ряд операций требуют удаления из буферного кеша всех буферов, относящихся к определенной таблице. К таким операциям относятся команды TRUNCATE и DROP таблицы, прерванная команда CREATE TABLE AS SELECT, а также VACUUM, когда требуется удалить пустые блоки из конца таблицы.
Для удаления просматривается весь буферный кеш, что при больших размерах кеша может быть затратным. Теперь для небольших таблиц будет вестись специальная структура в памяти с информацией о занятых буферах, что позволит избежать сканирования буферного кеша целиком.
Тестирование показало, что при shared_buffers от 100GB опустошение (truncate) тысячи таблиц ускорилось более чем в 100 раз.
Это продолжение работы, начатой в 13 версии.
postgres_fdw: пакетный режим для вставки записей
commit: b663a413
Выборка данных из внешних таблиц postgres_fdw использует пакетный режим. Записи передаются с внешнего сервера пачками по 100 штук (значение параметра fetch_size по умолчанию). Это существенно быстрее, чем передавать их каждый раз по одной. А вот вставка, изменение, удаление работают построчно. И потому очень медленно.
Для оптимизации было доработано FDW API. Правда доработано только в части пакетного режима для операций вставки. Обновления и удаления оставлены на будущее. Разумеется, первой оберткой, которая воспользовалась новым API, стала postgres_fdw.
Посмотрим что получилось. Настраиваем postgres_fdw для работы с внешними таблицами в базе данных demo:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
Внешняя таблица будет располагаться в соседней базе postgres:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
За эталонную скорость возьмем скорость вставки в локальную таблицу. И включим timing для замеров:
CREATE TABLE bookings_local (LIKE bookings);
\timing
Вставка в локальную таблицу:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788
Time: 165,653 ms
А теперь вставка во внешнюю таблицу, пакетный режим отключен. (На самом деле он включен, просто по умолчанию размер пакета ― 1 строка).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788
Time: 6729,867 ms (00:06,730)
Почти в 40 раз медленнее! И это между базами одного кластера, где нет сетевых задержек.
Повторим эксперимент, но установим размера пакета (batch_size) равным 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788
Time: 679,632 ms
Совсем другое дело. Конечно, проигрыш локальной вставке по-прежнему заметен, ~4 раза, но всё-таки не 40!
И напоследок. Размер пакета для вставки (batch_size) можно было задать и в параметрах внешнего сервера, тогда он бы действовал для всех таблиц, в которых не определен явно.
Удаление индексных строк «снизу вверх»
commit: 9dc718bd, d168b666
Данная оптимизация пытается до последнего избежать деления индексной страницы на две при операциях UPDATE в ситуациях, когда столбцы индекса не менялись. Прежде чем добавлять новую версию строки в индекс, нужно посмотреть, а нельзя ли удалить на этой странице уже не нужные строки. Например, если найдена цепочка ненужных дублирующихся индексных строк, ссылающихся на одну табличную строку, то эти строки можно удалить. Питер Гейган, автор патча, назвал это «удалением снизу вверх» («bottom-up deletion»).
Похожую задачу (избежать разрастания индексов) решает оптимизация HOT-обновление. Если UPDATE не изменяет ни один из индексированных столбцов, то новые версии строк в индексах могут не создаваться. А если индексов на таблице несколько, а изменяется столбец только одного из них? В таком случае HOT-обновление не помощник.
Проверим что сможет «удаление снизу вверх». Для эксперимента возьмем таблицу с двумя по отдельности проиндексированными столбцами и отключенной автоматической очисткой.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size
--------+-------------+-------------
8192 | 16384 | 16384
Перед массовым обновлением в таблице одна строка. Размер таблицы ― одна страница, а оба индекса занимают по две страницы (служебная страница + страница с данными).
Теперь 100000 раз изменяем только один столбец col2 и смотрим размер таблицы и индексов.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 | 2121728 | 2260992
Эти результаты получены в PostgreSQL 12. Как видим, HOT-обновление не сработало и оба индекса почти одинаково выросли в размерах.
Теперь этот же эксперимент в PostgreSQL 13:
t_size | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 | 663552 | 2260992
Индекс t_col1, в котором не было изменений, вырос намного меньше, в ~3,5 раза. Это результат известной оптимизации 13 версии: дедупликация индексов. Но тем не менее вырос.
И, наконец, смотрим что в PostgreSQL 14:
t_size | t_col1_size | t_col2_size
---------+-------------+-------------
2818048 | 16384 | 2260992
Вот это да! В индексе t_col1 так и осталась всего одна страница с данными. Это круто!
Конечно, если бы автоочистка была включена, она бы могла успеть вычистить часть мертвых строк за время эксперимента. Но на то и эксперимент. К тому же в реальных условиях, при частых обновлениях (прекрасный пример ― таблицы очередей), автоочистка точно не будет успевать всё вовремя вычищать.
Описание от Виктора Егорова.
Параллельное выполнение REINDEX CONCURRENTLY
commit: f9900df5
В статье о ноябрьском коммитфесте уже писалось о неблокирующем параллельном выполнении CREATE INDEX CONCURRENTLY. Аналогичная оптимизация теперь и для REINDEX CONCURRENTLY.
Процедурные языки
Процедуры стали выполняться быстрее
commit: ee895a65
Процедуры были задуманы для того, чтобы была возможность завершать транзакции. Если такая процедура, выполняющая COMMIT, сама вызывается многократно, например в цикле, то на каждой итерации цикла все операторы внутри процедуры будут заново разбираться.
Серьезных причин выполнять разбор команд заново не было, что и было устранено в патче. Теперь вызов процедур в цикле требует меньше работы и ресурсов. И, как следствие, выполняется быстрее.
PL/pgSQL: оператор присваивания полностью переработан
commit: 844fe9f1, c9d52984, 1788828d, 1c1cbe27
Без лишних слов:
DO $$
<>
DECLARE
a bookings[];
x bookings;
BEGIN
/* Присвоение элементу массива составного типа */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* Присвоение срезу массива */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00)
NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00)
NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00)
DO
Теперь внутри PL/pgSQL блока можно присваивать значения элементам массива составного типа, а также срезам массива.
Для этого оператор присваивания в PL/pgSQL был полностью переработан. А парсер сервера научился разбирать выражения PL/pgSQL.
Для вычисления выражения больше не нужно формировать команду вида «SELECT expr». В этом легко убедиться посмотрев на сообщение об ошибке в следующем примере:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a"
LINE 1: 2 + 'a'
^
QUERY: 2 + 'a'
CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
Слова SELECT в строке QUERY больше нет.
Репликация
Обработка репликой изменения параметров конфигурации на мастере
commit: 15251c0a
Изменение на мастере параметров, влияющих на размер общей памяти сервера, не проходит бесследно для физических реплик. Когда на реплику приходит запись WAL об изменении таких параметров, реплика автоматически останавливается, прерывая все текущие сеансы. Список параметров можно уточнить в документации.
Это не очень хорошо. Поэтому сделали так: получая запись об изменения параметров, реплика продолжает работать, но ставит репликацию на паузу и выдает сообщение в журнал. Администратор может дождаться завершения важных сеансов, обновить параметры в конфигурационных файлах, чтобы они соответствовали значениям на мастере, и снять репликацию с паузы. Реплика после этого всё равно сразу остановится, но поскольку изменения в конфигурацию уже внесены, её можно сразу же запускать, сведя к минимуму время простоя.
Изменение restore_command без перезапуска сервера
commit: 942305a3
Продолжение работы Сергея Корнилова, принятой в 13 версию. Тогда появилась возможность изменять без перезагрузки сервера параметры primary_conninfo, primary_slot_name и wal_receiver_create_temp_slot.
Теперь к ним добавился restore_command.
Сервер
Улучшение использования расширенной статистики
commit: 25a9e54d
Расширенная статистика стала использоваться в большем числе случаев для оценки кардинальности условий в запросах. В частности, теперь расширенная статистика будет использоваться, когда разные условия, для которых по отдельности может использоваться расширенная статистика, объединены через OR.
В примере соберем расширенную статистику по аэропортам вылета и прилета. А затем посчитаем количество рейсов между Шереметьево и Пулково или в обратном направлении.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
Точное количество рейсов ― 610. Сравним с оценками планировщика в 13 и 14 версиях.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Как видим оценка в 14 версии практически точная.
Общая инфраструктура поддержки индексной нотации для любых типов данных
commit: c7aba7c1, 0ec5f7e7, 676887a3
Индексная нотация используется для работы с массивами. Например найдем элемент с индексом 3:
SELECT (ARRAY[10,20,30,40,50])[3];
array
-------
30
Но есть и другие типы данных, где такой синтаксис было бы удобно использовать. В первую очередь речь о json. Именно с идеи сделать поддержку индексной нотации для json начался длинный путь работы над этим патчем Дмитрия Долгова.
И вот спустя несколько лет такая поддержка появилась. Первый патч создает необходимую инфраструктуру индексных нотации для произвольных типов данных. Второй патч добавляет индексную нотацию к типу hstore, а третий ― к типу jsonb.
Теперь вместо специальных функций и операторов можно извлекать необходимые части из json-значения. Найдем телефон в контактных данных одного из билетов:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------------------------------------------------
contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"}
phone | "+70844502960"
Индексная нотация может использоваться и для записи в jsonb. Добавим в ранее найденный контакт Ирины Антоновой еще и адрес:
UPDATE tickets
SET contact_data['address'] =
'{"city": "Москва",
"street": "Дмитрия Ульянова",
"building": "7А"
}'::jsonb
WHERE ticket_no = '0005432000994';
Заметим, что адрес сам по себе является составным и для обращения к его частям можно также использовать индексную нотацию:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]----------------------------------------------------------------
address | {"city": "Москва", "street": "Дмитрия Ульянова", "building": "7А"}
city | "Москва"
street | "Дмитрия Ульянова"
building | "7А"
phone | "+70844502960"
email | "antonova.irina04121972@postgrespro.ru"
Это очень удобно!
(Уточнение. Все контакты в демо-базе вымышленные и в Постгрес Про нет такой сотрудницы.)
Описание для hstore от depesz.
Команды SQL
Мультидиапазонные типы данных
commit: 6df7a969
У каждого диапазонного типа данных теперь есть свой мультидиапазонный тип. Такой тип по сути представляет собой массив отдельных диапазонов. Диапазоны внутри мультидиапазонного типа не должны пересекаться между собой, но между диапазонами могут быть пропуски.
Обычные диапазоны представляют собой непрерывные интервалы значений соответствующего подтипа: диапазон типа in4range для подтипа int, диапазон типа timestamptz для подтипа timestamp, и т.д. Но что, если нужно хранить диапазоны с пропусками в некоторых местах? Вот тут приходят на помощь мультидиапазоны.
Допустим мы хотим хранить в таблице периоды времени проведения коммитфестов для каждой версии PostgreSQL. Отдельный коммитфест можно представить в виде диапазона длиной в месяц. А вот как представить все пять коммитфестов одной версии?
Диапазон для подтипа timestamptz называется tstzrange, а мультидиапазон ― tstzmultirange. Имеющиеся типы описаны в документации. Создаем таблицу:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Для формирования значений используем конструктор:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
Список функций и операторов для работы с мультидиапазонными типами включает в себя те же, что и для обычных диапазонов, плюс предназначенные только для мультидиапазонов.
Например, можем узнать, над какой версией PostgreSQL работало сообщество разработчиков в прошедший Новый год:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version
---------
14
Или даты начала и конца работы над 13 версией:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper
------------------------+------------------------
2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
Можно создавать новые пользовательские мультидиапазонные типы. Это полезно в тех случаях, когда встроенного диапазонного и соответствующего ему мультидиапазонного типа нет. Используется та же команда CREATE TYPE… AS RANGE, в которой можно указать имя и для автоматически создаваемого мультидиапазонного типа.
Например нас интересуют диапазоны и мультидиапазоны времени, подтип time. Для создания диапазона понадобится функция, вычисляющая разницу между двумя значениями типа time:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Создаем тип для диапазона времени, а заодно и для мультидиапазона:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Теперь рабочее время можно сформировать следующим выражением:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours
-------------------------------------------
{[09:00:00,13:00:00),[14:00:00,18:00:00)}
Описание от depesz.
Функции ltrim и rtrim для двоичных строк
commit: a6cf3df4
Обрезать одновременно байты в начале и конце строки bytea можно было и раньше функцией btrim. Теперь обрезать можно с каждого края по отдельности новыми функциями ltrim и rtrim для двоичных строк.
Фраза GRANTED BY в командах GRANT и REVOKE
commit: 6aaaa76b
Для совместимости со стандартом SQL в команды GRANT и REVOKE добавлена необязательная фраза GRANTED BY. Например:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
Имя роли в GRANTED BY должно совпадать с текущей ролью. Так что выдать/отобрать права от имени другой роли не получится. Фраза добавлена в целях соответствия стандарту.
Это продолжение работы, описанной в статье о сентябрьском коммитфесте.
Системное администрирование
initdb --no-instructions
commit: e09155bd
Утилита initdb используется для инициализации кластера. И в конце своей работы выводит инструкцию как кластер запускать:
Success. You can now start the database server using:
pg_ctl -D /usr/local/pgsql/data -l logfile start
Вот только это не всегда верно. Например в пакетных дистрибутивах debian для запуска кластера предназначена утилита pg_ctlcluster, а не pg_ctl. И параметры у неё другие.
С новым параметром --no-instructions утилита initdb перестанет давать советы по запуску, чем пакетные дистрибутивы могут воспользоваться.
pg_dump: восстановление отдельной секции в качестве самостоятельной таблицы
commit: 9a4c0e36, 9eabfe30
Если в логическую копию pg_dump включена секционированная таблица, то восстановить из такой копии отдельную секцию как самостоятельную таблицу не получится. Сразу за командой CREATE TABLE идет команда ALTER TABLE… ATTACH PARTITION, которая не только не нужна в такой ситуации, но и завершается ошибкой, т.к. родительскую таблицу мы не восстанавливали.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
...
CREATE TABLE public.t_p1 (
id integer
);
ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001);
...
Теперь команды ALTER TABLE… ATTACH PARTITION для всех секций выгружаются отдельно и после всех команд на создание секций CREATE TABLE. Поэтому при восстановление отдельной секции, указанной в опции -t, будет выполняться только команда CREATE TABLE, что дает возможность восстановить секцию как самостоятельную таблицу.
На этом пока всё. Ждем финальный мартовский коммитфест 14-й версии.
