Bruce Momjian Senior Database Architect Nov 7, 2019
You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from foreign data sources, like Nosql stores and other Postgres servers.
What you might not have considered is that materialized views and foreign data wrappers can be used together. Materialized views speed data access by summarizing data so it isn't necessary to query the base tables that are referenced by materialized views. Foreign data wrappers, because they are remote, can be slow to access. By combining the two features, you can get fast access to remote data.
Let's see this in action! First, let's set up the foreign table:
CREATE DATABASE fdw_test; \connect fdw_test; CREATE TABLE world (greeting TEXT); \connect test CREATE EXTENSION postgres_fdw; CREATE SERVER postgres_fdw_test FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'fdw_test'); CREATE USER MAPPING FOR PUBLIC SERVER postgres_fdw_test OPTIONS (password ''); CREATE FOREIGN TABLE other_world (greeting TEXT) SERVER postgres_fdw_test OPTIONS (table_name 'world'); \det List of foreign tables Schema | Table | Server --------+-------------+------------------- public | other_world | postgres_fdw_test
populate it with some data:
INSERT INTO other_world SELECT * FROM generate_series(1, 100000);
and create a materialized view on the foreign table:
CREATE MATERIALIZED VIEW mat_view (first_letter, count) AS SELECT left(greeting, 1), COUNT(*) FROM other_world GROUP BY left(greeting, 1);
Now we can compare select times for foreign tables and materialized views:
\timing SELECT left(greeting, 1) AS first_letter, COUNT(*) FROM other_world GROUP BY left(greeting, 1); first_letter | count --------------+------- 1 | 11112 2 | 11111 3 | 11111 4 | 11111 5 | 11111 6 | 11111 7 | 11111 8 | 11111 9 | 11111 Time: 354.571 ms SELECT * FROM mat_view; first_letter | count --------------+------- 1 | 11112 2 | 11111 3 | 11111 4 | 11111 5 | 11111 6 | 11111 7 | 11111 8 | 11111 9 | 11111 Time: 0.783 ms
The materialized view is much faster, but also a contrived example. It is interesting that refreshing the materialized view takes a similar time to selecting from the foreign table:
REFRESH MATERIALIZED VIEW mat_view; Time: 364.889 ms
The above output is from Postgres 9.6. Thanks to this improvement in Postgres 10:
Push aggregates to foreign data wrapper servers, where possible (Jeevan Chalke, Ashutosh Bapat)
This reduces the amount of data that must be passed from the foreign data wrapper server, and offloads aggregate computation from the requesting server. The postgres_fdw fdw is able to perform this optimization. There are also improvements in pushing down joins involving extensions.
Postgres 10 gets faster foreign table aggregate selects than in 9.6, but still slower than using materialized views:
SELECT left(greeting, 1) AS first_letter, COUNT(*) FROM other_world GROUP BY left(greeting, 1); first_letter | count --------------+------- 1 | 11112 2 | 11111 3 | 11111 4 | 11111 5 | 11111 6 | 11111 7 | 11111 8 | 11111 9 | 11111 Time: 55.052 ms
You don't even need to use aggregates in materialized views — you can just copy the contents of a remote table into a materialized view for faster access, and refresh the materialized view occasionally (though logical replication in Postgres 10 does this better):
CREATE MATERIALIZED VIEW mat_view2 AS SELECT * FROM other_world;
Now we can run a performance test on the foreign table and its local copy:
\o /dev/null SELECT * FROM other_world; Time: 317.428 ms SELECT * FROM mat_view2; Time: 34.861 ms
In summary, materialized views and foreign data wrappers are two features that work well together. Materialized views allow remote data to be cached locally, either entire tables or aggregate summarizations. The cache can be refreshed using refresh materialized view. Also, Postgres 10 speeds up aggregate queries on foreign tables.
This post originally appeared on Bruce's personal blog.
Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.