public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Need an idea to operate massive delete operation on big size table.
Date: Wed, 15 Jan 2025 10:22:21 -0500
Message-ID: <CANzqJaCkSt3MkaeVT4wXxLROx3RY4dsArvGTYfn-VX6JxVTBfw@mail.gmail.com> (raw)
In-Reply-To: <CAP44Ew=Bsk5VLZMDXV0ZbeuwZqBmh6XG4Tg2iKnFoyq_-koxvA@mail.gmail.com>
References: <CAHOGQfWPQhxoyQ5GEUEFwtdXan=krr5Ue0DDPSKJGpcW9Szi4Q@mail.gmail.com>
<CANzqJaDsDMzwf_YAN3_gXqdUji1GvJg-M4Bn-kW_UT5xy0HB-g@mail.gmail.com>
<CAP44Ew=Bsk5VLZMDXV0ZbeuwZqBmh6XG4Tg2iKnFoyq_-koxvA@mail.gmail.com>
Sadly, Postgresql does not have (super-handy) "DISABLE" clauses.
On Wed, Jan 15, 2025 at 10:12 AM youness bellasri <[email protected]>
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 à 16:08, Ron Johnson <[email protected]> a
> écrit :
>
>> On Wed, Jan 15, 2025 at 9:54 AM Gambhir Singh <[email protected]>
>> 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.
>>
>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Need an idea to operate massive delete operation on big size table.
In-Reply-To: <CANzqJaCkSt3MkaeVT4wXxLROx3RY4dsArvGTYfn-VX6JxVTBfw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox