#true MPP
Секрет 38 (Случай сортировки без мастера)
Я тут подумал, GP - это про BigData. А где много данных, там не обходится без статистики,
которой до сих пор не уделялось должного внимания.
В продолжение находки
загорелся идеей проверить теорию автора на практике.

Вообще, медиана в отличие от среднего часто используется для более адекватной интерпретации реальности.
Напр., средняя зарплата трудоспособного населения РФ в 2024 может сильно отличаться от медианы за счет
наличия олигархата в хвосте распределения.
Иначе говоря, если из 76 млн человек у 50% зарплата не выше 50к RUR, то и медиана будет не выше 50к,
при средней 88к.

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

Теперь к сути.

Напомню, что расчет медианы требует сортировки множества.
Например, если у вас в таблице ряд натуральнях чисел от 1 до 1000, то ф-я

select percentile_disc(0.5) within group(order by n) as median from tst

вернет 500, а ее аналог percentile_cont(0.5) - 500.5 = среднее между верхней границей левого множества и нижней границей правого.

Я проверил, вычисление медианы действительно выполняется на мастере, создав сет из 100 млн чисел, взяв 100 000 раз первые 1000:

create table tst
as
select generate_series(1, 1000) n;

insert into tst
select a.*
from tst a
         join generate_series(1, 100000) on 1 = 1

Намеренно создал его DISTRIBUTED RANDOMLY, почему скажу позже

Расчет медианы дал план:

explain analyze 
select percentile_disc(0.5) within group(order by n) as median from tst
Aggregate  (cost=3743.50..3743.51 rows=1 width=4) (actual time=73917.174..73917.175 rows=1 loops=1)
  ->  Gather Motion 372:1  (slice1; segments: 372)  (cost=0.00..3741.00 rows=1000 width=4) (actual time=0.539..8346.740 rows=100001000 loops=1)
        ->  Seq Scan on tst  (cost=0.00..11.00 rows=3 width=4) (actual time=0.118..18.218 rows=270117 loops=1)
Planning time: 0.876 ms
  (slice0)    Executor memory: 34184K bytes.
"  (slice1)    Executor memory: 218K bytes avg x 372 workers, 218K bytes max (seg0)."
Memory used:  983040kB
Optimizer: Postgres query optimizer
Execution time: 74008.427 ms

median=500
Теперь рассчитаем локальные медианы и возьмем их среднее, т.к. на каждой ноде имеем случайную выборку из исходной :

select avg(median) from
(
select percentile_disc(0.5) within group(order by n) as median from dbg.tst
group by gp_segment_id) a;
Execution time: 2332.995 ms

median=500.4919354838709677

Ускорение в 31.2 раза, что есть круто, но до тех пор, пока мы не вспомним, что у нас 372 сегмента, и e;t не совсем ясно почему рез-т
оказался на порядок медленнее чем кратность уменьшенного объема данных на каждом узле.

Проверил расчет для множества из 1 млрд, размножив x10 существующий сет

insert into tst
select a.*
from tst a
         join generate_series(1, 10) on 1 = 1

Время расчета 15 m 12 s 474 ms и 28 s 268 ms для MPP подхода соовт-но.
Ускорение x 32.35.
Рез-ты также почти совпали : 500 и 500.4704301075268817 соотв-но. Кстати, для данного теста MPP все вычисленные
медианы на нодах лежат в границах от 499(min) до 501(max)

📌Видно, что расчет медианы хорошо скейлится при кратном увеличении выборки и коэф. ускорения стабильно держится в интервале 31 - 32
К слову, в нашем случае, объем случайной выборки на каждом шарде не превышает 0,27% (=1/372 узла), тогда как автор в оригинале рекомендует не опускаться ниже 2%,
но в его случае рез-том является медиана только подвыборки, я же ем всего слона по частям.

📌Остается, добавить, что в книге Канемана "Шум" приводится масса примеров когда "мудрость толпы" оказывается ближе к истине чем отдельные индивиды.
Думаю, дал еще один достойный пример для его книги, где - толпа - это множество узлов кластера, в котором погрешность оценки ответа на поставленный вопрос
каждым из них нивелируется при усреднении до почти безукоризненного уровня за такую смешную цену! -)

Всех с праздником и хорошего отдыха!

Translation
Secret 37 (Plan ahead during the right seasons)
Unexpectedly and by chance, a new secret has matured.
Let's talk about proactivity, because as we know, 90% of the costs of the IT product budget are due to design errors.

A former colleague at Lamoda, whose channel ("Инжиниринг Данных") is at the top of my list, wrote today something that I am sure many in IT resonate with, I quote, because it is the best I could say:

... I saw good terms about the type of work - deep work vs. shallow work.
Deep work is a deep immersion in work that allows you to focus on the problem, learn the necessary technologies and processes. Usually, such work requires at least several hours without distractions, and at the end of the process you get satisfaction. You are not so tired and do not burn out from such intense work.
**Shallow work**, __on the contrary, is work in fits and starts, when the context between tasks and projects often changes.
Even well-planned work in the deep work format can easily be turned into shallow work. It is enough to start responding to messages in the messenger from colleagues, managers, friends. Or participate in frequent meetings.
And so it turns out: it seems like a day has passed, but there is no result.

Due to my job, a lot of code passes through me, the performance of which I supervise, so I always have a bucket of Vaseline for developers at hand.
It so happened that today I just made another function that formats a lot of problematic code into a lot of tickets, and since now this is done by a script,
I added the metric of the execution time of a non-optimal SQL query to the ticket.

These were lyrics, now to the point.

Returning to Secret 1, probably many of us, due to our lazy nature, highlight the delta exactly as in the bad code branch:

SELECT *
FROM foo t
where t.key not in (select pk from big_tbl)

In deed, if the target table, i.e. big_tbl has partitions, say by the list by src_cd and the incoming set of source codes in the delta table foo is known, then the query

SELECT *
FROM foo t
where t.key not in (select pk from big_tbl where src_cd IN (<prt_list>)

will save you from unnecessary problems.

In our case, we got a 27 TB spill on a similar query from the framework, where there was no cutoff by partitions.
Although our query gets only the 1st line from the query above through LIMIT 1 to determine the presence of new data, this does not change the essence.
As expected, the query does not cause problems with the cutoff.

Just to be clear the refactoring option presented in the good code branch of Secret 1 remains working in any case and without partition prunning,
although it works noticeably longer than NOT IN with the cutoff, but almost without spills.

Looking deeply into delta loading topic, then probably someone uses such queries as (I exaggerate so as not to obscure the essence)

UPDATE big_tbl tgt
SET version_id = :1
FROM dlt
WHERE 1 = 1
AND (dlt.account_rk = tgt.account_rk)

We have hundreds of them on prod.
So, the funniest thing is that the time of such a query does not really depend on the number of rows in dlt, if it is small compared to the size of tgt, as it should be for delta.
And I noticed this thanks to the new metric that appeared today in automatically generated tickets.

Here is a real example of the same query above, where the number of rows in dlt differs by 2 orders of magnitude, but the update time changes insignificantly:
41k rows - 00:03:49
5.5 mln rows - 00:04:26
big_tbl size is 4 TB

And the reason? It's the same, the query doesn't have a src_cd cutoff, i.e. partition prunning which wouldn't be out of place for a partitioned big_tbl:

UPDATE big_tbl tgt
SET version_id = :1
FROM dlt
WHERE 1 = 1
AND (dlt.account_rk = tgt.account_rk)
AND tgt.src_cd IN (<prt_list>)

It remains to say that for UPDATE queries that have a partition cutoff, the time has been reduced from minutes to seconds.
📌
The moral is that if your target tables are not partitioned today, use a cutoff for the incoming set in your update queries if appropriate.
Tomorrow, they will be cut into sections, as was the case with our DW.

Translation
#true MPP
Secret 38 (A case of sorting without a master mode)
I believe, conceptually GP is about BigData. And where there is a lot of data, there is no way around statistics,
which has not been given due attention until now at the channel.
Continuing with the discovery
I got the idea to test the author's theory in practice.

In general, the median, unlike the average, is often used for a more adequate interpretation of reality.
For example, the average salary of the working population of the Russian Federation in 2024 may differ greatly from the median due to the presence of oligarchy at the tail
of the distribution.
In other words, if out of 76 million people, 50% have a salary no higher than 50k RUR, then the median will not be higher than 50k,
with an average of 88k.

Statistical formulas, including the median, are also widely used in AI algorithms:
-- Outlier processing:
-- in time series processing and filtering
-- In image processing algorithms for smoothing without strong blurring of edges
etc.

Now to the point.

Let me remind you that calculating the median requires sorting the set.
For example, if you have a series of natural numbers from 1 to 1000 in your table, then the function

select percentile_disc(0.5) within group(order by n) as median from tst

will return 500, and its analogue percentile_cont(0.5) - 500.5 = the average between the upper boundary of the left set and the lower boundary of the right set.

I checked that the median calculation is indeed performed on the master by creating a set of 100 million numbers, repeating 100,000 times the first 1000:

create table tst
as
select generate_series(1, 1000) n;

insert into tst
select a.*
from tst a
join generate_series(1, 100000) on 1 = 1

I intentionally created it DISTRIBUTED RANDOMLY, explain why later.

Calculating the median gave me the plan:

explain analyze
select percentile_disc(0.5) within group(order by n) as median from tst

Aggregate (cost=3743.50..3743.51 rows=1 width=4) (actual time=73917.174..73917.175 rows=1 loops=1)
-> Gather Motion 372:1 (slice1; segments: 372) (cost=0.00..3741.00 rows=1000 width=4) (actual time=0.539..8346.740 rows=100001000 loops=1)
-> Seq Scan on tst (cost=0.00..11.00 rows=3 width=4) (actual time=0.118..18.218 rows=270117 loops=1)
Planning time: 0.876 ms
(slice0) Executor memory: 34184K bytes.
" (slice1) Executor memory: 218K bytes avg x 372 workers, 218K bytes max (seg0)."
Memory used: 983040kB
Optimizer: Postgres query optimizer
Execution time: 74008.427 ms

median=500
Now we calculate local medians and take their average, because on each node we have a random sample from the original set:

select avg(median) from
(
select percentile_disc(0.5) within group(order by n) as median from dbg.tst
group by gp_segment_id) a;
Execution time: 2332.995 ms

median=500.4919354838709677

Acceleration by 31.2 times, which is cool, but until we remember that we have 372 segments, and it is not entirely clear why the result
turned out to be an order of magnitude slower than the reduced data volume on each node.

I checked the calculation for a set of 1 billion by multiplying the existing set x10

insert into tst
select a.*
from tst a
join generate_series(1, 10) on 1 = 1

Exec time is 15 m 12 s 474 ms and 28 s 268 ms for the MPP approach respectively.
Acceleration x 32.35.
The results also almost matched: 500 and 500.4704301075268817 respectively. By the way, for this MPP test, all calculated
medians on nodes lie within the range from 499(min) to 501(max)

📌It is clear that the median calculation scales well with a multiple increase in the sample and the acceleration is stably kept in the range of x31 - 32.
By the way, in our case, the volume of a random sample on each shard does not exceed 0.27% (= 1/372 nodes), while the author in the original recommends not to fall below 2%,
but in his case the result is the median of only a subsample, while I eat the whole elephant in parts.

📌It remains to add that in Kahneman's book "Noise: A Flaw in Human Judgment" there are many examples when the "wisdom of the crowd" turns out to be closer to the truth than individuals point of view.
I think I gave another worthy example for his book, where - the crowd is a set of cluster nodes, in which the error in estimating the answer to the question posed by each of them is leveled out when averaging to an almost flawless level for such a ridiculous price! -)

Happy weekend to everyone and have a good rest!

Друзья, Ровно через неделю, в Пт, 20 июн, поговорим тут в прямом эфире с автором о черных дырах базах данных, in-memory и не очень. Приглашенный гость Director, R&D Arenadata; founder & CTO Picodata - Константин Осипов. Начало в 19.00 мск

На заметку
#alter #table
В продолжение,
предположим, вы создали таблицу foo AO/(CO|RO) и собрали стат-ку, потом добавили столбец в который либо внесли данные либо оставили as is.
При любом запросе к таблице получим предупреждение:

Continuing with,
let's assume you created a table foo with AO/(CO|RO) option and collected statistics, then added a column and either partially updated it or left it as is.
For any query to the table, we will receive a warning

One or more columns in the following table(s) do not have statistics: foo

Возникает вопрос - как узнать какие колонки в табл-е без статы, ответ :
The question arises - how to find out what columns are in a table without statistics, the answer:

SELECT
    att.attname AS column_name,
    att.attnum AS column_id,
    rel.relname AS table_name
FROM
    pg_attribute att
JOIN
    pg_class rel ON att.attrelid = rel.oid
WHERE
    rel.relname = 'foo' -- имя вашей таблицы
    AND att.attnum > 0 -- исключая системные колонки
    AND NOT att.attisdropped -- исключая удалённые колонки
    AND NOT EXISTS (
        SELECT 1
        FROM pg_stats stats
        WHERE
            stats.schemaname = 'public' -- ваша схема
            AND stats.tablename = rel.relname
            AND stats.attname = att.attname
    )

Друзья, через 8 ч начинаем, ссылка на стрим появится под этим постом в 19.00

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

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

#GUC
На заметку
Часто встает вопрос при анализе деградации GP, вызванной динамическим кодом не твоих рук, была ли собрана статистика по измененной таблице, которая аффектит LA.
Глобально, для этого предусмотрен параметр (в show all)
Note
A question often arises when analyzing GP degradation caused by dynamic code made not by your hands, whether statistics were collected on the modified table that affects LA.
Globally, there is a parameter for this (in show all)

gp_autostats_mode_in_functions,none
gp_autostats_on_change_threshold,2147483647,Threshold for number of tuples added to table by CTAS or Insert-to  

Но есть нюанс, если вы знаете под каким пользаком был выполнен запрос (а вы почти всегда это знаете) И запрос вернул данные
But there is a nuance, if you know under what role the query was executed (and you almost always know this) AND the query returned data

select setconfig
from pg_db_role_setting
where setdatabase = <pg_database.oid> -- имя БД, на которую действует настройка
  and setrole in (select usesysid from pg_user where usename = 'lady_bird_user') -- имя роли (или юзера)

напр. вида
eg. kinda

{gp_autostats_mode=on_change,gp_autostats_mode_in_functions=on_change,gp_autostats_on_change_threshold=100000}

то это значит, если данные в таблицу записаны в рамках PL/pgSQL функции сбор статистики для транзакции от 100 000 новых строк гарантирован.
Соотв-но для созданных в таком режиме работы БД крохотных таблиц важно помнить, что стата собрана не будет, что может выйти боком, если табл-а сопрягается далее с массивными табл-ами, о чем писал неоднократно
this means that if the data is written to the table within the PL/pgSQL function, statistics for a transaction of 100,000 new rows is guaranteed.
Accordingly, for tiny tables created in this mode of operation of the database, it is important to remember that statistics will not be collected,
which can backfire if the table is further coupled with massive tables, which I have written about many times

В заключение этого удивительного разговора, свежего как озон после осеннего июньского дождя, оставлю wish-list того, что хотелось бы иметь в GP :
1) Поддержка Oracle хинта /*+ materialize */ - напр., часто в запрос надо прокинуть 2 константных DATE поля для
ограничения between по диапазону дат в join-е с партицированной табл-ой и для этого приходится этот диапазон явно сохранить в отдельную таблицу,
из которой вычитать в переменные, которые передать в запрос

2) Поддержка Oracle хинта /*+ RESULT_CACHE*/, чтобы рез-ты одного и того же запроса можно достать из кэша.

3) Поддержка Oracle опции ALTER TABLE ... SET UNUSED для мгновенного удаления колонки и очистка этого удаленного поля при вакуум

4) Есть удобный drop table if exists, но хорошо бы иметь create table if not exists (в PostgreSQL 17 это сделали)

5) Поправить баг в выдаче расхода RAM в JSON версии плана запроса explain. Сейчас он ограничен int4, значения выше 2^32-1 отображаются <0 (Аренадата уже фиксит )
Если в вас есть, что добавить - you are welcome!!!

#DATA#Фото дня#

Друзья, прошла аномально крупная японская свеча на покупку акций Аренадата ( тикер #DATA ).
Как бы ни развивалась история с обвинением Сергею Мацоцкому( основатель IBS, и, если верить СМИ, держатель крупного пакета акций #DATA )
по даче взятки, безапеляционно верю в компанию, в технологию MPP, продолжаю в нее инвестировать.

Секрет 39 ( Не перегружайте запрос лишними полями )
В продолжение wish-list, пример на проме не заставил себя ждать
Запрос

with cte as (
SELECT 
    a.report_dt,
    a.c1, ... , a.c41
   FROM foo a
     JOIN ( SELECT b.report_dt,
            b.agreement_rk,
            max(b.version_id) AS version_id
           FROM foo b
          GROUP BY b.report_dt, b.agreement_rk) c USING (report_dt, agreement_rk, version_id)
)
select 'total' as nm, '2999-12-31' as report_dt, count(*) from cte
union all
select 'q1' as nm, report_dt, count(*) from cte
where report_dt between '2025-01-01' and '2025-03-31'
group by 2
union all
select 'q2' as nm, report_dt, count(*) from cte
where report_dt between '2025-04-01' and '2025-06-30'
group by 2;

создал спилл 20 TB.

Почему меня зацепил этот запрос ?
По ряду причин.
Если исключить список лишних полей a.c1, ... , a.c41( которые разработчик видимо на автомате скопипастил из кода аксессора, который наш фреймворк использует для доступа к витринам), не влияющих на результат , спилл падает до 3.5 TB:

with cte as (
SELECT 
    a.report_dt
   FROM foo a
     JOIN ( SELECT b.report_dt,
            b.agreement_rk,
            max(b.version_id) AS version_id
           FROM foo b
          GROUP BY b.report_dt, b.agreement_rk) c USING (report_dt, agreement_rk, version_id)
)
<тот же набор сабтоталов из 3х селектов, что в исходном запросе>

Если оставить все поля, но выбрать из cte
либо

select 'q1' as nm, report_dt, count(*)
where report_dt between '2025-01-01' and '2025-03-31'
group by 2

либо

select 'total' as nm, '2999-12-31' as report_dt, count(*)

спилл падает до 2.8 TB и 2.6 TB соотв-но, т.е. с учетом погрешности нашего коллекторра спиллов, он одинаков.

Наверное, если бы была поддержка хинта MATERIALIZE как в Oracle, таких аномалий не было бы.

Действительно, если сохранить cte

with cte as (
SELECT 
    a.report_dt,
   FROM foo a
     JOIN ( SELECT b.report_dt,
            b.agreement_rk,
            max(b.version_id) AS version_id
           FROM foo b
          GROUP BY b.report_dt, b.agreement_rk) c USING (report_dt, agreement_rk)
)

в таблу AO/CO zstd1 (24 млрд туплов), то получим спилл данной операции CTAS 2.3 TB, выборка сабтоталов из которой спила уже не создает.

В исходном же варианте, планы запросов для
вариантов

 
select 'total' as nm, '2999-12-31' as report_dt, count(*) from cte

и

select 'total' as nm, '2999-12-31' as report_dt, count(*) from cte
union all
select 'q1' as nm, report_dt, count(*) from cte
where report_dt between '2025-01-01' and '2025-03-31'
group by 2

разительно отличаются.

Друзья, лето в самом разгаре и для тех кто еще думает у меня 2 хорошие новости. Во первых, несмотря на спад туристов на 70% в Анапе, на других ККК, как-то в Кабардинке и Дивноморском аншлаг, по мазуту норма, вода прогрелась до 25, самое то! А с открытием аэроопорта в Геленджике море стало еще доступнее. Во вторых, если кто-то пресытился морем или горами Кавказа И хочет отдохнуть семьей, компанией до 8 чел, то рекомендую классное, уникальное место на берегу р.Кубань, за качество отвечаю, ссылка ниже.

На заметку
А вы знали, что не каждая транзакция откатывается при ошибке ?
Если pl/pgsql функция абортнулась из-за нехватки места
schema's disk space quota exceeded with name : public
то таблицы, которые были ей созданы не откатываются, а остаются как будто транзакция корректно завершилась.
Note:
Did you know that not every transaction is rolled back on error?
If the pl/pgsql function aborted due to lack of space with error
"schema's disk space quota exceeded with name : public"
then the tables that were created by it are not rolled back, but remain as if the transaction had completed correctly.