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

From Mitu Verma
Subject delete is getting hung when there is a huge data in table
Date
Msg-id 84BC7AB0D621A74893EC9C9E151293B02268692B@ESESSMB207.ericsson.se
Whole thread Raw
Responses Re: delete is getting hung when there is a huge data in table  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: delete is getting hung when there is a huge data in table  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: delete is getting hung when there is a huge data in table  (Uwe Schroeder <uwe@oss4u.com>)
Re: delete is getting hung when there is a huge data in table  (Andomar <andomar@aule.net>)
Re: delete is getting hung when there is a huge data in table  (Fabio Ugo Venchiarutti <fabio@vuole.me>)
List pgsql-general
Hi,

I am facing an issue with the deletion of huge data.
We have a cronscript which is used to delete the data of last 3 months from one of the tables.
Data in the table is large (8872597 as you can see the count below) since it is from last 3 months.

fm_db_Server3=# select count(*) from audittraillogentry ;


  count
---------
8872597
(1 row)

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

Then we stopped the script,terminated the database sessions by using SELECT pg_terminate_backend(proc pid) and run the
followingcommand 

delete from audittraillogentry where intime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3') OR
outtime <= to_timestamp('2015-01-30 23:59:59.999', 'YYYY/MM/DD-HH24:MI:SS.FF3'); 
still this delete operation is not working and not a single row has been deleted from the table.

Now i have following questions -

1. If postgreSQL has some limitations for deletion of large data?
2. If i should run the vacumm, after stopping the cron script ? because probably to get the "smaller" table?
3. if dropping the indexes can help here? now sure.
4.if i should think about partitioning , if there is any limitation while delaing with large data in postgreSQL?

regards
Mitu

_____
___________________________________


pgsql-general by date:

Previous
From: Melvin Davidson
Date:
Subject: Re: plpgsql functions organisation
Next
From: Tom Lane
Date:
Subject: Re: delete is getting hung when there is a huge data in table