From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joe Conway <mail@joeconway.com>
cc: "James Pang (chaolpan)" <chaolpan@cisco.com>,
        Jim Mlodgenski <jimmy76@gmail.com>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: alter table xxx set unlogged take long time
In-reply-to: <e1920272-57a6-7c5a-924d-be26065ad9ad@joeconway.com>
References: 
 <PH0PR11MB5191F1E9F55101897E453814D6949@PH0PR11MB5191.namprd11.prod.outlook.com>
 <CAB_5SRdsCDx8Mq974S=WxWBG+vSD7axZVtAoTvMvzQ+6ghffuA@mail.gmail.com>
 <PH0PR11MB51919A15ED33C356120145F7D6949@PH0PR11MB5191.namprd11.prod.outlook.com>
 <2240967.1658839372@sss.pgh.pa.us>
 <PH0PR11MB51917F811DDACDBC4B0D9AEAD6949@PH0PR11MB5191.namprd11.prod.outlook.com>
 <CAB_5SRf6JmebYvcVXmzRtpDB1e9+KsGHLJGznjmWzpg2LxcPAQ@mail.gmail.com>
 <PH0PR11MB51918C3648CF7FECAC90CABED6949@PH0PR11MB5191.namprd11.prod.outlook.com>
 <e1920272-57a6-7c5a-924d-be26065ad9ad@joeconway.com>
Comments: In-reply-to Joe Conway <mail@joeconway.com>
	message dated "Wed, 27 Jul 2022 10:35:02 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2883167.1658933183.1@sss.pgh.pa.us>
Date: Wed, 27 Jul 2022 10:46:23 -0400
Message-ID: <2883168.1658933183@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2883168.1658933183%40sss.pgh.pa.us>
Precedence: bulk

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:

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.

			regards, tom lane