System catalogs, also referred to as system tables or system views, are essential to the internal structure and management of the database, forming the foundation of YugabyteDB’s architecture.
YugabyteDB extends PostgreSQL’s system catalog, which serves as a centralized repository for storing metadata about the database. This includes information about tables, indexes, columns, constraints, functions, users, privileges, extensions, query statistics, and more. All system catalog tables and views are organized within the pg_catalog schema.
When you read from a system catalog table, the data that is returned is local the node where you ran the query. That means if you don’t know which node initiating a SQL statement (i.e. CREATE INDEX) then you’ll have to log into each node if you are looking for statistics on a long running index backfill via the pg_stat_progress_create_index system catalog table.
But if, like me, you are too lazy to log into each node, you can consolidate the data from a system catalog table from each nodes in your cluster to one node. We can do this with foreign data wrappers!
Example:
I have a three node cluster and and am connected to node 127.0.0.1.
yugabyte=# SELECT host FROM yb_servers() ORDER BY host;
host
-----------
127.0.0.1
127.0.0.2
127.0.0.3
(3 rows)
yugabyte=# SELECT inet_server_addr();
inet_server_addr
------------------
127.0.0.1
(1 row)
Let’s install the extension, and do some set up:
yugabyte=# CREATE SERVER server2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.2', dbname 'yugabyte', port '5433');
CREATE SERVER
yugabyte=# CREATE SERVER server3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.3', dbname 'yugabyte', port '5433');
CREATE SERVER
yugabyte=# CREATE USER MAPPING FOR yugabyte SERVER server2 OPTIONS (user 'yugabyte', password 'password');
CREATE USER MAPPING
yugabyte=# CREATE USER MAPPING FOR yugabyte SERVER server3 OPTIONS (user 'yugabyte', password 'password');
CREATE USER MAPPING
Now we are ready to create the foreign tables. For this example, we’ll be using the pg_stat_progress_create_index system catalog table.
yugabyte=# CREATE FOREIGN TABLE pg_stat_progress_create_index_server2 ( pid integer, datid oid, datname name, relid oid, index_relid oid, command text, phase text, lockers_total bigint, lockers_done bigint, current_locker_pid bigint, blocks_total bigint, blocks_done bigint, tuples_total bigint, tuples_done bigint, partitions_total bigint, partitions_done bigint) SERVER server2 OPTIONS (schema_name 'pg_catalog', table_name 'pg_stat_progress_create_index');
CREATE FOREIGN TABLE
yugabyte=# CREATE FOREIGN TABLE pg_stat_progress_create_index_server3 ( pid integer, datid oid, datname name, relid oid, index_relid oid, command text, phase text, lockers_total bigint, lockers_done bigint, current_locker_pid bigint, blocks_total bigint, blocks_done bigint, tuples_total bigint, tuples_done bigint, partitions_total bigint, partitions_done bigint) SERVER server3 OPTIONS (schema_name 'pg_catalog', table_name 'pg_stat_progress_create_index');
CREATE FOREIGN TABLE
To help make querying the consolidated data easier, let’s create a database view. In the view we’ll UNION the data from the local pg_stat_progress_create_index system table and thepg_stat_progress_create_index from the other two nodes in the cluster.
yugabyte=# CREATE VIEW pg_stat_progress_create_index_consolodated AS SELECT 'node1' node, * FROM pg_stat_progress_create_index UNION ALL SELECT 'node2' node, * FROM pg_stat_progress_create_index_server2 UNION ALL SELECT 'node3' node, * FROM pg_stat_progress_create_index_server3;
CREATE VIEW
Let’s see how this works with a table called TEST. We’ll insert some data then create an index inititated from a different node. While the index is backfilling, we should be able to get the current status of the backfill from our new view, even thought the CREATE INDEX statement was inititaed from a different node than we connected!
yugabyte=# CREATE TABLE test(c1 INT PRIMARY KEY, c2 INT);
CREATE TABLE
yugabyte=# INSERT INTO test SELECT g, g FROM generate_series(1, 5000000) g;
INSERT 0 5000000
yugabyte=# \! ysqlsh -h 127.0.0.3 -c "CREATE INDEX ON test(c2);" &
yugabyte=# SELECT * FROM pg_stat_progress_create_index_consolodated;
node | pid | datid | datname | relid | index_relid | command | phase | lockers_to
tal | lockers_done | current_locker_pid | blocks_total | blocks_done | tuples_total | tuples_done | partitions
_total | partitions_done
-------+------+-------+----------+-------+-------------+---------------------------+--------------+-----------
----+--------------+--------------------+--------------+-------------+--------------+-------------+-----------
-------+-----------------
node3 | 3549 | 13251 | yugabyte | 16402 | 0 | CREATE INDEX CONCURRENTLY | initializing |
| | | | | 0 | 0 |
0 | 0
(1 row)
Have Fun!
