Deleting in batches from PostgreSQL in Ruby

PostgreSQL doesn't support LIMIT in DELETE queries. But it's a bad idea to delete huge numbers of records at a time if the operation will be more than a few seconds. So deleting in small batches can be done like this:

DELETE FROM mytable WHERE id = ANY(ARRAY(SELECT id from mytable WHERE myfield = 10 LIMIT 10))

To use this code from ActiveRecord in Ruby and get the number of affected rows:

rows_affected_count = ActiveRecord::Base.connection.execute(query).cmd_tuples

You can do this in a loop to keep the queries moving through the system without slow queries choking things up. When rows_affected_count is less than the size of the batch, you're done.