When working with YugabyteDB, it is often useful to see how tablets are distributed across nodes.
Two especially helpful questions are:
● How many tablets is each node the leader for?
● How many total replicas does each node host?
This can help you quickly spot:
● leader imbalance
● uneven replica placement
● hot nodes
● topology distribution across cloud / region / zone
The good news is that you can answer all of this with pure SQL using yb_tablet_metadata and yb_servers().
What the system views provide
yb_tablet_metadata includes tablet placement details such as:
●
leader→ the current tablet leader inhost:portformat●
replicas→ an array of all replica locations for the tablet
Example:
SELECT * FROM yb_tablet_metadata LIMIT 1;
Example output:
. tablet_id | oid | db_name | relname | start_hash_code | end_hash_code | leader | replicas
----------------------------------+-------+----------+---------------+-----------------+---------------+----------------+---------------------------------------------------
df98d5aa830649b196d9dca8922ce69d | 16480 | yugabyte | _test_numeric | 0 | 65536 | 127.0.0.1:5433 | {127.0.0.1:5433,127.0.0.2:5433,127.0.0.3:5433}
yb_servers() lets you map each node back to its topology:
SELECT * FROM yb_servers() LIMIT 1;
Example output:
. host | port | num_connections | node_type | cloud | region | zone | public_ip | uuid | universe_uuid
-----------+------+-----------------+-----------+-------+-----------+------------+-----------+------+--------------
127.0.0.2 | 5433 | 0 | primary | aws | us-east-2 | us-east-2a | 127.0.0.2 | ... | ...
Query: leader and replica counts per node
The following query shows both:
● leader_count = number of tablets where the node is leader
● replica_count = number of tablet replicas hosted on the node
It also joins back to yb_servers() so you can see cloud, region, and zone.
WITH replica_counts AS (
SELECT
replica,
COUNT(*) AS replica_count
FROM (
SELECT unnest(replicas) AS replica
FROM yb_tablet_metadata
) r
GROUP BY replica
),
leader_counts AS (
SELECT
leader,
COUNT(*) AS leader_count
FROM yb_tablet_metadata
GROUP BY leader
)
SELECT
s.host || ':' || s.port AS node,
s.cloud,
s.region,
s.zone,
COALESCE(l.leader_count, 0) AS leader_count,
COALESCE(r.replica_count, 0) AS replica_count
FROM yb_servers() s
LEFT JOIN leader_counts l
ON l.leader = s.host || ':' || s.port
LEFT JOIN replica_counts r
ON r.replica = s.host || ':' || s.port
ORDER BY leader_count DESC, replica_count DESC;
Why this works
For leaders, the logic is simple:
● each row in
yb_tablet_metadatahas a singleleader● so you can group directly by that value
For replicas, each row contains an array:
●
replicas = {node1,node2,node3}● so you first need to
unnest(replicas)before grouping
Using yb_servers() as the base table is helpful because it ensures every server appears in the output, even if it currently has zero leaders.
Example output
. node | cloud | region | zone | leader_count | replica_count
-------------------+-------+-----------+------------+--------------+---------------
127.0.0.1:5433 | aws | us-east-2 | us-east-2a | 128 | 256
127.0.0.2:5433 | aws | us-east-2 | us-east-2b | 126 | 256
127.0.0.3:5433 | aws | us-east-2 | us-east-2c | 130 | 256
What to look for
| Pattern | Meaning |
|---|---|
| Even replicas, uneven leaders | Leader imbalance → potential hot node 🔥 |
| Uneven replicas | Placement / RF issue 🚨 |
| Node with 0 leaders | Follower-only node 👀 |
| One node dominates leaders | Likely performance bottleneck ⚠️ |
In a healthy cluster, you would usually expect:
● replica counts to be fairly even
● leader counts to also be reasonably balanced
If replica counts are even but leader counts are skewed, that may indicate leader imbalance.
If one node has noticeably more leaders than the others, that node may receive more request traffic and do more work.
This quick query is a handy way to validate placement and leadership distribution without leaving SQL.
Final takeaway
If you want a fast SQL-only way to inspect tablet distribution in YugabyteDB, combining yb_tablet_metadata with yb_servers() gives you a very useful per-node summary.
It is a simple but powerful check for:
● tablet leadership balance
● replica distribution
● cloud / region / zone visibility
Have Fun!