pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: James Pang (chaolpan) <chaolpan@cisco.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim Mlodgenski <jimmy76@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: alter table xxx set unlogged take long time
Date: Thu, 28 Jul 2022 08:21:54 -0400
Message-ID: <5482d675-5843-271d-cc3c-9df742249baf@joeconway.com> (raw)
In-Reply-To: <PH0PR11MB51912E6C7BB2DAD565FCD980D6969@PH0PR11MB5191.namprd11.prod.outlook.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>
	<2883168.1658933183@sss.pgh.pa.us>
	<ffb85e8c-cf2d-0f5a-c805-ad66a5a23601@joeconway.com>
	<PH0PR11MB51912E6C7BB2DAD565FCD980D6969@PH0PR11MB5191.namprd11.prod.outlook.com>

On 7/28/22 03:47, James Pang (chaolpan) wrote:
> 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.

(Note - please don't top post on these lists)

Yes, wal_level = minimal is a big help in my experience if you can 
tolerate it.

Similarly synchronous_commit = off might help as long as you are 
prepared to reload some data in the event of a crash (which generally is 
true when bulk loading). As noted in the docs:

   This parameter can be changed at any time; the
   behavior for any one transaction is determined by
   the setting in effect when it commits. It is
   therefore possible, and useful, to have some
   transactions commit synchronously and others
   asynchronously.


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





view thread (14+ messages)

Message-ID: <5482d675-5843-271d-cc3c-9df742249baf@joeconway.com>
Permalink:  ../5482d675-5843-271d-cc3c-9df742249baf@joeconway.com/
Also on:    postgresql.org/message-id/5482d675-5843-271d-cc3c-9df742249baf@joeconway.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mail@joeconway.com, chaolpan@cisco.com, tgl@sss.pgh.pa.us, jimmy76@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: alter table xxx set unlogged take long time
  In-Reply-To: <5482d675-5843-271d-cc3c-9df742249baf@joeconway.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox