Таки зависит. Иногда реально единственный вариант - агрегировать в структуры данных в оперативке и периодически батчи в СУБД писать. Биллинг - хороший пример. Хотя бы потому что клиенты не обидятся, если у вас считалка почему-то жестко рухнет и минуту статистики потеряет - они от этого ничего не прогадают.
И да, насчёт блокировок, гонок и этого вот всего - на свете есть погромисты, которые умеют не косячить в таких задачах. А ещё на свете давно уже есть немало языков, которые многие потенциально проблемные места выявляют ещё на этапе сборки.

А kafka не очень подходит для ситуаций, когда у вас миллионы сообщений в секунду размером по 50 байт. Туда всё-таки лучше бо́льшими кусками писать, хотя бы килобайт в идеале. Вот и выходит что один хрен агрегировать нужно самому сначала.

Сейчас бы, вместо изучения sql, сесть и научиться велосипедить под самые популярные ORM с прицелом на то, чтобы запросы нормальные получались.

Ого, serializable в pg12.3 - не serializable.
http://jepsen.io/analyses/postgresql-12.3

Кто-нибудь знает примеры g2 и g2-item?

Я нашел только определения:
G2-item: Item Anti-dependency Cycles (write skew on disjoint read)

G2: Anti-Dependency Cycles (write skew on predicate read)

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

Почти на всех реальных тестах (тестах извлекающих данные из базы, а не вычисляющим числа Фиббоначи) pl/pgsql оказывался быстрее других встроенных языков включая pl/v8 (несмотря на JIT).
Хотя, возможно, именно при работе с массивами - это не так:
http://okbob.blogspot.com/2014/05/a-speed-of-pl-languages-for-atypical.html
Мне всегда было интересно увидеть реальный plpgsql код, который тормозит.
Я много раз думал насчёт JIT для plpgsql, но останавливало меня то, что на любых тестах в профиле я видел что угодно (GetSnapshotData, SPI_*,...) но только не интерпретатор plpgsql.
Так что был бы весьма признателен если бы вы мне прислали код вашей процедуры . А если ещё и сл схемой БД и каким-то генератором данных, чтобы можно было самому эту фукнцию запустить и попрофилить - было бы вообще идеально.

да у него там кастомная агрегатная функция, которая как не только мне показалась лишней, а внутри тихий ужас чего только стоит это
FOREACH i in array state.ids
       LOOP
           IF i = id THEN
               RETURN ROW(state.ids, state.accum);
           end if;
       END LOOP;
сейчас автор вроде бы убрал этот кусок, как и было предложено сменить на id=any(state.ids), может чего еще поправил
и теперь с его слов выполняется за 0.8 с.

вот примерный код агрегатной функции

create or replace function common.sum_value_transition(
   state   common.sum_value_state,
   val     numeric,
   unit    integer,
   id      integer
)returns common.sum_value_state AS $$
declare
   i integer;
   accum numeric;
BEGIN
   FOREACH i in array state.ids
       LOOP
           IF i = id THEN
               RETURN ROW(state.ids, state.accum);
           end if;
       END LOOP;

   IF jsonb_exists(state.accum, unit::text) THEN
       accum = jsonb_extract_path(state.accum, unit::text)::numeric + val;
   ELSE
       accum = val;
   end if;
   RETURN ROW(id || state.ids, state.accum || jsonb_build_object(unit::text, accum))::common.sum_value_state;
END;
$$ LANGUAGE plpgsql;

Понятно, начал читать сверху вниз и не дочитал до конца:(

В принципе, производительность сильно поднялась после замены foreach i in array ... на IF id = any(state.ids) ...
Теперь приемлемая, но все равно в несколько раз хуже, чем при этой же функции, написанной на С (правда, сильно оптимизированной - массив заменен b-деревом)

я вам вчера писал, вы не хотите слушать.
почему выросла производительность?
вы ваши неэффективные костыли заменили рабочим решением.
дальше также надо.
проблема ТОЛЬКО в вашем коде, а не "тормозах" pgplsql перед С.
я не удивлюсь, что оптимизированный код на pgplsql будет БЫСТРЕЕ, чем то, что вы написали на С.
В разработке я придерживаюсь инженерного, а не академического подхода.
Т.е. я тупее, чем те кто создавал средства, которые использую, значит их возможности надо использовать по максимуму.
Все просто.

Этот bug уже исправлен, и, соответственно, был добавлен isolation test для его воспроизведения — можете посмотреть там.

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

вам вчера ванга пытался пример привести, не зачем создавать массивы, и придумывать алгоритмы, которые готовы и эффективны
в субд, один из них я вам написал.
я не могу вам помочь, т.к. вы задачу не описали ПОЛНОСТЬЮ.
но мое мнение, вам тупо надо приготовить данные, которые вы храните в массиве через cte например,  и затем с агрегировать обычным sum.
т.е. в начале вы готовите данные, а затем обрабатываете. В вашем случае каша. Вы не даете пг помочь вам.

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

хз почему вас пугают вложенные селекты

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

Спасибо, вас не затруднит скинуть ссылку на комит?

> И да, насчёт блокировок, гонок и этого вот всего - на свете есть погромисты, которые умеют не косячить в таких задачах.

И на свете их на самом деле человек этак сто. ;) Но да, есть много "погромистов", которые почему-то думают, что умеют.