PostgreSQL 15: Часть 2 или Коммитфест 2021-09

30 сентября вышла новая версия PostgreSQL 14… но хватит уже жить прошлым☺, в этот же день закончился второй коммитфест 15 версии. А значит прямо сейчас можно узнать что нового ждет нас через год.

Список изменений в этом выпуске получился не очень длинным, но кое-что интересное всё-таки найдется:

• Как заставить очистку в «агрессивном» режиме работать менее агрессивно?
• Кто на самом деле владелец схемы PUBLIC?
• Cколько разделяемой памяти потребуется для запуска сервера? А количество огромных страниц?

PostgreSQL 14

14 версия пока не отпускает. И начать придется опять с неё.

VACUUM: Чрезвычайные меры для предотвращения зацикливания счетчика транзакций
commit: 1e55e7d1

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

Чтобы избежать проблем с зацикливанием счетчика транзакций, автоматическая очистка может переходить в «агрессивный» режим работы. Такой режим наступает, когда хотя бы в одной таблице базы данных есть строки с незамороженными номерами транзакций, «возраст» которых превышает значение autovacuum_freeze_max_age.

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

Так было в предыдущих версиях, так будет и в новой. Но вместе с тем, в 15 версии, для ускорения в таких условиях выполнения своей основной задачи ― замораживание номеров транзакций ― процесс очистки может перейти в специальный защитный режим работы. В этом режиме будут отменены регламентированные задержки (autovacuum_vacuum_cost_delay, vacuum_cost_delay). Кроме того не будут выполняться некоторые необязательные работы, например очистка индексов. Данные меры позволят очистке быстрее заморозить старые транзакции и перейти в обычный режим работы.

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

Значение по умолчанию для параметра выбрано таким, чтобы переход в защитный режим осуществлялся только через некоторое время после работы в «агрессивном» режиме очистки. Значение autovacuum_freeze_max_age по умолчанию 200 миллионов транзакций, а vacuum_failsafe_age значительно больше ― 1,6 миллиарда, когда до перехода на новый круг остается не так много и пора ускоряться.

Новым параметром можно управлять и вручную, на уровне сеанса пользователя. Но не стоит выставлять слишком малые значения. Даже если установить vacuum_failsafe_age в 0, то процесс очистки неявно будет использовать значение равное 105% от autovacuum_freeze_max_age. Т.е. по сути эффективный диапазон значений параметра vacuum_failsafe_age от autovacuum_freeze_max_age*1,05 до 2,1 миллиарда.

Аналогичный параметр добавлен для мультитранзакций ― vacuum_multixact_failsafe_age. Работает в паре с autovacuum_multixact_freeze_max_age.

pg_upgrade: предупреждение о необходимости обновления расширений
commit: e462856a, 5090d709

После обновления на новую версию при помощи pg_upgrade нужно не забыть об обновлении версий установленных расширений. За этим нужно было следить самостоятельно.

Теперь утилита pg_upgrade сама подсказывает о том, какие из установленных расширений требуют обновления. Более того, утилита создает скрипт с командами ALTER EXTENSION… UPDATE.

Изменение портировали не только в 14 версию, но и на все версии начиная с 9.6.

PostgreSQL 15

pgbench: COPY FREEZE для pgbench_accounts
commit: 06ba4a63

Регулярно использующие утилиту pgbench могут сэкономить время на инициализации данных (ключ -i). Ведь таблица pgbench_accounts теперь заполняется командой COPY с параметром FREEZE. Экономия происходит за счет последующей очистки таблиц. Благодаря недавней доработке COPY FREEZE, команде VACUUM нечего делать в таблице pgbench_accounts.

Параметр FREEZE будет использован только для сервера не младше 14 версии и только для несекционированной pgbench_accounts (--partitions=0), ведь для секционированных таблиц COPY FREEZE пока использовать нельзя.

amcheck: добавлена проверка последовательностей
commit: c3b011d9

Функция verify_heapam расширения amcheck теперь проверяет и последовательности. Ведь с точки зрения хранения, последовательность представляет собой обычную таблицу из одной строки и ряда служебных столбцов:

SELECT * FROM flights_flight_id_seq;

 last_value | log_cnt | is_called
------------+---------+-----------
      33121 |       0 | t
(1 row)

Параллельное выполнение DISTINCT
commit: 22c4e88e

Возможность распараллеливать операции агрегирования появилась достаточно давно, в 2016 году. Но параллельного выполнения DISTINCT придется подождать до 15 версии.

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

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

EXPLAIN (COSTS OFF)
SELECT DISTINCT flight_id FROM ticket_flights;

                      QUERY PLAN                       
-------------------------------------------------------
 HashAggregate
   Group Key: flight_id
   ->  Gather
         Workers Planned: 2
         ->  HashAggregate
               Group Key: flight_id
               ->  Parallel Seq Scan on ticket_flights

так и при помощи сортировки (SORT) с последующей проверкой на уникальность (UNIQUE):

EXPLAIN (COSTS OFF)
SELECT DISTINCT fare_conditions FROM ticket_flights;

                         QUERY PLAN                          
-------------------------------------------------------------
 Unique
   ->  Sort
         Sort Key: fare_conditions
         ->  Gather
               Workers Planned: 2
               ->  HashAggregate
                     Group Key: fare_conditions
                     ->  Parallel Seq Scan on ticket_flights

Оптимизирована сортировка одного столбца
commit: 91e9e89d

Следующий запрос возвращает отсортированные значения столбца book_ref.

EXPLAIN (COSTS OFF)
SELECT book_ref FROM tickets ORDER BY book_ref;

        QUERY PLAN         
---------------------------
 Sort
   Sort Key: book_ref
   ->  Seq Scan on tickets

Индекса по этому столбцу нет, поэтому для сортировки используется узел Sort.

Сортировка именно в таких условиях, когда сортируется только один столбец и он же возвращается узлом Sort, теперь будет быстрее за счет использования более оптимальной процедуры сортировки одного столбца (Datum sort вместо tuple sort).

Права по умолчанию на схему PUBLIC
commit: b073c3cc

Известный факт, что права по умолчанию на схему public весьма небезопасны. Подробности и рекомендации были опубликованы еще три года назад CVE-2018-1058.

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

Больше такой возможности у обычного пользователя не будет.

Вот как это выглядит. Создадим базу данных, пользователя и попробуем этим пользователем создать таблицу:

=# CREATE DATABASE test;
=# CREATE ROLE alice LOGIN;

=# \connect test alice;

You are now connected to database "test" as user "alice".

alice=> CREATE TABLE a (id int);

ERROR:  permission denied for schema public

Нет прав на создание таблицы! Проверим права на схему public:

\dn+ public

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |

Появились два важных отличия от предыдущих версий.

• У псевдороли public на схему public теперь только привилегия USAGE. Больше нет CREATE по умолчанию. Именно поэтому alice и не смогла создать таблицу.
• Владельцем базы данных является роль pg_database_owner, вместо суперпользователя кластера, как было раньше.

Что же это за роль pg_database_owner? Это псевдороль, которой соответствует владелец базы данных. Таким образом, чтобы понять кто владелец схемы public нужно посмотреть на владельца базы данных:

\l test

                             List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
------+----------+----------+-------------+-------------+-------------------
 test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Чтобы alice всё же могла создавать объекты в схеме public, ей нужно выдать привилегию CREATE на схему. Либо можно сделать alice владельцем базы данных, а значит и схемы public:

=# ALTER DATABASE test OWNER TO alice;

alice=> CREATE TABLE a (id int);

CREATE TABLE

Стоит напомнить, что правами владельца объекта обладает не только непосредственно роль владельца, но и любая роль в неё включенная:

=# CREATE ROLE bob LOGIN;
=# GRANT alice TO bob;

bob=> CREATE TABLE b (id int);

CREATE TABLE

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

Новые функции для регулярных выражений
commit: 64243370

В семействе функций для работы с регулярными выражениями пополнение. Добавлены regexp_like, regexp_count, regexp_instr, regexp_substr. А у regexp_replace появились новые аргументы.

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

К тому же подобные функции есть в Oracle и DB2, что облегчит процесс миграции с этих СУБД. И совсем не случайно, что автор патча ― Жиль Даролд ― заодно и автор известной утилиты ora2pg.

Определение требуемого размера разделяемой памяти перед запуском сервера
commit: 0c39c292, bd178805, 43c1c4f6, 0bd305ee

Количество выделенной серверу разделяемой памяти можно посмотреть в появившемся в 13 версии представлении pg_shmem_allocations.

Но что если нужно изменить параметры, влияющие на размер памяти, и перестартовать сервер? Например, увеличить shared_buffers, wal_buffers, max_connections, max_locks_per_transaction, pg_stat_statements.max, что-то еще. Сколько оперативной памяти потребуется серверу для запуска и работы? А сколько огромных (huge) страниц, если они используются?

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

Теперь можно получить оценку размера до старта сервера. Для этого появились два новых параметра shared_memory_size и shared_memory_size_in_huge_pages, показывающие размер разделяемой памяти и количество огромных страниц соответственно:

SELECT name, setting, unit, context 
FROM pg_settings
WHERE name ~ 'shared_memory_size';

               name               | setting | unit | context  
----------------------------------+---------+------+----------
 shared_memory_size               | 142     | MB   | internal
 shared_memory_size_in_huge_pages | 71      |      | internal

(Здесь 71 огромная страница, по 2МБ каждая, соответствуют 142МБ общей разделяемой памяти.)

А утилита postgres (собственно сам сервер) научилась вычислять значения этих параметров на остановленном сервере:

$ pg_ctl stop

waiting for server to shut down.... done
server stopped

$ echo 'shared_buffers=512MB' >> ./data/postgresql.conf
$ postgres -C shared_memory_size

548
2021-10-07 17:28:41.189 MSK [98628] LOG:  database system is shut down

$ postgres -C shared_memory_size_in_huge_pages

274
2021-10-07 17:28:50.981 MSK [98631] LOG:  database system is shut down

---

На этом пока всё. Продолжение следует после ноябрьского коммитфеста.