PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

For this recipe, we will continue to use the pg-primary and pg-report database servers. All queries should be performed by the postgres user in the pgbench database. Follow these steps to enforce better remote JOIN performance:

1. Create a view for the basis of the join on pg-primary:

CREATE OR REPLACE VIEW v_pgbench_accounts_self_join AS
SELECT a1.aid, a2.bid, a2.abalance
  FROM pgbench_accounts a1
  JOIN pgbench_accounts a2 USING (aid)
 ORDER BY a1.aid DESC; 

2. Grant access to bench_user on the new view on pg-primary:

GRANT SELECT ON v_pgbench_accounts_self_join 
   TO bench_user; 

3. Create a foreign table that references the view on pg-report:

CREATE FOREIGN TABLE pgbench_accounts_self ( aid INTEGER NOT NULL, bid INTEGER, abalance ...