> 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. 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; 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. -- Shaun M Thomas - 2ndQuadrant PostgreSQL Training, Services and Support shaun.tho...@2ndquadrant.com | www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
