View cluster-wide tablet metadata and leadership - Yugabyte Db

The yb_tablet_metadata view provides a YSQL-accessible interface for fetching tablet distribution and leadership information across a YugabyteDB cluster.

While the yb_local_tablets view provides information about tablets on the local node, yb_tablet_metadata exposes tablet placement and replica roles cluster-wide, serving as the YSQL equivalent of the YCQL system.partitions table.

The yb_tablet_metadata view is useful for:

Identifying the location of all tablets for a specific table.
Determining the leader node for a specific tablet.
Identifying the tablet for a given tuple, in case of hash-sharded tables.

Note that the view returns tablet information for YSQL objects and the system transaction table only.

The following table describes the columns of the yb_tablet_metadata view.

Column	Type	Description
tablet_id	text	A unique identifier (UUID) representing the tablet.
oid	oid	The object identifier (OID) for the table/index that the tablet belongs to.
db_name	text	Name of the database this relation belongs to.
relname	text	Name of table/index whose data is stored on the tablet.
start_hash_code	int	Starting hash code (inclusive) for the tablet. (NULL for range-sharded tables.)
end_hash_code	int	Ending hash code (exclusive) for the tablet. (NULL for range-sharded tables.)
leader	text	IP address, port of the leader node for the tablet.
replicas	text[]	A list of replica IP addresses and port (includes leader) associated with the tablet.

Examples

Because this view is accessible via YSQL, run your examples using ysqlsh.

Select nodes for a particular table

To find all tablets, their leaders, and replicas for a specific table:

sql

SELECT * FROM yb_tablet_metadata WHERE db_name = 'yugabyte' AND relname = 'test_table';

output

+----------------------------------+-------+----------+-------------+-----------------+---------------+------------------+--------------------------------------------------------+
| tablet_id                        | oid   | db_name  | relname     | start_hash_code | end_hash_code | leader           | replicas                                               |
|----------------------------------+-------+----------+-------------+-----------------+---------------+------------------+--------------------------------------------------------|
| 3987b6a16bf94fbd92262744197350d7 | 16384 | yugabyte | test_table  | 0               | 10922         | 127.0.0.2:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| dd50b59c7dcb493680093ffa5b195634 | 16384 | yugabyte | test_table  | 10922           | 21845         | 127.0.0.1:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| bed5b3c3eee747e99622a4e21acf437a | 16384 | yugabyte | test_table  | 21845           | 32768         | 127.0.0.3:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| da4bad5faa9f448f890cce57c775cd94 | 16384 | yugabyte | test_table  | 32768           | 43690         | 127.0.0.2:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 52176c704c614846bbd80f481678519e | 16384 | yugabyte | test_table  | 43690           | 54613         | 127.0.0.1:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| ea252119fe774ba9bdc585504fae9398 | 16384 | yugabyte | test_table  | 54613           | 65536         | 127.0.0.3:5433   | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
+----------------------------------+-------+----------+-------------+-----------------+---------------+------------------+--------------------------------------------------------+

Join with yb_servers

You can join yb_tablet_metadata with yb_servers() to find infrastructure details (cloud, region, zone) for the tablet leader.

sql

SELECT
    ytm.tablet_id,
    ytm.oid,
    ytm.db_name,
    ytm.relname,
    ytm.start_hash_code,
    ytm.end_hash_code,
    ys.host,
    ys.port,
    ys.cloud,
    ys.region,
    ys.zone,
    ys.uuid as server_uuid,
    ys.universe_uuid
FROM yb_tablet_metadata ytm
JOIN yb_servers() ys
    ON split_part(ytm.leader, ':', 1) = ys.host
    AND split_part(ytm.leader, ':', 2)::int = ys.port
ORDER BY ytm.start_hash_code;

output

+----------------------------------+-------+----------+------------+-----------------+---------------+-----------+------+--------+-------------+-------+----------------------------------+--------------------------------------+
| tablet_id                        | oid   | db_name  | relname    | start_hash_code | end_hash_code | host      | port | cloud  | region      | zone  | server_uuid                      | universe_uuid                        |
|----------------------------------+-------+----------+------------+-----------------+---------------+-----------+------+--------+-------------+-------+----------------------------------+--------------------------------------+
| 3987b6a16bf94fbd92262744197350d7 | 16384 | yugabyte | test_table | 0               | 10922         | 127.0.0.2 | 5433 | cloud1 | datacenter1 | rack1 | 0e7b350cc6db42c3a5f0fde35352700f | 68a94218-f52d-428d-b95b-f98122d19035 |
| dd50b59c7dcb493680093ffa5b195634 | 16384 | yugabyte | test_table | 10922           | 21845         | 127.0.0.1 | 5433 | cloud1 | datacenter1 | rack1 | 228be93b9b164d8c99dc775e4acc805f | 68a94218-f52d-428d-b95b-f98122d19035 |
| bed5b3c3eee747e99622a4e21acf437a | 16384 | yugabyte | test_table | 21845           | 32768         | 127.0.0.3 | 5433 | cloud1 | datacenter1 | rack1 | ad618d063e2f4980bb3bc74a1a296565 | 68a94218-f52d-428d-b95b-f98122d19035 |
| da4bad5faa9f448f890cce57c775cd94 | 16384 | yugabyte | test_table | 32768           | 43690         | 127.0.0.2 | 5433 | cloud1 | datacenter1 | rack1 | 0e7b350cc6db42c3a5f0fde35352700f | 68a94218-f52d-428d-b95b-f98122d19035 |
| 52176c704c614846bbd80f481678519e | 16384 | yugabyte | test_table | 43690           | 54613         | 127.0.0.1 | 5433 | cloud1 | datacenter1 | rack1 | 228be93b9b164d8c99dc775e4acc805f | 68a94218-f52d-428d-b95b-f98122d19035 |
| ea252119fe774ba9bdc585504fae9398 | 16384 | yugabyte | test_table | 54613           | 65536         | 127.0.0.3 | 5433 | cloud1 | datacenter1 | rack1 | ad618d063e2f4980bb3bc74a1a296565 | 68a94218-f52d-428d-b95b-f98122d19035 |
+----------------------------------+-------+----------+------------+-----------------+---------------+-----------+------+--------+-------------+-------+----------------------------------+--------------------------------------+

Find a tablet for a given key

Use the yb_hash_code() function to find the tablet_id and leader node for a given key in hash-partioned tables.

Check the table structure:

sql

\d test_table

output

+--------+------------------+-----------+
| Column | Type             | Modifiers |
|--------+------------------+-----------+
| a      | text             |  not null |
| b      | integer          |           |
| c      | double precision |           |
+--------+------------------+-----------+
Indexes:
    "test_table_pkey" PRIMARY KEY, lsm (a HASH)

Note that a is the only primary key.

Get the hash code for a specific key:

sql

SELECT *, yb_hash_code(a) FROM test_table WHERE a = 'k1';

output

+----------+------+------+--------------+
| a        | b    | c    | yb_hash_code |
|----------+------+------+--------------+
| k1       | 2016 | 10.2 | 8000         |
+----------+------+------+--------------+

Query yb_tablet_metadata to find which tablet this key belongs to:

sql

SELECT
    yb_hash_code('k1'::text) as hash_code,
    t.tablet_id,
    t.start_hash_code,
    t.end_hash_code,
    t.leader
FROM yb_tablet_metadata t
WHERE t.relname = 'test_table'
  AND yb_hash_code('k1'::text) >= t.start_hash_code
  AND yb_hash_code('k1'::text) < t.end_hash_code;

output

+-----------+----------------------------------+-----------------+---------------+----------------+
| hash_code | tablet_id                        | start_hash_code | end_hash_code | leader         |
|-----------+----------------------------------+-----------------+---------------+----------------|
| 8000      | 6e60770b44ad49489ccb6949b8131c0e | 0               | 10922         | 127.0.0.3:5433 |
+-----------+----------------------------------+-----------------+---------------+----------------+

To find the tablet_id for all rows of the table, use the following query:

sql

SELECT
    tt.a,
    tt.b,
    tt.c,
    yb_hash_code(tt.a) as hash_code,
    ytm.tablet_id,
    ytm.start_hash_code,
    ytm.end_hash_code,
    ytm.leader
FROM test_table tt
JOIN yb_tablet_metadata ytm
  ON yb_hash_code(tt.a) >= ytm.start_hash_code
    AND yb_hash_code(tt.a) < ytm.end_hash_code
ORDER BY tt.a;

output

+----------------------------------+------+-------+-----------+----------------------------------+-----------------+---------------+----------------+
| a                                | b    | c     | hash_code | tablet_id                        | start_hash_code | end_hash_code | leader         |
|----------------------------------+------+-------+-----------+----------------------------------+-----------------+---------------+----------------|
| github.com/yugabyte/yugabyte-db/ | 2020 | 108.2 | 58057     | 78c89b1113cf4f3ea1cd439851804ce1 | 54613           | 65536         | 127.0.0.3:5433 |
| yb                               | 2020 | 108.2 | 5962      | 6e60770b44ad49489ccb6949b8131c0e | 0               | 10922         | 127.0.0.1:5433 |
| ybdb                             | 2020 | 108.2 | 44116     | 6df5e7b1746b4c2b97bada5d49e5083a | 43690           | 54613         | 127.0.0.2:5433 |
| yugabyte                         | 2016 | 10.2  | 8000      | 6e60770b44ad49489ccb6949b8131c0e | 0               | 10922         | 127.0.0.1:5433 |
| yugabytedb                       | 2016 | 10.2  | 30096     | 97b6f53d462b4c2381a19f6285e4779e | 21845           | 32768         | 127.0.0.2:5433 |
+----------------------------------+------+-------+-----------+----------------------------------+-----------------+---------------+----------------+

{{<tip title="Get hash codes in YCQL">}} To obtain hash codes in YCQL, you can use the partition_hash() function, which, similar to yb_hash_code(), also dumps hash codes. You can use the partition_hash() function in YCQL to link rows with their tablets. {{</tip>}}

Join with Active Session History

Join yb_active_session_history with yb_tablet_metadata on tablet_id to get the metadata of a tablet that is part of wait_event_aux.

sql

SELECT
     tablet_id,
     db_name,
     relname,
     start_hash_code,
     end_hash_code,
     wait_event,
     wait_event_component,
     wait_event_type,
     COUNT(*)
 FROM
     yb_active_session_history
 JOIN
     yb_tablet_metadata
 ON
     wait_event_aux = SUBSTRING(tablet_id, 1, 15)
 GROUP BY
     tablet_id,
     db_name,
     relname,
     start_hash_code,
     end_hash_code,
     wait_event,
     wait_event_component,
     wait_event_type
 ORDER BY
     relname,
     count DESC;

output

+----------------------------------+----------+------------------+-----------------+---------------+------------------------------------+----------------------+-----------------+-------+
| tablet_id                        | db_name  | relname          | start_hash_code | end_hash_code | wait_event                         | wait_event_component | wait_event_type | count |
|----------------------------------+----------+------------------+-----------------+---------------+------------------------------------+----------------------+-----------------+-------+
| dfc98983540e4b98b4e3b39041f65ab3 | yugabyte | idx_test_table_b | 32768           | 65536         | Raft_ApplyingEdits                 | TServer              | Cpu             | 3     |
| dc151ee1a59745d69201290307862b7d | yugabyte | idx_test_table_b | 0               | 32768         | Raft_ApplyingEdits                 | TServer              | Cpu             | 2     |
| dc151ee1a59745d69201290307862b7d | yugabyte | idx_test_table_b | 0               | 32768         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 2     |
| dc151ee1a59745d69201290307862b7d | yugabyte | idx_test_table_b | 0               | 32768         | OnCpu_Active                       | TServer              | Cpu             | 2     |
| dfc98983540e4b98b4e3b39041f65ab3 | yugabyte | idx_test_table_b | 32768           | 65536         | OnCpu_Active                       | TServer              | Cpu             | 2     |
| dfc98983540e4b98b4e3b39041f65ab3 | yugabyte | idx_test_table_b | 32768           | 65536         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 2     |
| 1f7aee0ceea445959129b23285756dad | yugabyte | idx_test_table_c | 0               | 32768         | Raft_ApplyingEdits                 | TServer              | Cpu             | 20    |
| b6eea846ecd9433f89ea7ccaadff8cec | yugabyte | idx_test_table_c | 32768           | 65536         | Raft_ApplyingEdits                 | TServer              | Cpu             | 19    |
| 1f7aee0ceea445959129b23285756dad | yugabyte | idx_test_table_c | 0               | 32768         | OnCpu_Active                       | TServer              | Cpu             | 19    |
| b6eea846ecd9433f89ea7ccaadff8cec | yugabyte | idx_test_table_c | 32768           | 65536         | OnCpu_Active                       | TServer              | Cpu             | 19    |
| 1f7aee0ceea445959129b23285756dad | yugabyte | idx_test_table_c | 0               | 32768         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 10    |
| b6eea846ecd9433f89ea7ccaadff8cec | yugabyte | idx_test_table_c | 32768           | 65536         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 10    |
| 1f7aee0ceea445959129b23285756dad | yugabyte | idx_test_table_c | 0               | 32768         | OnCpu_Passive                      | TServer              | Cpu             | 1     |
| b6eea846ecd9433f89ea7ccaadff8cec | yugabyte | idx_test_table_c | 32768           | 65536         | OnCpu_Passive                      | TServer              | Cpu             | 1     |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | OnCpu_Passive                      | TServer              | Cpu             | 83    |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | OnCpu_Passive                      | TServer              | Cpu             | 83    |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | OnCpu_Active                       | TServer              | Cpu             | 51    |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | OnCpu_Active                       | TServer              | Cpu             | 49    |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | Raft_ApplyingEdits                 | TServer              | Cpu             | 34    |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | Raft_ApplyingEdits                 | TServer              | Cpu             | 33    |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 15    |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | ConflictResolution_ResolveConficts | TServer              | RPCWait         | 14    |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | WAL_Append                         | TServer              | DiskIO          | 2     |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | Rpc_Done                           | TServer              | WaitOnCondition | 1     |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | Raft_WaitingForReplication         | TServer              | RPCWait         | 1     |
| c853133c0db445928ae1262c01238d2d | yugabyte | test_table       | 32768           | 65536         | RocksDB_NewIterator                | TServer              | DiskIO          | 1     |
| 99ed7472ccde4af787cb0bcfd4fd90bd | yugabyte | test_table       | 0               | 32768         | RocksDB_NewIterator                | TServer              | DiskIO          | 1     |
+----------------------------------+----------+------------------+-----------------+---------------+------------------------------------+----------------------+-----------------+-------+