PostgreSQL 14: Часть 2 или «в тени тринадцатой» (Коммитфест 2020-09)

Главным событием сентября в мире PostgreSQL безусловно является выход 13 версии. Однако жизненный цикл PostgreSQL 14 идет своим чередом и в сентябре прошел второй коммитфест изменений. О том, что интересного было в первом рассказывалось в предыдущей статье. А прочитав эту можно узнать почему 5! больше не выдаст 120, разобраться что общего у хирурга и DBA, выяснить сколько же записей в пустой таблице и многое другое.

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

psql: \d показывает метод доступа в расширенном списке объектов
commit: 07f386e

Вывод команды \d[tmi]+ пополнился новым столбцом Access Method. Но новых методов доступа для таблиц пока не реализовано. Поэтому для таблиц и материализованных представлений значение столбца всегда будет heap. Однако мы ждем появления и других значений, в частности очень интересна недавняя инициатива компании Cybertec по продвижению zheap.

А пока команда \di+ может показывать полезную информацию об индексных методах доступа (при наличии индексов отличных от btree).

psql: \d показывает измененное целевое значение расширенной статистики
commit: 3c99230

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

Автодополнение по табуляции в psql улучшено для команд: COPY и \copy; IMPORT FOREIGN SCHEMA; VACUUM; REINDEX; DEALLOCATE.

pg_dump: оптимизация работы в режиме --data-only
commit: 5423853

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

Проверка значений параметров pg_test_fsync --secs-per-test и pg_test_timing --duration
commit: 4d29e6d

Утилиты сервера pg_test_fsync и pg_test_timing сложно отнести к часто используемым. Возможно поэтому никто не замечал, что их можно запустить с некорректными значениями параметров --secs-per-test и --duration соответственно. Микаэль Пакье исправил это.

Документация

Не только сборка из исходных кодов
commit: d2511d71

В главе Server Setup and Operation появились уточнения о пакетных сборках. Не особенности конкретных сборок, а то, что в пакетных сборках основные операции с сервером(инициализация, запуск, останов) могут выполняться иначе, чем при сборке из исходников.

Портировано в 13 версию.

Шаблон нового процедурного языка
commit: adbe62d0, 51300b45

В документации по 13 версии в главе Writing a Procedural Language Handler раздела Internals описан пример кода на C для создания нового процедурного языка. Для большего удобства, шаблон для создания процедурного языка (PL/Sample) поместили в исходный код, а именно в каталог src/test/modules/plsampe. Здесь есть всё для правильного оформления нового языка: управляющий и sql файлы расширения, файл на C с основной процедурой обработчика языка, инфраструктура тестирования.

Мониторинг и управление

Amcheck: соседние индексные страницы одного уровня в B-дереве корректно ссылаются друг на друга
commit: 39132b78

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

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

Упоминание об этой проблеме есть в майском выступлении Андрея Бородина «Что и зачем мы делаем в Open Source базах данных», правда тогда еще не было известно, что в августе его патч будет принят.

Включение в log_line_prefix значения pg_stat_activity.leader_pid
commit: b8fdee7d

В 13 версии в pg_stat_activity добавили столбец leader_pid — номер ведущего процесса при выполнении параллельных запросов. А в 14 версии номер ведущего процесса можно включить в log_line_prefix при помощи спецсимвола %P.

Представление pg_backend_memory_contexts
commit: 3e98c0ba

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

EXPLAIN (BUFFERS) без ANALYZE
commit: 9d701e62

Команду EXPLAIN можно выполнить с опцией BUFFERS и без ANALYZE. Следующий запрос показывает сколько буферов было прочитано для построения плана:

EXPLAIN (BUFFERS) SELECT * FROM bookings;

                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on bookings  (cost=0.00..4301.88 rows=262788 width=21)
 Planning:
   Buffers: shared hit=11 read=2

Такое поведение немного необычно. Ведь EXPLAIN без ANALYZE всегда выдавал только оценку стоимости будущего плана. А теперь появился вариант запуска, когда мы получаем фактические затраты (в виде количества буферов) на построение плана.

Включено в 13 версию.

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

Повышение масштабируемости клиентских соединений: GetSnapshotData()
commit: dc7420c2, 1f51c17c, 941697c3, 5788e258, 73487a60, 623a9ba7

Андрес Фройнд, после перехода в Микрософт, серьезно занялся вопросами масштабируемости клиентских соединений. В недавней статье Андрес очень подробно рассказал об исследовании причин замедления работы PostgreSQL при увеличении количества подключений и выявил, что основная проблема кроется в функции GetSnapshotData().

Функция возвращает снимок данных, на основании которого определяется какие строки должны быть видны, а какие нет. Святая святых любой СУБД! Поскольку снимок данных в PostgreSQL включает в себя список активных транзакций, то интуитивно понятно, что чем больше сеансов в системе, тем длиннее может быть этот список и это может вызывать проблемы масштабируемости.

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

После полугодового обсуждения, в августе пошли первые коммиты. Список наиболее важных здесь приведен. В вышеупомянутой статье Андрес сообщает, что скоро в следующей расскажет о достигнутых результатах. Но уже сейчас понятно, что это одно из самых масштабных(масло масляное?) изменений в рамках 14 версии.

Ускорение умножения очень больших чисел типа numeric
commit: 88709176

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

Инкрементальная сортировка для оконных функций
commit: 62e221e1c

Инкрементальная сортировка появилась в 13 версии. Теперь этот же механизм используется и для запросов с оконными функциями.

Немного модифицируем пример из предыдущей ссылки:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) 
SELECT ticket_no, MAX(passenger_name) OVER (ORDER BY ticket_no, passenger_id) 
FROM tickets;

                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 WindowAgg (actual rows=366733 loops=1)
   ->  Incremental Sort (actual rows=366733 loops=1)
         Sort Key: ticket_no, passenger_id
         Presorted Key: ticket_no
         Full-sort Groups: 11461  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         ->  Index Scan using tickets_pkey on tickets (actual rows=366733 loops=1)
 Planning Time: 0.118 ms
 Execution Time: 313.352 ms

C отключенной инкрементальной сортировкой запрос выполняется существенно дольше:

SET enable_incremental_sort = OFF;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT ticket_no, MAX(passenger_name) OVER (ORDER BY ticket_no, passenger_id) 
FROM tickets;

                          QUERY PLAN                          
--------------------------------------------------------------
 WindowAgg (actual rows=366733 loops=1)
   ->  Sort (actual rows=366733 loops=1)
         Sort Key: ticket_no, passenger_id
         Sort Method: external merge  Disk: 18744kB
         ->  Seq Scan on tickets (actual rows=366733 loops=1)
 Planning Time: 0.113 ms
 Execution Time: 1257.468 ms

Синхронизация статистики SLRU выполняется процессом контрольной точки
commit: dee663f7

Синхронизация с диском (fsync) для статистики SLRU откладывается до следующей контрольной точки. Это разгружает систему ввода/вывода, особенно если включен track_commit_timestamps. Выигрыш особенно будет заметен при восстановлении.

Ускорение функции compactify_tuples
commit: 19c60ad6

Оптимизирована внутренняя функция compactify_tuples, которая активно используется в процессах очистки и восстановления. Ничего настраивать не нужно. Просто работает быстрее. И чем больше записей на странице ‒‒ тем быстрее.

Уточнение оценки количества строк для пустых таблиц
commit: 3d351d91

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

CREATE TABLE t (id int, code text);
EXPLAIN SELECT * FROM t;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on t  (cost=0.00..22.70 rows=1270 width=36)

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

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

INSERT INTO t SELECT g.x, random()::text FROM generate_series(1,100000) AS g(x);
TRUNCATE t;
VACUUM ANALYZE t;

EXPLAIN SELECT * FROM t;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on t  (cost=0.00..22.70 rows=1270 width=36)

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

Сейчас поведение изменилось. После создания таблицы и до первого выполнения VACUUM или ANALYZE значение pg_class.reltuples = -1 и планировщик по-прежнему ориентируется на заполненные 10 страниц в таблице:

CREATE TABLE t (id int, code text);
SELECT reltuples FROM pg_class WHERE oid = 't'::regclass;

 reltuples
-----------
        -1

EXPLAIN SELECT * FROM t;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on t  (cost=0.00..22.70 rows=1270 width=36)

Однако как только по таблице будет собрана статистика или выполнена очистка, значение reltuples устанавливается в реальное ‒‒ для пустой таблицы в 0 и планировщик сможет правильно оценить кардинальность:

ANALYZE t;
EXPLAIN SELECT * FROM t;

                    QUERY PLAN                    
--------------------------------------------------
 Seq Scan on t  (cost=0.00..0.00 rows=1 width=36)

Заметим, что планировщик вместо 0 пишет 1, но это уже другая история.

Подводя итог. Вынесенный в начало статьи вопрос о количестве строк в пустой таблице имеет вполне определенный ответ. PostgreSQL 14 оценивает это количество либо как 0, либо -1, в зависимости от факта сбора статистики.

Репликация

Потоковая логическая репликация
commit: 46482432, 45fdc973, 808e13b2, 7259736a

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

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

Во-первых, расходы на временное хранение данных в процессе wal sender. Конечно, данные не будут накапливаться в оперативной памяти. Параметром logical_decoding_work_mem, появившимся еще в 13 версии (commit: cec2edfa), можно регулировать объем оперативной памяти переупорядочивающего буфера. При превышении этого размера данные транзакций будут сбрасываться на диск.

Во-вторых, большие объемы передачи данных могут привести к отставанию репликации. И вот для решения этой проблемы публикацию теперь можно объявлять с параметром streaming:

CREATE|ALTER PUBLICATION … WITH (STREAMING = ON);

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

Для реализации потребовалось очень серьезно переработать механизмы логической репликации: обработку транзакций на стороне публикации (wal sender), протокол передачи через WAL, применение данных на стороне подписчика (logical_replication_worker). Работа велась долго (первое письмо датируется 2017 годом) и изменения применялись в несколько этапов (см. список только основных коммитов).

pg_waldump выводит содержимое сообщений в записях WAL для логической репликации
commit: 9f1cf97b

Это удобно для диагностики логической репликации. Запомним позицию в журнале WAL, отправим сообщение логической репликации и найдем сообщение утилитой pg_waldump:

SELECT pg_current_wal_insert_lsn();

 pg_current_wal_insert_lsn
---------------------------
 0/8A8DAAC8

SELECT pg_logical_emit_message(false, 'my_prefix', '12345'::text);

 pg_logical_emit_message
-------------------------
 0/8A8DAB10

\! pg_waldump --start 0/8A8DAAC8 -p data

rmgr: LogicalMessage len (rec/tot):     65/    65, tx:          0, lsn: 0/8A8DAAC8, prev 0/8A8DAA90, desc: MESSAGE non-transactional, prefix "my_prefix"; payload (5 bytes): 31 32 33 34 35
… остальная часть вывода опущена...

Сервер

Построение индексов GiST с сортировкой
commit: 16fa9b2b

Казалось бы GiST это не про сортировку, про сортировку btree. Однако во многих случаях можно использовать кривую Мортона для сортировки многомерных данных. И тогда становится возможным быстро строить индекс GiST также как и btree в виде дерева.

Андрей Бородин, вдохновленный прототипом Никиты Глухова, предложил и довел до коммита новый вариант построения индексов GiST, основанный на Z-упорядочивании.

Измерять скорость работы на ноутбуке весьма сомнительный способ показать увеличение производительности. Но всё-таки попробуем воспроизвести пример Андрея и создать индекс GiST вот для такой таблички:

CREATE TABLE x AS SELECT point (random(),random()) FROM generate_series(1,10000000,1);

Включим \timing и заодно посмотрим на размер индекса.

Версия 13:

CREATE INDEX ON x USING gist (point);

Time: 93597,070 ms (01:33,597)

SELECT pg_size_pretty(pg_indexes_size('x'));

 pg_size_pretty
----------------
 709 MB

Версия 14:

CREATE INDEX ON x USING gist (point);

Time: 9232,814 ms (00:09,233)

SELECT pg_size_pretty(pg_indexes_size('x'));

 pg_size_pretty
----------------
 474 MB

Дополнительные комментарии излишни, но с ними можно познакомиться в переписке.

Оптимизация добавления записей в pg_depend
commit: 63110c62, 8febfd18

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

Изменены системные лимиты, связанные с предотвращением зацикливания счетчика транзакций
commit: cd5e8225

Система начнет выдавать предупреждения, когда самое старое значение XID в базе данных окажется в 40 миллионах транзакций от точки зацикливания (раньше было в 11 миллионах транзакций).

Если эти предупреждения игнорировать, система отключится и не будет начинать никаких транзакций, как только до точки зацикливания останется менее 3 миллионов транзакций (раньше был 1 миллион транзакций).

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

Команды SQL

Использование ключевых слов в качестве псевдонимов без явного указания AS
commit: 1ed6b89, 06a7c31

Всё началось с предложения Питера Эйзентраута удалить поддержку постфиксных операторов. Тем более, что для единственного поддерживаемого оператора вычисления факториала есть замена в виде функции:

SELECT 5!, factorial(5);

 ?column? | factorial
----------+-----------
      120 |       120

Постфиксные операторы сильно усложняют грамматический разбор и накладывают некоторые ограничения на синтаксис команд. Например для псевдонима выражения в команде SELECT нельзя выбрать большинство ключевых слов без указания AS:

SELECT 1 column;

ERROR:  syntax error at or near "column"
LINE 1: SELECT 1 column;

SELECT 1 AS column;

 column
--------
      1

Так работает в 13 версии.

Первый коммит 1ed6b89 удаляет поддержку постфиксных операторов. А второй коммит 06a7c31 расширяет грамматические возможности. В PostgreSQL 14 больше нельзя вычислить факториал выражением 5!, но подавляющее большинство ключевых слов (411 из 450) можно объявлять псевдонимами без AS:

select 1 column;

 column
--------
      1

Список ключевых слов можно получить функцией pg_get_keywords.

Использование CURRENT_ROLE там, где поддерживается CURRENT_USER
commit: 45b9805

Проверяя соответствие стандарту SQL, Питер Эйзентраут обнаружил, что в PostgreSQL поддерживается только CURRENT_USER, но не CURRENT_ROLE в таком варианте команды GRANT:

GRANT ROLE role_name TO role_specification GRANTED BY CURRENT_USER;

Упущение исправлено, теперь CURRENT_ROLE можно использовать везде, где раньше было только CURRENT_USER. Например в командах на изменение владельца объекта:

ALTER objtype objname OWNER TO CURRENT_USER | CURRENT_ROLE;

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

pg_hba.conf и pg_ident.conf поддерживают перенос строк
commit: 8f8154a

Длинную строку в конфигурационных файлах pg_hba.conf и pg_ident.conf можно разбить на несколько, если в конце строки поставить обратную косую черту \.

Модуль pg_surgery ― инструмент для хирургического вмешательства
commit: 34a947ca

Пополнение в модулях contrib. Новый модуль ориентирован на устранение проблем в данных, связанных с видимостью строк в таблице. В дальнейшем там могут появится инструменты для устранения других проблем с данными, отсюда и такое название широкого применения ― pg_surgery.

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

• не видны строки, которые должны быть видны
• видны строки, которые не должны быть видны

Посмотрим как можно решать(?) эти две проблемы на примере таблицы с первичным ключом и одной записью.

CREATE EXTENSION pg_surgery;
CREATE TABLE t (id int PRIMARY KEY, code text) WITH (autovacuum_enabled=off);
INSERT INTO t VALUES (1, 'Один');
SELECT ctid, * FROM t;

 ctid  | id | code
-------+----+------
 (0,1) |  1 | Один

Изменим строку. Это приведет к появлению новой версии строки:

UPDATE t SET code = 'Единица' WHERE id = 1;
SELECT ctid, * FROM t;

 ctid  | id |  code   
-------+----+---------
 (0,2) |  1 | Единица

Но предыдущая версия никуда не делась (автоочистка на таблице специально отключена), поэтому попробуем её оживить, причем сразу в «замороженном» виде:

SELECT heap_force_freeze('t'::regclass, ARRAY['(0,1)']::tid[]);
SELECT ctid, * FROM t;

 ctid  | id |  code   
-------+----+---------
 (0,1) |  1 | Один
 (0,2) |  1 | Единица
(2 rows)

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

Вторая функция расширения принудительно помечает «мертвыми» все строки из переданного массива. Избавимся от ненужной записи:

SELECT heap_force_kill('t'::regclass, ARRAY['(0,1)']::tid[]);
SELECT ctid, * FROM t;

 ctid  | id |  code   
-------+----+---------
 (0,2) |  1 | Единица
(1 row)

Вывод здесь простой ― pg_surgery не выполняет никаких проверок, а просто принудительно включает/отключает видимость строк. Вся ответственность за согласованность данных лежит на пользователе. Модуль стоит использовать только как шанс последней надежды на восстановление таблицы, возможно частичное. Любое неосторожное движение может привести к более худшим последствиям.

Разное

Уточнение сообщения об ошибке при выборе границы диапазона для секции в секционированной таблице
commit: 6b2c4e5

Вот как выглядит сообщение об ошибке в 13 версии:

CREATE TABLE p (id int) partition by range (id);
CREATE TABLE c_p1 PARTITION OF p FOR VALUES FROM (1) TO (0);

ERROR:  empty range bound specified for partition "c_p1"
DETAIL:  Specified lower bound (1) is greater than or equal to upper bound (0).

В 14 версии появляется новая строка с указателем на неверное значение нижней границы диапазона:

CREATE TABLE c_p1 PARTITION OF p FOR VALUES FROM (1) TO (0);

ERROR:  empty range bound specified for partition "c_p1"
LINE 1: CREATE TABLE c_p1 PARTITION OF p FOR VALUES FROM (1) TO (0);
                                                          ^
DETAIL:  Specified lower bound (1) is greater than or equal to upper bound (0).

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

REINDEX для секционированных таблиц
commit: a6642b3a

Команда REINDEX TABLE | INDEX может использоваться для секционированных таблиц и индексов. До этого требовалось выполнять REINDEX для каждой секции.

Новая функция string_to_table
commit: 66f1630

Как и следует из названия, функция разбивает строку на несколько строк по заданному разделителю. Раньше это можно было сделать в два приема, сначала превратить строку в массив (string_to_array), а затем массив разложить на строки (unnest):

SELECT string_to_table('1,2,3',','), unnest(string_to_array('4,5,6',','));

 string_to_table | unnest
-----------------+--------
 1               | 4
 2               | 5
 3               | 6

По замерам автора патча, Павла Стехуле, string_to_table на 15% быстрее варианта unnest(string_to_array()).

Уточнение сообщения об ошибке vacuum
commit: 7e453634, a3c66de6

Сообщение об ошибке при выполнении очистки (vacuum) дополнено информацией о смещении внутри страницы, необходимой для определения версии строки, на которой произошла ошибка.

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

---

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