02.09.2025 15:08
badcasedaily1 34 12000 Источник

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

Постгрес – не просто реляционная БД, а настоящий кладезь фич, о которых начинающий разработчик может и не догадываться. Всё началось с того, что 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. Решения здесь не лежат на поверхности: нужен опыт и знание скрытых механизмов базы. Приходите на бесплатные уроки, которые помогут избежать типичных ловушек и научат управлять сложными сценариями:

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

Комментарии (34)


  1. WaldemarsonTheForester
    02.09.2025 16:51
    #28787298

    Всё началось с того, что PostgreSQL изначально писался на С/C++ и всегда тянуло к расширению стандартного SQL набора возможностей.

    И звучит как-то кривовато, и совершенно непонятно, причем тут C/С++? Если бы писали на чем-то другом, то не тянуло бы?


  1. Writer4
    02.09.2025 16:51
    #28787300

    Всё это хорошо (без иронии), но тут возникает философский вопрос. Вроде как с такими трудами уходили с MSSQL, Oracle, и тут привязываемся опять к конкретной бд. Да, вроде как на горизонте не видно альтернативы, но я бы аккуратно подходил к использованию нестандартных возможностей.


    1. surly
      02.09.2025 16:51
      #28787852

      Причина, по которой уходили с Oracle и MSSQL, для Postgres неактуальна.


      1. romych2004
        02.09.2025 16:51
        #28791384

        Когда выбирали MS, Oracle - никто о текущем положении дел и подумать не мог. Уверен, что сейчас мы тоже много о чем и подумать не можем, что будет в будущем. В общем, я бы не зарекался.


    1. ahdenchik
      02.09.2025 16:51
      #28789466

      SQL - это как BASIC. Фактически стандарта нет, всё равно под каждую машину программу приходилось поправлять. С SQL всё аналогично.


      1. Writer4
        02.09.2025 16:51
        #28790198

        Да, тут согласен, по любому специфика используется, и тот же limit offset в pg это спасение по сравнению с оракловыми подзапросами . Вопросы по совсем экзотике.


  1. Akina
    02.09.2025 16:51
    #28787302

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

    А как будет выглядеть аналогичный фортель для MERGE?


  1. GerrAlt
    02.09.2025 16:51
    #28787314

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

    Если у вас была строка, у нее было xmin=100, xmax=0, вы ее обновили, у этой строки стало xmin=100, xmax=101, добавилась строка с xmin=101, xmax=0

    Если вы просто выставили строку, то у вас в таблице появилась строка xmin=101, xmax=0

    Ваша чудо-магия работает (если работает - я не проверял) на том что в случае обновления вам возвращают старую строку, только в ней xmax может быть !=0, а в таком случае и значения колонок в возвращаемой строке будут до обновления, вы уверены что все к этому готовы? В вашем объяснении я не нашел указания на это.


    1. Akina
      02.09.2025 16:51
      #28787664

      Если у вас была строка, у нее было xmin=100, xmax=0, вы ее обновили, у этой строки стало xmin=100, xmax=101, добавилась строка с xmin=101, xmax=0

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


      1. GerrAlt
        02.09.2025 16:51
        #28788690

        Что вы имеете ввиду под "обработать дважды"?

        Postgres работает как MVCC, у него внутри при редактировании всегда происходит закрытие старой строки простановкой xmax=номер_текущей_транзакции, и добавление новой строки с актуальными значениями и xmin=номер_текущей_транзакции, xmax=0.

        Отсюда вывод, если вы ожидаете в ответе по своему запросу строку у которой xmin !=0, то это должна быть старая версия строки, до редактирования.

        Где-то в моих рассуждениях ошибка?


        1. Akina
          02.09.2025 16:51
          #28788738

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

          WITH new_data AS (
    SELECT * FROM (VALUES
        (1, 'Ivan',  1000),
        (2, 'Alisa',  800),
        (1, 'Ivan',  1234) -- повторный дубликат по "id"
    ) 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;

          Постгресс в этом случае генерирует ошибку. Именно по причине MVCC. Кстати, с моей точки зрения это неправильно... ну или по крайней мере некорректно.


          1. GerrAlt
            02.09.2025 16:51
            #28792758

            Ок, давайте по порядку:

            1) я запустил ваш код из примера (поменял название колонки с name на first_name) и получил:

            у меня пустая таблица, но мне сказали что я не вставляю а обновляю - кажется что-то уже идёт не так.

            2) Если вы понимаете как работает MVCC подскажите пожалуйста, если у меня в таблице в Postgresql было 2 строки, я обновил одну из них (поменял значение в одной из колонок на другое допустимое), сколько после этого строчек в таблице?


            1. Akina
              02.09.2025 16:51
              #28793538

              у меня пустая таблица, но мне сказали что я не вставляю а обновляю - кажется что-то уже идёт не так.

              Жаль, вы не указали, в каком именно месте вам это сказали и кто именно. Если обратиться к статье, то там предполагается наличие записи с id=1 в таблице employees до запуска запроса - как того, что в статье, так и соответственно показанного мной.

              у меня в таблице в Postgresql было 2 строки, я обновил одну из них (поменял значение в одной из колонок на другое допустимое), сколько после этого строчек в таблице?

              Точно так же, две. Или вы спрашиваете про физическое хранилище данных? там до вакуума - три.


              1. GerrAlt
                02.09.2025 16:51
                #28795876

                Или вы спрашиваете про физическое хранилище данных? там до вакуума - три.

                Именно, их там 3(не обязательно до ваккуума - зависит от горизонта транзакций и возможности внутристраничной очистки), и они выглядят там так:

                • неизменявшаяся строчка, xmin=1, xmax=0

                • старая версия строчки, xmin=1, xmax=2

                • новая версия строчки, xmin=2, xmax=0

                Теперь смотрим на колонку в returning: xmax=0 as inserted. Какие из трех строчек дадут true в эту колонку? Кажется только старая версия строчки. Если так то это значит что остальные значения в колонках у записи имеющей inserted=true будут старые, до обновления (новые лежат в новой версии). Я где-то не прав?


      1. mayorovp
        02.09.2025 16:51
        #28789126

        Это возможно, потому что это буквально то как работает UPDATE (либо INSERT ON CONFLICT DO UPDATE) в Postgres.


        1. Akina
          02.09.2025 16:51
          #28789358

          А вот PostgreSQL с вами не согласен и генерирует ошибку

          ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

          HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

          https://dbfiddle.uk/Ey61jgND


          1. mayorovp
            02.09.2025 16:51
            #28789382

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

            Смотрите, всё работает: https://dbfiddle.uk/vbVlF7m5


            1. Akina
              02.09.2025 16:51
              #28793540

              Да, наверное... ну переклинило, бывает.


  1. ahdenchik
    02.09.2025 16:51
    #28788006

    SELECT DISTINCT ON

    Спасибо!

    Недавно полчаса на собесе потратил на вывод первого из группы, а оно вона как! И ведь знал когда-то, но забыл


    1. Ivan22
      02.09.2025 16:51
      #28788158

      Если знать оконные функции, эти все костыли с DISTINCT ON не нужны.

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


      1. Akina
        02.09.2025 16:51
        #28788742

        Угу... и именно что костыль - ибо сортировка финального набора далеко не всегда соответствует сортировке для отбора одной записи группы. И начинается заворачивание в подзапрос... забавно, что подавляющее большинство в этом случае не выносит подзапрос в CTE, по крайней мере поначалу.


        1. edo1h
          02.09.2025 16:51
          #28790452

          так cte — это вроде бы просто синтаксический сахар для подзапросов, нет?


          1. Akina
            02.09.2025 16:51
            #28791110

            Ну не совсем. Подзапрос трудно, к примеру, использовать несколько раз, придётся использовать несколько его копий. Не говоря уж о рекурсивных CTE, их в виде подзапроса далеко не всегда можно оформить, а в некоторых СУБД и вовсе никак.

            CTE скорее надо воспринимать как инлайновые представления.


      1. Dansoid
        02.09.2025 16:51
        #28792178

        Насколько мне известно, DISTINCT ON работает в разы быстрее, чем использование ROW_NUMBER. Поэтому важно учитывать возможности конкретной базы данных.


        1. Ivan22
          02.09.2025 16:51
          #28792220

          Откуда такой миф??? С чего это вдруг разница, да еще и в разу, если и там и там - одна единственная сортировка по колонкам указанным либо в ROW_NUMBER либо в ORDER BY в запросе с DISTINCT ON 


    1. Panaman
      02.09.2025 16:51
      #28793578

      Для такой задачи знаю три диалектонезависимых способа для выбора последней записи:

      1. Через подзапрос

         SELECT field1,
 		field2,
 		history_field -- поле, где хранится историчность
  FROM table1 t1
 WHERE t1.history_field  = (SELECT max(history_field)
   							  FROM table1
   							 WHERE field1 = t1.field1)
   AND field1 = 'some_value';

      2. Через оконные функции

        WITH cte AS (SELECT field1,
					field2,
					history_field,
					RANK() OVER (PARTITION BY field1 ORDER BY history_field DESC) AS rnk
			   FROM table1
			  WHERE 1=1
				AND field1 = 'some_value')		  
SELECT field1,
	   field2,
       history_field
  FROM cte
  WHERE rnk = 1;

      3. Через левый джойн к самой себе

      SELECT t1.field1,
	   t1.field2,
       t1.history_field
  FROM table1 t1
  LEFT JOIN table1 t2 ON t1.field1 = t2.field1
  					  AND t1.history_field > t2.history_field
 WHERE t2.field1 IS NULL
   AND t1.field1 = 'some_value'

      Теперь еще один знаю, но проприетарный для Postgres.

      Все эти способы в моем понимании тождественны, но на разных базах (похоже, в зависмиости от объема таблиц и индексов) показываают разные результаты по быстродействию - иногда быстрее один, иногда другой. Гуру SQL разъясните, какой способ считается каноничным и наиболее предпочтителен к использованию?


      1. Akina
        02.09.2025 16:51
        #28795646

        Вот чисто из общих соображений.

        Поскольку вы выбираете одну запись, у которой field1 = 'some_value', а не записи для всех значений группирующего поля, то первый запрос предпочтителен. Сортировка потребуется всем запросам. Но у второго - оверхед вычисления дополнительного поля, а третий очевидно потратит ещё больше ресурсов на ненужное связывание.

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

        А если нужно выбрать все группы по field1, то предпочтительнее будет аналог первого запроса, но с выносом подзапроса в CTE.

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


        1. Ivan22
          02.09.2025 16:51
          #28797462

          1. эти три запроса не эквивалентны, если у нас history_field не уникальна

            то первый и третий запросы не смогут вернуть одну запись, второй сможет, надо только RANK на row_number заменить

          2. по перформансу, если на history_field навесить индекс, первый запрос можно неплохо ускорить (особенно с фишкой замены max на order by с limit 1), третий будет nested loop (без сортировки) , но который скорее всего даже медленне сортировки изза условия t1.history_field > t2.history_field которое индексом не ускорить


          1. Akina
            02.09.2025 16:51
            #28798328

            если у нас history_field не уникальна

            Ну вообще-то чисто из логики это либо порядковый номер, либо штамп времени события, так что уникальность в пределах группы должна обеспечиваться ограничением UNIQUE (field1, history_field).


            1. Ivan22
              02.09.2025 16:51
              #28799562

              хорошая теория, жаль с реальной жизнью не совпадает.

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


              1. Akina
                02.09.2025 16:51
                #28800158

                В реальности полно случаев когда бывают полные дубликаты и по порядковым номерам, и по штампам времени, и по чему угодно.

                Ну так это говорит скорее об ошибке проектирования. Логика-то требует уникальности, а ограничения нет.


  1. vmalyutin
    02.09.2025 16:51
    #28798226

    Пошел тест PostgreSQL Advanced проходить. Бац, а там вопросы как из одного docker перенести файл в другой docker. Я не против таких вопросов, но блин, причем тут PostgreSQL??? Тогда б уж про COPY спрашивали. Файлик мне мой девопс перетащит. Самое интересное, что тест я прошел :) Как бы половина вопросов вообще не про PG, а я их прошел имея опыт ну самый начальный в девопс делах.


    1. ahdenchik
      02.09.2025 16:51
      #28800004

      Докер предназначен для того чтобы между запусками софт в его контейнерах не сохранял своё состояние.

      А вся суть БД - это, напротив, скрупулёзно точное сохранение состояний данных в ней.

      Таким образом, идея держать инстанс БД в докере - спорная


  1. vmalyutin
    02.09.2025 16:51
    #28798232

    xmax в проде??? Вы разве не понимаете, что системные поля это предмет для не контролируемых изменений!?