Foreign Data Wrappers

I was recently setting up a demo to show off query logging features. Two common extensions, pg_stat_statements and pg_stat_monitor, store data locally. In the case of a distributed database, it is helpful to combine the query runtimes on all nodes.

YugabyteDB supports foreign data wrappers, so I decided to use this feature to combine query statistics from each of my three test nodes.

The libraries for the pg_stat_monitor extension are already installed, so the extension just needs to be created:

create extension postgres_fdw;

Then on one of the nodes, create a server for another node:

create server node2 foreign data wrapper postgres_fdw options (host '127.0.0.2', dbname 'moma_sql', port '5433');

Do a quick user mapping:

create user mapping for yugabyte server node2 OPTIONS (user 'yugabyte', password '');

Create a local schema to link to the foreign schema:

create schema from2;

And then insert that schema into the local schema:

import foreign schema public limit to (pg_stat_monitor) from server node2 into from2;

Now you can query both from both the local public.pg_stat_monitor and the remote from2.pg_stat_monitor from the single node.

If you’d like to see the full demo with provisioning, data imports, and various scenarios, check out the full repo https://github.com/dataindataout/xtest_ansible and the querytuning scenarios https://github.com/dataindataout/xtest_ansible/tree/main/scenarios/querytuning.