Re: Need an idea to operate massive delete operation on big size table.

Unless this is not an option, it would be very quick to do the following:

- Create a new table without indexes, insert deduplicated rows into the
new table.
- Index the new table
- Rename the old table
- Rename the new table to the old table name

The works really fast but not an option if you have foreign keys on
the table.

On Wed, Jan 15, 2025 at 9:22 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> Sadly, Postgresql does not have (super-handy) "DISABLE" clauses.
>
> On Wed, Jan 15, 2025 at 10:12 AM youness bellasri <
> younessbellasri(at)gmail(dot)com> 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 <ronljohnsonjr(at)gmail(dot)com> a
>> écrit :
>>
>>> On Wed, Jan 15, 2025 at 9:54 AM Gambhir Singh <gambhir(dot)singh05(at)gmail(dot)com>
>>> 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!
>

--

*Joseph M. Day*(866) 404-6119 :P
(866) 397-3931 :F
(312) 371-3054 :C