MIME-Version: 1.0
In-Reply-To: 
 <CAG1YDPeH+FBFv9_w1ktPn3JyJNA1F8L_6qaMcRZZQsbBjXtxww@mail.gmail.com>
References: 
 <CAMAYy4LD9TDO6ohfZwZ7OWOfu3oUZKQxi1Y=qt=36C65DL4jOw@mail.gmail.com>
 <CAG1YDPeH+FBFv9_w1ktPn3JyJNA1F8L_6qaMcRZZQsbBjXtxww@mail.gmail.com>
From: Rick Otten <rottenwindfish@gmail.com>
Date: Thu, 6 Jul 2017 12:05:33 -0400
Message-ID: 
 <CAMAYy4L=-n=VzyMi6s6tg0PSJcR6mXH2D2K1vk526uLEZkC31A@mail.gmail.com>
Subject: Re: partitioning materialized views
To: Shaun Thomas <shaun.thomas@2ndquadrant.com>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="f403045e736a5820810553a84913"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

On Thu, Jul 6, 2017 at 11:25 AM, Shaun Thomas <shaun.thomas@2ndquadrant.com>
wrote:

> > I'm curious if I'm overlooking other possible architectures or tools
> that might make this simpler to manage.
>
> One of the issues with materialized views is that they are based on
> views... For a concurrent update, it essentially performs a looped
> merge, which can be pretty ugly. That's the price you pay to be
> non-blocking. For this particular setup, I'd actually recommend using
> something like pglogical to just maintain a live copy of the remote
> table or wait for Postgres 10's logical replication.


Unfortunately the foreign database is Hadoop.  (As A Service)


> If you _can't_ do
> that due to cloud restrictions, you'd actually be better off doing an
> atomic swap.
>
> CREATE MATERIALIZED VIEW y AS ...;
>
> BEGIN;
> ALTER MATERIALIZED VIEW x RENAME TO x_old;
> ALTER MATERIALIZED VIEW y RENAME TO x;
> DROP MATERIALIZED VIEW x_old;
> COMMIT;
>
> This is an interesting idea.  Thanks!  I'll ponder that one.


> You could still follow your partitioned plan if you don't want to
> update all of the data at once. Let's face it, 3-4 hours is still a
> ton of data transfer and calculation.
>
>
yup.

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

<div dir=3D"ltr"><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">=
On Thu, Jul 6, 2017 at 11:25 AM, Shaun Thomas <span dir=3D"ltr">&lt;<a href=
=3D"mailto:shaun.thomas@2ndquadrant.com" target=3D"_blank">shaun.thomas@2nd=
quadrant.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span =
class=3D"">&gt; I&#39;m curious if I&#39;m overlooking other possible archi=
tectures or tools that might make this simpler to manage.<br>
<br>
</span>One of the issues with materialized views is that they are based on<=
br>
views... For a concurrent update, it essentially performs a looped<br>
merge, which can be pretty ugly. That&#39;s the price you pay to be<br>
non-blocking. For this particular setup, I&#39;d actually recommend using<b=
r>
something like pglogical to just maintain a live copy of the remote<br>
table or wait for Postgres 10&#39;s logical replication. </blockquote><div>=
<br></div><div>Unfortunately the foreign database is Hadoop. =C2=A0(As A Se=
rvice)</div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=
If you _can&#39;t_ do<br>
that due to cloud restrictions, you&#39;d actually be better off doing an<b=
r>
atomic swap.<br>
<br>
CREATE MATERIALIZED VIEW y AS ...;<br>
<br>
BEGIN;<br>
ALTER MATERIALIZED VIEW x RENAME TO x_old;<br>
ALTER MATERIALIZED VIEW y RENAME TO x;<br>
DROP MATERIALIZED VIEW x_old;<br>
COMMIT;<br>
<br></blockquote><div>This is an interesting idea.=C2=A0 Thanks!=C2=A0 I=
9;ll ponder that one.</div><div><br></div><div>=C2=A0</div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pad=
ding-left:1ex">
You could still follow your partitioned plan if you don&#39;t want to<br>
update all of the data at once. Let&#39;s face it, 3-4 hours is still a<br>
ton of data transfer and calculation.<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br></font></span></blockquo=
te><div><br></div><div>yup.</div><div><br></div></div><br></div></div>

--f403045e736a5820810553a84913--