Re: alter table xxx set unlogged take long time

On 7/27/22 10:46, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>> Then (completely untested) I *think* you could create the "partition" 
>> initially as a free standing unlogged table, load it, index it, switch 
>> to logged, and then attach it to the partitioned table.
> 
> I'm still of the opinion that this plan to load the data unlogged
> and switch to logged later is a loser.  Sooner or later you have
> got to write the data to WAL, and this approach doesn't eliminate
> that cost.  What it does do is create one whole extra cycle of
> writing the data to disk and reading it back.  I don't think
> it's an oversight that no such thing is suggested in our standard
> tips for bulk-loading data:

Yeah, agreed. I was mostly responding to the OP desire to use unlogged 
and not taking a stance on that.

> https://www.postgresql.org/docs/current/populate.html
> 
> What perhaps *is* an oversight is that we don't suggest
> use of COPY FREEZE there.  AFAIK that doesn't reduce the initial
> data loading cost directly, but it would save overhead later.

Oh, yes, very good point.


-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com