PostgreSQL High Availability Cookbook - Second Edition

How to do it...

For this recipe, we will focus on the pg-report database server. All queries should be performed by the postgres user in the pgbench database. Follow these steps to create and use a materialized view:

1. Rename the pgbench_accounts foreign table with this SQL statement:

        ALTER FOREIGN TABLE pgbench_accounts
              RENAME TO remote_accounts;

2. Use this SQL statement to create a materialized view:

        CREATE MATERIALIZED VIEW pgbench_accounts AS
        SELECT *
          FROM remote_accounts
         WHERE bid = 5
          WITH DATA;

3. Add an index to pgbench_accounts to make it usable:

        CREATE INDEX idx_pgbench_accounts_aid
            ON pgbench_accounts (aid);

4. Execute this SQL statement to produce a simple query plan:

        EXPLAIN ANALYZE
         SELECT *
           FROM pgbench_accounts