Deleting many rows without locking them

sql postgresql locking sql-delete
Björn Lindqvist picture Björn Lindqvist · Aug 6, 2010 · Viewed 10.9k times · Source

In PostgreSQL I have a query like the following which will delete 250k rows from a 1m row table:

DELETE FROM table WHERE key = 'needle';

The query takes over an hour to execute and during that time, the affected rows are locked for writing. That is not good because it means that a lot of update queries have to wait for the big delete query to complete (and then they will fail because the rows disappeared from under them but that is ok). I need a way to segment this big query into multiple parts so that they will cause the least interference with the update queries as possible. For example, if the delete query could be split up into chunks each with 1000 rows in them then the other update queries would at most have to wait for a delete query involving 1000 rows. 

DELETE FROM table WHERE key = 'needle' LIMIT 10000;

That query would work nicely, but alas it does not exist in postgres.

Answer

Frank Heikens picture Frank Heikens · Aug 6, 2010

Try a subselect and use a unique condition:

DELETE FROM 
  table 
WHERE 
  id IN (SELECT id FROM table WHERE key = 'needle' LIMIT 10000);

Related questions

How to detect query which holds the lock in Postgres?

I want to track mutual locks in postgres constantly. I came across Locks Monitoring article and tried to run the following query: SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More