Пять возможностей PostgreSQL, о которых редко вспоминают

Привет, Хабр!

Постгрес – не просто реляционная БД, а настоящий кладезь фич, о которых начинающий разработчик может и не догадываться. Всё началось с того, что PostgreSQL изначально писался на С/C++ и всегда тянуло к расширению стандартного SQL набора возможностей. Так однажды разработчики решили добавить в него JSONB, логику на уровне запросов и многое другое – что в итоге сделало его не хуже NoSQL-систем. Но вернёмся к малоизвестным фичам. Ниже – пять приёмов и возможностей, которые неожиданно полезны в повседневной работе.

Как подсчитать, сколько строк вставилось, а сколько обновилось

Многие знают про INSERT ... ON CONFLICT ... DO UPDATE (апсерт). Но мало кто использует хитрость с RETURNING для отличия новых записей от обновлённых. Ключ — в системном столбце xmax. После выполнения апсерта можно вернуть результат так:

WITH new_data AS (     SELECT * FROM (VALUES         (1, 'Ivan', 1000),         (2, 'Alisa',   800)     ) AS t(id, name, salary) ) INSERT INTO employees (id, name, salary) SELECT id, name, salary FROM new_data ON CONFLICT (id) DO UPDATE     SET name = EXCLUDED.name,         salary = EXCLUDED.salary RETURNING id, name, salary, (xmax = 0) AS inserted;

Вставляем или обновляем сотрудников. Условие (xmax = 0) AS inserted показывает, какие строки реально вставлены. Столбец xmax указывает на транзакцию, удалившую строку, или равен 0, если строка была просто создана и не удалялась. Т.е в тех строках, где xmax=0, свежая вставка, а где xmax<>0 — обновление. Опишем это подробнее:

• xmax — особый системный столбец PostgreSQL. Если строка вставлена без предшествующего удаления, то xmax равен 0. При обновлении старый вариант строки удаляется (с новым xmin) и xmax старой версии получает непустое значение.

• В RETURNING мы возвращаем всё поле xmax = 0. Это булево значение, где TRUE означает вставку, FALSE — апдейт. В примере можно увидеть в результате, что для каждой записи метка inserted показывает, что с ней произошло (пример вывода см. ниже, строки с inserted = t вставлены, f — обновлены).

Так можно вести точный учёт того, сколько строк обновлено и сколько – добавлено, без дополнительных запросов.

Гранулярные права: GRANT SELECT только на нужные столбцы

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

CREATE TABLE users (     id            SERIAL PRIMARY KEY,     username      TEXT,     personal_id   TEXT,     password_hash TEXT );  -- Создаём пользователя-аналитика CREATE USER analyst;  -- Сперва дали полные права, а потом отозвали GRANT SELECT ON users TO analyst; REVOKE SELECT ON users FROM analyst;  -- Даем только выборку из полей id и username GRANT SELECT (id, username) ON users TO analyst;

После этого пользователь analyst может делать запросы только к указанным колонкам:

-- Так будет ошибка (пароля нет в правах): -- SELECT id, username, password_hash FROM users; -- А вот это сработает: SELECT id, username FROM users;

В документации прямо указано, что SELECT может применяться ко всей таблице или только к перечисленным столбцам. Т.е Postgres хранит привилегии на уровне столбцов. Это удобно, чтобы закрыть поля типа password_hash или personal_id, пока открыты только id и username. Ценность фичи в том, что вы не вынуждены создавать отдельные представления: можно лишний раз открыть пользователю лишь часть таблицы.

DISTINCT ON: как выбирать первую или последнюю строку в группе

Когда нужно выбрать для каждой группы строк (скажем, пользователя или заказа) всего одну – например самую свежую запись – пригодится конструкция SELECT DISTINCT ON. В PostgreSQL она позволяет выбрать только первую строку по заданному ключу. Сначала перечисляем столбцы после DISTINCT ON, а потом используем ORDER BY, чтобы определить, какой из дубликатов считать первым. Пример: хранится лог действий пользователей, и мы хотим знать последнее действие каждого.

-- Таблица логов пользователя CREATE TABLE user_logs (     user_id INT,     ts      TIMESTAMP,     action  TEXT );  -- Предположим, данные в user_logs уже есть... -- Теперь выбираем последнее действие для каждого пользователя: SELECT DISTINCT ON (user_id) user_id, ts, action FROM user_logs ORDER BY user_id, ts DESC;

Это вернёт по одной строке на каждый user_id – ту, в которой ts максимален (из-за ORDER BY ts DESC). Главное помнить: ORDER BY должен начинаться с тех же столбцов, что указаны в DISTINCT ON, и далее по убыванию (или возрастанию) фактора времени или другого ранжирующего столбца. Если этого не сделать, база вернёт какую-то первую строку для группы, но результат может быть непредсказуемым.

Всё это часто незаслуженно забывают, а зря – это в целом проще оконных функций для этой задачи. Можно быстро получить, например, последний статус по каждой сущности без лишних подзапросов. Формально DISTINCT ON – расширение PostgreSQL над стандартом SQL, но оно полностью работает и очень полезно. Любопытно, что в стандарте SQL такого синтаксиса нет, а в ПГ он существует именно для первой строки группы.

Диапазонные типы и исключающие (EXCLUDE) ограничения

Ещё одна хитрость – специальные range-типы и ограничение EXCLUDE. В PostgreSQL есть встроенные типы диапазонов: int4range, tsrange (для timestamp), daterange и так далее. Их можно применять, когда нужно хранить, скажем, период брони или налоговый промежуток. А главное – рядом с ними появилась возможность задать исключающее ограничение, которое следит, чтобы такие интервалы не пересекались.

Типичный пример: таблица бронирований (reservation) со столбцом during типа tsrange. Мы хотим гарантировать, что временные периоды не накладываются друг на друга. Делаем так:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- расширение для нужных операторов CREATE TABLE reservation (     during tsrange,     EXCLUDE USING GIST (during WITH &&) );

&& – оператор пересечение для диапазонов. Ограничение EXCLUDE не даст вставить две записи с перекрывающимися периодами. Например, если сначала сделать

INSERT INTO reservation VALUES ('[2023-01-01 10:00, 2023-01-01 12:00)');

то попытка вставить

INSERT INTO reservation VALUES ('[2023-01-01 11:30, 2023-01-01 13:00)');

упадёт с ошибкой о нарушении ограничения (как показано в примере из документации). Так Postgres непосредственно заботится о непересечении времени брони.

Никаких функций-приложений или дополнительной логики — все гарантируется на уровне СУБД. лавное помнить: для EXCLUDE нужно GiST-индекс (или btree_gist расширение, как выше) и соответствующие операторы (для диапазонов они есть по умолчанию).

DO-блоки: анонимный код на PL/pgSQL в запросе

И последнее, что полезно знать – в PostgreSQL существует команда DO, позволяющая выполнить кусок PL/pgSQL прямо из консоли (анонимный блок кода). Это похоже на создание функции на ходу, но без объявления. Например, хотите запустить скрипт по сбору статистики или выдать права всем таблицам – не нужно создавать функцию, можно написать:

DO $$ BEGIN     -- Пример: выводим сообщение через RAISE NOTICE     RAISE NOTICE 'Сейчас выполняю анонимный PL/pgSQL блок';     -- Здесь могла бы быть любая логика: цикл, динамический EXECUTE и т.д. END $$; 

Код просто выведет уведомление. Конечно, в других сценариях можно применять DO для более полезных дел – например, динамически изменять настройки или обрабатывать ошибки. Главное – запомнить: DO исполняет переданный код один раз. По сути, это быстрое средство запустить PL/pgSQL без создания permanent объекта.

Замечу, что язык по умолчанию – plpgsql, и он уже установлен в базе. Но если нужен другой, можно указать LANGUAGE; правда, тогда потребуется установить его через CREATE EXTENSION. Ещё важно: внутри DO нельзя выполнять транзакционные команды (COMMIT/ROLLBACK) если вы уже в транзакции. Но для большинства задач (генерация логов, сложные миграции и т.п.) он отлично подходит.

В заключение: это далеко не всё, что PostgreSQL умеет. Просто среди обычных вещей легко пропустить что-то полезное. А они делают жизнь гораздо удобнее и при этом надёжнее.

Когда система под нагрузкой начинает «задыхаться» или окружение не удаётся поднять с первого раза — это больно знакомо каждому, кто работает с PostgreSQL. Решения здесь не лежат на поверхности: нужен опыт и знание скрытых механизмов базы. Приходите на бесплатные уроки, которые помогут избежать типичных ловушек и научат управлять сложными сценариями:

• 3 сентября в 20:00 — PostgreSQL. Углубленный анализ производительности

• 17 сентября в 20:00 — PostgreSQL & Docker

Хотите понять, насколько вы разбираетесь в PostgreSQL? Пройдите вступительное тестирование — это поможет оценить уровень и понять, подойдёт ли вам Продвинутый курс по работе с базой данных PostgreSQL в различных средах.