I'm pondering approaches to partitioning large materialized views and was
hoping for some feedback and thoughts on it from the [perform] minds.
PostgreSQL 9.6.3 on Ubuntu 16.04 in the Google Cloud.
I have a foreign table with 250M or so rows and 50 or so columns, with a
UUID as the primary key. Queries to the foreign table have high latency.
(From several minutes to more than an hour to run)
If I create a materialized view of this FT, including indexes, it takes
about 3-4 hours.
If I refresh the materialized view concurrently, it takes 4-5 DAYS.
When I run "refresh materialized view concurrently", it takes about an hour
for it to download the 250M rows and load them onto the SSD tempspace. At
that point we flatline a single core, and run I/O on the main tablespace up
pretty high, and then stay that way until the refresh is complete.
In order to speed up the concurrent refreshes, I have it broken into 4
materialized views, manually partitioned (by date) with a "union all view"
in front of them. Refreshing the data which is changing regularly (new
data, in one of the partitions) doesn't require refreshing the entire data
set. This works fairly well, and I can refresh the most recent partition
in 1 - 2 hours (daily).
However, sometimes I have to reach back in time and refresh the deeper
partitions. This is taking 3 or more days to complete, even with the data
broken into 4 materialized views. This approache lets me refresh all of
the partitions at the same time, which uses more cores at the same time
(and more tempspace), [I'd like to use as much of my system resources as
possible to get the refresh to finish faster.] Unfortunately I am finding
I need to refresh the deeper data more and more often (at least once per
week), and my table growth is going to jump from adding 3-5M rows per day
to adding 10-20M rows per day over the next month or two. Waiting 3 or 4
days for the deeper data to be ready for consumption in PostgreSQL is no
longer acceptable to the business.
It doesn't look like partman supports partitioning materialized views. It
also doesn't look like PG 10's new partitioning features will work with
materialized views (although I haven't tried it yet). Citus DB also seems
to be unable to help in this scenario.
I could create new materialized views every time I need new data, and then
swap out the view that is in front of them. There are other objects in the
database which have dependencies on that view. In my experiments so far,
"create and replace" seems to let me get away with this as long as the
columns don't change.
Alternatively, I could come up with a new partitioning scheme that lets me
more selectively run "refresh concurrently", and run more of those at the
same time.
I was leaning towards this latter solution.
Suppose I make a separate materialized view for each month of data. At the
beginning of each month I would have to make a new materialized view, and
then add it into the "union all view" on the fly.
I would then need a "refresh all" script which refreshed as many of them
concurrently as I am willing to dedicate cores to. And I need some handy
ways to selectively refresh specific months when I know data for a
particular month or set of months changed.
So, I actually have 2 of these 250M row tables in the Foreign Database,
that I want to do this with. And maybe more coming soon?
I'm curious if I'm overlooking other possible architectures or tools that
might make this simpler to manage.
Similarly, could I construct the "union all view" in front of the
partitions to be partition aware so that the query planner doesn't try to
look in every one of the materialized views behind it to find the rows I
want? If I go with the monthly partition, I'll start with about 36
materialized views behind the main view.
