Citus Scaling Custom Aggregation

[Luuya920]

I have implemented custom aggregation in regular PostgreSQL, which is partially aggregates and is parallelized. It also runs on the whole table. Now, I am trying to use citus to distribute the table and run my aggregation. I was expecting I would be able to reduce the execution time by using citus. However, it seems that citus is making the execution time extensively slower because it is pulling all the rows from the worker nodes and performing aggregation on the coordinator node. Is it possible to make it partially aggregates on the worker nodes and merge the result on the coordinator node? I mean just like the special-case aggregates on the docs? Here is the overview of aggregation:

CREATE AGGREGATE avg_speed_optimized(double precision[]) (
SFUNC = avg_speed_transfn_optimized,
STYPE = internal,
FINALFUNC = avg_speed_finalfn_optimized,
FINALFUNC_EXTRA,
COMBINEFUNC = avg_speed_combinefn_optimized,
SERIALFUNC = avg_speed_serializefn,
DESERIALFUNC = avg_speed_deserializefn,
PARALLEL = SAFE
);

[JelteF]

Currently we special case certain aggregates in Citus to do partial aggregation on the workers and combine it on the coordinator. To to this in a generic way for custom/extension aggregates we need something like the solutions proposed in this thread: https://www.postgresql.org/message-id/flat/TY2PR01MB3835CB1D110362A3213E5F6795D22%40TY2PR01MB3835.jpnprd01.prod.outlook.com#0e0884b5eee22ae527ca29ffe79fcbf0

If you have thoughts on this please chime in there.

Also, what's special about your avg aggregate? Based on the name I assume it is faster than the one built into postgres? If so, it would probably be a good candidate to upstream to regular Postgres.

[Luuya920]

Thanks for the clarification. My avg aggregate just a variation of regular average function. It works when column has a array of numbers. It is suited for finding average when things are stored in buckets.