PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»

    Особенности работы внутренних механизмов PostgreSQL позволяют ему быть очень быстрым в одних ситуация и «не очень» в других. Сегодня остановимся на классическом примере конфликта между тем, как работает СУБД и тем, что делает с ней разработчик — UPDATE vs принципы MVCC.

    Кратко сюжет из отличной статьи:
    Когда строка изменяется командой UPDATE, фактически выполняются две операции: DELETE и INSERT. В текущей версии строки устанавливается xmax, равный номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает с значением xmax предыдущей версии.
    Через какое-то время после завершения этой транзакции старая или новая версии, в зависимости от COMMIT/ROOLBACK, будут признаны «мертвыми» (dead tuples) при проходе VACUUM по таблице и зачищены.



    Но это произойдет далеко не сразу, а вот проблемы с «мертвецами» можно нажить очень быстро — при многократном или массовом обновлении записей в большой таблице, а чуть позже столкнуться с ситуацией, что и VACUUM не сможет помочь.

    #1: I Like To Move It


    Допустим, ваш метод на бизнес-логике работает себе, и вдруг понимает, что надо бы обновить поле X в какой-то записи:

    UPDATE tbl SET X = <newX> WHERE pk = $1;

    Потом, по ходу выполнения, выясняет, что поле Y надо бы обновить тоже:

    UPDATE tbl SET Y = <newY> WHERE pk = $1;

    … а потом еще и Z — чего уж мелочиться-то?

    UPDATE tbl SET Z = <newZ> WHERE pk = $1;

    Сколько версий этой записи теперь имеем в базе? Ага, 4 штуки! Из них одна актуальная, а 3 должен будет прибрать за вами [auto]VACUUM.

    Не надо так! Используйте обновление всех полей за один запрос — почти всегда логику работы метода можно так изменить:

    UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

    #2: Use IS DISTINCT FROM, Luke!


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

    UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

    Примерно в таком виде запрос встречается достаточно часто и почти всегда не для заполнения пустого нового поля, а для коррекции каких-то ошибок в данных. При этом сама корректность уже существующих данных вообще не учитывается — а зря! То есть запись переписывается, даже если там лежало ровно то, что и хотелось — а зачем? Поправим:

    UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

    Многие не в курсе про существование такого замечательного оператора, поэтому вот шпаргалка по IS DISTINCT FROM и другим логическим операторам в помощь:

    … и немного про операции над сложными ROW()-выражениями:

    #3: А я милого узнаю по… блокировке


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

    UPDATE tbl SET processing = TRUE WHERE pk = $1;

    Даже если эти процессы предметно делают независимые друг от друга вещи, но в рамках одного ID, на этом запросе второй клиент «залочится», пока не закончится первая транзакция.

    Решение №1: задача сведена к предыдущей

    Просто снова добавим IS DISTINCT FROM:

    UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

    В таком виде второй запрос просто ничего не будет менять в базе, там и так уже «все как надо» — поэтому и блокировка не возникнет. Дальше факт «ненахождения» записи уже обрабатываем в прикладном алгоритме.

    Решение №2: advisory locks

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

    Решение №3: без[д]умные вызовы

    А вот точно-точно у вас должна происходить одновременная работа с одной и той же записью? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..
    Тензор
    Разработчик системы СБИС

    Комментарии 14

      0

      Статья ни о чём. "Обновляйте по 3 колонки сразу, а не 3 раза по одной" © Капитан Очевидность.
      А как насчёт HOT-updates и внутристраничной очистки? В этом случае ничего не распухает, сколько не обновляй.
      Тема пессимистических блокировок также не раскрыта, даже не упомянули рабочую лошадку SELECT FOR UPDATE SKIP LOCKED/NOWAIT.

        0
        «Обновляйте по 3 колонки сразу, а не 3 раза по одной» © Капитан Очевидность.
        Это ровно так. Но если бы я не сталкивался регулярно с большим количеством разработчиков, которые даже не задумываются об этом, статьи бы не было.
        «Да я же просто из своего метода позвал пару чужих, а что они там одну и ту же запись обновляют каждый отдельно — что такого?», а потом приходится бороться с table bloat. Лучше уж сработать на упреждение с очевидной для кого-то статьей.
        Тема пессимистических блокировок также не раскрыта
        В этой статье и не предполагалось ее раскрывать, хотите — напишите свою, будет только больше хороших статей.
          0
          1: I Like To Move It

          Верно ли то же самое, если все апдейты делаются в одной транзакции? То есть реально новая запись делается при каждом апдейте, или же делается одна финальная запись при commit-е?

            0
            CREATE TABLE t(v integer);
            INSERT INTO t VALUES(1);
            BEGIN;
              SELECT ctid FROM t;
              -- (0,1)
              UPDATE t SET v = v + 1;
              SELECT ctid FROM t;
              -- (0,2)
              UPDATE t SET v = v + 1;
              SELECT ctid FROM t;
              -- (0,3)
            COMMIT;
            ctid — это «физический» адрес кортежа в таблице
        0
        Кстати.только узнал про такой update в postgres.
        Вопрос по вашему пункту 1. Если бы все 3 апдейта происходили в одной транзакции, то что бы было в итоге?
          0
          А вот выше как раз пример — все равно будет столько версий, сколько UPDATE.
          Если повезет, и отработает HOT update, то хотя бы индексы не будут «пухнуть», но heap — все равно. Ну и накладные расходы на саму запись в heap/WAL никто не отменял.
            0

            Heap тоже не будет из-за внутристраничной очистки. Очередной update, обнаружив нехватку места в странице, выкинет старые версии и пометит указатели как dead.

              0
              Те же начальные условия, обновляем в цикле:
              DO $$
              DECLARE
                i integer;
                _ctid tid;
              BEGIN
                FOR i IN 1..1000 LOOP
                  UPDATE t SET v = v + 1 RETURNING ctid INTO _ctid;
                  RAISE NOTICE 'ctid: %', _ctid;
                END LOOP;
              END;
              $$ LANGUAGE plpgsql;

              NOTICE:  ctid: (0,2)
              NOTICE:  ctid: (0,3)
              NOTICE:  ctid: (0,4)
              ...
              NOTICE:  ctid: (4,95)
              NOTICE:  ctid: (4,96)
              NOTICE:  ctid: (4,97)
              

              Все-таки записи «уехали» на следующую страницу, так что heap «разбух». Хуже того, autovacuum его уже просто так не сократит, п.ч. последняя страница — непустая.
          0
          В разделе "#3: А я милого узнаю по… блокировке" решение номер один такой себе совет.
            0
            Почему, если…
            Дальше факт «ненахождения» записи уже обрабатываем в прикладном алгоритме.
              0
              А вот кстати вопрос — как второй запрос узнает, что обновлять нечего, если первый запрос еще не отработал и запись заблокирована?
                0
                Как-то примерно так:


                Достал из одной из старых презентаций. Тут речь про «ничего не делающий» DELETE, но с UPDATE ситуация такая же.
            +1
            В тему по dead tuples.

            В postgresql по дефолту стоит достаточно высокое значение для autovacuum_vacuum_scale_factor, равное 0.2 (т.е. vacuum будет вызван, когда мертвых записей будет 20% от всей таблице).

            Для многих проектов лучше уменьшить значение до 0.05 или даже до 0.02. Профит особенно хорошо будет заметен для таблиц, где вставки (inserts) происходят гораздо чаще, чем updates.
            Проблема даже не в том, что чистка 20% таблицы может занять много времени (но и это тоже). Само наличие большого количества dead tuples влияет на скорость запросов к таблице (select) и на то, как используются индексы.

            Но эти значения, конечно, лучше подбирать исходя из конкретных проектов/данных/таблиц.
              0

              А ещё можно изменять умолчания для конкретных больших таблиц:


              ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01)

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

            Самое читаемое