Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNJNT-004dqH-IX for pgsql-general@arkaria.postgresql.org; Sat, 22 Jul 2023 20:38:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qNJMS-00H6Yd-8o for pgsql-general@arkaria.postgresql.org; Sat, 22 Jul 2023 20:37:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qNJMR-00H6YV-Rx for pgsql-general@lists.postgresql.org; Sat, 22 Jul 2023 20:37:55 +0000 Received: from mail-qv1-xf33.google.com ([2607:f8b0:4864:20::f33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qNJMO-001Bqb-55 for pgsql-general@lists.postgresql.org; Sat, 22 Jul 2023 20:37:55 +0000 Received: by mail-qv1-xf33.google.com with SMTP id 6a1803df08f44-636274ce31eso22278096d6.1 for ; Sat, 22 Jul 2023 13:37:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1690058271; x=1690663071; h=to:references:message-id:cc:date:in-reply-to:from:subject :mime-version:content-transfer-encoding:from:to:cc:subject:date :message-id:reply-to; bh=a0zr7jBJOa+3ZLt5goaBvmyPUfN2c9qlr1UeVmrHRnA=; b=TC9US0GAqVgMa2IbD5CZ9D8jxWh7e7FTXLlmZcS4EXXviW1vwX9ZBd1U1zy3yrtSHt mNlqepiBUAttfy0+uGHG+KEWMtWpkpfdNEurCETp+Z3mNy3Cxal4oPFdfWZXIyPvr5Mz Z449pFddTN9m/qAyX3cPVaLP37NgIvyCahxzq1qNu0mvTyyEcLkZfM5orxm5r/uqPuz4 GvmViG1FDXTMTVP60+JXKElkcI5J/MTmEJmaFGXMp/prKOVU0Dvedx5r38tZS/aG7c4S dxurOn5BBN9zNmiFvgC+BiCchRrRVaBVm8cWPUJZtLtkkJRpBIasvBnZGshgBXEmIPjD EokQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1690058271; x=1690663071; h=to:references:message-id:cc:date:in-reply-to:from:subject :mime-version:content-transfer-encoding:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=a0zr7jBJOa+3ZLt5goaBvmyPUfN2c9qlr1UeVmrHRnA=; b=Tv5pw9/8wDkTDFPbfldBU5H0JlSLfIOV4m9o//AWieq1bkc3t10CLrxmu/1L3KtMsU gLK4JQ8FMvXOVkZHHeJE6Pa0yQeCqPZ6rtMZANnTANi+90MLNjhMHVEaRc5IFd8woK1l d4UIE/UPDCaiJ9g3ekX61xfaEmjEWzU8VzoPcSLrAGzkNL09IL4HN8mh05RPlbXlBK0P OlF1+Oo3GIw1O3zr1vdTtfpNznDaK6SEwIkdwAogn1/cYHcJ6Y2LJ7S3/FAyTeTbSsSP 4k5I9pL2avudzszvwfkMDsr3UFFs0sS5cuDvwsyfJaFAhhQeQWQgcUKswX58EKsc8JX9 hvAg== X-Gm-Message-State: ABy/qLa5hZnE+7sjKcq1idurV8IB11ae7fCrKkjqTS9OuVSZ9l98oqJk ctwMVZ8CYtVQFczeFY6JpMg= X-Google-Smtp-Source: APBJJlF9OfVG5AlzKD6FE7RpA86dHTf5n9mymRzWnUdbrhGKqK/eg7ZxdyD4ZHruHEoO39QTBPnBeg== X-Received: by 2002:a0c:be91:0:b0:635:feb0:843b with SMTP id n17-20020a0cbe91000000b00635feb0843bmr3358695qvi.51.1690058271405; Sat, 22 Jul 2023 13:37:51 -0700 (PDT) Received: from smtpclient.apple ([2600:380:183a:11e9:4191:9f14:bdef:ecc7]) by smtp.gmail.com with ESMTPSA id g14-20020a0caace000000b0063627a022b0sm2317886qvb.49.2023.07.22.13.37.50 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 22 Jul 2023 13:37:50 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-D6F3CEFE-53A3-42ED-9028-E8D9FD049292 Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (1.0) Subject: Re: Effects of dropping a large table From: Gus Spier In-Reply-To: Date: Sat, 22 Jul 2023 16:37:39 -0400 Cc: David Rowley , pgsql-general@lists.postgresql.org Message-Id: <430335C4-3803-4F88-9042-96BA65166043@gmail.com> References: To: Rob Sargent X-Mailer: iPhone Mail (20F75) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-D6F3CEFE-53A3-42ED-9028-E8D9FD049292 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Isn=E2=80=99t this a perfect opportunity to= use the TRUNCATE command to quickly remove the data? And follow up by delet= ing the now empty tables?
Regards,
Gus

Sent from my iPhone

On J= ul 19, 2023, at 7:33 PM, Rob Sargent <robjsargent@gmail.com> wrote:
=EF=BB=BF= =20 =20 =20
On 7/19/23 17:15, David Rowley wrote:
=
On Wed, 19 Jul 2023 at 07:41, R=
ob Sargent <robjsargent@gmail.com> wrote:
You might consider deleting p=
ortions of the table in separate (consecutive) batches (maybe 5% per delete)=
.  And then truncate table is not logged so that might be an alternative.
Can you explain why this would b=
e a useful thing to do?

It sounds to me like it would just create a load of needless WAL from
the deletes and the vacuum that cleans up the dead rows each of which
is more likely to cause lag problems on the replica servers, which the
OP is trying to avoid.

David
No, you're right.  I= was remembering problems with _deleting_ essentially all of a large table (with limited resources).  The drop might not have th= e same problem.  But aren't they logged/transactional and then in the WALs anyway.

=20
= --Apple-Mail-D6F3CEFE-53A3-42ED-9028-E8D9FD049292--