On Thu, Jul 6, 2017 at 11:25 AM, Shaun Thomas <shaun.tho...@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.
