MIME-Version: 1.0
From: Rick Otten <rottenwindfish@gmail.com>
Date: Thu, 6 Jul 2017 11:03:54 -0400
Message-ID: 
 <CAMAYy4LD9TDO6ohfZwZ7OWOfu3oUZKQxi1Y=qt=36C65DL4jOw@mail.gmail.com>
Subject: partitioning materialized views
To: "pgsql-performa." <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="f403045e736ade248d0553a76c02"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--f403045e736ade248d0553a76c02
Content-Type: text/plain; charset="UTF-8"

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.

--f403045e736ade248d0553a76c02
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I&#39;m pondering approaches to partitioning large materia=
lized views and was hoping for some feedback and thoughts on it from the [p=
erform] minds.<div><br></div><div>PostgreSQL 9.6.3 on Ubuntu 16.04 in the G=
oogle Cloud.</div><div><br></div><div>I have a foreign table with 250M or s=
o rows and 50 or so columns, with a UUID as the primary key.=C2=A0 Queries =
to the foreign table have high latency. =C2=A0(From several minutes to more=
 than an hour to run)</div><div><br></div><div>If I create a materialized v=
iew of this FT, including indexes, it takes about 3-4 hours. =C2=A0</div><d=
iv><br></div><div>If I refresh the materialized view concurrently, it takes=
 4-5 DAYS.</div><div><br></div><div>When I run &quot;refresh materialized v=
iew concurrently&quot;, it takes about an hour for it to download the 250M =
rows and load them onto the SSD tempspace. =C2=A0 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.</div><div><br></div><div>In o=
rder to speed up the concurrent refreshes, I have it broken into 4 material=
ized views, manually partitioned (by date) with a &quot;union all view&quot=
; in front of them.=C2=A0 Refreshing the data which is changing regularly (=
new data, in one of the partitions) doesn&#39;t require refreshing the enti=
re data set.=C2=A0 This works fairly well, and I can refresh the most recen=
t partition in 1 - 2 hours (daily).</div><div><br></div><div>However, somet=
imes I have to reach back in time and refresh the deeper partitions.=C2=A0 =
This is taking 3 or more days to complete, even with the data broken into 4=
 materialized views.=C2=A0 This approache lets me refresh all of the partit=
ions at the same time, which uses more cores at the same time (and more tem=
pspace), =C2=A0 [I&#39;d like to use as much of my system resources as poss=
ible to get the refresh to finish faster.] =C2=A0 Unfortunately I am findin=
g 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 t=
o adding 10-20M rows per day over the next month or two.=C2=A0 Waiting 3 or=
 4 days for the deeper data to be ready for consumption in PostgreSQL is no=
 longer acceptable to the business.</div><div><br></div><div>It doesn&#39;t=
 look like partman supports partitioning materialized views.=C2=A0 It also =
doesn&#39;t look like PG 10&#39;s new partitioning features will work with =
materialized views (although I haven&#39;t tried it yet). =C2=A0 Citus DB a=
lso seems to be unable to help in this scenario.</div><div><br></div><div>I=
 could create new materialized views every time I need new data, and then s=
wap out the view that is in front of them.=C2=A0 There are other objects in=
 the database which have dependencies on that view. In my experiments so fa=
r, &quot;create and replace&quot; seems to let me get away with this as lon=
g as the columns don&#39;t change.</div><div><br></div><div>Alternatively, =
I could come up with a new partitioning scheme that lets me more selectivel=
y run &quot;refresh concurrently&quot;, and run more of those at the same t=
ime.</div><div><br></div><div>I was leaning towards this latter solution.=
=C2=A0</div><div>=C2=A0</div><div>Suppose I make a separate materialized vi=
ew for each month of data.=C2=A0 At the beginning of each month I would hav=
e to make a new materialized view, and then add it into the &quot;union all=
 view&quot; on the fly.</div><div><br></div><div>I would then need a &quot;=
refresh all&quot; script which refreshed as many of them concurrently as I =
am willing to dedicate cores to.=C2=A0 And I need some handy ways to select=
ively refresh specific months when I know data for a particular month or se=
t of months changed.</div><div><br></div><div>So, I actually have 2 of thes=
e 250M row tables in the Foreign Database, that I want to do this with.=C2=
=A0 And maybe more coming soon?=C2=A0</div><div><br></div><div>I&#39;m curi=
ous if I&#39;m overlooking other possible architectures or tools that might=
 make this simpler to manage.</div><div><br></div><div><br></div><div>Simil=
arly, could I construct the &quot;union all view&quot; in front of the part=
itions to be partition aware so that the query planner doesn&#39;t try to l=
ook in every one of the materialized views behind it to find the rows I wan=
t? =C2=A0 If I go with the monthly partition, I&#39;ll start with about 36 =
materialized views behind the main view.<br></div><div><br></div><div><br><=
/div><div><br></div></div>

--f403045e736ade248d0553a76c02--