Version de Citus et de PostgreSQL
SELECT version(), citus_version();
+--------------------------------------------------+---------------------+
| PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2) on ... | Citus 11.1.4 on ... |Workers
SELECT master_get_active_worker_nodes();
+-------------------------------+
| (docker-citus_worker2-1,5432) |
| (docker-citus_worker1-1,5432) |
| (docker-citus_worker3-1,5432) |Cette requête permet de vérifier si on est effectivement en distribué. En démarrant Citus sur un seul noeud, on aura un coordinateur (master) sans worker. Dans ce cas, les contraintes liées au caractère distribué sont allégées.
Noeuds actifs
SELECT * FROM pg_dist_node;
+---------+---------+-----------------------+----------+-----+------------------+
| nodeid | groupid | nodename | nodeport | ... | shouldhaveshards |
+---------+---------+-----------------------+----------+-----+------------------+
| 1 | 1 | docker-citus_worker-1 | 5432 | | true |
| 3 | 0 | localhost | 5432 | | false |Des noeuds peuvent être ajoutés ou retirés avec les fonction citus_add_node() et citus_remove_node().
SELECT citus_add_node('localhost', 5432, groupId => 0);
SELECT citus_remove_node('localhost', 5432);Sharding
Globalement
SELECT * from pg_dist_shard;
+--------------+---------+--------------+---------------+---------------+
| logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue |
+--------------+---------+--------------+---------------+---------------+
| sw_dist01 | 102104 | t | -2147483648 | -2013265921 |
| sw_dist01 | 102105 | t | -2013265920 | -1879048193 |
| sw_dist01 | 102106 | t | -1879048192 | -1744830465 |
| sw_dist01 | 102107 | t | -1744830464 | -1610612737 |
| ...Pour une table
SELECT * from pg_dist_shard where logicalrelid='sw_ref01'::regclass;La table citus_shards stocke aussi des données de distribution. Elle permet de retrouver facilement quelles tables sont distribuées.
SELECT distinct table_name::text, citus_table_type, colocation_id
FROM citus_shards
ORDER BY table_name::text;
+--------------+------------------+---------------+
| table_name | citus_table_type | colocation_id |
+--------------+------------------+---------------+
| sw_dist01 | distributed | 18 |
| sw_ref01 | reference | 19 |On peut aussi demander la situation pour une table.
SELECT *
FROM citus_shards
WHERE table_name='sw_dist01'::regclass;
+------------+---------+-------------------+------------------+---------------+-----------+----------+------------+
| table_name | shardid | shard_name | citus_table_type | colocation_id | nodename | nodeport | shard_size |
+------------+---------+-------------------+------------------+---------------+-----------+----------+------------+
| sw_dist01 | 102040 | sw_dist01_102040 | distributed | 2 | worker1-1 | 5432 | 0 |
| sw_dist01 | 102041 | sw_dist01_102041 | distributed | 2 | worker2-1 | 5432 | 0 |
| sw_dist01 | 102042 | sw_dist01_102042 | distributed | 2 | worker3-1 | 5432 | 0 |Par ailleurs, on peut demander avoir le shard d’une valeur de distribution dans le cluster. Et indirectement avoir le nombre de lignes par shard.
SELECT get_shard_id_for_distribution_column('sw_dist01', '00000000-0000-0000-0000-000000000000');
SELECT count(*), get_shard_id_for_distribution_column('sw_dist01', tenant_key) shard_id
FROM sw_dist01
GROUP BY shard_id;