Fedkin is thinking: “Нагруженные счетчики на postgres Недавно смотрел доклад и наткнулся на…”

Нагруженные счетчики на postgres

Недавно смотрел доклад и наткнулся на интересный хак

Есть классическая таблица со счетчиками


create table counters (
  id int primary key,
  cnt bigint
);

И обновлениями по id


update counters set cnt = cnt + 1 where id = ...

Если нагрузка небольшая или размазана по счетчикам с разными id, все будет ок. При этом если начнется массовый поток апдейтов на небольшой набор каунтеров, будут интересные спецэффекты:

• как известно, update физически не обновляет строку, а создает ее новую версию из-за MVCC

• autovacuum, который должен чистить "мертвые" версии строк, может этого не делать по куче причин

• и постгрес, чтобы сделать очередной update по id, начинает вычитывать кучу версий строк в поисках "живой" версии (похожая ситуация описывалась в этом посте)

Как следствие — серьезная деградация производительности

—

Решение 1 — не заставлять делать постгрес, что он не должен делать

Решение 2 — подсказать постгресу, какую именно версию строки нужно прочитать

Добавляем к счетчикам колонку last_updated — таймстемп последнего обновления


create table counters (
  id int primary key,
  cnt bigint,
  last_updated timestamp
);

create index g1 on counters(id, last_updated);

Имея такое, мы можем попросить постгрес сделать апдейт по конкретному ctid (физическому местоположению строки), при этом внутренний селект будет выполняться быстро даже в случае серьезного блоата


update counters
set cnt = cnt + 1, last_updated = now()
  where ctid = (
    select ctid
    from counters
    where id = ...
    order by last_updated desc
    limit 1
  );

Важный момент — если между селектом и апдейтом ctid "последней" версии строки поменялся (кто-то конкуретно обновил счетчик), то наш апдейт ничего не поменяет. Поэтому важно этот запрос обернуть в блокировку по id

Получается как-то так


select pg_advisory_lock(...)

update counters
set cnt = cnt + 1, last_updated = now()
  where ctid = (
    select ctid
    from counters
    where id = ...
    order by last_updated desc
    limit 1
  );

select pg_advisory_unlock(...)

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

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