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 1tY54L-003ChC-TR for pgsql-admin@arkaria.postgresql.org; Wed, 15 Jan 2025 15:12:34 +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 1tY54K-00Ct4G-Jn for pgsql-admin@arkaria.postgresql.org; Wed, 15 Jan 2025 15:12:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tY54K-00Ct48-4w for pgsql-admin@lists.postgresql.org; Wed, 15 Jan 2025 15:12:32 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tY54J-000YKO-1b for pgsql-admin@lists.postgresql.org; Wed, 15 Jan 2025 15:12:31 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-e4a6b978283so1741022276.0 for ; Wed, 15 Jan 2025 07:12:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736953950; x=1737558750; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Vsq+amFTzQVTDPHReRcccRlGf5s9da3BkxdL0l6jjCE=; b=ZN19sQS8Hf8oDRpcqvQEQ6+6dvk4CEtgwFljSxg0jn2v+/RRQEvATBNYY1WVGgKc1O 8Xz5f/OhWQDD+edLa0h8lQiXkaOVNDrywQZvsk6BpA1wD900qN1dLbNhv4X/YaG9YYWN JKpuGc/l07mgOQE8RcpdgQvfCF2Q4/sGyvZZRQMfK9BKGgbpCgazPJFqrWqreXYIL4Bx yZir6F6PxhI3ugwAIWrfwqJbpt0imibH7YiT7HXifbspLt+1VLz7r55iWwAqrd1vXaD9 Bd8Zy6x1heQ9mJG54s2A2qlhxtO6Vtd+paRYyr633CKSJv9J1mqRIhE1l1Glloccm7+q viuw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736953950; x=1737558750; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Vsq+amFTzQVTDPHReRcccRlGf5s9da3BkxdL0l6jjCE=; b=kqPWwJoak7p0nQxE5JIQ2LS/4uKn9Nbc5qjJUNgud4Jm2J1KEi8rx/aaNF8E67PoU+ YlkXtWMeK7Z0GW+4tundUqAh636p5nXlUIT9WnifvivqTU3jMBOjH6BzRXJquBqMrT1B IE4dJDoOEZGd7pYznnLzlIvqx07r4fgZW7G0YVBusesYG/6LqKpkYSojcMcuzpaWxJwA 8fnWWoXa9WOOEp2uRQg4bDcdhE5F+esnq5wgxw5vxynr0P0L+x7CCwGeeRiFEFbkwJnW cIRruJ499EFgpWcHKeSQmdP/9ZwJUsXoRgJp5oNzoSYHmbDKaf5sYlajWBxqz+wwEKT6 KXkg== X-Gm-Message-State: AOJu0YyF4Lqj6AlZbXwpOOkxWIovP4+0sJ9Zy5gEaRw9/aHwsv617C81 UkeuHc/VMLXgDXl1dbWN12aISlx6FyIcTsywPXz+5yinQND4wCf9aTsgRVrXGCeSlF3WY9T7i7c SU8s3TWmSO4SJNPYNYSR2xi356ps= X-Gm-Gg: ASbGncteBpH6c0wAQrgquopSnA4/IScT+LlQHe8YQ9mMAPGOEzeTYP+0b3C/iLDE6H7 qFv1ccd1wYni1pooL+emcgUx46omRR8DARm1agw== X-Google-Smtp-Source: AGHT+IHfDkorMYndz5aZOPx+o0YcndwTty1FQPzbq+Q/G5OSOPZTdiqiMPuo3kI7O48jBKbJF0Rr7o9O2HnNVw4Txvs= X-Received: by 2002:a05:690c:4c09:b0:6ee:7916:2fa3 with SMTP id 00721157ae682-6f6c9a61ebbmr25363507b3.2.1736953950261; Wed, 15 Jan 2025 07:12:30 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: youness bellasri Date: Wed, 15 Jan 2025 16:12:19 +0100 X-Gm-Features: AbW1kvZD-Ir9jYujYiPVoQXTt3kUOYbmgnGlgxxF_8FteGYiuLdA6NGrm65gIOM Message-ID: Subject: Re: Need an idea to operate massive delete operation on big size table. To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000003259a8062bc01be4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003259a8062bc01be4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 1. *Batch Deletion* Instead of deleting all records at once, break the operation into smaller batches. This reduces locking, transaction log growth, and the risk of timeouts. 2. *Use Indexes* Ensure that the columns used in the WHERE clause of the delete queries are indexed. This speeds up the identification of rows to delete. 3. *Disable Indexes and Constraints Temporarily* If the table has many indexes or constraints, disabling them during the delete operation can speed up the process. Re-enable them afterward. Le mer. 15 janv. 2025 =C3=A0 16:08, Ron Johnson a =C3=A9crit : > On Wed, Jan 15, 2025 at 9:54=E2=80=AFAM Gambhir Singh > wrote: > >> Hi, >> >> I received a request from a client to delete duplicate records from a >> table which is very large in size. >> >> Delete queries (~2 Billion) are provided via file, and we have to execut= e >> that file in DB. Last time it lasted for two days. I feel there must be >> another way to delete records in an efficient manner >> > > Maybe the delete "queries" are poorly written. Maybe there's no > supporting index. > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000003259a8062bc01be4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

1.=C2=A0Batch Deletion

Instead of deleting all records at once, break = the operation into smaller batches. This reduces locking, transaction log g= rowth, and the risk of timeouts.

2.=C2=A0Use= Indexes

Ensure that the columns used in= the=C2=A0WHERE=C2=A0clause of the delete queries are indexed. This = speeds up the identification of rows to delete.

3.= =C2=A0Disable Indexes and Constraints Temporarily

If the table has many indexes or constraints, disabling t= hem during the delete operation can speed up the process. Re-enable them af= terward.


Le=C2=A0mer. 15 janv. 2025 =C3=A0=C2=A016= :08, Ron Johnson <ronljohnson= jr@gmail.com> a =C3=A9crit=C2=A0:
On Wed, Jan 15, = 2025 at 9:54=E2=80=AFAM Gambhir Singh <gambhir.singh05@gmail.com> wrote:
Hi,

I received a request f= rom a client to delete duplicate records from a table which is very large i= n size.=C2=A0

Delete queries (~2 Billion) are provided via file, and= we have to execute that file in DB. Last time it lasted for two days. I fe= el there must be another way to delete records in an efficient manner
=

Maybe the delete=C2=A0"qu= eries" are poorly written.=C2=A0 Maybe there's no supporting index= .

--
Death to = <Redacted>, and butter sauce.
Don't boil me, I'm still al= ive.
<Redacted> lobster!
--0000000000003259a8062bc01be4--