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 1tY5E8-003GJ3-K0 for pgsql-admin@arkaria.postgresql.org; Wed, 15 Jan 2025 15:22:41 +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 1tY5E5-00D0op-Uj for pgsql-admin@arkaria.postgresql.org; Wed, 15 Jan 2025 15:22:38 +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 1tY5E5-00D0jh-HG for pgsql-admin@lists.postgresql.org; Wed, 15 Jan 2025 15:22:38 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tY5E3-000ZRs-1K for pgsql-admin@lists.postgresql.org; Wed, 15 Jan 2025 15:22:37 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-2a01707db44so2119117fac.2 for ; Wed, 15 Jan 2025 07:22:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736954553; x=1737559353; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=DiHEsu/dO7FawHgmTCA8uKCM31faUd32X6Q4D59+bxM=; b=LYQyOg/13JzPh7o/p/8n/9ZrSstS22eE6tFxO6FwkvvZj7jH0OWI2A3VWFqDewlhJq 2J6cxF12I9EPr01jLBm5i/Pw5DM9J/Qpjifx8xRfO74Y692XDc5SPgF9yC8AnflDWOJ/ F4MaTeNDYUVsmzRtJ6aFBXDpnV53iYtEfdYEU9IdE9t0sw0G0QsMbUWJooqIbs90IcOC cQMba7cglhoFn/s5/sdx2jOmboYszhDmYt8igCi7pYnaQk5qz4BRaAyxypHhtGkNGvRx jVfUqeUSwrpT/t4wDw84DOG9HqNip4LbP7ZbXWZI2owl+rMajT/q4Dtkd/pbhgNK+u9X 2N4A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736954553; x=1737559353; h=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=DiHEsu/dO7FawHgmTCA8uKCM31faUd32X6Q4D59+bxM=; b=sdGgdXrefw2JgRX2p0p8oRhuNjyGt899gbFOBi99WeDCQo9VBE+krBP8S3dxGzYZLH dsjc9kJi91qDVStK4twIQY2FaNVpGhXsR8CNXGJl6UGU7SWfcNi+UKvOTQe0VAuUfFFJ 8m0lYsmaczdc2L/3z6+VkK7gwT4kgbOyU3rlEx50RtpesiIpQMQAVXgeV2/xZx92O7zI 6hpBv9euSDMM+pp0nv38qUuqlGSF4c+Wk5xfcHgS2H9ECYZM3P6XgZagvsY6KykMTGJz q3pkWerMmFvw2RkZLEt56YX6Q7rJ03lcJED62hqQPQ+DDVmp12j6C5TfM+3Qmup1UfWb W0vA== X-Gm-Message-State: AOJu0YzWnNJbYKV0ElF8WBknBbD+CaEZzkN3UyLO3xGe8XXoLZ0odHMD udZHUIZL5uBj5BVLzUMhzh1CHvqzs9zSB6AtM4wIQlLcmkzB1nTvx2X2MAZlNTmjfgtNfpkGBpn m8MUCTYmPkVkoYrjVHB5o+QmJE5d3ow== X-Gm-Gg: ASbGncvFA8S8HoFwm5sefbmqt81LTXgV1H3ARSUcg2dU9I0fdSZaco34b80D7FvlOXS e/XUIzqDbo3gL1g+xA2Q2RHhOUOFPAj8wWjZKTNhS/VZ6kAl6tVGC1rCybGL+D3GmnAIlqPJn X-Google-Smtp-Source: AGHT+IFByc2vRd+dRqq1SPQItpahS5TpKaN1bvj5pF2tgt1rYumx0QAgJ6/gWJA6us3SYawg6wIFA992oj7/kGmD7AU= X-Received: by 2002:a05:6870:ce03:b0:296:e10f:af14 with SMTP id 586e51a60fabf-2aa06a052dcmr17911609fac.39.1736954552991; Wed, 15 Jan 2025 07:22:32 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 15 Jan 2025 10:22:21 -0500 X-Gm-Features: AbW1kvY7GtEojI07o-JuuT9v5g75Vw07YKCLa56BJqdILMOcwQGRecJkhEQljbk Message-ID: Subject: Re: Need an idea to operate massive delete operation on big size table. To: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000001f48c0062bc03f0e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001f48c0062bc03f0e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Sadly, Postgresql does not have (super-handy) "DISABLE" clauses. On Wed, Jan 15, 2025 at 10:12=E2=80=AFAM youness bellasri wrote: > 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 >>> execute 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. >> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001f48c0062bc03f0e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sadly, Postgresql does not have (super-handy) "D= ISABLE" clauses.

On Wed, Jan 15, 2025 at 10:12=E2= =80=AFAM youness bellasri <= younessbellasri@gmail.com> wrote:

1.=C2=A0Batch Deletion

Instead of deleting al= l records at once, break the operation into smaller batches. This reduces l= ocking, transaction log growth, and the risk of timeouts.

2.=C2=A0Use Indexes

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

3.=C2=A0Disable Indexes and Constraints Tempor= arily

If the table has many indexes or c= onstraints, disabling them during the delete operation can speed up the pro= cess. Re-enable them afterward.


Le=C2=A0mer. 15 janv. 2025 =C3=A0=C2=A01= 6:08, Ron Johnson <ronljohnsonjr@gmail.com> a =C3=A9crit=C2=A0:

--
Death = to <Redacted>, and butter sauce.
Don't boil me, I'm still= alive.
<Redacted> lobster!
--0000000000001f48c0062bc03f0e--