После года разработки опубликована новая стабильная ветка СУБД PostgreSQL 14. Обновления для новой ветки будут выходить в течение пяти лет до ноября 2026 года.

Основные новшества:

• Добавлена поддержка доступа к данным JSON при помощи выражений, напоминающий работу с массивами:

  
     SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];
  
     SELECT * FROM test WHERE details['attributes']['size'] = '"medium"';
  
  
  
  

  Аналогичный синтаксис реализован и для данных в формате ключ/значение, обеспечиваемых типом hstore. Подобный синтаксис изначально реализован с использованием универсального фреймворка, который в будущем может быть задействован и для других типов. Пример для типа hstore:

  
    INSERT INTO mytable VALUES ('a=>b, c=>d');
    SELECT h['a'] FROM mytable;
    UPDATE mytable SET h['c'] = 'new';
  

• Семейство типов для определения диапазонов расширено новыми типами "multirange", позволяющими задавать упорядоченные списки неперекрывающихся диапазонов значений. В дополнение к каждому существующему range-типу предложен свой multirange-тип, например, типу "int4range" соответствует "int4multirange", а "daterange" - "datemultirange". Применение новых типов упрощает оформление запросов, манипулирующих со сложными последовательностями диапазонов.

  
     SELECT '{[3,7), [8,9)}'::int4multirange;
     SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
  

• Внесены оптимизации для повышения производительности высоконагруженных систем, обрабатывающих большое число соединений. В некоторых тестах наблюдается двукратный прирост производительности.
• Повышена эффективность работы индексов B-tree и решена проблема с разрастанием индексов при частом обновлении таблиц.
• Добавлена поддержка работающего на стороне клиента (реализован на уровне libpq) режима конвейерной (pipeline) передачи запросов, позволяющего значительно ускорить сценарии работы с БД, связанные с выполнением большого числа мелких операций записи (INSERT/UPDATE/DELETE) за счёт отправки следующего запроса не дожидаясь результата предыдущего. Режим также помогает ускорить работу при соединениях с большими задержками доставки пакетов.
• Расширены возможности для распределённых конфигураций, включающих несколько серверов PostgreSQL. В реализации логической репликации появилась возможность отправки в потоковом режиме транзакций, находящихся в процессе выполнения, что позволяет значительно повысить производительность репликации крупных транзакций. Кроме того, оптимизировано логическое декодирование данных, поступающих в процессе логической репликации.
• В механизме подключения внешних таблиц Foreign Data Wrapper (postgres_fdw) добавлена поддержка параллельной обработки запросов, которая пока применима только при подключении к другим серверам PostgreSQL. В postgres_fdw также добавлена поддержка добавления данных во внешние таблицы в пакетном режиме и возможность импорта секционированных таблиц через указание директивы "IMPORT FOREIGN SCHEMA".
• Внесены оптимизации в реализацию операции VACUUM (сборка мусора и упаковка дискового хранилища). Добавлен аварийный режим очистки ("emergency mode"), пропускающий несущественные операции чистки, если создаются условия ухода на второй круг номеров идентификаторов транзакций (transaction ID wraparound). Снижены накладные расходы при обработке индексов в формате B-Tree. Значительно ускорено выполнение операции "ANALYZE, собирающей статистику о работе БД.
• Добавлена возможность настройки метода сжатия, применяемого в системе TOAST, отвечающей за хранение больших данных, таких как блоки текста или геометрическая информация. Помимо метода сжатия pglz в TOAST теперь можно использовать алгоритм LZ4.
• Расширены средства для мониторинга за работой СУБД. Добавлены представления для отслеживания прогресса выполнения команд "COPY" (pg_stat_progress_copy), статистики о слотах репликации (pg_stat_replication_slots) и активности, связанной с WAL-логом транзакций (pg_stat_wal). Добавлена функция compute_query_id, включающая в различных подсистемах, таких как pg_stat_activity и EXPLAIN VERBOSE, отслеживание запросов с присвоением уникального для каждого запроса идентификатора.
• В планировщик запросов добавлены оптимизации, улучшающие параллельную обработку запросов и позволяющие поднять производительность одновременного выполнения операций последовательного сканирования записей, параллельного запуска запросов в PL/pgSQL при помощи команды "RETURN QUERY" и параллельного выполнения запросов в "REFRESH MATERIALIZED VIEW". Для повышения производительности цикличных вложенных слияний (join) реализована поддержка дополнительного кэширования.
• Для оптимизации выражений теперь может быть использована расширенная статистика, а для оптимизации оконных функций - инкрементальная сортировка.
• В хранимых процедурах, позволяющих управлять транзакциями в блоках кода, реализована поддержка определения возвращаемых данных при помощи параметров "OUT".
• Добавлена функция date_bin для округления значений с типом timestamp в соответствии с заданным интервалом.

  
     SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
     2020-02-11 15:30:00
  

• Добавлены определённые в стандарте SQL выражения SEARCH и CYCLE, упрощающие упорядочивание и выявление циклов в рекурсивных обобщённых табличных выражениях (Common Table Expression, CTE).

  
     WITH RECURSIVE search_tree(id, link, data) AS (
         SELECT t.id, t.link, t.data
         FROM tree t
       UNION ALL
         SELECT t.id, t.link, t.data
         FROM tree t, search_tree st
         WHERE t.id = st.link
     ) SEARCH DEPTH FIRST BY id SET ordercol 
     SELECT * FROM search_tree ORDER BY ordercol;
  

• В утилите psql улучшено автодополнение команд табуляцией, в команде "\df" добавлена возможность показа аргументов функций, а в команде "\dX" расширена выводимая статистика.
• Предоставлены предопределённые роли pg_read_all_data и pg_write_all_data позволяющие выдавать доступ сразу ко всем объектам базы (таблицам, представлениям и схемам) в режимах только для записи и только для чтения.

  
     GRANT pg_read_all_data TO user1;
  

• В новых установках по умолчанию обеспечено применение парольной аутентификации с использованием метода SCRAM-SHA-256 вместо md5 (параметр "password_encryption" при генерации postgresql.conf теперь устанавливается в значение 'scram-sha-256').