Вийшла PostgreSQL 14: показуємо основні зміни

Розробники після року роботи опублікували нову стабільна гілку об’єктно-реляційної системи керування базами даних (СКБД) 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′).