pg_restore, custom setting, and materialized view -- performance question

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: pg_restore, custom setting, and materialized view -- performance question
Date: 2021-06-14 16:50:48
Message-ID: CAOC+FBXeMpwP3kDFfsMTTBC7Q8siQk61P9mREji-8QNRy_kJKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

More of a general question here, but I notice when restoring a ~750GB
database and using very specific settings to help the performance, the
materialized view section at the end takes hours and hours, whereas
everything prior to that is very tidy and efficient, maybe two hours with 8
parallel jobs.

What I found then was doing pg_restore while excluding the materialized
view data at the end, then switching the database to more 'proper'
production settings, and then concurrently refreshing the materialized
views (the restore does not seem to want to try concurrently) is far more
efficient.

I was wondering if anyone else had this experience and if anyone else
executed a restore in such a fashion?

psql 13.3

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Browse pgsql-admin by date

  From Date Subject
Next Message Julien Rouhaud 2021-06-14 17:03:16 Re: Kill postgresql process
Previous Message abbas alizadeh 2021-06-14 16:32:40 Re: Kill postgresql process