citus
citus copied to clipboard
Add UDF that presents pg_stat_io for all nodes
To present pg_stat_io from all the nodes in a distributed Citus cluster, we can add a UDF like the following query in the 12.1 blog post https://www.citusdata.com/blog/2023/09/22/adding-postgres-16-support-to-citus-12-1/#pg-stat-io
WITH workers_stat_io AS ( SELECT * FROM run_command_on_workers
($$ SELECT coalesce(to_jsonb (array_agg(worker_stat_io.*)), '[{}]'::jsonb)
FROM (SELECT backend_type, object, context, reads, writes, writebacks,
extends, hits, evictions, fsyncs FROM pg_stat_io
WHERE reads > 0 OR writes > 0) AS worker_stat_io; $$))
SELECT * FROM jsonb_to_recordset((
SELECT jsonb_agg(all_workers_stat_io_as_jsonb.workers_stat_io_as_jsonb)::jsonb
FROM ( SELECT jsonb_array_elements(result::jsonb)::jsonb ||
('{"worker_port":' || nodeport || '}')::jsonb
AS workers_stat_io_as_jsonb FROM workers_stat_io)
AS all_workers_stat_io_as_jsonb))
AS ( worker_port integer, backend_type text, context text, reads bigint, writes bigint,
writebacks bigint, extends bigint, hits bigint, evictions bigint, fsyncs bigint);