Rebalance helpers

rebalance_helper.sql

-- shows the current data size on the TARGET nodes of online rebalance
-- update "nodename" in the query with the host address of the target node
-- or not have a nodename filter to see all the nodes
SELECT * FROM run_command_on_workers($$
 SELECT  pg_size_pretty(sum(pg_total_relation_size(srrelid)))
FROM pg_subscription_rel,pg_stat_subscription
WHERE srsubid = subid AND subname = 'citus_shard_move_subscription';$$) WHERE nodename = '10.0.0.21';


-- shows the current data size on the TARGET nodes of online rebalance
-- update "nodename" in the query with the host address of the target node
SELECT * FROM run_command_on_workers($$
 SELECT  pg_size_pretty(sum(pg_total_relation_size(srrelid)))
FROM pg_subscription_rel,pg_stat_subscription
WHERE srsubid = subid AND subname ilike '%citus%';$$) WHERE nodename = '10.0.0.21';


-- show the data size on the SOURCE nodes of the online rebalance
-- update "nodename" in the query with the host address of the source node
-- or not have a nodename filter to see all the nodes
SELECT * FROM run_command_on_workers($$
 SELECT pg_size_pretty(sum(pg_total_relation_size(prrelid))) 
FROM pg_publication_rel, pg_publication WHERE  pubname = 'citus_shard_move_publication';$$) 
 WHERE nodename = '10.0.0.21';


-- see the data size for all nodes
select run_command_on_workers($$select pg_size_pretty(pg_database_size('citus'))$$);


-- see the size of colocated shards of a given shard id
 SELECT pg_size_pretty(citus_shard_cost_by_disk_size(671189)::bigint);
 pg_size_pretty
----------------
 1013 GB
(1 row)


-- see the colocation group sizes of all shards
-- NOTE: if you have too many partitions/shards in the system
-- this query may take a while
WITH shard_sizes AS(
   SELECT shardid, result::bigint size FROM
   (SELECT (run_command_on_shards(logicalrelid::text,$cmd$SELECT pg_total_relation_size('%s')$cmd$)).*
   FROM pg_dist_partition pp where pp.partmethod='h')a
),
colocated_shard_sizes AS(
   SELECT colocationid, nodename, CASE WHEN  shard_group IS NULL THEN NULL ELSE shard_group[1] END shardid, group_size
   FROM
   (
       SELECT colocationid,nodename,array_agg(ps.shardid) shard_group ,sum(size) group_size
       from shard_sizes ss,pg_dist_shard ps, pg_dist_shard_placement psp,
       pg_dist_partition pp
       WHERE (ss.shardid=ps.shardid AND pp.logicalrelid=ps.logicalrelid AND psp.shardid=ps.shardid AND pp.partmethod='h')
       GROUP BY shardmaxvalue,shardminvalue,nodename,colocationid
   )a
) SELECT * FROM colocated_shard_sizes;