pg_stat_activity from all servers in YugabyteDB

When you query the PostgreSQL performance views, like pg_stat_activity or pg_stat_statements in YugabyteDB, you see only the view from the current node you are connected to. If you want a global view of the cluster, you need to query all of them.

Here is how to create a global view using the PostgreSQL Foreign Data Wrapper.

Note that I prefix all objects I create with gv$, meaning "global view", (this is familiar to Oracle RAC users). The following scripts re-creates the objects by dropping them, so be careful if you already use the gv$ namespace. I use format() and \gexec as I described in Generate SQL Script in PostgreSQL. To be safe, I create all that in a gv$database

For each tablet server visible in yb_servers(), I create the FDW server and user mapping for my current user to be able to connect to it:

create database gv$;
\c gv$

create extension if not exists postgres_fdw;

select format('
 create server if not exists "gv$%1$s"
 foreign data wrapper postgres_fdw
 options (host %2$L, port %3$L, dbname %4$L)
 ', host, host, port, current_database()) from yb_servers();
\gexec

select format('
 drop user mapping if exists for admin
 server "gv$%1$s"
 ',host) from yb_servers();
\gexec

select format('
 create user mapping if not exists for current_user
 server "gv$%1$s"
 --options ( user %2$L, password %3$L ) 
 ',host, 'admin', 'bsky-social-axqcu-h6eed')
 from yb_servers();
\gexec

Here, as I'm connected as a superuser, I don't need to provide the password and this is why the options is in comment. If you are using this on YugabyteDB Managed, you need to provide the admin password.

I import pg_stat_activity and pg_stat_statements from each server into their own schema:

select format('
 drop schema if exists "gv$%1$s" cascade
 ',host) from yb_servers();
\gexec

select format('
 create schema if not exists "gv$%1$s"
 ',host) from yb_servers();
\gexec

select format('
 import foreign schema "pg_catalog"
 limit to ("pg_stat_activity","pg_stat_statements","pg_stat_database")
 from server "gv$%1$s" into "gv$%1$s"
 ', host) from yb_servers();
\gexec

You can list the views created: \d gv$*.*

For each one, I create a global view to concatenate them:

with views as (
select distinct foreign_table_name
from information_schema.foreign_tables t, yb_servers() s
where foreign_table_schema = format('gv$%1$s',s.host)
)
select format('drop view if exists "gv$%1$s"', foreign_table_name) from views
union all
select format('create or replace view "gv$%2$s" as %1$s',
 string_agg(
 format('
 select %2$L as gv$host, %3$L as gv$zone, %4$L as gv$region, %5$L as gv$cloud,
 * from "gv$%2$s".%1$I
 ', foreign_table_name, host, zone, region, cloud)
 ,' union all '), foreign_table_name
) from views, yb_servers() group by views.foreign_table_name ;
\gexec

That's all. Now I can query gv$pg_stat_activity and gv$pg_stat_statements which have an additional column gv$host,gv$zone, gv$region, gv$cloud to identify the server, zone, region and cloud provider.

For example:

select now()-query_start "start",state, substr(query, 1, 30),
 gv$host, gv$zone, datname, application_name, usename, client_hostname
from gv$pg_stat_activity where state is not null
order by now()-query_start
;

I also added pg_stat_databases where not all columns are relevant to YugabyteDB, but the sum of commits and rollbacks per cloud region and zone can be interesting:

 select sum(xact_commit) commits,sum(xact_rollback) rollbacks,
  gv$cloud, gv$region, gv$zone, gv$host
from gv$pg_stat_database
group by
 gv$cloud, gv$region, gv$zone, gv$host
order by 1;

YugabyteDB is elastic and resilient. When the nodes are added or removed, you should run the scripts above again. Note that this is a temporary solution and such views will probably be implemented in the future. The global statistics are also visible from REST API and User Interface (YugabyteDB Managed portal for the managed service, yugabyted UI for Open Source).