citusdata / citusdata/citus

Add UDF that presents pg_stat_io for all nodes

Open
#7,272 0 comments 1 reaction 0 assignees View on GitHub
feature pg16_support
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

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

```sql
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);
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.