Re: delete is getting hung when there is a huge data in table - Mailing list pgsql-general

 > Now issue is that when this script for the deletion of data is
launched , it is taking more than 7 days and doing nothing i.e not a
single row has been deleted.

Deleting a large number of rows can take a long time.  Often it's
quicker to delete smaller chunks.  The LIMIT clause is not supported by
DELETE, so you need some kind of subquery.

We use something like:

do $_$declare
     num_rows bigint;
begin
     loop
         delete from YourTable where id in
             (select id from YourTable where id < 500 limit 100);
         get diagnostics num_rows = row_count;
         raise notice 'deleted % rows', num_rows;
         exit when num_rows = 0;
     end loop;
end;$_$;

This deletes rows with an id smaller than 500 in chunks of 100.

Kind regards,
Andomar