Does "wal_level=minimal" help reducing wal emitting a lot for COPY and CREATE INDEX? We plan to remove "set unlogged/log" , instead , just set "wal_level=minimal" ,then COPY data in parallel, then create index.
Thanks, James -----Original Message----- From: Joe Conway <m...@joeconway.com> Sent: Wednesday, July 27, 2022 11:02 PM To: Tom Lane <t...@sss.pgh.pa.us> Cc: James Pang (chaolpan) <chaol...@cisco.com>; Jim Mlodgenski <jimm...@gmail.com>; pgsql-performance@lists.postgresql.org Subject: Re: alter table xxx set unlogged take long time On 7/27/22 10:46, Tom Lane wrote: > Joe Conway <m...@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
