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

[oxff]

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

[Kilor]

«Обновляйте по 3 колонки сразу, а не 3 раза по одной» © Капитан Очевидность.

Это ровно так. Но если бы я не сталкивался регулярно с большим количеством разработчиков, которые даже не задумываются об этом, статьи бы не было.
«Да я же просто из своего метода позвал пару чужих, а что они там одну и ту же запись обновляют каждый отдельно — что такого?», а потом приходится бороться с table bloat. Лучше уж сработать на упреждение с очевидной для кого-то статьей.

Тема пессимистических блокировок также не раскрыта

В этой статье и не предполагалось ее раскрывать, хотите — напишите свою, будет только больше хороших статей.

[Throwable]

1: I Like To Move It

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

[Kilor]

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 — это «физический» адрес кортежа в таблице

[faustxp]

Кстати.только узнал про такой update в postgres.
Вопрос по вашему пункту 1. Если бы все 3 апдейта происходили в одной транзакции, то что бы было в итоге?

[Kilor]

А вот выше как раз пример — все равно будет столько версий, сколько UPDATE.
Если повезет, и отработает HOT update, то хотя бы индексы не будут «пухнуть», но heap — все равно. Ну и накладные расходы на саму запись в heap/WAL никто не отменял.

[oxff]

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

[Kilor]

Те же начальные условия, обновляем в цикле:

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 его уже просто так не сократит, п.ч. последняя страница — непустая.

[Fragster]

В разделе "#3: А я милого узнаю по… блокировке" решение номер один такой себе совет.

[Kilor]

Почему, если…

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

[mkll]

А вот кстати вопрос — как второй запрос узнает, что обновлять нечего, если первый запрос еще не отработал и запись заблокирована?

[Kilor]

Как-то примерно так:


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

[Perlovich]

В тему по dead tuples.

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

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

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

[oxff]

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

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01)

[bolk]

С такой шпаргалкой по is distinct from голову всё себе продавишь. Там правило простое — NULL в таком выражении ведёт себя как обычное рядовое значение, без этих заморочек «не равен ничего, включая самого себя».