Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oH2Wj-0002lf-Gl for pgsql-performance@arkaria.postgresql.org; Thu, 28 Jul 2022 12:22:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oH2Wi-0000CP-95 for pgsql-performance@arkaria.postgresql.org; Thu, 28 Jul 2022 12:22:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oH2Wh-0000CF-L8 for pgsql-performance@lists.postgresql.org; Thu, 28 Jul 2022 12:22:03 +0000 Received: from mail-vk1-xa32.google.com ([2607:f8b0:4864:20::a32]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oH2Wd-0005fQ-3S for pgsql-performance@lists.postgresql.org; Thu, 28 Jul 2022 12:22:03 +0000 Received: by mail-vk1-xa32.google.com with SMTP id z9so757642vkb.9 for ; Thu, 28 Jul 2022 05:21:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; h=message-id:date:mime-version:user-agent:subject:content-language:to :cc:references:from:in-reply-to:content-transfer-encoding; bh=qfoKFUlJ5m1zvnY9/lqE87Z0Sxu72PWPrvl2nx5xAhQ=; b=CHGLKv5N7uqPKlSC73Wzg0qav12nvJ4MH5nxlpFu3Y822fhr/fDo1GE9QnhIgYJI6W zyRQxwyosRAVC6cLt9vL8FdwZXC8hMp+iCGCzXnYnxjkVQTfo6TMbYMYP1NFGrDBgf0X ZP84lO5C5Bygla6kXuxARk0fqEmdvbKeLGXvkzZ6YkwLSc4N1hpyip0/HKQRGpR2bghl DKWv6YjiwLxyXcZRMDWmzdDNq6NqailDTaZhsyyt6cYQCcKq5YybmCCRvuMy4cF2q8SW FoIQU0ZF6Dbr0WPF4aThaLsT4UPYkY12S7ZbCn1/WwBQz7UemyhgbYOrqbWMqXQIvfPH j+jA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:cc:references:from:in-reply-to :content-transfer-encoding; bh=qfoKFUlJ5m1zvnY9/lqE87Z0Sxu72PWPrvl2nx5xAhQ=; b=Nap2FpHFtOn8MFxDUncxKJzu6Tv7u5RRRtsYPunA4AG2DwK+QsBHzCUlPSdFLcnUQp DXfoIe5Vjlmljzn7tBxa0kay7Bp8IALwad2kjl6qHn5q73PauzHOOQzqIu403FfVMM8c Q1s4x3igVC9QPmwKnx3J7YGfUutCiwcTGWqVK//bLSBzeQXpRI3RMaynEGoi8x9/9YOR MPz/t9X+y8BUd3IwowrIjllrMKLAiNirRNxDoLZgybKTKvoyVJgAP7RdCeDdIT7E8qrz L3uKBPSuYVnoqmvDgQlIXK4i8wTFhNDvDmcoEACM5h/rcvu9b+gnTefS+jrq9jaMwm9O m/EA== X-Gm-Message-State: AJIora97HkoZuQ0hoz5bsut/1CG5Bvxw3qcskzV0hXcgUqeuYHE5d9mG IgBqY6043+wqStYSH25U+QUxGg== X-Google-Smtp-Source: AGRyM1uABlqe9MZoRu+qGEmSwMAMTDE9XWGAMbnhjKSqxWqtOaGiHyDXiqgYMb9N9/2CYOz9Jj2qLg== X-Received: by 2002:a05:6122:da8:b0:376:c611:9cdf with SMTP id bc40-20020a0561220da800b00376c6119cdfmr2271229vkb.36.1659010916437; Thu, 28 Jul 2022 05:21:56 -0700 (PDT) Received: from [192.168.4.41] (072-017-018-098.res.spectrum.com. [72.17.18.98]) by smtp.gmail.com with ESMTPSA id j14-20020ab0250e000000b00384cd1abe50sm88470uan.32.2022.07.28.05.21.55 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 28 Jul 2022 05:21:55 -0700 (PDT) Message-ID: <5482d675-5843-271d-cc3c-9df742249baf@joeconway.com> Date: Thu, 28 Jul 2022 08:21:54 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: alter table xxx set unlogged take long time Content-Language: en-US To: "James Pang (chaolpan)" , Tom Lane Cc: Jim Mlodgenski , "pgsql-performance@lists.postgresql.org" References: <2240967.1658839372@sss.pgh.pa.us> <2883168.1658933183@sss.pgh.pa.us> From: Joe Conway In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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