PostgreSQL High Availability Cookbook - Second 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, ...